How to update two tables with one dataset?

Question

I have a dataset with data from two SQL tables in.

How can I update both tables with the updated dataset? (since join does not work)

             da = new SqlDataAdapter("select * from xxxx join.....", conn);
             builderForTable1 = new SqlCommandBuilder(da);
             da.Update(dataSetEmp, "Table");

Answer 1

Public Shared Function GetRecords(ByRef CNobj As System.Data.SqlClient.SqlConnection,
                                   ByRef RSobj As System.Data.DataSet,
                                   ByVal SQLquery As String,
                                   ByVal TableName() As String,
                                   Optional ByVal PrimKeys() As String = Nothing,
                                   Optional ByRef adapter As SqlDataAdapter = Nothing) As Integer

    On Error Resume Next
    Dim DataOkay As Integer = 0                             '[Set to negative if data bad]

    Dim suppliersAdapter As SqlDataAdapter = New SqlDataAdapter()

    'Create connection object, won't create if already exists
    If ConnectToDB(CNobj, False) = False Then
        'error creating object, Session("ErrorMsg") set by called code
        Return -1
    End If

    ' Open the connection.
    If CNobj.State = ConnectionState.Closed Then
        CNobj.Open()
    End If


    ' Create a SqlCommand to retrieve Suppliers data.
    Dim suppliersCommand As SqlCommand = New SqlCommand(SQLquery, CNobj)

    suppliersCommand.CommandType = CommandType.Text

    ' Set the SqlDataAdapter's SelectCommand.
    suppliersAdapter.SelectCommand = suppliersCommand


    ' Fill the DataSet.
    RSobj = New DataSet()
    suppliersAdapter.Fill(RSobj)
    If (Err.Number <> 0) Then
        DataOkay = -1
        _contexts.Session("ErrorMsg") = "Error reading records: " +
            DelDoubleQuotesInString(Err.Description)
        Call Err.Clear()
    ElseIf (RSobj.Tables.Count = 0) Then
        DataOkay = -2
        _contexts.Session("ErrorMsg") = "No tables read reading records for sql=" + SQLquery
    Else
        ' A table mapping names the DataTables.
        Dim IDX As Integer
        For IDX = 0 To TableName.Count - 1
            RSobj.Tables(IDX).TableName = TableName(IDX)
            If PrimKeys IsNot Nothing Then
                Dim primstr = PrimKeys(IDX)
                Dim primarr = Split(primstr, ",")
                Dim primcol(primarr.Count) As DataColumn
                Dim pidx As Integer
                For pidx = 0 To primarr.Count
                    primcol(pidx) = RSobj.Tables(IDX).Columns.Item(primarr(pidx))
                Next
                RSobj.Tables(IDX).PrimaryKey = primcol
            End If

        Next
        adapter = suppliersAdapter
    End If
    Return DataOkay
End Function

  Public Shared Function UpdateDB(ByRef dset As System.Data.DataSet, ByVal adapter As SqlDataAdapter)
    If dset.HasChanges = False Then
        Return True
    End If
    ' has multiple tables, select's separated by ;
    Dim SelectQry As String = adapter.SelectCommand.CommandText
    Dim commands() As String = Split(SelectQry, ";")
    Dim idx As Integer = 0

    For Each Table As System.Data.DataTable In dset.Tables
        'Connection object is global in shared ASP.NET module
        If CNobj.State = ConnectionState.Closed Then
            CNobj.Open()
        End If
        'Change select for table being updated.
        adapter.SelectCommand.CommandText = commands(idx)


        Dim cb As SqlCommandBuilder
        cb = New SqlCommandBuilder(adapter)

        adapter.InsertCommand = cb.GetInsertCommand(True)
        adapter.UpdateCommand = cb.GetUpdateCommand(True)
        adapter.DeleteCommand = cb.GetDeleteCommand(True)

        Try

            adapter.AcceptChangesDuringUpdate = True
            'force order delete, update, insert
            adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Deleted))
            Table.AcceptChanges() 'Have to do after delete!
            adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
            adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Added))
        Catch
            HttpContext.Current.Session("ErrorMsg") = "Error saving data to DB: " + Err.Description
            adapter.SelectCommand.CommandText = SelectQry
            Return False
        End Try
        idx += 1
        cb.Dispose()
    Next
    'Put original select back for next call
    adapter.SelectCommand.CommandText = SelectQry
    Return True
End Function

I included both routines I'm using. The first routine will get multiple tables if called with select with semicolons. I pass in array of table names to assign to each table. if the dataset is going to update to the server, then I also pass in the primary keys and reference to the adapter to be passed in to the update call.

I know it is in VB and not C#, but the Classic ASP I am converting to ASP.NET was in VB already and I didn't want to port to C# on top of it.

The update code splits the select strings for each update call for each table.

ERROR - the above code doesn't work for all tables. For one table if I don't do acceptchanges it won't update - get concurrency error. On another table, it states I have my 13 modified rows before calling acceptchanges -but that goes to zero after the call and thus no records are updated. My guess is that the update SQL generated for one table that gets the concurrency error is crap and that acceptchanges should not be called. I'll update post when I resolve this.

Answer 2

Perhaps you could use a transaction?

SqlTransaction tr = conn.BeginTransaction();
    da = new SqlDataAdapter("select * from xxxx join.....", conn);
    builderForTable1 = new SqlCommandBuilder(da);
    da.Update(dataSetEmp, "Table");
    // ...
tr.Commit();

Reference:

• SqlTransaction

Answer 3

The SQLDataAdapter.Fill() method can do this if the query you provide to the data adapter returns multiple resultsets.

var da = new SqlDataAdapter("select * from customers; select * from orders;", conn);
da.Fill(myDataset);

var customersDataTable = myDataset.Tables[0];
var ordersDataTable = myDataset.Tables[1];

Then you can add your own DataRelations if necessary.

myDataset.Relations.Add(new DataRelation(
    "CustomerOrders",
    customersDataTable.Columns["CustomerID"],
    ordersDataTable.Columns["CustomerID"]
)