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

Answer 1

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?

Answer 2

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.