"Dot Net Newbie" <si******@dodgeit.com> wrote in message
news:a4**************************@posting.google.c om...
New to DOTNET so please be gentle:
I have an in-memory dataset that I want move to a SQL Server database.
The XML schema with which the dataset was built matches exactly the
table schema in SQL Server (same fields, same relations, etc.).
The dataset has eight tables all of which are linked to each other by
an identity field.
From what I understand, I should be able to "flush" the data from the
dataset in memory to the tables in SQL Server with just a few lines of
code.
I've been googling all afternoon but have had no success to point me
in the right direction.
Am I on the right track? Is there a way to move the data to SQL
Server at once?
Minimally,
Dim ds As DataSet
Dim con As SqlConnection
For Each dt As DataTable In ds.Tables
Dim cmd As New SqlCommand("select * from " & dt.TableName, con)
Dim da As New SqlDataAdapter(cmd)
Dim cb As New SqlCommandBuilder(da)
da.Update(dt)
Next
Now usually you need a bit more, since you won't be able to flush the tables
in just any order. Foreign key relationships will dictate the order. So
actually this is more realistic
Dim ds As DataSet = . . .
'make a copy of the changes for the transaction
Dim changes As DataSet = ds.GetChanges()
Dim con As SqlConnection = ...
Dim tran As SqlTransaction = con.BeginTransaction()
Try
For Each tn As String In New String() {"Table1", "Table2",
"Table3"}
Dim dt As DataTable = changes.Tables(tn)
Dim cmd As New SqlCommand("select * from " & dt.TableName,
con)
Dim da As New SqlDataAdapter(cmd)
Dim cb As New SqlCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.UpdateCommand = cb.GetUpdateCommand
da.DeleteCommand = cb.GetUpdateCommand
da.InsertCommand.Transaction = tran
da.UpdateCommand.Transaction = tran
da.DeleteCommand.Transaction = tran
da.Update(dt)
Next
ds.AcceptChanges()
tran.Commit()
Catch ex As Exception
ds.RejectChanges()
tran.Rollback()
Throw
Finally
con.Close()
End Try
Now going one step further you should ditch the CommandBuilder, or use it
only the first time and save the Insert, Update and Delete commands for each
table since it's expensive to recreate them every time.
David