In the last article we saw that how we can collect all the selected values from the Datagrid control using checkboxes and append it in the StringBuilder object. In this article we will see that how we can put multiple values (which we have already collected) into database. This article will demonstrate how you can insert multiple values in database with less overhead. We will see couple of ways of doing the same task and you are free to choose any way you wish.

Introduction:

In the last article we saw that how we can collect all the selected values from the Datagrid control using checkboxes and append it in the StringBuilder object. In this article we will see that how we can put multiple values (which we have already collected) into database. This article will demonstrate how you can insert multiple values in database with less overhead. We will see couple of ways of doing the same task and you are free to choose any way you wish.

String Concatenation:

The most common way of inserting values is by the use of string concatenation. We will concatenate all the values into sql string command and when the concatenation is complete we will have a long string representing all the insertions which can later be send to the command object to be executed.

private string GetSelectedItems() { StringBuilder str = new StringBuilder(); foreach(DataGridItem dgi in myDataGrid.Items) { CheckBox myCheckBox = (CheckBox) dgi.Cells[2].Controls[1]; if(myCheckBox.Checked == true) { // Appends items using StringBuilder object str.Append("INSERT INTO CodeSamples(Name) VALUES("); str.Append("'"); str.Append(dgi.Cells[0].Text); str.Append("'"); str.Append(")"); } } // Checks to see if the str is empty or not if(str!=null && str.Length!=0) { return str.ToString(); }   else { return null; } }

 We have already discussed most of the code so I will not go over that again. Let's just see the main code that we have added. Inside the if condition we are making our string by using the StringBuilder object. This is a simple concatenation which makes a string representing the values which you wish to insert. If you print out the str object you will see something like this:

INSERT INTO Articles(Name) VALUES('Your Value here')INSERT INTO Articles(Name) VALUES('Your Value here') and so on.

At the end of the method we are simply checking that if the str object is null or have length '0' and if it is than we return null indicating that the user has selected nothing. Next we see how we can send the string to the command object so that this can be executed. I am using Enterprise Library for data access you can simply use SqlCommand object to execute the query.

// This event inserts the records in the database using string concatenation private void Button1_Click(object sender, System.EventArgs e) { string query = GetSelectedItems(); Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper insertCommandWrapper = db.GetSqlStringCommandWrapper(query); try { db.ExecuteNonQuery(insertCommandWrapper); } catch(Exception ex) { Label1.Text = ex.Message; } }

The dark side of using this approach is that if you have many values that you need to insert the string might look quite ugly. Also by using the string as a query we are open for SQL INJECTIONS.

Passing Xml to Stored Procedure:

Now we will see a much better technique of inserting multiple values to the database. In this approach we will create a string of xml representation and than we will send the xml string to the stored procedure.

private void Button3_Click(object sender, System.EventArgs e) { StringBuilder sb = new StringBuilder(); sb.Append("<items>"); // Gets the selected rows in the datagrid foreach(DataGridItem dgi in myDataGrid.Items) { CheckBox myCheckBox = (CheckBox) dgi.Cells[2].Controls[1]; // Check to see if the CheckBox is checked or not if(myCheckBox.Checked == true) { // Make the string here sb.Append("<item id=\""); sb.Append(dgi.Cells[0].Text); sb.Append("\">"); sb.Append("</item>"); } } }

In the code above we are just making a string that has xml tags. We are trying to make a string in the following format:

<items>

<item id = "String1">

</item>

<item id = "String2">

</item>

</items>

The strings here is the data that we are sending to the database. This technique is much faster than the traditional string concatenation technique. The heart and soul of this method is the stored procedure which uses system procedures and XML functions. Let's take a look at the code used to run the stored procedure.

sb.Append("</items>"); Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper insertCommandWrapper = db.GetStoredProcCommandWrapper("usp_InsertManyRows"); insertCommandWrapper.AddInParameter("@XMLDOC",DbType.String,sb.ToString()); try { db.ExecuteNonQuery(insertCommandWrapper); Label1.Text = "Data Inserted"; } catch(Exception ex) { Label1.Text = ex.Message; }

In the above code we are just executing the stored procedure and attaching the parameters. Now lets take a look at the stored procedure.

CREATE PROCEDURE dbo.usp_InsertManyRows @XMLDOC varchar(8000) AS declare @xml_hndl int --prepare the XML Document by executing a system stored procedure exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC --insert into table Insert Into CodeSamples ( Name ) Select IDToInsert From OPENXML(@xml_hndl, '/items/item', 1) With ( IDToInsert nvarchar(200) '@id' ) GO

There are few special things to notice about this stored procedure. We are using a system stored procedure named sp_xml_preparedocument. This system stored procedure parses the xml text and provide the parsed document for consumption. The OPENXML Function provides a method to read the xml text. The use of the with keyword indicates that what we are trying to read which in this case the nvarchar(200) "Name" column.

This method is much faster and recommended instead of the string concatenation method.

I hope you liked this article and happy coding !