insert many rows from tableadapter (vb2005)

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

I have to insert a lot of rows from a tableadapter to a sql table.
The operation is slow because table adptert send a single insert command for
each row.
Is there any way to have it with only one batch command? Or a faster
solution?

Thank you
Alessandro
 
http://msdn2.microsoft.com/en-us/library/aadf8fk2.aspx
In ADO.NET 2.0, the DbDataAdapter exposes an UpdateBatchSize property.
Setting the UpdateBatchSize to a positive integer value causes updates to
the database to be sent as batches of the specified size. For example,
setting the UpdateBatchSize to 10 will group 10 separate statements and
submit them as single batch. Setting the UpdateBatchSize to 0 will cause the
DataAdapter to use the largest batch size that the server can handle.
Setting it to 1 disables batch updates, as rows are sent one at a time.

http://msdn2.microsoft.com/en-us/library/system.data.common.dbdataadapter.updatebatchsize.aspx
 
ADO.NET was not (originally) intended to perform bulk operations. However in
v 2.0 they added the SqlBulkCopy method that can pass rows back up to the
server far, far faster than any Tableadapter solution.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Thanks for Misbah and William's prompt reply.

Hello Alessandro,

DbDataAdapter.UpdateBatchSize Property is a value that indicates the number
of commands that can be executed in a batch. This could achieve what you
need. By the way, don't forget to set the UpdatedRowSource property of your
InsertCommand to None or OutputParameters. Otherwise, an exception will be
thrown.

SqlBulkCopy method is another solution if you just want to insert new rows
into table. This method is much faster than the TableAdapter solution. But,
you may re-design your solution.
http://technet.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy
..aspx
[SqlBulkCopy Class]

Hope this helps. Let me know if there is anything unclear, we are glad to
assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top