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
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
prefix udt indicate user defined table.
Step 2 : create stored procedure with user defined type
Step 3 : execute stored procedure from c#
sqlcon : sql connection object
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