再戰DataSet

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

多資料表的DataSet DataSet DataTables datatable DataRelationss DataRelation

多資料表的DataSet

多資料表的DataSet

多資料表的DataSet

多資料表的DataSet

多資料表的DataSet

多資料表的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

多資料表的DataSet

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

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

多資料表的DataSet建立關聯

多資料表的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

DataSet與物件繫結

多資料表的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

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