How do I do this...?

  • Thread starter Thread starter Kev
  • Start date Start date
K

Kev

Hi,

I have a database that needs a whole bunch of records
added. The data comes from an XML file. I have grabbed
the nodes in question, looped through them and I now want
to populate SQL.

Obviously I can pass each nodes data to a stored proc
which will add to the table but this means lots of
network traffic and I am sure it is not as efficient as
dumping the data into SQL in one go.

So, my question is, how do I create a client side
recordset, populate the data into it, and then update the
server, using ADO.Net via VB.Net. This would be more
efficient, right?

This is probably pretty basic but if someone could show
me a sample that dumps several records into a table (just
make one up - I'll be able to follow) it would really
help clarify a few things.

Thanks to all in advance...
 
Ok there are a couple of issues. The first is the XML file. You can take
any dataset and write it to XML via DataSet.WriteXml("C:\somewhere.xml")

Now, the reverse is ReadXML. Whether or not you can read it in is another
story, but I'd try and then see how many tables I had using
DataSet.Tables.Count. I would play with WriteXML so I got a good
understanding of how it works....trust me, it will open your mind up to a
lot.

Now, you can use the DiffGram option with your Read/Write XML and that will
write out the state of the rows. Once again, I'd change the row states and
see what happens, just so you see how it works.

So let's say you have a DataSet from XML and the DiffGram indicates all new
changes/deletes/inserts etc. All you'd need to do is declare a
CommandBuilder with a SELECT statment that matched the Schema of your
Dataset, (The select command is how the CB infers the schema. Then, fire an
Update against your DataAdapter. THis of course is precipitated against
having the rowstates that aren't inidcating that they haven't changed....

Ok, so what if they haven't? Well, you have a few choices, the first of
which is creating the dataset blank, adding the rows individually so you
know they'll be considered Added. This of course depends on if you need
everything added or not, but this is of course totally up to you and under
your control.

Also, you don't need to use the commandbuilder, but it's an easy way to do
it in many cases. Head over to www.betav.com and check out Bill Vaughn's
Articles/MSDN section, he discusses this in depth. You can roll your own
update logic and take full control over what goes to the database and how it
gets there.

HTH,

Bill
 
Ok, I should point out the XML file is generated by
software completely unrelated to SQL... so I think when
you mention diffgrams etc you are assuming the XML file
was generated by, say, saving a dataset... woudl that be
right...?

What I have is an XML file... some of the data I need in
one table, and then I need to add a bunch of records to a
table (which has a relationship with the first table
mentioned).

Anyway, it's more the best method of doing a bulk update
I am interested in... I figure building a client side
recordset/dataset and pumping the data into it, and then
sending it in one go to the server is most efficient. But
not sure how to achieve this using ADO.Net.

Also, this will be in an NT service which will be running
on the SQL server (limited funds)... so does this affect
how this should be done?

Hope that is a little clearer...


Cheers
 
No, you can write XML from Any Dataset, SQL doesn't even have to come into
play at all.

Go ahead and just use WriteXML to get a feel for it. You can
programatically create dataset and write it out, but it will take a few
seconds. Let me see If I can find an example from where I did it...hold on
 
Hi Kev,

If I understand correctly, you need to send all the changes to the SQL
Server in one shot, right? If yes, then you could do this with SQL Server
2000. You could pass XML into stored procedure as a TEXT parameter. Then
inside of SP, you could use sp_xml_preparedocument, sp_xml_removedocument
and OPENXML to make actual manipulations with that data. I have tested it
some time ago and in most cases you will get at least two times performance
improvement
 
I know you can save a dataset as XML... that is
irrelevant to my question... the XML I am working with
has NOTHING to do with SQL or datasets... it is generated
by another application.

I am able to process the file fine but it's getting the
data into SQL most efficiently that I was asking about.
In particular sending a bunch of records for the same
table.

Anyway, I think the post from Val shows he read the
question so I'll see how far I get with that.

Cheers
 
Gidday Val,

I haven't used this method before but I can see value in
the approach... assuming it will work with any valid XML
file of course...

I didn't really think SQL was that efficient when it came
to text manipulation so that's why I was considering the
disconnected recordset/dataset being sent to SQL in one
go. I'll look into your suggestion and see where it leads
me...

Cheers
 
Back
Top