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

save datatable to database-in-dotnet-using-csharp

Recently I wanted to save DataTable data into 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 insert 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:

Cannot access destination table 'MyTable'

We can choose which columns from DataTable will be copied to a database table and can even map a 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:

The given ColumnMapping does not match up with any column in the source or destination.

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.

    Tags:

    ,

Share this page

2 Comments

Add a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top