Hi Guys,
I saw somuch discussions on this topic but not found the proper
answers - but after a struggle I found the following answer - here is
the code snippet.
Protected Friend Function BatchUpdate(ByVal updatedDataSet As DataSet)
As Boolean
Dim oSqlCmd As New SqlCommand()
Dim oDataTable As DataTable
Dim oSqlBuilder As SqlCommandBuilder
Dim oSqlTransaction As SqlTransaction
Try
oSqlCmd.Connection = Me.Connection
If Me.Connection.State = ConnectionState.Closed Then
oSqlCmd.Connection.Open()
End If
oSqlTransaction = Me.Connection.BeginTransaction
oSqlCmd.Transaction = oSqlTransaction
For Each oDataTable In updatedDataSet.Tables
If oDataTable.TableName <> "Table" AndAlso
oDataTable.Rows.Count > 0 Then
oDataAdapter = New SqlDataAdapter()
oSqlBuilder = New SqlCommandBuilder(oDataAdapter)
oSqlCmd.CommandText = "Select * from " +
oDataTable.TableName
oSqlCmd.CommandType = CommandType.Text
oDataAdapter.SelectCommand = oSqlCmd
oDataAdapter.UpdateCommand =
oSqlBuilder.GetUpdateCommand
oDataAdapter.InsertCommand =
oSqlBuilder.GetInsertCommand
oDataAdapter.InsertCommand.CommandText &= ";
Select * From " + oDataTable.TableName + _
" Where " +
oDataTable.Columns(0).ColumnName + " = @@IDENTITY"
oDataAdapter.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord
AddHandler oDataAdapter.RowUpdated, AddressOf
oDataAdapter_MyRowUpdated
oDataAdapter.Update(updatedDataSet.Tables(oDataTab le.TableName).Select("",
"", DataViewRowState.ModifiedCurrent))
oDataAdapter.Update(updatedDataSet.Tables(oDataTab le.TableName).Select("",
"", DataViewRowState.Added))
End If
Next
'Commit the transaction if everything is successful
oSqlTransaction.Commit()
'Close the connection and exit the subroutine.
Me.Close()
Catch Ex As Exception
'Rollback the transaction if anything fails.
oSqlTransaction.Rollback()
'Close the connection and exit the subroutine.
Me.Close()
Return False
End Try
Return True
End Function
Private Sub oDataAdapter_MyRowUpdated(ByVal sender As Object,
ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
If e.StatementType = StatementType.Insert Then e.Status =
UpdateStatus.SkipCurrentRow
If e.StatementType = StatementType.Delete Then e.Status =
UpdateStatus.SkipCurrentRow
End Sub