.NET : How to save DataTable to Database Table using C#

Recently I wanted to save DataTable data into the database. I assumed this would be easy using Entity Framework, but it turns out it is not, since DataTable consists of rows and columns while the Entity Framework deals with objects. Another way is to use ADO.NET by iterating through the rows in the DataTable and inserting each row using SqlCommand and INSERT statement, but there is an easier way to accomplish this and that is by using SqlBulkCopy class, which is the focus of this article.

Before we begin, note that SqlBulkCopy can only copy data to the database table, it cannot create either table or missing columns inside the database.

If you try to use SqlBulkCopy to copy DataTable to non-existing database table (for example 'MyTable'), it will give the following error message:

We can choose which columns from DataTable will be copied to a database table and can even map DataTable columns to different columns in a database table, but if we attempt to copy a DataTable column that is missing in a database table, then we get this error:

Now let's focus on the C# code. The code will first open a SQL connection with using statement. This way, we don't need to close the connection ourselves. Then we will map the columns in DataTable to the columns in the database table. Next, we set the destination table name in SqlBulkCopy and finally call WriteToServer method to save the data to the server.

The code is below and after that, the yellow highlighted lines are explained in more detail.

using System.Data.SqlClient;

string connectionString = @"Data Source = MyServerName/Instance; Integrated Security=true; Initial Catalog=YourDatabase";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        foreach (DataColumn c in myDataTable.Columns)
            bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);

        bulkCopy.DestinationTableName = myDataTable.TableName;
        try
        {
            bulkCopy.WriteToServer(myDataTable);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

Code Explanation

Let's take a closer look at those highlighted lines:

• Line 3:

  string connectionString = @"Data Source = MyServerName/Instance; Integrated Security=true; Initial Catalog=YourDatabase";
  

  First, we need to have a connection string. Set Data Source to your SQL server and instance. You also need to set Initial Catalog to your database name. By setting Integrated Security=true, we tell it to connect to the database using Windows Authentication. To learn more about Connection Strings and their parameters, check this article.

• Lines 9, 10:

  foreach (DataColumn c in myDataTable.Columns)
      bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
  

  This code will do the mapping between the DataTable columns and database Table Columns. In the above example, we assume that all columns in DataTable will map to the Columns with the same name in the SQL table, so the arguments in ColumnMappings.Add method are the same for both source and destination columns. You can also map the columns between different names or map it using column indexes instead of names.

• Line 12:

  bulkCopy.DestinationTableName = myDataTable.TableName;
  

  Here we set the SQL destination table that the sqlBulkCopy will use to DataTable name, but we could simply use hardcoded string.

• Line 15:

  bulkCopy.WriteToServer(myDataTable);
  

  This is where the saving of data happens. We use our DataTable object as an argument in WriteToServer method. The method is wrapped with try / catch block, so any exception will be caught and written to console.

And we are done.

Conclusion

If you need to copy data in the DataTable to SQL database table, you don't need to use ADO.NET, create an INSERT statement, and loop through each row, instead, you can simply use the SqlBulkCopy class using WriteToServer method that accepts a DataTable object as an argument.

I hope you have found the article useful. Did you have the same problem and if so, how did you solve it? Let us know in the comments.