Description
A .NET DataTable is an memory representation of an MS SQL Server table. DataTable object allows you to create the table in memory, add rows to it, edit values in specific columns of a row, etc, until all the data is exactly what you want. In this article, I am going write C# code examples to Insert DataTable into SQL Table using SQLBulkCopy and using SQL Table-Valued Parameters.
Summary
- Insert DataTable into SQL Table using SQLBulkCopy
- Insert DataTable into SQL Table using Table-Valued Parameters
Insert DataTable into SQL Table using SQLBulkCopy in C#
This is an easy and recommended method to Insert DataTable content into SQL Table in C#. Once the DataTable is ready, it is just a simple statement to insert all the rows at once. So rather than hundreds of thousands of insert statements, it is just one bulk copy, and rather than taking minutes or longer to run, it just takes seconds to dump all the records into sql table.
Insert DataTable into SalesHistory SQL Table:
public static void InsertDataTableintoSQLTableusingSQLBulkCopy()
{
DataTable salesData = GetSalesData();
using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.SQLExpress;
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
{
sqlconnection.Open();
// create table if not exists
string createTableQuery = @"Create Table [SalesHistory]
( SaleDate datetime, ItemName nvarchar(1000),ItemsCount int)";
SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
command.ExecuteNonQuery();
// Copy the DataTable to SQL Server Table using SqlBulkCopy
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlconnection))
{
sqlBulkCopy.DestinationTableName = salesData.TableName;
foreach (var column in salesData.Columns)
sqlBulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
sqlBulkCopy.WriteToServer(salesData);
}
}
}
Get Sales History DataTable Content:
private static DataTable GetSalesData()
{
DataTable salesHistory = new DataTable("SalesHistory");
// Create Column 1: SaleDate
DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType("System.DateTime");
dateColumn.ColumnName = "SaleDate";
// Create Column 2: ItemName
DataColumn productNameColumn = new DataColumn();
productNameColumn.ColumnName = "ItemName";
// Create Column 3: ItemsCount
DataColumn totalSalesColumn = new DataColumn();
totalSalesColumn.DataType = Type.GetType("System.Int32");
totalSalesColumn.ColumnName = "ItemsCount";
// Add the columns to the SalesHistory DataTable
salesHistory.Columns.AddRange(new DataColumn[] { dateColumn, productNameColumn,
totalSalesColumn });
// Let's populate the datatable with our stats.
// You can add as many rows as you want here!
// Create a new row
DataRow dailyProductSalesRow = salesHistory.NewRow();
dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
dailyProductSalesRow["ItemName"] = "Nike Shoe-32";
dailyProductSalesRow["ItemsCount"] = 10;
// Add the row to the SalesHistory DataTable
salesHistory.Rows.Add(dailyProductSalesRow);
return salesHistory;
}
Insert DataTable into SQL Table using SQL Table-Valued Parameters in .NET C#
This is an another method to Insert DataTable rows into SQL Table in C#. SQL Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use Table-Valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command.
Insert DataTable into SalesHistory SQL Table by Table-Valued Parameter:
public static void InsertDataTableintoSQLTable()
{
DataTable salesData = GetSalesData();
using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.SQLExpress;
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
{
sqlconnection.Open();
// create table if not exists
string createTableQuery = @"Create Table [SalesHistory]
( SaleDate datetime, ItemName nvarchar(1000),ItemsCount int)";
SqlCommand createCommand = new SqlCommand(createTableQuery, sqlconnection);
createCommand.ExecuteNonQuery();
// create type if not exists
string createTypeQuery = @"CREATE TYPE dbo.SalesHistoryTableType AS TABLE
(SaleDate datetime, ItemName nvarchar(1000),ItemsCount int)";
createCommand = new SqlCommand(createTypeQuery, sqlconnection);
createCommand.ExecuteNonQuery();
// Copy the DataTable to SQL Server Table using Table-Valued Parameter
string sqlInsert = "INSERT INTO [SalesHistory] SELECT * FROM @SalesHistoryData";
SqlCommand insertCommand = new SqlCommand(sqlInsert, sqlconnection);
SqlParameter tvp= insertCommand.Parameters.AddWithValue("@SalesHistoryData",salesData);
tvp.SqlDbType = SqlDbType.Structured;
tvp.TypeName = "dbo.SalesHistoryTableType";
insertCommand.ExecuteNonQuery();
}
}
Get DataTable Content to Insert into SQL Table:
private static DataTable GetSalesData()
{
DataTable salesHistory = new DataTable("SalesHistory");
// Create Column 1: SaleDate
DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType("System.DateTime");
dateColumn.ColumnName = "SaleDate";
// Create Column 2: ItemName
DataColumn productNameColumn = new DataColumn();
productNameColumn.ColumnName = "ItemName";
// Create Column 3: ItemsCount
DataColumn totalSalesColumn = new DataColumn();
totalSalesColumn.DataType = Type.GetType("System.Int32");
totalSalesColumn.ColumnName = "ItemsCount";
// Add the columns to the SalesHistory DataTable
salesHistory.Columns.AddRange(new DataColumn[] { dateColumn, productNameColumn,
totalSalesColumn });
// Let's populate the datatable with our stats.
// You can add as many rows as you want here!
// Create a new row
DataRow dailyProductSalesRow = salesHistory.NewRow();
dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
dailyProductSalesRow["ItemName"] = "Nike Shoe-32";
dailyProductSalesRow["ItemsCount"] = 10;
// Add the row to the SalesHistory DataTable
salesHistory.Rows.Add(dailyProductSalesRow);
return salesHistory;
}