ASP.NET: How To Use BulkEditGridview To Save Hours In Database Editing
The GridView is a fantastic, versatile, and powerful tool. Unfortunately, it is also unrealistic in several aspects. Say you have a page in your website’s administrative back-end that is to update all of your prices for your products. With a typical GridView, if you wanted to update, say, 10 of these prices, you will require at minimum 20 postbacks, which depending on the speed of your server can be a lengthy process. Now imagine that you have over 1000 lines to edit. 2000 postbacks, minimum… sound like a daunting task that may take you hours in just waiting for your server to respond?
What if I told you that you could edit an unlimited number of records in a GridView with one click, one postback, and one call to your database, and that only the records you modified would be updated? What if I told you it was way, way easier than you think? You may think I am crazy at first if you have any experience with GridViews, but let me introduce you to the panacea of GridView woes, a little tool created by Matt Dotson called BulkEditGridView (more recently moved to this location on CodePlex). I have implemented this tool into nearly every editing application I have that requires a GridView and it has shaved not minutes or hours, but days of off time waiting for the server to post back.
I have been using this tool for quite some time now, but have waited until I had a full understanding of its power and nuances before I posted about it. The links I provided you will give you more background on the why and how its production came about (and where to download it), but the documentation on its use is a bit on the scattered side. That’s where I come in!
To give you a bit of perspective, the BulkEditGridView is little more than a custom user control that inherits the GridView. However, the functionality it provides is considered more “real world applicable”, because on binding, it places every row in the GridView in Edit mode automatically. You may then either tab through columns or click through records you wish to edit, and then once you’re done with ALL your editing, you click your designated save button and everything is updated! It also knows which rows were edited and stores them in a Generics
List Of(GridViewRow) called
DirtyRows, which can be accessed programmatically.
Enough already, you say. How do I use it? Here’s a step-by-step of how I have implemented it. Please note that this is how I have implemented it. There may be better ways, but this is how it has worked for me. In this documentation, I am going to assume you have downloaded and installed the .dll for this control.
Step 1: Add BulkEditGridView to your .aspx page
Remember, it is essentially a GridView, so add it like you would to any other page. You may specify bound columns, template columns, or anything of the like the same way you would a regular GridView. The only extra property you must specify is the
SaveButtonId property. This is just a button you have placed on your page that when pressed will make the BulkEditGridView work its magic.
Step 2: Add Save Button to page
As mentioned in the previous step, the BulkEditGridView requires that you specify which button is to be pressed for it to run. Any codebehind for this button is not required, but I typically add some catch-all code for when no edits have been made:
Protected Sub btnSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) If BulkEditGridView1.DirtyRows.Count = 0 Then litMsg.Text = "No bulk updates made." End If End Sub
Step 3: Define your
RowUpdating Event Handler
This is where all the magic happens. When your Save Button is pressed, if you have any DirtyRows the
RowUpdating event will fire for each row in that collection. You should create your
RowUpdating event the same way you would for a regular GridView, but include the following modifications:
Declare a Static counter variable (
Static count As Integer), and immediately increment it at the beginning of your event handler. This will come in handy later.
Declare a Static sql string variable (
Static sql As String) or System.Text.StringBuilder if you are looking to optimize. This is where you will store all of your UPDATE strings for your database so that once you have iterated through all your rows, you may send them to your database to be run all at once. In other words, rather than update your database every time the
RowUpdatingevent is called, return the sql string from your update function that you would typically run and add it on to a growing string of calls to make.
At the end of the
RowUpdatingevent, include some conditional code like:
If count = BulkEditGridView1.DirtyRows.Count Then Dim rowIndexes As New StringBuilder For Each row As GridViewRow In BulkEditGridView1.DirtyRows 'find out which rows were updated rowIndexes.Append(row.RowIndex + 1 & ", " ) 'real count, not 0 based Next rowIndexes.Remove(rowIndexes.Length - 2, 2) 'get rid of final comma msg = "Rows " & rowIndexes.ToString & " have been updated." AddOrUpdateData(sql.ToString) 'your function to call your database BindGridView() 'your function to bind your GridView litMsg.Text = msg End If
The preceding code does four things: it checks to see if it has completed running the
RowUpdatingfor each modified row (remember that static
countvariable?), it iterates back through the
DirtyRowsso that you may relay back to the user which rows were updated, it updates your database using one call (the
AddOrUpdateDatafunction, which is however you call your database to make updates), and it re-binds your GridView. NOTE: when building your SQL string, remember to end your separate UPDATE commands with a semicolon!
Some notes to consider:
If you are using template fields, do NOT include controls you wish to modify (textboxes, etc) inside of a Panel control. For some reason it will not detect them. What I did to get around this (if I wanted to display a panel on
RowDataBoundbased on a variable, for instance) is I created two Literals: one where I would want to place the
<div style="display:none;">and one for the closing
At least for me, Intellisense doesn’t work for the BulkEditGridView in the .aspx sourcecode edit mode, so either know your stuff or drop a regular GridView into your page first, build it the way you want, and then copy and paste the guts into a BulkEditGridView once you’re done.
Once you update all your items and rebind your BulkEditGridView, it will show up again in edit mode. To toggle this “Bulk Edit” mode, I have sometimes created one BulkEditGridView and one normal GridView on the same page, and then a button to bind and show one and hide the other. For me it has created a seamless user experience and only costs you one postback.
And that’s it! I know this is a long post, but when you get used to using this amazing control, you will see that it boils down to only a few different things you need to change to get it working for you.