Inserting Multiple Items in the Database Part I

We always come across situations when we need to insert multiple records in the database in one shot. Off course there are many ways that you can insert data in the database but some methods are more efficient than the others. In this article we will look how you can extract the values of the columns based on the selected checkbox columns. This is a two part article and in the second part I will tell you that how you can insert the items in the database.

Introduction:

User Interface:

Since we need to select multiple records we need some sort of interface that will allow us to do so. For this reason we will use the Datagrid with checkbox columns.

As you can see that we have three columns in the Datagrid control namely Name,Url and Selection column which is a checkbox column. The "Name" column and the "Url" column are the bound columns whereas the checkbox column is a template column. You might have also noticed that there is a "Select All" header which is added to the Checkbox column. The "Select All" column enables you to select all the checkboxes just like hotmail and yahoo inbox functionality.

Selecting Independent Checkboxes:

Let's start by simply selecting independent checkboxes, which means that we will select a single checkbox and display the value of name column. The first thing to notice here is the column numbering. Column numbering starts with '0' and increments for the next column. Hence, in the above image "Name" is the column 0, "Url" is the column 1 and checkboxes are the column 2. As you might have guessed that we need to play around with column 2 in order to get the checkbox values.

The following code is implemented under the button click event.

StringBuilder str = new StringBuilder();

The first thing we did is declare the StringBuilder object. You can also use a simple string but that will be bad for performance since strings are immutable.

foreach(DataGridItem dgi in myDataGrid.Items)

{

CheckBox myCheckBox = (CheckBox) dgi.Cells[2].Controls[1];

if(myCheckBox.Checked == true)

{

// This generates a string and formats the string

str.Append(dgi.Cells[0].Text);

str.Append("<BR>");

}

In the above code we have a simple loop that iterates through each item of the Datagrid control. Inside the loop we are getting the value of the checkbox column. This is done by casting the dgi.Cells[2].Controls[1] to the checkbox and if you remember earlier I pointed out that checkbox column is the column number 2 in the Datagrid. So, we get the value of the checkbox column and we use a simple if statement to check that if the checkbox is checked or not. If the checkbox is checked than we append the value of the first column (Name) which is represented by dgi.Cells[0].Text into the StringBuilder object. Finally we are using the html line break tag to separate all the selections on new lines.

Now if you combine the Name column with the Url column so the when you click on Name you are taken to the appropriate url. This functionality is also very simple to implement, all you have to do is to use the Stringbuilder object append method to concatenate the two columns in correct order.

// This generates a string and formats the string

str.Append("<a href='");

str.Append(hyper.Text);

str.Append("'>");

str.Append(dgi.Cells[0].Text);

str.Append("</a>");

str.Append("<BR>");

You can see that we only used the Append method and made a simple html link out of the data coming from the Datagrid.

Selecting All Columns with a single click:

The above example will work good if you have 10-15 columns but consider that you need to select 50 columns. Off course you will not like to select all the columns manually by clicking each one of them. For this reason we will implement functionality that will let us to select all the columns using a single check in the checkbox control.

This functionality can be added server side as well as client side. Client side functionality is more useful in this case since we don't want to trigger the postback each time we check the checkbox. Let's name the header checkbox "SelectAll" so that in the html view we will know that which checkbox we are talking about.

Next we need to attach the onclick event with the checkbox control. The following line does the trick.

<asp:CheckBox onclick="javascript:SelectAllCheckboxes(this);" id="SelectAll" runat="server"></asp:CheckBox>

Here "SelectAllCheckboxes" is the name of the java script function which we will implement below:

function SelectAllCheckboxes(spanChk){

// Added as ASPX uses SPAN for checkbox
var oItem = spanChk.children;
var theBox=(spanChk.type=="checkbox")?spanChk:spanChk.children.item[0];
xState=theBox.checked;

elm=theBox.form.elements;
for(i=0;i<elm.length;i++)
if(elm[i].type=="checkbox" && elm[i].id!=theBox.id)
{
//elm[i].click();
if(elm[i].checked!=xState)
elm[i].click();
//elm[i].checked=xState;
}
}

The good thing about this java script function is that it keep track that if you are selecting the checkbox or you are deselecting it. Later when the items are selected you can simply use the for loop that we saw earlier to select the values of all checked columns. In the part 2 of this article we will see that how to use the same interface and insert multiple records into the database using various techniques.