Creating a datatable on a SQL server from a dataset

This is probably covered somewhere, but I am having a total brain
cramp.

I have a DataSet that gets loaded from an XML file. I want to do some
processing on it and then save it to a SQL Server data table. I am
currently doing by the brute force expedient of creating a
SqlConnection, building a string that contains the SQL statements to
CREATE the data table and then executing the SQL via an
ExecuteNonQuery.

It works, but there is a very nagging loud voice in my head telling me
that I am doing it the hard way.

If anyone know of an easier way or has a link to some sample code that
does a similar type of process, I would be greatly appreciative.

TIA,

If it ain't broke....fix it anyway!

[To Email Replies Remove the nospam from the address]

William
Tue Oct 03 00:29:51 CDT 2006

I would try to create a DataReader from the XML and pass it to SqlBulkCopy.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

"Yellow Dog" <BigYellowDog@nospam.houston.rr.com> wrote in message
news:obq3i21s6aa6v50bsgp0rocc3905p8esuj@4ax.com...
>
> This is probably covered somewhere, but I am having a total brain
> cramp.
>
> I have a DataSet that gets loaded from an XML file. I want to do some
> processing on it and then save it to a SQL Server data table. I am
> currently doing by the brute force expedient of creating a
> SqlConnection, building a string that contains the SQL statements to
> CREATE the data table and then executing the SQL via an
> ExecuteNonQuery.
>
> It works, but there is a very nagging loud voice in my head telling me
> that I am doing it the hard way.
>
> If anyone know of an easier way or has a link to some sample code that
> does a similar type of process, I would be greatly appreciative.
>
> TIA,
>
> If it ain't broke....fix it anyway!
>
> [To Email Replies Remove the nospam from the address]

Yellow
Thu Oct 05 01:15:50 CDT 2006

On Mon, 2 Oct 2006 22:29:51 -0700, "William \(Bill\) Vaughn"
<billvaRemoveThis@nwlink.com> wrote:

>I would try to create a DataReader from the XML and pass it to SqlBulkCopy.

Thanks for the reply. I guess I was a little vague on the question. I
can insert the records into the SQL data table without any problems
(although I had not tried SqlBulkCopy). The code I have walks the
DataTable and inserts the rows.

What I am looking for is some way to take an ADO.NET DataTable loaded
from an Xml file and to create the corresponding SQL data table. The
wrinkle is that at the time I load the Xml file, I don't know what the
schema of the Xml file is, so I can't create the SQL data table
without parsing the Xml schema and creating the new SQL data table "on
the fly".

I was hoping there was some simple mechanism that would allow me to
create a new Sql data table from a DataTable object.

SqlBulkCopy needs the Sql data table to be created before the copy is
initiated.


Thanks
If it ain't broke....fix it anyway!

[To Email Replies Remove the nospam from the address]