Deleting Data
Now recall our asp:button above, and its default JavaScript onclick event handler attached on Page_Load. Aside from this we also notice it has another OnClick event (this one being server based) that gets raised when the button is clicked, rather pressed, that'll allow it to fire the server-side DeleteStore method to delete our data:
public void DeleteStore (Object sender, EventArgs e)
{
string dgIDs = "";
bool BxsChkd = false;
foreach (DataGridItem i in MyDataGrid.Items)
{
CheckBox deleteChkBxItem = (CheckBox) i.FindControl ("DeleteThis");
if (deleteChkBxItem.Checked)
{
BxsChkd = true;
// Concatenate DataGrid item with comma for SQL Delete
dgIDs += ((Label) i.FindControl ("StoreID")).Text.ToString() + ",";
}
}
// Set up SQL Delete statement, using LastIndexOf to remove tail comma from string.
string deleteSQL = "DELETE from Stores WHERE stor_id IN (" + dgIDs.Substring (0, dgIDs.LastIndexOf (",")) + ")";
if (BxsChkd == true)
{ // Execute SQL Query only if checkboxes are checked, otherwise error occurs with initial null string
try
{
SqlHelper.ExecuteNonQuery (objConnect, CommandType.Text, deleteSQL);
OutputMsg.InnerHtml += "<font size=4><b>Store information has been deleted.</b></font>";
OutputMsg.Style["color"] = "green";
}
catch (SqlException err)
{
OutputMsg.InnerHtml += err.Message.ToString(); //"<font size=4><b>An error occurred and the record could not be deleted</b></font>";
OutputMsg.Style["color"] = "red";
}
//Refresh data
BindData();
}
}
Since having wired the two client/server methods together, it's our JavaScript code that actually intercepts this button's call and goes first. If you confirm OK, then will the deleting server-side method execute, otherwise it'll cancel all events after that point and prevent anything from posting back.
Looking at the DeleteStore() method, you'll notice that it is actually does a few things. First, it set's up the string variable dgIDs that will hold all of our selected DataGrid IDs. Next, it loops through the DataGrid, and gathers all of the selected item ids that are based on the row's TemplateColumn ID, which is why I kept the ID control as a TemplateColumn and the rest BoundColumns as these types of controls do not support the ID property we need for referencing our data. After this, it will, upon verifying checked items, gather all the ids and assign them to our dgIDs variable, that'll be used with our SQL deleteSQL delete statement.
The deleteSQL delete statement uses the "WHERE IN" argument to perform the multiple deletes in one shot. Since we need to separate each id with a comma, you'll notice that in the loop I attach a comma after each collected item. This way we'll have all of our items clearly defined in our SQL. One problem however is that since we add on a comma after each collected item, the last one as well will include a tail-end comma and SQL won't like this. For example, once we loop through the DataGrid, gather up all of the selected items, and assign it to our delete string we could end up with something like this:
DELETE from Stores WHERE stor_id IN (2,4,6,7,)
Notice the last comma; that's a no-no. To quickly and easily remedy this, we must remove the last comma, and we do this by pulling the substring we need from the "dgIDs" string using LastIndexOf (",") effectively removing the last comma, and properly formatting the delete statement for SQL, like so:
DELETE from Stores WHERE stor_id IN (2,4,6,7)
Finally, DeleteStore proceeds to execute the query against the database. Incidentally, for those wondering why I have a conditional with BxsChkd? Well it's because if I don't initially select any items, I'm returned an error on Page_Load due to our SqlHelper having nothing initialized. Therefore, by do so, our DeleteStore method will remain silent, and happily waiting in the wings until it does get the actual go ahead.
So that's the crux of our DataGrid application, and technology behind doing multiple checkbox deletes a la Hotmail and Yahoo style. And on that note, here's all the code. Just have SQL Server ready, DAAB installed, then cut and paste the code on the next page, and have fun.