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.

CREATETYPEUDT_VinsertASTABLE

(

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

InsertintoVinsert

(

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.

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.

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 (NameVARCHAR(100),AgeINT,VidINT))

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.