Append DataSet To Existing Table

[Wayne Wengert]

I am using VB ASP.NET. In my page I convert an uploaded XML file to a
dataset as follows:

Dim ds1 As DataSet = New DataSet

ds1.ReadXml(strPathName, XmlReadMode.Auto)

Now I want to append all the rows of ds1 to an existing table in an SQL
Server database. I know I can do things like looping through the dataset and
issuing Update SQL statements to do this but I suspect there is a better
way? I tried Google but am not getting any usable hits. Does anyone have a
suggestion on a better way to accomplish adding the contents of a dataset to
an existing table?



Wayne

 

[Guest]

Hi Wayne,

You can use the DataAdapter class to get your changes from the DataSet into
the SQL Server database. Microsoft has published a HowTo article with some
sample source code on how to do this:
http://support.microsoft.com/kb/308055/EN-US/

Cheers,
Gwynn

 

[Wayne Wengert]

Thanks for that pointer Gwynn. I'll check that out.

Wayne

 

[Wayne Wengert]

Gwynn;

That article seems to simply show how the Update, Insert and Delete commands
can be built when using a Select for a single table. The Update/Insert apply
to the original table. In my case, I am getting the dataset from an XML file
and writing the output to an SQL table. For right now I simply coded the
Insert command manually but it seems like there ought to be a more efficient
way?

Wayne

 

[Guest]

Wayne,

What do you mean by more efficient? More efficient as in less source code or
more efficient as in executing faster during runtime?

The article shows how you specify a Select statement for the DataAdapter so
that the CommandBuilder can dynamically determine the SQL needed to execute
the Update, Insert, and Delete commands. This helps out if you're not all
that familiar with SQL.

It sounds like you've coded up the Insert statement yourself in which case
you wouldn't need the CommandBuilder code but would still need to use the
DataAdapter to update the SQL Database.

Could you please post your source code so I can check it out?

Cheers,
Gwynn

 

[Wayne Wengert]

I create the dataset from an XML file. I do not issue any Select statement.
That is part of the difference!

Wayne