Reputation: 309
tried to insert many records of a data table to SQL table by stored procedure.I need insert data table's records all together.
I'm using C# programming language and i want to send many records to SQL server by ADO.net stored procedure all together. i want to know about data table types and use it if that helps me.
Upvotes: 0
Views: 2136
Reputation: 46425
To pass many rows efficiently to a stored procedure, use a table-valued parameter. The C# app can specify parameter type structured and a value of a data table. For maximum performance, make sure the DataTable
column types match the server-side table type column types (including max length for string columns).
Below is an example excerpt from the documentation link above:
// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);
// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
// Execute the command.
insertCommand.ExecuteNonQuery();
}
Here are T-SQL snippets to create the table type and proc.
CREATE TYPE dbo.CategoryTableType AS TABLE
( CategoryID int, CategoryName nvarchar(50) );
GO
CREATE PROC dbo.usp_InsertCategories
AS
@tvpNewCategories dbo.CategoryTableType READONLY
INSERT INTO dbo.Categories (CategoryID, CategoryName)
SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;
GO
Even for trivial inserts, TVP performance can provide performance similar to SqlBulkCopy (many thousands per second) with the advantage of a stored procedure interface.
Upvotes: 1