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
Here is a Snapshot.
![Insert Only two Records]()
For inserting into a Datatable I am creating a Datatable with 2 Columns.
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:
- First create a user-defined table type by selecting in Object Explorer.
- Inside that select your database.
After selecting it just expand it.
- Inside that select the Programmability Folder.
After selecting it just expand it.
- You will see a folder with Name Types.
- Just select and expand it and you will see a user-defined table type.
- Just right-click on the folder and select "New User-Defined Table Type...".
Here is a Snapshot.
![User-defined Table Type]()
After selecting you will see this view.
![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.
![Update Records]()
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:
- Name
- Age
Here is a Snapshot.
![Insert using XML]()
For inserting in the 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 us start with SQL
To ceate a user-defined table type in SQL here is the procedure.
- First create a user-defined table type in Object Explorer.
- Inside that select your database .
After selecting it just expand it.
- Inside that select the Programmability Folder.
After selecting it just expand it.
- You will see a folder with Name Types.
- Just select and expand it; you will see a user-defined table type.
- Just right-click on the folder and select "New User-Defined Table Type...".
Here is a snapshot.
After selecting you will see this view.
![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.
![Snapshot of Update]()
Completed insert with XML.