Insert and Update in SQL Using User-Defined Table Type in C#

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:

1. First create a user-defined table type by selecting in Object Explorer.
2. Inside that select your database.After selecting it just expand it.
3. Inside that select the Programmability Folder. After selecting it just expand it.
4. You will see a folder with Name Types.
5. Just select and expand it and you will see a user-defined table type.
6. 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).

CREATE TABLE Vinsert
(
 Vid INT primary key Identity (1,1) not null,
 Name NVARCHAR(100),
 Age INT
)

Here I created a user-defined type.

CREATE TYPE UDT_Vinsert AS TABLE
(
 Name varchar(100),
 Age Int
)

Here in this Stored Procedure I used “User_Define_Table_Type”.

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.

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

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)