Save C# DataTable to Sql Server Table

Introduction

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 : http://goo.gl/418AKF

Basics

I have created a demo table product.product table definition

 

In c# project,we have a data table populated with some test data.

product table with data

 

Now we need to insert this c# table into sql server product table.

Old Way

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.

Best Practice

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

How to alter user defined table ?

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

Post A Reply