Every time we face problem bulk insertion through store procedure to perform the following task
1)Multiple insert in table
2)Select multiple rows using in-clause at where condition
3)Delete multiple rows
Save data from grid to database table in sql server 2005/2008
Here I am writing some very good code to pass multiple value in store procedure using xml string which is much faster and we can pass a large value having multiple rows and column some time using Gridview and other similar control.
This is the best and optimize method to insert multiple records in one go from asp.net to database table in sql server 2005/2008 using the XML formatting.
Convert Datatable/Dataset to XML string
public string DataTableToXML(DataTable table)
StringWriter xml = new StringWriter();
string xmldata = string.Empty;
table.TableName = "journal";
xmldata = xml.ToString();
the out put XML of the above code will be as given in the store-procedure variable.
Handling of XML string inside Store procedure/Query
Pass this xml string in your store procedure and handle this string in your SP by as following code given
Create procedure handlexml(@journalstring varchar(1000))