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

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.