Class Properties Or List Class XML Serialization And Bulk Insert Using C# And SQL Server

The objective of this blog is to implement bulk insert / update data from UI or Business Layers where most of the time, we use looping to insert data. For this scenario, we can convert class or List Collection into XML format. The useful thing that we need most of the times is serialization which I will implement here for generating XML.

In most of the cases, I have seen that the easiest way to convert to XML is by using the DataTable.

private static string ConvertDataTableToXML(DataTable dtData) {
DataSet dsData = new DataSet();
StringBuilder sbSQL;
StringWriter swSQL;
string XMLformat;
try {
sbSQL = new StringBuilder();
swSQL = new StringWriter(sbSQL);
dsData.Merge(dtData, true, MissingSchemaAction.AddWithKey);
dsData.Tables[0].TableName = "SampleDataTable";
foreach(DataColumn col in dsData.Tables[0].Columns) {
col.ColumnMapping = MappingType.Attribute;
}
dsData.WriteXml(swSQL, XmlWriteMode.WriteSchema);
XMLformat = sbSQL.ToString();
return XMLformat;
} catch (Exception sysException) {
throw sysException;
}
}

After converting any DataTable, we will get the below string.

<NewDataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="SampleDataTable">
<xs:complexType>
<xs:attribute name="Name" type="xs:string" />
<xs:attribute name="Address" type="xs:string" />
<xs:attribute name="Phone" type="xs:string" /> </xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<SampleDataTable Name="Franklin" Address="Hosur" Phone="46945616" />
<SampleDataTable Name="SAMRAj Gill" Address="DT-HOSUR" Phone="78971651" />
</NewDataSet>

Let's remove the tag <xs:schema …..></xs:schema>. Now checking in SQL Server to open XML file and read from it. For that, we have to add the below code in Query Window and check. And, we put this in Query window of SQL Server. It will show like below.

DECLARE @SET1 NVARCHAR(MAX)
SET @SET1 = '<NewDataSet> < SampleDataTable Name = "Franklin"
Address = "Hosur"
Phone = "46945616" / > < SampleDataTable Name = "SAMRAj Gill"
Address = "DT-HOSUR"
Phone = "78971651" / > < /NewDataSet>'
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @SET1
BEGIN TRANSACTION
--INSERT INTO dbo.some_table(ID, Name, [Address], Phone)
select '1'
as ID, Name, [Address], Phone
from
OPENXML(@XMLDocPointer, '/NewDataSet/SampleDataTable', 1) with(Name NVARCHAR(100), [Address] NVARCHAR(100), Phone NVARCHAR(100))
EXEC sp_xml_removedocument @XMLDocPointer

OUTPUT

ID Name Address Phone

1 Franklin Hosur 46945616

1 SAMRAj Gill DT-HOSUR 78971651

Now, we have to generate only the below code from C# Class properties or List of class. Then, pass to SQL Server.

<NewDataSet>
<SampleDataTable Name="Franklin" Address="Hosur" Phone="46945616" />
<SampleDataTable Name="SAMRAj Gill" Address="DT-HOSUR" Phone="78971651" />
</NewDataSet>

In this, <NewDataSet> and <SampleDataTable> tags are fixed. We have to generate only he following.

Name="SAMRAj Gill" Address="DT-HOSUR" Phone="78971651" />

Where First attributes are property names of class. Attribute values are Class object value.

class Employee {
public string Name {
get;
set;
}
public string Address {
get;
set;
}
public string Phone {
get;
set;
}
}
List < Employee > oEmp = new List < Employee > ();
oEmp.Add(new Employee() {
Name = "Franklin", Address = "Hosur", Phone = "46945616"
});
oEmp.Add(new Employee() {
Name = "SAMRAj Gill", Address = "DT-HOSUR", Phone = "78971651"
});
string strXML = EmployeeConvertToXML(oEmp);

You can create your own common method to achive below.

private static string EmployeeConvertToXML(List < Employee > oEmp) {
StringBuilder sb = new StringBuilder();
sb.Append("<NewDataSet>");
foreach(Employee oe in oEmp) {
sb.Append("<SampleDataTable Name=\"" + oe.Name + "\" Address=\"" + oe.Address + "\" Phone=\"" + oe.Phone + "\" />");
}
sb.Append("</NewDataSet>");
return sb.ToString();
}

The following will be the stored procedure to save to the Table some_table.

Create proc SaveToSome_table
@SET1 NVARCHAR(MAX)
AS
BEGIN
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @SET1
BEGIN TRANSACTION
INSERT INTO dbo.some_table(ID, Name, [Address], Phone)
select '1'
as ID, Name, [Address], Phone
from
OPENXML(@XMLDocPointer, '/NewDataSet/SampleDataTable', 1) with(Name NVARCHAR(100), [Address] NVARCHAR(100), Phone NVARCHAR(100))
EXEC sp_xml_removedocument @XMLDocPointer
END