DataTable optimal saving to sql database

Question

Hello, I have DataTable with same structure in SqlServer DB Table. To save data in a this DataTable to Sql DB I use code below:

sqlCommand mcd;

for(int i=0;i<dataTable.Rows.Count;i++)
{
mcd=new SqlCommand();
cmd.CommandText="Insert into MSSQLtable values("+dataTable.Rows[i][0]+dataTable.Rows[i][1]+")";
cmd.ExecuteNonQuery();

}

How can I Insert Data with another way?

Answer 1

There's a few other ways, check out the blog post I wrote here on high performance bulk loading to SQL Server from .NET.

To summarise, you can use an SqlDataAdapter to perform batch inserts (or updates), or for better performance you can use SQLBulkCopy (inserts only). The SqlDataAdapter approach demonstrated there took ~25s to insert 100,000 records into the database, compared to ~0.8s for SqlBulkCopy.

There's pros/cons to each approach - e.g. SqlDataAdapter is handy if you want to handle errors nicely and continue in the event of an error with a particular row.