Data Manipulation (INSERT,UPDATE) using DataSet Object with VB.NET

I am newbie at using DataSet. I am writing a program with VB.NET, there I have to select data from one table. Then I have to update about 4 tables and insert to 2 Tables. Which approach will be ok for me? I'm thi开发者_如何学Gonking to use DataSet. If anyone can point out that problem , please show me with Sample code to update DataSet. Thanks you all very much.

This is a link you can refer to:

Public Function CreateCommandAndUpdate( _
    ByVal connectionString As String, _
    ByVal queryString As String) As DataSet

    Dim dataSet As DataSet = New DataSet

    Using connection As New OleDbConnection(connectionString)
        connection.Open()
        Dim adapter As New OleDbDataAdapter()

        adapter.SelectCommand = New OleDbCommand( _
            queryString, connection)

        Dim builder As OleDbCommandBuilder = _
            New OleDbCommandBuilder(adapter)

        adapter.Fill(dataSet)

        ' Code to modify the data in the DataSet here. 

        ' Without the OleDbCommandBuilder this line would fail.
        builder.GetUpdateCommand()
        adapter.Update(dataSet)
    End Using
    Return dataSet
End Function

And here's an example on how to delete:

Private Sub btnDeleteUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteUser.Click
Dim reponse_del As Integer
If txtSearch.Text = "" Then
MessageBox.Show("Please type a user name into the text box")
End If

'clear and refill Dataset
OleDAPass.SelectCommand.Parameters("UserName").Value = txtSearch.Text
DS_Pass1.Clear()
OleDAPass.Fill(DS_Pass1)
'no records of the search name
If DS_Pass1.Tables("PwordStore").Rows.Count = 0 Then
MessageBox.Show("Record not found")
ElseIf DS_Pass1.Tables("PwordStore").Rows.Count = 1 Then 'record exists delete it
MessageBox.Show("Are you sure you wish to delete this user?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
If reponse_del = DialogResult.Yes Then
OleDAPass.SelectCommand.Parameters ("UserName").Value = txtSearch.Text
'delete row
DS_Pass1.Tables("PwordStore").Rows(0).Delete()
OleDAPass.Update(DS_Pass1, "PwordStore")
End If
DS_Pass1.PwordStore.AcceptChanges()
DS_Pass1.Clear()
txtSearch.Text = ""
End If
End Sub

You should work with data adapter - best way to learn - look for some examples in MSDN or Google it, then - try to solve your problem - then - if you encounter a problem - post it here.

in basics: you need a connection, a command, a dataadapter and a dataset.

you use datadapter fill to get the data from DB to the dataset, and update to put the data back to the database. you need to tell the data adapter how to do that, or use a wizard to auto-generate the commands (INSERT, UPDATE ect...)

  Public Function MaintanenceImport(ByVal con As IDbConnection, ByVal TransDate As Date, ByVal ds2 As DataSet)
        Try
            For index1 = 0 To ds2.Tables.Count - 1
                tblname = ds2.Tables(index1).TableName
                ds1 = Util.GetDataSet4Query(" select * from " & tblname & " ")
                index = 0
                ds1.Tables(index).TableName = "" & tblname & ""
                'For index = 0 To ds1.Tables.Count - 1
                str1 = ""
                Str = ""
                cnt1 = 0
                '    '====Comparing the Xml TableName and the Database Table Name
                '    '==== ds1-from Database;ds2-from xml
                '    If (ds1.Tables(index).TableName = ds2.Tables(index1).TableName) Then
                '        tblname = ds2.Tables(index1).TableName
                l = 0
                '=== Counting the Columns of xml dataset
                While (l <= ds2.Tables(index1).Columns.Count - 1)
                    n = 0
                    '=== Counting the Columns of DataBase dataset
                    While (n <= ds1.Tables(index).Columns.Count - 1)
                        '==== Comparing the Column Names and seperating the matching and unmatching fields
                        If ((UCase(ds2.Tables(index1).Columns(l).ColumnName).Trim) = (UCase(ds1.Tables(index).Columns(n).ColumnName).Trim) = True) Then
                            f2 = True
                            Exit While
                        Else
                            f2 = False
                        End If
                        n += 1
                    End While
                    '=== Collecting the Unmatched Column Names
                    If f2 = False Then
                        cnt3 = cnt3 + 1
                        If cnt3 = 1 Then
                            str4 = ds2.Tables(index1).Columns(l).ColumnName
                            col1 = str4
                            m1 = 0
                            a1(m1) = 1
                        Else
                            str4 = str4 + "," + ds2.Tables(index1).Columns(l).ColumnName
                            m1 += 1
                            a1(m1) = l
                        End If
                    End If
                    '=== Collecting the matching column names
                    If f2 = True Then
                        cnt1 = cnt1 + 1
                        If cnt1 = 1 Then
                            str1 = ds2.Tables(index1).Columns(l).ColumnName
                            col = str1
                            m = 0
                            a(m) = l
                        Else
                            str1 = str1 + "," + ds2.Tables(index1).Columns(l).ColumnName
                            m += 1
                            a(m) = l
                        End If

                    End If
                    l += 1
                End While

                '=================================================================
                '===>     code to fetch value from xml for insert            <====
                '=================================================================
                i = 0
                '==== Take a row count of dataset from xml
                While (i <= ds2.Tables(index1).Rows.Count - 1)
                    k = 0
                    f1 = False
                    '==== Take a row count of dataset from Database
                    While (k <= ds1.Tables(index).Rows.Count - 1)
                        '=== Comparing the First Field of the row 
                        If (ds2.Tables(index1).Rows(i)(0).ToString() = ds1.Tables(index).Rows(k)(0).ToString()) Then
                            '== if matches true Else false
                            f1 = True
                            Exit While
                        Else
                            f1 = False
                        End If
                        k += 1
                    End While
                    '== if false it indicates NewRecord
                    If f1 = False Then
                        m = 0
                        '=================================================================
                        '===>if column names collected are similar to the dbase table<====
                        '=================================================================

                        If a Is Nothing Then
                            cnt = 0
                            cnt = cnt + 1
                            j = 0
                            While (j < ds2.Tables(index1).Columns.Count)
                                If cnt = 1 Then
                                    Str = ds2.Tables(index1).Rows(i)(j).ToString()
                                    q = str
                                Else
                                    str = str + "," + "'" + Replace(ds2.Tables(index1).Rows(i)(j).ToString(), "'", "''") + "'"
                                    cnt += 1
                                End If
                                j += 1
                            End While
                            '========================================================================
                            '===>if column names collected are not similar to the database table<====
                            '========================================================================
                            '=== Collecting the values 
                        Else
                            m = 0
                            cnt = 0
                            cnt += 1
                            cnt2 = cnt1
                            For m = 0 To cnt2 - 1
                                p = a(m)
                                If p.ToString Is Nothing Then
                                    Exit For
                                Else
                                    If cnt = 1 Then
                                        str = "'" + ds2.Tables(index1).Rows(i)(p).ToString() + "'"
                                        'q = str
                                    Else
                                        str = str + "," + "'" + Replace(ds2.Tables(index1).Rows(i)(p).ToString(), "'", "''") + "'"
                                    End If
                                    cnt += 1
                                End If
                            Next
                        End If
                        Dim TransCon As IDbConnection
                        Dim Trans As IDbTransaction
                        Try
                            '== Inserting the collected values into table
                            TransCon = Util.GetConnection
                            TransCon.Open()
                            Trans = TransCon.BeginTransaction(IsolationLevel.ReadCommitted)
                            sql3 = "insert into " & tblname & "(" & str1 & ") values(" & str & ")"
                            '== Check Identity Column
                            If Util.ExecuteQryNSP(TransCon, CmdType.Text, ExecuteMode.ExecuteScalar, Trans, "select count(*) from SysColumns where Id=object_id('" & tblname & "') and Colstat&1=1") > 0 Then
                                Util.ExecuteQryNSP(TransCon, CmdType.Text, ExecuteMode.ExecuteNonQuery, Trans, "set IDENTITY_INSERT " & tblname & " on ")
                                Util.ExecuteQryNSP(TransCon, CmdType.Text, ExecuteMode.ExecuteNonQuery, Trans, sql3)
                                Util.ExecuteQryNSP(TransCon, CmdType.Text, ExecuteMode.ExecuteNonQuery, Trans, "set IDENTITY_INSERT " & tblname & " off ")
                            Else
                                Util.ExecuteQryNSP(TransCon, CmdType.Text, ExecuteMode.ExecuteNonQuery, Trans, sql3)
                            End If
                            Trans.Commit()
                        Catch ex As Exception
                            Trans.Rollback()
                        End Try
                        '=== Updating the Records from Xml to DataBase
                    Else
                        Try

                            Dim da1 As New SqlDataAdapter
                            Dim builder As New SqlCommandBuilder(da1)
                            da1.SelectCommand = New SqlCommand("select * from " & tblname & "", con)
                            '== Collecting the column names in a string array
                            Dim str2() As String = str1.Split(",")
                            '=== Filling the Xml DataSet into the DataTable
                            Dim dt1 As DataTable = ds2.Tables(index1)
                            '=== Filling the DataBase dataset into the datatable
                            Dim dt2 As DataTable = ds1.Tables(index)
                            Dim drcpu As DataRow
                            Dim drVendor As DataRow
                            '=== By Comparing row by row and field by field Updating done
                            For Each drcpu In dt1.Rows
                                For Each drVendor In dt2.Rows
                                    If drVendor(str2(0)) = drcpu(str2(0)) Then
                                        For Each Me.str3 In str2
                                            If Not IsDBNull(drcpu(str3)) Then
                                                If drVendor(str3).ToString = drcpu(str3).ToString Then
                                                    Continue For
                                                Else
                                                    If IsDBNull(drVendor(str3)) Then
                                                        drVendor.BeginEdit()
                                                        drVendor(str3) = drcpu(str3).ToString
                                                        drVendor.EndEdit()
                                                        da1.UpdateCommand = New SqlCommand("update " & tblname & " set " & str3 & "='" & Trim(drVendor(str3)) & "'  where " & str2(0) & "= " & drVendor(str2(0)) & "")
                                                        da1.Update(ds1, "" & tblname & "")
                                                    End If
                                                    Dim Store2 As String
                                                        Dim Store1 As String
                                                        Store2 = (UCase(CType(drVendor(str3), String)).Trim)
                                                        Store1 = (UCase(CType(drcpu(str3), String)).Trim)
                                                        If (Store2).Equals(Store1) Then
                                                            Continue For
                                                        Else
                                                            drVendor.BeginEdit()
                                                            drVendor(str3) = drcpu(str3).ToString
                                                            drVendor.EndEdit()
                                                            Dim SqlStr As String = ""
                                                            If tblname = "MasterSettings" Then
                                                                SqlStr = "update " & tblname & " set " & str3 & "='" & Replace(Trim(drVendor(str3)), "'", "''") & "'  where " & str2(0) & "= " & drVendor(str2(0)) & " and " & str2(1) & "= " & drVendor(str2(1)) & ""
                                                            Else
                                                                SqlStr = "update " & tblname & " set " & str3 & "='" & Replace(Trim(drVendor(str3)), "'", "''") & "'  where " & str2(0) & "= " & drVendor(str2(0)) & ""
                                                            End If
                                                            da1.UpdateCommand = New SqlCommand(SqlStr)
                                                            da1.Update(ds1, "" & tblname & "")
                                                        End If
                                                    End If
                                                    'End If
                                                End If
                                        Next

                                    End If
                                Next
                            Next
                        Catch ex As Exception
                            MsgBox(ex)
                        End Try
                    End If
                    i += 1
                End While
            Next
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function