教你建立動態 MSSQL 資料庫表 (6) — 查看資料、預存程序、檢視表

本系列第六部分，示範 VB.NET 程式碼查看資料表資料、預存程序與檢視表內容，並封裝通用 SQL 執行方法。

查看資料表資料

Private Sub btnViewData_Click(...) Handles btnViewData.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()
    Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)
    Dim ds As New DataSet("myTable")
    da.Fill(ds, "myTable")
    DataGrid1.DataSource = ds.Tables("myTable").DefaultView
End Sub

查看預存程序結果

Private Sub btnViewSP_Click(...) Handles btnViewSP.Click
    ' ... 連線設定同前 ...
    Dim da As New SqlDataAdapter("myProc", conn)
    Dim ds As New DataSet("SP")
    da.Fill(ds, "SP")
    DataGrid1.DataSource = ds.DefaultViewManager
End Sub

查看檢視表

Private Sub btnViewView_Click(...) Handles btnViewView.Click
    ' ... 連線設定同前 ...
    Dim da As New SqlDataAdapter("SELECT * FROM myView", conn)
    Dim ds As New DataSet()
    da.Fill(ds)
    DataGrid1.DataSource = ds.DefaultViewManager
End Sub

通用 SQL 執行封裝

Private Sub ExecuteSQLStmt(ByVal sql As String)
    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()
    cmd = New SqlCommand(sql, conn)
    Try
        cmd.ExecuteNonQuery()
    Catch ae As SqlException
        MessageBox.Show(ae.Message.ToString())
    End Try
End Sub

此方法供前幾部分建立資料庫、資料表、預存程序、檢視表、索引、規則、修改表、刪除表等 DDL/DML 操作呼叫。