In one of my previous contributions I demonstrated a way to edit database data in the asp.net datagrid component. Before and after that many more (good) stories have appeared on this matter. The most of these stories work with an ad-hoc list of data and not on a live database. As the latter introduces some pitfalls and the datagrid component is really popular I will take another look at the matter, again through the eyes of a developer used to tools like Delphi or VS rather then ASP. I will be tackling the problem using as many RAD options of Visual Studio .NET as possible and will be working with strong typed object oriented code. This time I will take a slightly different approach and will dig deeper into working with template columns. Some sidesteps will take a look at a client side scriptlet as well at reducing network- or database- traffic.

Accessing the data

The demo application consist of one webform with a grid. The data for the grid comes from a sql-server database. You can create this database and its tables from VS.NET. In the server explorer right-click the SQL-server to create a new database. Right click on the tables node to create a new table or right click an existing table to modify its design.

A lot of database administration can be done from within VS.NET. Note the sql-server toolbars. Besides designing tables and views you can browse and even edit the contents of the database, double clicking a table opens a grid on its contents.

The only essential thing you cannot do from VS.NET is the administration of user accounts, for this you need the SQL server manager.

The demo database has a table with an ID column and three text columns. A second table holds some message strings. When I drop the tables on the webform VS.NET generates the components to access the data. For further details on these data components I refer to my former story. In this app I create two sqlDataAdapters for the SomeData table. The first one, sqlDataAdapterGrid, selects sorted rows on a selected number of columns. This is the sql select statement:

When dropping the table on the webform VS.NET had also created a delete- update- and insert-command for the table, I discard these. The second data-adapter, sqlDataAdapterRow, will do the updates, it selects all columns for one selected row

In this component  I keep the generated sql update, insert and delete commands.

A third dataadapter, sqlDataAdapterMessages, reads the data from the messages table.

For every sqldatadapter I generate a dataset component. Now the data is available to controls on the page and my code. I drop a datagrid on the form, set its properties and can start coding.

Rebuilding a web form on roundtrips

Before exploring the grid I want to take a step back to consider the way a web application works. Specially when working with a database there are some things you have to take care off. Running a web application is a matter of serving requests from the browsers of end users. Every time the user posts a request, most by clicking something, the web-server will respond with a response which holds a(n) (updated) webpage. The main steps in building the page are :

1. The webform object is created.
2. The state of the form, preserved over the roundtrip, is restored.
3. The load event is fired.
4. The event handlers of all button, grid and other components are fired.
5. The pre-render event is fired.
6. The contents of the page and its component is rendered as html to the response.
7. The unload event is fired.
8. The webform object is disposed.

The code of an asp.net webform reflects this process, you can hook into most of these events straight from the webform designer. Select the webform in the properties window and choose the events page.

The Page_Load event is already generated by VS.NET, with a comment. This is a good place to open the connection to the database.

In a lot of example code a lot more happens here, like reading data from the database and binding the data to the grid. I will not do that yet. The eventhandlers of the controls, like button-clicks might update the data. It is a lot more efficient to wait to the PreRender event. When that event is fired all handlers of button clicks and the like have executed. The page is now ready to start writing the actual response which will be sent to the user. It is the last opportunity to update the components.

The data-adapter fills the dataset with data from the database after which this data is bound to the grid. Now the response page will be rendered and the page object is unloaded from the web server. When unloading the Unload event is fired, in the event handler the connection to the database is closed

In classical asp many developers store the connection in the session object. ADO.NET has a built-in connection pool which makes the opening and closing of a connection not a very expensive operation. Using the event model the connection is opened at the start of creating the response and closed when the response is ready. All code on the page accesses the database using this connection.

Editing and updating a simple column

The datagrid has an edit button column. When the user clicks this the form is posted back to the server and the associated event handler is fired.

The items of the datagrid are the rows. This handler sets the EditItemIndex property of the grid to the index of the row who fired this event. In the browser the fields of this row can now be edited and instead of the edit button there appears an update and a cancel button.

Clicking the cancel button results in a postback to the webserver where the cancel command is executed

All this eventhandler has to do is reset the EditItemIndex property to a non-existing row.

When the update button is clicked the update command is executed, now the web server has to do a little more

The hardest thing is to get is the edited value. The eventhandler is passed a DataGridCommandEventArgs object. This object has an Item property which is the grid-row being edited. The row has a cell for every column and each cell contains a number of controls. In this simple edit the cell contains just one textbox, the first control of the cell is typecast to a textbox and its text is read. Now I have the edited value.

A datagrid has an indexed property DataKeys, its items hold the value of the keyfield to the data. This field was set in the first page of the grid's property builder. The Item has an ItemIndex property which serves as an indexer to get the keyfield value for the edited record. The value is used as a parameter for the query which gets the row. The resulting row is declared as a row of type DataSetRow.SomeDataRow.  After filling in the new fieldvalue the data is written to the database using the Update method of the data-adapter. Finally the grid's EditItemIndex is reset to end the editing.

Instead of re-reading the row from the database I could have used the row from the grid's dataset. The advantage of the approach used here is that I can read fields from the database who are not in the grid or its dataset. I will do that later on when working with a template column. Another advantage is that the Update method of the data-adapter only has to process one row instead of all the rows to find the one I updated.

Adding a simple row

To add a row there is a separate button on the form. In the click event it adds an empty row to the grid's dataset and set the EditItemIndex to initiate the index.

All event handlers of the controls, including this one, are handled before the Page_Prerender event. In the latter the rows are read from the database and added to the dataSetGrid1 dataset using the datasets's merge method. As this click handler executes first the row added will always be the first row in the grid. So the EditItemIndex of the grid is set to 0 to point to this first row.

Now we have a problem when writing the result to the database. The update command did a re-read on a row being edited, something which makes no sense for a new row. The update handler has to know whether it is writing an edit or an insert. To distinguish these states I declare an enumeration

The state of the grid has to be preserved over multiple roundtrips. On each roundtrip the page object is completely reconstructed so I cannot store it in a variable of the webform. In classical asp you would store such a value in the session object. In asp.net every page (and every control) has a ViewState property. In here you can store objects whose value is to be preserved over roundtrips. The ViewState is a collection of objects indexed by an identifying string. For this naming string I declare a constant. For the state I declare a private field initialized on gridState.browse. The Page_Prerender event saves the grids's state in the viewState.

In the Page_Load event the state of the grid is read form the ViewState, cast into its original type and stored in the private field.

Now all event handlers can read and alter the state. The add button sets the state to gridState.insert.

The edit event handler sets the state to gridState.edit.

The update eventhandler uses the state to see how it has to handle the database update

The state is tested in a switch. When it is  gridstate.edit the same code we met before is executed to get the row. When the state is gridstate.insert a new row is created and added to the dataset. To keep the compiler happy I had to initialize dr to null. I can check the new value to see if the row got initialized. The row's field receive their new value and the Update method writes the result to the database. Finally the grid is set to a browse state by setting the EditItemIndex to -1. Which is reflected in the state field by setting that to gridstate.browse.

Deleting a row

The grid has a button column to delete a row. When the button is clicked the DeleteCommand is executed. The code in the event handler looks pretty much the same as the code for editing a row.

The row is read from the database into the dataset. The row is deleted from the dataset after which the update method of the data-adapter will delete the row from the database.

When the end-user clicks the button the row is gone. What would be nice is the possibility to confirm a deletion in a dialog. The server-side controls don't have any dialog methods, Jscript does. A little snippet of client side script can do the trick. The data grid has an event which fires when a row is created. You can reach this event via the property window, the datalist control has an ItemCreated event in the list. In there I assign a snippet of Jscript to the delete button of the newly created item

The event handler first checks the type of item it is creating, only the row items contain a delete column. In the row it grabs the first control in the fourth cell (column) which should be the delete linkbutton. This button is rendered as an HTML control which does have a lot of attributes. These attributes can bet set via the Attributes property of the control. Amongst the attributes are the client-side event handlers, they contain pieces of script which execute in the browser. One of these attributes is OnClick, a snippet of Jscript, like return confirm('Do you want to delete this row ?');, makes a perfect value for this property. It is an expression which fires a message dialog. When the user answers the dialog with no the expression returns false. And the latter will cancel the postback to the server and so it does cancel the deletecommand. When the user clicks OK the postback to the server does occur and the deletecommand will be processed to actually delete the row.

The syntax of the expression will not be checked until the script is actually executed. When you forget the concluding ";" your browser will display an error message when the delete link is clicked.

Template columns

The datagrid component has several types of columns.

• Button columns like the edit and delete columns.
• Bound columns. The column contains data, like the anytext column we just met.
• Hyperlink columns. The data represents a hyperlink, the user can click the column to follow the hyperlink.
• Template columns. The cells of the column contains a template of HTML in which multiple controls can find a place..

You can convert a column to a template column from the column editor by clicking the link in the properties dialog :

To edit the template right click the datagrid and select the column to edit.

On the form designer you will now only see the template column, all other columns are hidden for the moment. To switch back to a full view on the grid right click the template and choose "End template editing".

The template has four parts. The HeaderTemplate is what the column header will look like. The ItemTemplate is what the column cell will look like when browsing. The EditItem template is what the cell will look like when editing the row. And the FooterTemplate is what the column footer will look like.

The result of converting a bound column to a template column gives you a good idea. The ItemTemplate contains a label control, in there the grid can display data. The EditItemTemplate contains a textbox, in there the user can edit data. The contents of a template are not limited to one control, you can fill a template with almost any number of controls, just if it was another form. But there are some differences between a template and a webform

• A webform is default in grid layout, a template is always in flow layout. Which implies that you can enter plain text in the template between the controls. To force the layout of the text and controls to defined rows and columns you can use a (plain html) table in the template.
• The controls in the template do not support server side events. The events page of all the controls in a template is blank.
• The controls in the template are no members of the webform class. This means you cannot access them directly from your code.

I give the datagrid a template column and I create an edit template. In there will be a textbox bound to the display value of the column, a validator control to validate the value, a dropdownlist to pick an initial value from the messages dataset  and a second text box to edit a field which is not in any column.

The controls are bound to the dataset using the databindings dialog, started from the object inspector. Default the controls are bound to the dataitem of the container. The dataitem contains the data of the current row in the grid, the row being edited.

Binding to the DataItem does not work for hiddentext field as it is not found in the dataset of the grid. I bind the hiddentext control to the DataSetRow, that dataset contains a full row of data as it is read from the database when an edit is started. All controls should be bound to this dataset as my application is editing a single row read from the database and not one of the many rows of the dataset which populate the grid.

The dropdownlist control is bound to the dataSetMessages1, it will display the list of messages in the dropdownlist.

Editing and updating fields in the template column

To get the editing to work for the template column I have to do a couple of things. The template contains items from the row detail dataset, I have to read the row from the database in the edit command event handler.

The datarow is read into the dataSetRow1. The DataBind method of a control fills the control with data from the associated dataset. The DataBind method also binds all the controls contained by the control. When I perform a DataGrid1.DataBind() in the PreRender event I bind the dataset to the grid and will also bind all controls in the grid's template columns (when visible) according to the databindings of the controls contained.

The template column contains a dropdownlist, it's dataset should be filled to perform a successful binding. This is taken care of in the prerender event handler

The dropdownlist will only be used to inititaliaze new rows. The state is checked to see if a new row is being added. If the dropdown is not used it does not have to be visible either. The visibility of controls can be set from code at run time. The problem is to get to the control. The controls in a template are no members of the webform class so they cannot be accessed directly from code. To get to the text in a simple edit, which is in fact the most basic form of a template, I used the fact that the textbox was the first control in the first cell of the Item.

You could start counting for all controls in your template to find the dropdownlistbox but this will lead to code which is not very clear and very difficult to maintain. The webcontrol class has a very use method FindControl which searches in the collection and subcollections of controls contained for a control with a given name. An Item is also a webcontrol, using this method the previous line can be written as :

Note that the code in both cases assumes that it will find a TextBox control. If no control is found or if the control is not a TextBox and the as operator leads to a null variable an exception is raised.

Using the FindControl method the prerender event handler searches the gridrow for the dropdownlist and sets its visibility. It cannot do this until the datagrid is bound to its dataset.

The item under edit is obtained from the Items collection of the grid. In the item FindControl returns the dropdownlist, using a constant for the name. The visibility of the list is set. As an extra the the add button is hidden when it's not appropiate to have one.

When it comes to updating the database the update command event handler uses the same FindControl method:

Again the control names are stored in constants. Note the trim method which will strip trailing blanks from the text values found

Adding new rows in the template column

To initiate the addition of a new row you have to do two things. First add the row to the full dataset which is bound to the grid, just like when adding a simple row. The template will work with the single row dataset, which contains fields which are not in the multi-row set. This single row has to be initialized as well. An easy way is to fill it from the multi row dataset:

DataSetGrid1 contains one row after AddSomeDataRow. This (empty) row is copied to dataSetRow1 by a call to Merge. DataSetGrid1 does not contain columns for all columns in dataSetRow1, these missing columns will get a default value.

When writing the updates to the database the selected value in the dropdownlist is pre-pended to the text.in the textbox

Again I use the FindControl method to get the dropdownlist.

Now all the details are ready and I can use the template to edit and add rows in the grid. The user can pick a value in the dropdownlist and the input is validated before the actual postback.

A closer view on the viewstate

In this paper I have used the viewstate to preserve the state of the grid over round trips. You can see this viewstate on the client in the the response as a hidden, encoded, field. In the the postback the browser posts the viewstate back to the server.

The viewstate reflects changes made to the page compared to the state the page was in as created by the webform class. The textbox controls use their viewstate to store their text, the datagrid uses the viewstate to store things like the columns layout and the edititemindex. The viewstate takes some toll as it has to go over the wire on every roundtrip. If a control does not change over roundtrips (for instance : most buttons don't) the viewstate of the control is actually only excess luggage in the network traffic. The good thing about the viewstate is that you can disable it per control. In the property window you will find the enableviewstate property in every control. EnableViewState is default set to true. In a datagrid which only displays data all its properties are set in the constructing events. There is no need to maintain a state there and if you set the EnableViewState property of the control to false you really save on network traffic. But if you want to do updates in the grid you are going to need its viewstate.

To conclude

In this story I have presented a way to do editing in the datagrid. There are many ways to get this done and  and I do hope you found some inspiration here to do it your way. Template columns are a little different but there is a lot you can do with them. The DataList componenet exploits them further, in a sequel to this story I will do some explorations over there.