Suppose , I have to insert multiple records at a single click. The option for a developer to insert the data in database with the help of 2 options.
1. With XML
2. With User defined table type.
Here, we are defining both these options one by one.
1.With XML
We have a grid with 3 columns boxid, quantity and weight and we have picked the data from the gridview and store these data in 3 different array objects .suppose in the below example quantity is a string has value “200,300,140”.we fetch the values from string to array.
string[] arrrQ = quantity.Split(','); //fetch the value from string to array
string[] arrW = weight.Split(',');
string[] arrB = boxtypeId.Split(',');
XmlDocument oXmlProductDetail = new XmlDocument(); // create an object of XmlDocument class
XmlElement oXmlElement = oXmlProductDetail.CreateElement("doc");// create an element with specified name
for (int i = 0; i < arrrQ.Length; i++)
{
XmlElement oXmlIM = oXmlProductDetail.CreateElement("Record");//create another element
oXmlIM.SetAttribute("boxid", arrB[i]);
oXmlIM.SetAttribute("quantity", arrrQ[i]);
oXmlIM.SetAttribute("weight", arrW[i]);
oXmlElement.AppendChild(oXmlIM); // add the element “Record” under the main element “doc”
}
oXmlProductDetail.AppendChild(oXmlElement); //Finally add the element in the xmldocument object.
---------------//pass the xml paramenter to the method as under
int intResult = objname.XYZ (oXmlProductDetail);
//Now write a method in a class to pass the xml to the specified stored procedure. Create a argument of type XMLDocument type
public static int XYZ(XmlDocument data)
{
objDic.Add("@paramenter1 ", data.OuterXml);
}
Stotred procedure
CREATE PROCEDURE [dbo].[procedure_name]
(
@paramenter1 xml
)
AS
BEGIN
-- POPULATING TEMP TABLE FROM XML VARIABLE
INSERT INTO @tbl (boxid,quantity,[weight])
select x.value(N'@boxid', N'int') AS boxid
, x.value(N'@quantity', N'int') AS quantity
, x.value(N'@weight', N'decimal(10,2)') AS [weight]
FROM @ProductDetail.nodes(N'/doc/Record') t(x)
===========================
2.With Table Type
===========================
1.First create user defined table type
CREATE TYPE [dbo].[ tabletype_name] AS TABLE(
[col_tt1] [int] NULL,
[col_tt2] [int] NULL
)
2. create a stored procedure and used the user defined table type.
CREATE PROCEDURE [dbo].[procedure_name]
(
@paramenter tabletype_name READONLY
)
AS
BEGIN
INSERT INTO table1([col1],[col2)
SELECT [col_tt1],[col_tt2],'A' FROM @paramenter
END
Coding end:
3. passing the datatable to the above parameter.
public static void MethodName(DataTable dt)
{
objDic.Add("@RegionDetail", dt);
}
1. With XML
2. With User defined table type.
Here, we are defining both these options one by one.
1.With XML
We have a grid with 3 columns boxid, quantity and weight and we have picked the data from the gridview and store these data in 3 different array objects .suppose in the below example quantity is a string has value “200,300,140”.we fetch the values from string to array.
string[] arrrQ = quantity.Split(','); //fetch the value from string to array
string[] arrW = weight.Split(',');
string[] arrB = boxtypeId.Split(',');
XmlDocument oXmlProductDetail = new XmlDocument(); // create an object of XmlDocument class
XmlElement oXmlElement = oXmlProductDetail.CreateElement("doc");// create an element with specified name
for (int i = 0; i < arrrQ.Length; i++)
{
XmlElement oXmlIM = oXmlProductDetail.CreateElement("Record");//create another element
oXmlIM.SetAttribute("boxid", arrB[i]);
oXmlIM.SetAttribute("quantity", arrrQ[i]);
oXmlIM.SetAttribute("weight", arrW[i]);
oXmlElement.AppendChild(oXmlIM); // add the element “Record” under the main element “doc”
}
oXmlProductDetail.AppendChild(oXmlElement); //Finally add the element in the xmldocument object.
---------------//pass the xml paramenter to the method as under
int intResult = objname.XYZ (oXmlProductDetail);
//Now write a method in a class to pass the xml to the specified stored procedure. Create a argument of type XMLDocument type
public static int XYZ(XmlDocument data)
{
objDic.Add("@paramenter1 ", data.OuterXml);
}
Stotred procedure
CREATE PROCEDURE [dbo].[procedure_name]
(
@paramenter1 xml
)
AS
BEGIN
-- POPULATING TEMP TABLE FROM XML VARIABLE
INSERT INTO @tbl (boxid,quantity,[weight])
select x.value(N'@boxid', N'int') AS boxid
, x.value(N'@quantity', N'int') AS quantity
, x.value(N'@weight', N'decimal(10,2)') AS [weight]
FROM @ProductDetail.nodes(N'/doc/Record') t(x)
===========================
2.With Table Type
===========================
1.First create user defined table type
CREATE TYPE [dbo].[ tabletype_name] AS TABLE(
[col_tt1] [int] NULL,
[col_tt2] [int] NULL
)
2. create a stored procedure and used the user defined table type.
CREATE PROCEDURE [dbo].[procedure_name]
(
@paramenter tabletype_name READONLY
)
AS
BEGIN
INSERT INTO table1([col1],[col2)
SELECT [col_tt1],[col_tt2],'A' FROM @paramenter
END
Coding end:
3. passing the datatable to the above parameter.
public static void MethodName(DataTable dt)
{
objDic.Add("@RegionDetail", dt);
}
Comments