Below is a simple method that can store complete datatable to database in a go.
private static bool StoreInDataBase(DataTable dtDetails, string connectionString, string tableName)
{
if (dtDetails != null && dtDetails.Rows.Count > 0)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
{
bulkcopy.DestinationTableName = tableName;
try
{
bulkcopy.WriteToServer(dtDetails);
}
catch (Exception ex)
{
{
if (dtDetails != null && dtDetails.Rows.Count > 0)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
{
bulkcopy.DestinationTableName = tableName;
try
{
bulkcopy.WriteToServer(dtDetails);
}
catch (Exception ex)
{
//Log Exception
return false;
}
}
connection.Close();
}
}
return true;
}
return false;
}
}
connection.Close();
}
}
return true;
}
Its better than iterating complete dataTable and writing one record at a time.