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:
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:
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:
Here we set the SQL destination table that the sqlBulkCopy will use to DataTable name, but we could simply use hardcoded string.
-
Line 15:
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.
ian hayse
June 18, 2017Thank you for a clearly laid out example!
Riyas
August 12, 2017how can we remove if datatable contains empty rows?
thanks in advance
Mehboob A Chhipa
April 30, 2020Saved my day, in 5 minute I created and dumped the spreadsheets into sql server.
Thx
chagbert
May 9, 2023Thanks for this insight. 2 ques:
1. How can we pass the column names (in the case when DT is different from actual DB table);
2. Does this function (SqlBulkCopy) UPDATE existing DB table records or INSERT them as new records; or does BOTH (attempts to update first then inserts if not found) ..
Many thanks
admin
May 10, 2023Hi,
It has been years since I used SqlBulkCopy, but here are two links of the possible solutions to your issues.
First about Mapping to Different Columns problem.
Regarding the 2nd question, the SqlBulkCopy is primarily designed for inserting new records into a database table rather than updating existing records, but there are workarounds, such as this one.