Insert and Update in SQL Using User-Defined Table Type and XML

Introduction

In this article, I show various ways to Insert and Update in SQL Using User-Defined Table Type and XML.

• Using Datatable
• Using XML

Insert Records using a Datatable

Let us Start with C#

In this example, I will insert only two records

• Name
• Age

Here is a Snapshot.

For inserting into a Datatable, I am creating a Datatable with 2 Columns.

• Name
• Age

DataTable DT = new DataTable();   // A Data table .  

Adding 2 Columns

DT.Columns.Add("Name",typeof(string));  
DT.Columns.Add("Age",typeof(int));  

Adding new Datarow in Datatable

DataRow DR = DT.NewRow();  
DR["Name"] = txtname.Text; // adding field value  
DR["Age"] = txtage.Text;   // adding field value  

Adding Datarow into Datatable

DT.Rows.Add(DR); 

 Let's 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 Object Explorer.
2. Inside that, select your database.
   After selecting it, expand it.
3. Inside that, select the Programmability Folder.
   After selecting it, expand it.
4. You will see a folder with Name Types.
5. Select and expand it, and you will see a user-defined table type.
6. 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 varchar(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 proc [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();  
}  

To Update Records 

Here is a snapshot.

Let us start with SQL.

Here I created a user-defined type.

CREATE TYPE UDT_VUpdate AS TABLE   
(       
        Name varchar(100),  
        Age Int ,  
        Vid int  
 )  

Here in this Stored Procedure, I used "User_Define_Table_Type."

create proc [dbo].[Usp_VUpdate]  
@UserDefineTable UDT_VUpdate readonly  
 as   
  begin  
Update Vinsert  
set    
Name = r.Name,  
Age  = r.Age  
from @UserDefineTable r  
where Vinsert.Vid = r.Vid   
end  

Here I am passing a Datatable to SQL.

public void DatasetInsert(DataTable dt)  
{  
    con.Open();  
    SqlCommand cmd = new SqlCommand("Usp_VUpdate", con);  
    cmd.Parameters.AddWithValue("@UserDefineTable", dt); // passing Datatable  
    cmd.CommandType = CommandType.StoredProcedure;  
    cmd.ExecuteNonQuery();  
    con.Close();  
}  
protected void btnUpdate_Click(object sender, EventArgs e)  
{  
    DataTable DT = new DataTable();  
    DT.Columns.Add("Name", typeof(string));  
    DT.Columns.Add("Age", typeof(int));  
    DT.Columns.Add("Vid", typeof(int));  
    DataRow DR = DT.NewRow();  
    DR["Name"] = txtname.Text;  
    DR["Age"] = txtage.Text;  
    DR["Vid"] = 1;  
    DT.Rows.Add(DR);  
    DatasetInsert(DT); //calling datatable method here  
}  

Completed insert with "User_Define_Table_Type."

Insert Records using XML in SQL

(XML is case sensitive. If you write Name as NAME, it will not find it.)

Let us start with C#

In this example, I will insert only two records:

1.  Name
2. Age

Here is a Snapshot.

For inserting in the Datatable, I am creating a Datatable with 2 Columns.

1. Name
2. Age

DataTable DT = new DataTable();   // A Data table.  

Adding 2 Columns.

DT.Columns.Add("Name",typeof(string));  
DT.Columns.Add("Age",typeof(int));  

Adding new Datarow in Datatable.

DataRow DR = DT.NewRow();  
DR["Name"] = txtname.Text; // adding field value  
DR["Age"] = txtage.Text;   // adding field value  

Adding Datarow into Datatable

DT.Rows.Add(DR);  

Let us start with SQL

To create a user-defined table type in SQL, here is the procedure.

1. First, create a user-defined table type 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; you will see a user-defined table type.
6. 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 varchar(100),    
     Age Int    
)  

Here in this Stored Procedure, I used XML. (Insert)

Create proc Usp_InsertXMLdata  
@UserDefinexml xml  
as  
   
begin   
DECLARE @XMLdoc AS INT   
EXEC sp_xml_preparedocument @XMLdoc output  
,@UserDefinexml  // Parameter which we have passed  
Insert into Vinsert  
(  
Name,Age                       
)  
select   
Name,Age                       
FROM openxml(@XMLdoc, '/NewDataSet/table', 2) // Nodes which we are select   
 with   
(  
Name varchar(100),  
Age       int           
)   
EXEC sp_xml_removedocument @XMLdoc // Removing Xml after Inserting.   
end  

Let us start with C#

Here I am passing XML to SQL.

protected void btnsave_Click(object sender, EventArgs e)  
{  
    DataSet DS = new DataSet();  
    DataTable DT = new DataTable();  
    DT.TableName = "table";             
    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);  
    DS.Tables.Add(DT);  // Adding Datatable to Dataset  
    DatasetInsert(DS); //calling datatable method here  
}  
public void DatasetInsert(DataSet DS)  
{  
    con.Open();  
    SqlCommand cmd = new SqlCommand("Usp_InsertXMLdata", con);  
    cmd.Parameters.AddWithValue("@UserDefinexml", DS.GetXml());  
    // CONVERTING DATASET TO XML AND PASSING PARAMETER  
    cmd.CommandType = CommandType.StoredProcedure;  
    cmd.ExecuteNonQuery();  
    con.Close();  
}  

Here is a snapshot of the insert.

Here in this Stored Procedure, I used XML. (Update)

Create PROCEDURE Usp_UpdateXMLdata    
 @UserDefinexml XML    
AS    
BEGIN    
            DECLARE @XMLdoc AS INT    
            EXEC sp_xml_preparedocument @XMLdoc OUTPUT    
           ,@UserDefinexml    
            UPDATE v    
            SET  v.Name = r.Name    
                        ,v.Age = r.Age    
            FROM Vinsert v // giving alias to table Vinsert as v    
            INNER JOIN (    
                        SELECT NAME    
                                    ,Age    
                                    ,Vid    
                        FROM openxml(@XMLdoc, '/NewDataSet/table', 2)    
                WITH ( Name VARCHAR(100),Age INT,Vid INT))    
           r ON v.Vid = r.Vid    
// Simple Inner join on xml which is coming and data we have in table and then Updating.    
            EXEC sp_xml_removedocument @XMLdoc     
END  

Let us start with C#

Here I am passing XML to SQL for updating.

protected void btnUpdate_Click(object sender, EventArgs e)  
{  
    DataSet DS = new DataSet();  
    DataTable DT = new DataTable();  
    DT.TableName = "table";  
    DT.Columns.Add("Name", typeof(string));  
    DT.Columns.Add("Age", typeof(int));  
    DT.Columns.Add("Vid", typeof(int));  
    DataRow DR = DT.NewRow();  
    DR["Name"] = txtname.Text;  
    DR["Age"] = txtage.Text;  
    DR["Vid"] = 1;  
    DT.Rows.Add(DR);  
    DS.Tables.Add(DT);  
    DatasetUpdate(DS); //calling datatable method here  
}  
public void DatasetUpdate(DataSet DS)  
{  
    con.Open();  
    SqlCommand cmd = new SqlCommand("Usp_UpdateXMLdata", con);  
    cmd.Parameters.AddWithValue("@UserDefinexml", DS.GetXml()); // CONVERTING DATASET TO XML AND PASSING PARAMETER  
    cmd.CommandType = CommandType.StoredProcedure;  
    cmd.ExecuteNonQuery();  
    con.Close();  
}  

Here is a snapshot of the update.

Completed insert with XML.

Conclusion

This article taught us about various ways to Insert and Update in SQL Using User-Defined Table Type and XML.