In Part 1 of this article we saw how to use TemplateColumns to create a column such
that all records of the DataGrid provided an editable interface. In this second and final part, we'll see how to
provide the user with an "Update All" Button that, when clicked, will commit all of the user's modifications to the database.
Saving All of the DataGrid's Changes to the Database
Now that we have a DataGrid with a completely editable interface, all that remains is writing a bit of code to iterate through
the DataGrid's records and update the database accordingly. To accomplish this we'll add an "Update All" Button Web control to
the page and add the necessary code in the Button Web control's Click event handler.
We can loop through each row in the DataGrid by enumerating the DataGrid's Items collection. Each DataGrid row
is represented by a DataGridItem class instance. For each item, we'll need to access each of its editable
columns' values. We can do this through the FindControl() method, passing in the ID of the Web control
for the column's editable interface. (For more on how to programmatically access the contents of a TemplateColumn, read:
Accessing the Contents of
a TemplateColumn.)
For each record in the DataGrid, once we have read in the TemplateColumns' values we need to issue an UPDATE statement
to save any changes made back to the underlying database table. To accomplish this we'll need to be able to have information
that uniquely identifies each row. If the data you are displaying has a single primary key field, you can just use the
DataGrid's DataKeyField property to track the unique ID for each record. The pseudocode for doing an "Update All"
will look like the following:
For Each record in the DataGrid
Read in the values from the TemplateColumns
Issue an UPDATE statement to the database
Next
The actual code isn't much more complex than the pseudocode, and is shown below. This code should be placed in the
"Update All" Button Web control's Click event handler:
'Create connection and command objects
Dim myConnection as New SqlConnection(connection string)
Dim myCommand as New SqlCommand(strSQL, myConnection)
Dim dgi as DataGridItem
For Each dgi in dgPopularFAQs.Items
'Read in the Primary Key Field
Dim id as Integer = Convert.ToInt32(dgPopularFAQs.DataKeys(dgi.ItemIndex))
Dim question as String = CType(dgi.FindControl("txtDescription"), TextBox).Text
Dim submittedBy as String = CType(dgi.FindControl("txtSubmittedBy"), TextBox).Text
'Issue an UPDATE statement...
Dim updateSQL as String = "UPDATE TableName SET Question = @Question, " & _
"SubmittedByName = @SubmittedByName WHERE FAQID = @ID"
myCommand.Parameters.Clear()
myCommand.Parameters.Add("@Question", question)
myCommand.Parameters.Add("@SubmittedByName", submittedBy)
myCommand.ExecuteNonQuery()
Next
When the user clicks the "Update All" Button, the page is posted back, the rows of the DataGrid are enumerated, and
for each record an UPDATE statement is reissued to the database. (In the live
demo no UPDATEs are issued; rather the output is merely displayed with a Response.Write()
statement...)
Conclusion
In this article we saw how to create a fully-editable DataGrid. To accomplish this, the DataGrid must first be created
so that each column is a TemplateColumn, with its <ItemTemplate> containing the editable user interface.
Next, code needs to be written that will iterate through the records of the DataGrid when some "Update All" Button is clicked,
sending an UPDATE statement to the database for each row in the DataGrid. For situations where users need to
edit many records of a DataGrid, the fully-editable DataGrid UI can prove to be more efficient than requiring users to repeatedly
click Edit and Update buttons, one row at a time.
