473,372 Members | 1,211 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Send Strongly-typed Dataset with Multiple Tables to SQL Server

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?
Nov 21 '05 #1
8 6860
(If you are using a sqldataadapter)
Call the update method of the dataadapter passing the dataset as as argument.
"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?

Nov 21 '05 #2

"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
Nov 21 '05 #3
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/de...chematopic.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" <si******@dodgeit.com>
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?

Nov 21 '05 #4
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?
Nov 21 '05 #5
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?
Nov 21 '05 #6

"Dot Net Newbie" <si******@dodgeit.com> wrote in message
news:a4**************************@posting.google.c om...
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/de...anidcrisis.asp
http://support.microsoft.com/default...en-us%3B310350
David
Nov 21 '05 #7
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("Primar yKey")
myDataset.Tables("ChildTable").row(0).item("Parent ForeignKey")

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


Tinkerbee
Nov 21 '05 #8
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 :)
Nov 21 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: MrMagooba | last post by:
Hi, I have a contact page that, when submit is clicked it checks the inputs then outputs to a "read only" version of the input form fromwhich the email can be sent. How would I go about changing...
1
by: Jack Menendez | last post by:
I have a forms based plugin architecture using C# that includes help files using MSHelpServices. However, my strongly named assemblies cannot be created because Interop.MSHelpServices is not strongly...
36
by: dcrespo | last post by:
Hi all, I have a program that serves client programs. The server has a login password, which has to be used by each client for logging in. So, when the client connects, it sends a string with a...
4
by: Tamir Khason | last post by:
I have a form. On form there is my control (all of control's assemblies signed by strong key), BUT while running I recieve he located assembly 'MyFooAssembly' is not strongly named. While...
1
by: San | last post by:
Hi, Why strongly named assembly can refer other strongly named assembly ? Thanks with Regards, San.
5
by: Oleg Subachev | last post by:
When I try to use strongly named assembly1 that references non-strongly named assembly2 I get the following error: "The located assembly '<assembly2 name>' is not strongly named." How can I...
7
by: rom | last post by:
i know it's not exactly the place to post this question but i didn't find a betrer group. when i send a zipped folder with an asp.net application i cant use it on the destination computer. when i...
1
by: DotNetJunkies User | last post by:
I have a .NET DLL that uses ADO 2.8 DLL. I am not able to "strongly name" the .NET DLL. Any comments, work arounds ? CHDe --- Posted using Wimdows.net NntpNews Component -
2
by: Charlie Brown | last post by:
I can open an application such as Notepad and use SendKeys to send input to the Notepad. What I would like to do is reference an already running application and then send keyboard input to it. ...
0
by: Dave Burns | last post by:
Hi, I have a C++ managed assembly (.dll) which links to a bunch of native libraries. Everything works fine if I don't make the managed assembly a strongly named one. Once I make it a strongly...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.