再戰DataSet.

Presentation on theme: "再戰DataSet."— Presentation transcript:

1 再戰DataSet

2 多資料表的DataSet 加入三個資料表到dataset 使用內建的connection、dataadapter與dataset物件
Dataadapter >> selectcommand commandtext+connection屬性 Dataset >> 不具型別

3 多資料表的DataSet DataSet DataTables datatable DataRelationss DataRelation

4 多資料表的DataSet

5 多資料表的DataSet

6 多資料表的DataSet

7 多資料表的DataSet

8 多資料表的DataSet

9 多資料表的DataSet Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click SqlDataAdapter1.SelectCommand.CommandText = "select * from member" SqlConnection1.Open() SqlDataAdapter1.Fill(DataSet1, "客戶") DataGridView1.DataSource = DataSet1 DataGridView1.DataMember = "客戶" StatusBar1.Text = "目前有" + DataSet1.Tables.Count.ToString() + "個資料表" SqlConnection1.Close() End Sub

10 多資料表的DataSet

11 DataSet與物件繫結 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click If Not DataSet1.Tables.Contains("客戶") Then SqlDataAdapter1.SelectCommand.CommandText = "select * from member" SqlConnection1.Open() SqlDataAdapter1.Fill(DataSet1, "客戶") DataGridView1.DataSource = DataSet1 DataGridView1.DataMember = "客戶" StatusBar1.Text = "目前有" + DataSet1.Tables.Count.ToString() + "個資料表" TextBox1.DataBindings.Add("Text", DataSet1, "客戶.m_name") SqlConnection1.Close() End If End Sub

12 多資料表的DataSet 避免datagridview重複填入資料 資料繫結部份也要清除
If DataSet1.Tables.Contains("客戶") Then DataSet1.Tables("客戶").Clear() End If 資料繫結部份也要清除 TextBox1.DataBindings.Clear()

13 多資料表的DataSet建立關聯

14 多資料表的DataSet建立關聯 主副表連動
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click DataSet1.Relations.Add("客戶訂貨表", DataSet1.Tables("客戶").Columns("m_id"), DataSet1.Tables("交易").Columns("m_id")) DataGridView2.DataSource = DataSet1 DataGridView2.DataMember = "客戶" DataGridView3.DataSource = DataSet1 DataGridView3.DataMember = "客戶.客戶訂貨表" End Sub

15 DataSet與物件繫結

16 多資料表的DataSet資料異動 即時異動資料
Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit SqlDataAdapter1.UpdateCommand.CommandText = "update member set m_name='" + DataGridView1.CurrentRow.Cells(1).Value + "' where m_id=" + DataGridView1.CurrentRow.Cells(0).Value.ToString() SqlConnection1.Open() SqlDataAdapter1.UpdateCommand.Connection = SqlConnection1 SqlDataAdapter1.UpdateCommand.ExecuteNonQuery() SqlConnection1.Close() End Sub

17 多資料表的DataSet資料異動 非即時異動資料 SqlConnection1.Open() ‘ 建立commandbuilder
Dim dbcmdbuilder As SqlCommandBuilder dbcmdbuilder = New SqlCommandBuilder(SqlDataAdapter1) ‘ 清除參數 SqlDataAdapter1.UpdateCommand.Parameters.Clear() SqlDataAdapter1.UpdateCommand.CommandText = "UPDATE member SET m_name = WHERE m_id SqlDbType.NVarChar, 15, "m_name") SqlDbType.Int, 15, "m_age") Dim workParm As SqlParameter = SqlDbType.Int) workParm.SourceColumn = "m_id" workParm.SourceVersion = DataRowVersion.Original SqlDataAdapter1.Update(DataSet1.Tables("客戶")) SqlConnection1.Close()