In this article, I would like to explain the fastest way to save c# datatable to sql server table using user defined table type.
Download project from : https://goo.gl/k5R79w
I have created a demo table product.
In c# project,we have a data table populated with some test data.
Now we need to insert this c# table into sql server product table.
if you don’t know this new way of insertion. you will do something like below
foreach (var item in dtblproduct.Rows)
//save item into product table
there is no wrong in doing this. but insertion of tables with more rows will affect your application perfomance.
Step 1 : create user defined table
CREATE TYPE [dbo].[udtProduct] AS TABLE(
[ProductID] [int] NULL,
[ProductName] [varchar](50) NULL,
[ProductCode] [varchar](10) NULL
Step 2 : create stored procedure with user defined type
CREATE PROCEDURE ProductBulkInsertion
INSERT INTO Product
Step 3 : execute stored procedure from c#
SqlCommand sqlcmd = new SqlCommand("ProductBulkInsertion", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
productTable : c# dataTable which is to be inserted
There is no command to alter or modify user defined type in sql server.you need to drop and recreate the type.
But in management studio we can recreate type using script.
1.generate script for the type.(in new query window or as a file)
2.delete user defied table.
3.modify the create script and then execute.
? Download project from : http://goo.gl/418AKF
? video tutorial : watch from here