Use case
You want to insert multiple rows (say from 2 to 999 rows) from your .NET application to a database table.
You are using .NET Framework 3.5 or above with SQL Server 2008 or above.
Approaches
Insert one row at a time
As you can imagine, this would have a performance hit because of too many connections getting opened/closed – more chatty
Use a CSV of rows
This approach is chunkier than the above approach. Overall, below is the approach:-
– Create a comma separated string of rows from the application
– Send CSV to a stored procedure from application
– The stored procedure would make use of a UDF to parse the CSV into a table variable
– The stored procedure would insert data from the table variable to the actual table
Use SqlDbType.Structured
The above approach solves the problem of the application being too chatty to the database. However, It’s not elegant and involves too much of “manual” parsing of string.
In .NET Framework 3.5 and onwards, SqlCommand can make use of another parameter type named SqlDbType.Structured which enables a .NET application to send a DataTable (yes, a “System.Data.DataTable” object) directly to the stored procedure which can be directly used as a table inside the stored procedure as If It was a table in the database.
In the below example we will send a DataTable of email addresses from .NET application to a database stored procedure which would insert data directly from this table to the actual table.
Steps
Database changes:-
/*Create a user-defined table type which will hold the contents of the DataTable passed from the application.
This will be used as a Table Valued Parameter.
*/
CREATE TYPE [dbo].[EmailAddressList] AS TABLE
(
[EmailAddress] [NVARCHAR](100) NULL
);
/*Create the actual table to which we will insert data from the DataTable*/
CREATE TABLE EmailAddressDetails
(
EmailAddress NVARCHAR(100),
CreatedOn DateTime DEFAULT GetDate()
)
/*Create the stored procedure which will be called from the application*/
CREATE PROCEDURE EmailAddresses_InsertBatch
@EmailAddressBatch [EmailAddressList] READONLY
AS
BEGIN
INSERT INTO EmailAddressDetails (EmailAddress)
SELECT E.EmailAddress FROM @EmailAddressBatch E
END
Application changes
//Function to create a DataTable with dummy email addresses
//The DataTable created here should match the schema of the User defined table type created above.
private DataTable CreateEmailAddressDataTable()
{
DataTable emailAddressDT = new DataTable();
emailAddressDT.Columns.Add(“EmailAddress”, typeof(string));
int emailAddrressCount = 100;
for (int i = 0; i < emailAddrressCount; i++)
{
DataRow row = emailAddressDT.NewRow();
row[“EmailAddress”] = i.ToString() + “.something@xyz.com”;
emailAddressDT.Rows.Add(row);
}
return emailAddressDT;
}
//Function to call the stored procedure with DataTable
private void AddEmailAddressToDb()
{
DataTable dataTable = CreateEmailAddressDataTable();
string connectionString = “Server=YourServerName;Database=YourDatabaseName;UserId=ashish;Password=ashish;”;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = “EmailAddresses_InsertBatch”;
command.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter(“@EmailAddressBatch”, SqlDbType.Structured);
param.TypeName = “dbo.EmailAddressList”;
param.Value = dataTable;
command.Parameters.Add(param);
command.ExecuteNonQuery();
}
}
}
The above line highlighted in green is the gem in ADO.NET. 🙂 When we send DataTable the stored procedure, It populates the user defined table type which we can directly use in the stored procedure – inserting from it to the actual table in this case. No parsing of CSV in UDF – takes away big pain when you have a complex structure.
Note:– Microsoft recommends this to be used when you are inserting less than 1000 rows. For more rows, consider using SqlBulkCopy.
Ashish Gupta 