關於在.NET裡的DataSet經過幾天的使用，稍微有了一些簡單的心得，DataSet裡包含有許多DataTable，這些DataTable彼此還可以透過DataRelation物件來建立表格的關聯性，加上有別於以往的Recordset必須持續連線的狀態，DataSet採用離線運作的模式，不會影響到Database的負擔。 而其中各種不同的元件關係如下圖。 基本上的觀念先釐清就方便了。
'--------------------------------------分格線---------------------------------------'
'--------------------------------------連結法一-------------------------------------'
Imports System.Data.SqlClient
Private cnn As New SqlConnection
Private cmd As New SqlCommand
Private dr As SqlDataReader
cnn.ConnectionString = "Data Source=G_HISBKS;Initial Catalog=XXX;User ID=XXX"
cnn.Open()
cmd.Connection = cnn
cmd.CommandText = "下SQL語法"
dr = cmd.ExecuteReader
while dr.Read()
txb_test.Text = dr("欄位名稱").ToString
end while
cmd.Dispose()
cnn.Close()
'--------------------------------------分格線---------------------------------------'
'--------------------------------------連結法二-------------------------------------'
Imports System.Data.SqlClient
Private cnn As New SqlConnection
Private cmd As New SqlCommand
cnn.ConnectionString = "Data Source=G_HISBKS;Initial Catalog=XXX;User ID=XXX"
cnn.Open()
cmd.Connection = cnn
cmd.CommandText = "下SQL語法"
cmd.ExecuteNonQuery()
cmd.Dispose()
cnn.Close()
'--------------------------------------分格線---------------------------------------'
'--------------------------------------連結法三-------------------------------------'
Imports System.Data.SqlClient
Private cnn As New SqlConnection
Private cmd As New SqlCommand
Private dt As New DataTable
Private dr As SqlDataReader
cnn.ConnectionString = "Data Source=G_HISBKS;Initial Catalog=XXX;User ID=XXX"
cnn.Open()
cmd.Connection = cnn
cmd.CommandText = "下SQL語法"
dr = cmd.ExecuteReader
dt.Load(dr)
obj_DataGridView.DataSource = dt
cmd.Dispose()
cnn.Close()
'--------------------------------------分格線---------------------------------------'
'--------------------------------------連結法四-------------------------------------'
Imports System.Data.SqlClient
Private cnn As New SqlConnection
Private cmd As New SqlCommand
Private da As New SqlDataAdapter
Private ds As New DataSet
cnn.ConnectionString = "Data Source=G_HISBKS;Initial Catalog=XXX;User ID=XXX"
cnn.Open()
cmd.Connection = cnn
cmd.CommandText = "下SQL語法"
da.SelectCommand = cmd
ds.Clear()
da.Fill(ds, "自設表單名")
dt.Load(dr)
obj_DataGridView.DataSource = ds
obj_DataGridView.DataMember = "自設表單名"
進行各種ds運用
cmd.Dispose()
cnn.Close()
'--------------------------------------分格線---------------------------------------'
'--------------------------------------連結法五-------------------------------------'
Imports System.Data.SqlClient
Private cnn As New SqlConnection
Private cmd As New SqlCommand
Private da As New SqlDataAdapter
Private ds As New DataSet
cnn.ConnectionString = "Data Source=G_HISBKS;Initial Catalog=XXX;User ID=XXX"
cnn.Open()
cmd.Connection = cnn
cmd.CommandText = "下SQL語法"
da.SelectCommand = cmd
ds.Clear()
da.Fill(ds, "自設表單名")
dt.Load(dr)
obj_DataGridView.DataSource = ds
obj_DataGridView.DataMember = "自設表單名"
進行各種ds運用
cmd.Dispose()
cnn.Close()
連結的方式多種，而取值也各有不同表示法
'-----------------------取值法一-----------------------------------------------------
'------------------------------------------------------------------------------------
cmd.CommandText = "select top 流水號 count from hn_news order by counter desc"
obj_txtBox.Text = cmd.ExecuteScalar.ToString
'單獨取得第一資料行的第一列值
'-----------------------取值法二-----------------------------------------------------
'------------------------------------------------------------------------------------
cmd.CommandText = "select * from hn_news order by counter desc"
dr = cmd.ExecuteReader
dr.Read() '讀一列
obj_txtBox.Text = dr("欄位名稱").ToString
'單筆資料行的值，再依欄位名去抓取各欄位值
'-----------------------取值法三-----------------------------------------------------
'------------------------------------------------------------------------------------
cmd.CommandText = "select * from hn_news order by counter desc"
da.SelectCommand = cmd '使用SqlDataAdapter的SelectCommand
ds.Clear()
da.Fill(ds, "tab1") '將SqlDataAdapter
obj_DataGridView.DataSource = ds
obj_DataGridView.DataMember = "自設表單名"
If MyDs.Tables(0).Columns.Count > 0 Then
obj_txtBox.Text1 = ds.Tables(0).Columns(1).ColumnName
obj_txtBox.Text2 = ds.Tables(0).Rows(0).Item("有效期限").ToString
obj_txtBox.Text3 = ds.Tables(0).Rows(0).Item("驗收人代號").ToString
End If
關於取值法三中
ds.Tables(0).Columns(1).ColumnName '表示取得table的欄位名稱，上圖中紅框區
此處中Tables(0)可寫成Tables("表單名")，Columns(1)可寫成Columns("欄位名")
ds.Tables(0).Rows(0).Item("有效期限").ToString
ds.Tables(0).Rows(0).Item("欄位名").ToString
此處中Item("欄位名")可寫成Item(0) 'index
如果寫ds.Tables(0).Rows(2).Item(4).ToString，表示是table(0)的第3行資料列 第5欄位的值
MyDs.Tables(0).Columns.Count '表示傳回的資料列數，用於判斷是否有傳回資料列
先暫存於此...
