How to insert data from strongly typed dataset into database (jbx01)

[jbrag]

Hi,
I have a strongly typed DataSet with 10+ tables, filled with data from
a XMLDocument, and SQLServer database matching the dataset schema. How
do I insert that data into the database with minimum coding effort
using ADO.NET and the .NET framework?

Thanks
(jbx01)

 

[W.G. Ryan MVP]

You'll need to configure a dataadapter to do it if simplicity is your goal.
You can use a CommandBuilder, the DataAdapter configuration wizard or write
your own (check out Bill Vaughn's 'Weaning Developers from the
CommandBuilder article for a great discussion on this - www.betav.com ->
Articles -> MSDN). Depending on how you got the dataset populated will
dictate your next strategy. If you're loading it from another database, then
just set AcceptChangesDuringFill to false on each adapter you're doing a
SELECT with to populate each respective table (actually, I just reread your
post and see that you are getting the data from an XML Document not another
db - but just as an FYI - if you ever come across this and want to do a data
base to database transfer and must use ADO.NET - this should help you out
http://www.expansys.com/product.asp?code=125564 ) . Then, have an adapter
for each of the tables in the dataset pointing to the destination - and call
update on the parent most and then each of the descendants in the chain - if
tables aren't related then it doesn't much matter.

If you are manually creating the rows and don't have a RowState of Added
(and they don't already exist in the destination table - which they appear
not to if I understand you correctly)... then using an Adapter won't work
since it looks to rowstate to determine which command to fire. And if it's
not Added, then it won't fire an Insert command for that row. In that
instance, you'll need to create your command and parameters, then just loop
through calling ExecuteNonQuery on each row (which is essentially all that
the Adapter is doing).

The key is once you load your dataset, check HasChanges() immediately after
loading it. Depending on the XML you got it from (ie if it has a diffgram)
then you may or may not have rowstate. This is the key factor here. You
can also go bleeding edge and use Yukon's xml features (if you're interested
I'll explain ) but that's probably a bit out of scope based on the current
description.)

If I misunderstood anything or you need further clarification, please let me
know.

Cheers,

Bill

 

[Cor Ligthert [MVP]]

JBrag,

In additions to Bills message.

You need for what he write of course a database that has tables and columns
confirm the tables and columns in your dataset.

Cor

 

[jbrag]

Hi Bill, I thank you very much for help and quick response, appreciate
it!
Please use (e-mail address removed) for reply next time - will reach me
directly.

I realized I should be more precise. So here it goes. I had an XSD
schema which I used to generate a database in my MS SQL Server 2000 (I
used a freebee tool that accepts XSD file and drops/recreates the
database based on the XSD schema, xsd2db.exe). I'm receiving XML files
(actually they are XML strings passed to my URL, using GET), and my
intent is to easy-way store it in my database. So using Microsoft
xsd.exe I generated a strongly typed DataSet class, which added it to
my website application .NET C# project. Next, I populate a strongly
typed dataset instance from the XMLDocument instance created off an XML
string. So there should be a full schema match between the XML stream,
the dataset and the database.

Now, the question is: having the dataset populated this way how do I go
about inserting the (XML) data into my database, in a possible painless
way, letting the technology do most of the work.

A critical point to make: I may need to change XSD multiple times, or
re-apply the solution to many applications, each with a different XSD,
so writing as little code as possible is critical in the solution.

Not to sound too negative, but what looked like a Microsoft promise
with an introduction of a concept of Strongly Typed Datasets is bit
underdeveloped. One would expect a simple method Save or Insert.

Jacek B.

 

[jbrag]

Bill,
Wasted another few hours experimenting with ado.net and my final
conclusion is I still need to write a tonn of custom code to just
insert a dataset (multiple tables) even knowing that each dataset
instance will be data-wise unique, database-wide.

Bit disappointed but not surprised, ditching the project... just too
expensive.

jacek b.