How to Insert All the Rows From Following GridView To Database Table.
If you see, Generally we find most of the people writing For or While Loops and multiple iterations to Insert Rows Into Database.
The above Approach causes Multiple Hits to database which may degrade the performance of database.
Let see how XML Datatype can Help us in Optimizing this task.
Write a For loop to Generate String As Following
<table>
<productid>17</productid>
<productname>Alice Mutton</productname>
<unitprice>39.0000</unitprice>
</table>
If you See Its Simple HTML with Opening and closing tags. Just Loop it and you will have Following String.
Each row should have Opening <Table> and closing </Table> Tags with Opening and Closing Field Tags.
string mystr=string.Empty;
mystr=’<table>
<productid>17</productid><productname>Alice Mutton</productname>
<unitprice>39.0000</unitprice>
</table>
<table>
<productid>3</productid><productname>Aniseed Syrup</productname>
<unitprice>10.0000</unitprice>
</table>
<table>
<productid>40</productid><productname>Boston Crab Mutton</productname>
<unitprice>18.4000</unitprice>
</table>
<table>
<productid>60</productid><productname>Camembert Pierrot</productname>
<unitprice>34.0000</unitprice>
</table>
<table>
<productid>18</productid><productname>Carnarvon Tigers</productname>
<unitprice>62.5000</unitprice>
</table>’
Now You can Pass this String as XML Parameter To Stored Procedure Which Executes Following Query To Extract Result From XML Parameter.
Declare @xmlData Xml;
Set @xmlData=mystr; -- Assigning XML String to XML DataType
How to Read Data From Xml Variable
Note : Field name as Case Sensitive and DataType Should Match.
Select x.data.value('productid[1]','int') as ProductId,
x.data.value('productname[1]','varchar(50)') as ProductName,
x.data.value('unitprice[1]','decimal(12,4)') as UnitPrice
From @xmlData.nodes('/table') as x(data)
Note : As Xml Doesn’t Support Following Symbols
& , < , >
We Need Replace These Symbols In XmlString Before Passing string Value to Store Procedure Parameter or XMLVariable.
With Following Characters
& -- &
< -- <
> -- >
the Output of Query can be Used To Update and Insert Data In a Table
No comments:
Post a Comment