How to Insert a whole dataset into database

[ad]

I use WebService upload a dataset to server.
There is only one DataTable in the DataSet.
I want to insert all rows of that DataSet into SQL Server 2005.

Is it necessay to insert rows one by one into database?
If this is true, I must write long SQL statement to do it.

Can I inert the whole dataset into database at a once?

 

[Cor Ligthert [MVP]]

Ad,

The only thing you need is that you are sure that the rowstate of every row
is new added.
(This is normal if you add a row) and than you can create your own
insertcommand for the dataadapter.

The most simple one by that is let the designer do it, and than copy and
paste it from the code.

(Be aware that you can than get errors because already exisiting rows)

(You can as well use the commandbuilder by the way).

I hope this helps,

Cor.

 

[Francois Malgreve]

I believe that if you use SQL Server 2005 you might use ADO.NET 2 and I
think that the new DataSet has a bulk insert feature. I read an article
about that long time ago but I never used it as we do not use 2.0 where i
work. Well it's not realesed yet

If you use .Net 1.1 then you might have to call DTS to insert large amount
of data. I am doing that by saving the DataSet into a file (Excel file) and
then call a DTS from the C# code. I am not sure if this is the best way but
i could not think of anything else to avoid to execute an SQL statements
50.000 times if my dataset contains 50.000 rows.

By the way I do not understand how the post of Cor helps in not calling the
SQL statement for each rows you want to insert....

Francois

 

[Paul Clement]

¤ I use WebService upload a dataset to server.
¤ There is only one DataTable in the DataSet.
¤ I want to insert all rows of that DataSet into SQL Server 2005.
¤
¤ Is it necessay to insert rows one by one into database?
¤ If this is true, I must write long SQL statement to do it.
¤
¤ Can I inert the whole dataset into database at a once?


Not directly no. You can export and then import using XML but that is the only method I am aware of.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Francois,

By the way I do not understand how the post of Cor helps in not calling
the
SQL statement for each rows you want to insert....

If you *have* a dataset
Than you can create an SQL dataadapter

Than you can use a commandbuilder. There is not a language given so
probably even this will go (so see it as pseudo however not to far from
reality and this works only when there are not more than 100 columns in the
datatable.).

\\\\
dim ds2 as new dataset
dim da as new sqlclient("Select * from TheTable, ConnectionObject)
da.fillschema(ds2)
dim cbm as new commandbuilder(da)
da.update(TheOriginalDataset, TheDataTableToInsert)
///

Actually will by the dataadapter every row independently be processed,
however it is not calling it yourself for each row that has to be inserted.

If you want to copy a datatable than I do not prefer this method.

I hope this answers your question?

Cor

 

[S.M. Altaf [MVP]]

I am surprised that nobody has suggested Stored Procedures yet. You can create a simple loop for your newly changed rows, and pass those values to the stored procedure. Let the SP do the work for you, and no long SQL statements.

HTH
Altaf




--------------------------------------------------------------------------------
All that glitters has a high refractive index.
www.mendhak.com
I use WebService upload a dataset to server.
There is only one DataTable in the DataSet.
I want to insert all rows of that DataSet into SQL Server 2005.

Is it necessay to insert rows one by one into database?
If this is true, I must write long SQL statement to do it.

Can I inert the whole dataset into database at a once?

 

[ad]

Thanks,

How can we change the rowstate of every row in my dataset to new added?




Cor Ligthert [MVP] 寫é“：

 

[Cor Ligthert [MVP]]

Ad,

How can we change the rowstate of every row in my dataset to new added?

That depends how you make the dataset, there are a lot of possibilities.

Cor

 

[ad]

Thanks,
My dataset is create by select command, like:
//--------------------------------------------------------------------
SqlCommand myCommand = new SqlCommand("SELECT * FROM Suppliers",
myConnection);
myCommand.CommandType = CommandType.Text;


myAdapter.SelectCommand = myCommand;
ds = new DataSet("Customers");
myAdapter.Fill(ds);
//--------------------------------------------------------------------

The rowstate of every datarow is Unchanged.
How can I change them to Added



Cor Ligthert [MVP] 寫é“：

 

[Cor Ligthert [MVP]]

Ad,

Have a look as well to the advices given by others by Francois as you do it
like this.

However the remedy for this is very simple.

You need only to set the property of the acceptchangesduringfill to false.

http://msdn.microsoft.com/library/d...aadapterclassacceptchangesduringfilltopic.asp

I hope this helps,

Cor

 

[ad]

Thanks,
I am use Sql Sever 2005 and ADO.NET 2.
I can't find any bulk insert article.
Could you tell me more detail?

 

[Paul Clement]

¤ I am surprised that nobody has suggested Stored Procedures yet. You can create a simple loop for your newly changed rows, and pass those values to the stored procedure. Let the SP do the work for you, and no long SQL statements.
¤
¤ HTH
¤ Altaf
¤

Yeah, I think it's the looping part (for all rows) that's going to make it real slow.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Ad,

There is an sample for that on MSDN (The data part than you see it)

http://msdn.microsoft.com/vbasic/downloads/2005/code/101samples/default.aspx

The same are there for C# in another section

I hope this helps,

Cor

 

[Francois Malgreve]

Well I guess that the original question was how to import a full dataset
efficiently (fast).

Using SQL statements, stored procedure and SQLDataAdapter it's all the same.
For 40.000 rows you need to do 40.000 inserts. For our situation, I cannot
see much differences between all those ways.

The ADO.NET 1.1 you can use DTS for you to do the job, or through XML.
I have problem doing that in ADO.NET 1.1 but I will post that in an other
post, after the weekend

But for ADO.NET 2.0, I believe that this kind may be greatly simplified. Ad,
as you asked for a few articles, check those 2 links. But with a little
research on google you will find much more articles, but those 2 links are
all you need, I believe.
http://www.c-sharpcorner.com/Code/2004/June/BulkCopy.asp
http://msdn2.microsoft.com/en-us/library/30c3y597(en-US,VS.80).aspx

Hope this helps,

Francois.