How do I do this...?

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...

William
Thu Dec 18 20:06:36 CST 2003

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
"Kev" <kev@nospam.com> wrote in message
news:03ba01c3c5cf$f49b33a0$a601280a@phx.gbl...
> 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...

Kev
Thu Dec 18 20:21:55 CST 2003

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

>-----Original Message-----
>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
>"Kev" <kev@nospam.com> wrote in message
>news:03ba01c3c5cf$f49b33a0$a601280a@phx.gbl...
>> 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...
>
>
>.
>

William
Thu Dec 18 20:53:33 CST 2003

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
"Kev" <kev@nospam.com> wrote in message
news:03ba01c3c5cf$f49b33a0$a601280a@phx.gbl...
> 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...

Val
Thu Dec 18 21:18:35 CST 2003

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

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp


"Kev" <kev@nospam.com> wrote in message
news:03ba01c3c5cf$f49b33a0$a601280a@phx.gbl...
> 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...

Kev
Thu Dec 18 22:30:22 CST 2003

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

>-----Original Message-----
>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
>"Kev" <kev@nospam.com> wrote in message
>news:03ba01c3c5cf$f49b33a0$a601280a@phx.gbl...
>> 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...
>
>
>.
>

Kev
Thu Dec 18 22:32:49 CST 2003

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


>-----Original Message-----
>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
>
>--
>Val Mazur
>Microsoft MVP
>Check Virus Alert, stay updated
>http://www.microsoft.com/security/incident/blast.asp
>
>
>"Kev" <kev@nospam.com> wrote in message
>news:03ba01c3c5cf$f49b33a0$a601280a@phx.gbl...
>> 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...
>
>
>.
>