Best way to Bulk Insert from a C DataTable

Question

I have a DataTable that I want to push to the DB  I want to be able to say like   myDataTable update      But after reading the MSDN docs  apparently this does inserts row by row       It should be noted that these statements are not performed as a batch process  each row is updated individually    What are my alternatives   Edit  I am using SQL Server 2005

User · Answer

Here s how I do it using a DataTable   This is a working piece of TEST code   using  SqlConnection con   new SqlConnection connStr         con Open            Create a table with some rows       DataTable table   MakeTable            Get a reference to a single row in the table       DataRow   rowArray   table Select         using  SqlBulkCopy bulkCopy   new SqlBulkCopy con                 bulkCopy DestinationTableName    dbo CarlosBulkTestTable            try                          Write the array of rows to the destination              bulkCopy WriteToServer rowArray                     catch  Exception ex                        Console WriteLine ex Message                        using

User · Answer

This is going to be largely dependent on the RDBMS you re using  and whether a  NET option even exists for that RDBMS   If you re using SQL Server  use the SqlBulkCopy class   For other database vendors  try googling for them specifically  For example a search for   NET Bulk insert into Oracle  turned up some interesting results  including this link back to Stack Overflow  Bulk Insert to Oracle using  NET

User · Answer

string connectionString  ServerName   DatabaseName   SecurityType  using  SqlConnection connection   new SqlConnection connectionString   using  SqlBulkCopy bulkCopy   new SqlBulkCopy connection         connection Open        bulkCopy DestinationTableName    TableName       try           bulkCopy WriteToServer dataTableName         catch  Exception e            Console Write e Message             Please note that the structure of the database table and the table name should be the same or it will throw an exception

User · Answer

If using SQL Server  SqlBulkCopy WriteToServer DataTable    SqlBulkCopy WriteToServer Method  DataTable    Or also with SQL Server  you can write it to a  csv and use BULK INSERT   BULK INSERT  Transact-SQL    If using MySQL  you could write it to a  csv and use LOAD DATA INFILE   LOAD DATA INFILE Syntax   If using Oracle  you can use the array binding feature of ODP NET   Bulk Insert to Oracle using  NET   If SQLite    How do I bulk insert with SQLite  Faster bulk inserts in sqlite3

User · Answer

SqlBulkCopy class is best for SQL server   Doing Bulk Upload Insert of DataTable to a Table in SQL server in C

[c#] Best way to Bulk Insert from a C# DataTable

Examples related to c#

Examples related to .net-2.0