Inserting Multiple Records in
the Database
Part I (Selecting Items from
the datagrid)
Introduction:
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 other. In this article we will look at that 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.
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. Below is the screen shot
of the interface which we will be using in this article.
As you can see that we have three
columns in the datagrid control namely Name,Url and Selection column which is
the 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.
If you see the datagrid image at
the top of this article you will notice that it has "Url" column. 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.
Attachments:
Project Files: InsertMultipleRecords.zip