教你建立動態 MSSQL 資料庫表 (4) — 建立資料庫、資料表與初始資料

本系列第四部分，示範 VB.NET 建立資料庫、資料表並插入初始兩筆資料的完整程式碼。

建立資料庫

Private Sub CreateDBBtn_Click(...) Handles CreateDBBtn.Click
    conn = New SqlConnection(ConnectionString)
    If conn.State <> ConnectionState.Open Then conn.Open()
    Dim sql As String = "CREATE DATABASE MyDataBase ON PRIMARY " & _
        "(Name=MyDataBase_data, filename='D:\MyDataBase.mdf', size=3, maxsize=5, filegrowth=10%) " & _
        "LOG ON (Name=MyDataBase_log, filename='D:\MyDataBase.ldf', size=3, maxsize=20, filegrowth=1)"
    cmd = New SqlCommand(sql, conn)
    Try
        cmd.ExecuteNonQuery()
    Catch ae As SqlException
        MessageBox.Show(ae.Message.ToString())
    End Try
End Sub

建立資料表並插入初始資料

Private Sub CreateTableBtn_Click(...) Handles CreateTableBtn.Click
    conn = New SqlConnection(ConnectionString)
    If conn.State = ConnectionState.Open Then conn.Close()
    ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;"
    conn.ConnectionString = ConnectionString
    conn.Open()
    sql = "CREATE TABLE myTable " & _
          "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY, " & _
          "myName CHAR(50) NOT NULL, myAddress CHAR(255), myValues FLOAT)"
    cmd = New SqlCommand(sql, conn)
    Try
        cmd.ExecuteNonQuery()
        ' 插入第一筆
        sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " & _
              "VALUES (1001, '【孟憲會之精彩世界】之一', 'https://XML.sz.luohuedu.Net/', 100)"
        cmd = New SqlCommand(sql, conn)
        cmd.ExecuteNonQuery()
        ' 插入第二筆
        sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " & _
              "VALUES (1002, '【孟憲會之精彩世界】之二', 'https://www.erp800.com/net_lover/', 99)"
        cmd = New SqlCommand(sql, conn)
        cmd.ExecuteNonQuery()
    Catch ae As SqlException
        MessageBox.Show(ae.Message.ToString())
    End Try
End Sub

連線字串在建立資料表前切換至新建的 MyDataBase。後續部分將示範表單介面佈局與查詢功能。