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

In this article I show various ways  to insert in SQL.

• Using Datatable
• Using XML

By showing examples of inserts using a Datatable in SQL.

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 in to Datatable

DT.Rows.Add(DR);

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 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".

Showing example of insert using XML in SQL.

(XML is case senstive if you write Name as NAME then 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 ceate 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. 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 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.