Send Strongly-typed Dataset with Multiple Tables to SQL Server

[Dot Net Newbie]

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?

 

[Mark Jones]

(If you are using a sqldataadapter)
Call the update method of the dataadapter passing the dataset as as argument.

 

[David Browne]

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

 

[Cor Ligthert]

DotNetNewbie,

In your situation I would do it like this.
(Use in the beginning a global dataset and global dataadapter as I show you
beneath somewhere in top of your program outside a method)

private ds as new dataset
private da as new SQLDataadapter

In the load event of the form

I would do see this sample
http://msdn.microsoft.com/library/d...tacommondbdataadapterclassfillschematopic.asp

However with a Select string as "Select * from mytable" and than of course
da.fillschema(ds)
Than directly
dim cmb as new commandbuilder(da).

In a let say button event I would than do after reading the dataset with by
instance readXML.
dim ds as dataset = MystronglytypedDataset

And than
try
da.update(ds)
catch ex as exception
'errror handling
end try

You do not need to open and close connections for this, that is all done by
the dataadapter.

I hope this helps a little bit?

Cor

 

[Dot Net Newbie]

Hi Guys,

Thanks so much for the responses. All of you have definitely helped
me progress further. However, I'm still not getting the desired
results. So far, what Mark and David have suggested works for the
Parent table, but I can't get it to work for children.


=============================
Here are the details for Mark
=============================
I was not using the SQL Adapter so I added two new ones to the form.
I used the "Wizard" on each one to bind a table from the database. As
you probably already know, when you add a SQL adapter, VB also creates
a new sql connection (SqlConnection1) on the form as well.

When I added the second adapter, no new SQL Connection was created.
I'm *assuming* it's using the one created by SQLAdapter1; especially
since I was able to select my child table from Query builder.

Ok, so now I have a form with two sql adapters and one sql connection.
I run through my code that builds the dataset and then add the
following lines:
myForm.sqlAdapter1.Update(myDataset) 'adapter for Parent Table
myForm.sqlAdapter2.Update(myDataset) 'adapter for Child Table

The first line works. My record inserts beautifully. The second one
however, does nothing. No new record, no error message, no nothing.

I can tell *something* has happened. When I check the parameters for
the InsertCommand between the two data adapters, Adapter1 has the
values that were inserted but Adapter2 has no parameter values.

Also, the rowstate of row(0) for the Parent Table has been set to
"unchanged" whereas the rowstate of row(0) for the Child Table is
still set to "Added".

Any ideas why the line of code runs without error for the child table
yet nothing happens in the database?

 

[Dot Net Newbie]

Hi Guys,

Thanks so much for the responses. All of you have definitely helped
me progress further. However, I'm still not getting the desired
results. So far, what Mark and David have suggested works for the
Parent table, but I can't get it to work for children.

====================
Here are the details
====================


Hey David,

Your sample code really helped me.

There were a couple of changes I had to make in order for it to work
(just in case someone else needs it):
con.Open() 'before dimensioning transaction
cmd.Transaction = tran 'before setting da commands (that's da as in
Data DataAdapter not the slang form of "the" )


The problem I had, as with Mark was adding the child records. I DID
get an error message with your code though.
When I execute "da.update(dt)", I ge this error message:
Run-time exception thrown : System.Data.SqlClient.SqlException -
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'Parent_To_Child'. The conflict occurred in database 'myDatabase',
table 'Parent', column 'ParentID'.
The statement has been terminated.

Assume in my child table, I have a foreign key called "ParentID" and
that it's an identity field. It's a negative number b/c this record
will be new everytime. I was pretty sure I read somewhere that when
you try to do what I'm doing, you want to use negative numbers for you
IDs so that DOT Not will know to add it as a new reocord and change
child keys appropriately.

But, my keys aren't changing. Any other help?

 

[David Browne]

Hi Guys,

Thanks so much for the responses. All of you have definitely helped
me progress further. However, I'm still not getting the desired
results. So far, what Mark and David have suggested works for the
Parent table, but I can't get it to work for children.

====================
Here are the details
====================


Hey David,

Your sample code really helped me.

There were a couple of changes I had to make in order for it to work
(just in case someone else needs it):
con.Open() 'before dimensioning transaction
cmd.Transaction = tran 'before setting da commands (that's da as in
Data DataAdapter not the slang form of "the" )


The problem I had, as with Mark was adding the child records. I DID
get an error message with your code though.
When I execute "da.update(dt)", I ge this error message:
Run-time exception thrown : System.Data.SqlClient.SqlException -
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'Parent_To_Child'. The conflict occurred in database 'myDatabase',
table 'Parent', column 'ParentID'.
The statement has been terminated.

Assume in my child table, I have a foreign key called "ParentID" and
that it's an identity field. It's a negative number b/c this record
will be new everytime. I was pretty sure I read somewhere that when
you try to do what I'm doing, you want to use negative numbers for you
IDs so that DOT Not will know to add it as a new reocord and change
child keys appropriately.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;310350


David

 

[Dot Net Newbie]

I was able make it work with data adapters as follows:
myAdapter1.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord
myAdapter1.Update(myDataset.Tables("ParentTable"))

myAdapter2.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord
myAdapter2.Update(myDataset.Tables("ChildTable"))

After running the code with the UpdatedRowSource property set, I
checked my key values:
myDataset.Tables("ChildTable").row(0).item("PrimaryKey")
myDataset.Tables("ChildTable").row(0).item("ParentForeignKey")

They were both automatically updated with the new values from SQL
server; just as I'd hoped








Tinkerbee

 

[Dot Net Newbie]

That link was EXACTLY what I was looking for! Thanks!

FYI: I still could not get your snippit of code to work.

I think I'm missing something obvious since I was able to acheive my
goal with Adapters located on Windows forms.

I tried something similar (inserted these two lines below "Dim cb As
New SqlCommandBuilder(da)":
da.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord
da.InsertCommand.CommandText = sCommandTextString 'where
sCommandTextString is the same InsertCommand.Command text from
myAdapter1.InsertCommand.CommandText

I compared the properties between the two adapters "da" and
"Adapater1". They were the same.

However, after running the update command, my Primary Key value is
still -1.

It's not that big a deal since I have what I need.

However, if you're still willing to help troubleshoot, I wouldn't mind
seeing the solution