c# save dataset to database

grahamor

Hi, I am generating a dataset from a webservice and i would like to save the whole dataset into one database table.

Do i need to loop through the dataset (only 1 table) and call a sproc for every row or is there an easier way ?

Thanks in advance

FindingNemo

grahamor wrote: »

Hi, I am generating a dataset from a webservice and i would like to save the whole dataset into one database table.

Do i need to loop through the dataset (only 1 table) and call a sproc for every row or is there an easier way ?

Thanks in advance

link should sort you out

http://quickstart.developerfusion.co.uk/QuickStart/howto/doc/adoplus/UpdateDataFromDB.aspx

lastbuilders

I would suggest using a dataadapter to save it. There is an example here. You will probably need to update the RowState of the elements to use it. Worth a look anyway.

http://msdn2.microsoft.com/en-us/library/xzb1zw3x(VS.80).aspx

Lastbuilders

Ginger

Just serialise your dataset and use XML parameters with SQL 2005 to save the data. its quick and dirty and works well.

evilhomer

Depending on the size of your dataset and the performance you require.

BCP is always good, here.

But if your SQL Server instance is on another box you may require a shared folder (not really a good idea if your webservice is exposed to the internet)

All of the options people have given will work, it's just down to which one is best for you and your particular situation.

Ginger

evilhomer wrote: »

BCP is always good, here.

And its extremely unneccessary in this scenario.. Why would you call an external program to do what you can do through code natively???

evilhomer wrote: »

But if your SQL Server instance is on another box you may require a shared folder (not really a good idea if your webservice is exposed to the internet)

Again not neccessary, ever heard of ADO.NET or ODBC???

evilhomer

Ginger wrote: »

And its extremely unneccessary in this scenario.. Why would you call an external program to do what you can do through code natively???

You're right, it probably is unnecessary. But if the dataset is big enough (highly unlikely in this case) then the performance gains are worth it. I frequently work with datasets over 500Mb that need to be put into DB's.

We use bulk insert mostly, so you wouldn't need to call out to the bcp program, just call your stored procedure with the correct parameters.

Ginger wrote: »

Again not neccessary, ever heard of ADO.NET or ODBC???

Again sorry, I'm still thinking in ADO.NET in .NET 1.1 which doesn't support SqlBulkCopy. Serves me right for still working in .NET 1.1 all the time(although not by choice)

I'm pretty sure that the ODBC standard specification doesn't include support for bulk copy, regardless of whether it exists or not. Performing Bulk Copy Operations (ODBC)

Edit: And out of curiosity, how would you get access to the file you have written to on your local machine to do the insert, if you didn't have a share of some sort?

Ginger

500MB is a hella large dataset. Have a look at the service broker in SQL 2005, it could provide you some better relialibity.

As for this issue, i think the OP has enough info to do it in a couple of different ways.

evilhomer

Ginger wrote: »

500MB is a hella large dataset. Have a look at the service broker in SQL 2005, it could provide you some better relialibity.

As for this issue, i think the OP has enough info to do it in a couple of different ways.

Yeah I have had a look at the service broker recently, it's pretty good stuff, need to get sometime to experiment with it now

grahamor

Thanks for the tips guys but i'm having trouble getting this to work....

            DataSet ds = //get info from webservice

            SqlDataAdapter sa = new SqlDataAdapter("select * from country", config.dbConnection);

            sa.AcceptChangesDuringFill = false;

            sa.Fill(ds);
            
            sa.Update(ds.Tables[0]);

            sa = null;
            ds = null;

The dataset is deffo getting generated but when i try and update the SqlDataAdapter nothing happens (no error is thrown either)

I dont really need the select statement there either, i just want to insert all the info from the dataset into a blank db table.

Thanks again