We may need to send multiple records to database from code behind, at that situation we will make multiple requests to db. If the number of records increased than it may take performance penalty. If we send all the records at once to database then performance will be increased. One of the method to send all the records at once to database is, sending data as XML data.
The following steps need to send data as XML to db
Convert data table to XML string ( data table contains the records which we need to send db) Send the XML string to the database. ( using command parameter)
Select the records from XML variable and perform the operations what we need.( in the stored procedure)
Convert data table to XML string
System.IO.StringWriter sw = new System.IO.StringWriter();
dataTable.WriteXml(sw, XmlWriteMode.IgnoreSchema);
XMLstring = sw.ToString();
where data table is the object for DataTable class.
XMLstring will get the dataTable data in XML format.
Following is the sample data for the XMLstring.
0 SYM_CompanyError 1 SYM_CompanyName
where DataSet1 is the name of the data set in which dataTable is there and table is the name of the table (dataTable’s name). Here we had two records with two columns that are “pid”, “phrase” in the table, here we can see them as xml nodes. So we have to take care about naming of the dataset, datatable, and columns why because this names will be used in the stored procedure and names are case sensitive in the stored procedure.
Send the XML string to the database.
To send the XML parameter add the string which contains XML as below to the sql parameter. .SqlHelper.AddParameter(SQLCommand, “@parameterName”, SqlDbType.Xml, ParameterDirection.Input, XMLString); and calling stored procedure is as usual.
Select the records from XML variable and perform the operations what we need.
We can get the data in xml as below.
@XMLdata ASXML,
AS
BEGIN
- - Selecting records which we have sent.
SELECT
TempRow.value('(pid/text())[1]','nvarchar(120)')AS PhraseID ,
TempRow.value('(phrase/text())[1]','nvarchar(120)')AS Phrase
FROM
@XMLdata .nodes('/DocumentElement/table')AS TempTable(TempRow))
END
@XMLdata is the data we have sent from the code behind in XML format.
@XMLdata .nodes('/DocumentElement/table') this selects sub nodes inside the nodes DocumentElement/table( data set name/ data table name) that is all table nodes will be selected as TempRow.
AS TempTable(TempRow)) means we are storing data in alias table “TempTable” and alias row TempRow
TempRow.value('(pid/text())[1]','nvarchar(120)')AS PhraseID , means
pid, phrase are names of node( name of column in data table), text() indicates text of the node, [1]' indicates the first node, always we have to use [1] here and 'nvarchar(120)' indicates the conversion to the destination type.
From here we can perform insert operations or we can use cursor to iterate among data.