Insert and Update in SQL Using User-Defined Table Type in C#
APPLIES TO: SQL Server and Azure SQL Database.
Let Start with [ SQL]
For creating a user-defined table type in SQL here is the procedure:
- First create a user-defined table type by selecting in Object Explorer.
- Inside that select your database.After selecting it just expand it.
- Inside that select the Programmability Folder. After selecting it just expand it.
- You will see a folder with Name Types.
- Just select and expand it and you will see a user-defined table type.
- Just right-click on the folder and select “New User-Defined Table Type…”.
Here is a Snapshot.
After selecting you will see this view.
Then I created a Table (SQL Table).
|
1 2 3 4 5 6 |
CREATE TABLE Vinsert ( Vid INT primary key Identity (1,1) not null, Name NVARCHAR(100), Age INT ) |
Here I created a user-defined type.
|
1 2 3 4 5 |
CREATE TYPE UDT_Vinsert AS TABLE ( Name varchar(100), Age Int ) |
Here in this Stored Procedure I used “User_Define_Table_Type”.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE [dbo].[Usp_Vinsert] @UserDefineTable UDT_Vinsert readonly --- Here i am assign User_Define_Table_Type to Variable and making it readonly AS BEGIN INSERT INTO Vinsert(Name,Age) SELECT Name, Age from @UserDefineTable  -- Here i am Select Records from User_Define_Table_Type END |
Let us Start with C# Here I am passing a Datatable to SQL. Here is the connection String.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ToString()); protected void btnsave_Click(object sender, EventArgs e) { DataTable DT = new DataTable(); DT.Columns.Add("Name", typeof(string)); DT.Columns.Add("Age", typeof(int)); DataRow DR = DT.NewRow(); DR["Name"] = txtname.Text; DR["Age"] = txtage.Text; DT.Rows.Add(DR); DatasetInsert(DT); //calling datatable method here } public void DatasetInsert(DataTable dt) { con.Open(); SqlCommand cmd = new SqlCommand("Usp_Vinsert", con); cmd.Parameters.AddWithValue("@UserDefinTable", dt); // passing Datatable cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); con.Close(); } |
Example INSERT AND UPDATE WITH MERGE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE PROCEDURE [dbo].[InsUpdAge] @UserDefineTable UDT_Vinsert readonly AS BEGIN MERGE UDT_Vinsert AS target USING @UserDefineTable AS source ON target.Name= source.Name WHEN MATCHED THEN UPDATE SET target.Age= source.Age WHEN NOT MATCHED THEN INSERT (Age) VALUES (source.Age) |
