Convert GridViewRows into a DataTable to edit data in ASP .NET

<< Do not put slash in your post title | Home | What is the advantage of WCF >>

Convert GridViewRows into a DataTable to edit data in ASP .NET

I recently ran into a business problem that I can't find any sample code online. I think I worked out a tolerable solution.

The business scenario is this:

All rows retrieve from the database must be in edit mode, but only some fields should be in edit mode, not all. Editing one row at a time is out of the question.
User might make changes and THEN want to sort the grid. Any user input needs to be preserved.
Have to use .NET control that come out of the box with Visual Studio due to maintenance concerns.

The solution i came up with basically get the GridViewRows on postback and put everything into a datatable, then I am free to do whatever I want with it. Also, there is no hard-coded control ID or column name or cell index, which makes it more reuseable.

Solution limitation:

Only 2 type of edit controls are considered: TextBox and DropDownList. Their ID have to be "txt/ddl" + database column name
Any additional field has to come before the first column or after the last column

Code (in VB.NET)

GridView:

<asp:GridView ID="grdClaims" runat="server" style="position: absolute; top: 224px; left:10px;"

AutoGenerateColumns="False" AllowPaging="False" EnableViewState="True"

AllowSorting="True" EmptyDataText="No claims found." EnableTheming="False" >

<asp:TemplateField HeaderText="Select" ItemStyle-CssClass="gridtext">

<asp:CheckBox ID="chkSelectAll" runat="server" onclick="fnSelectAll(this.checked)" />

</HeaderTemplate>

 <asp:CheckBox ID="chkSelect" runat="server" />

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="CLAIMNR" HeaderText="Claim#" SortExpression="CLAIMNR">

</asp:BoundField>

A Drop Down List in the GridView

<asp:TemplateField HeaderText="Delete" SortExpression="DELETECLMFLAG">

<asp:DropDownList ID="ddlDELETECLMFLAG" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "DELETECLMFLAG").ToString().Trim()%> '>

<asp:ListItem>Y</asp:ListItem>

<asp:ListItem>N</asp:ListItem>

</asp:DropDownList>

</ItemTemplate>

</asp:TemplateField>

Code behind:

[Updated on 2/22/2010 to fix a bug and for the code to be more efficient]

Dim dtClaims As System.Data.DataTable

Dim iColOffSet As Integer = 2 'This is because the display grid has 2 extra cells at the beginning

dtClaims = dtInsertClaimDetails.Clone() 'dtModifyClaimDetails was cloned the first time the dataset was populated

' Limitation for GridView:

' Data not from the datasource must be at the beginning or the end of the layout, and iColOffSet has to be set

' The code currently assumes that only TextBox or Drop Down List will be used and their ID must be "txt" + ColumnName for

' TextBox or "ddl" + ColumnName for Drop Down List

For Each drGridView As GridViewRow In gvInsertClaimDetails.Rows

Dim dr As System.Data.DataRow = dtClaims.NewRow()

For i As Integer = iColOffSet To dr.ItemArray.Count + iColOffSet - 1

Dim sColumnName As String = dtClaims.Columns(i - iColOffSet).ColumnName.ToString()

Dim sInputOrData As String

If Not drGridView.Cells(iColOffSet).FindControl("txt" + sColumnName) Is Nothing Then

'Found the textbox

sInputOrData = CType(drGridView.Cells(iColOffSet).FindControl("txt" + sColumnName), TextBox).Text

ElseIf Not drGridView.Cells(iColOffSet).FindControl("ddl" + sColumnName) Is Nothing Then

'Found the drop down list

sInputOrData = CType(drGridView.Cells(iColOffSet).FindControl("ddl" + sColumnName), DropDownList).SelectedValue

Else

sInputOrData = drGridView.Cells(i).Text

End If

' If the data is empty, do not insert back into datatable if the type is numbers, leave them NULL

If (dtClaims.Columns(i - iColOffSet).DataType.ToString() = "System.Int32" _

Or dtClaims.Columns(i - iColOffSet).DataType.ToString() = "System.Double") _

And (String.IsNullOrEmpty(sInputOrData) Or sInputOrData = " ") Then

' Do nothing

Else

' Set the column

dr.Item(i - iColOffSet) = System.Convert.ChangeType(sInputOrData, dtClaims.Columns(i - iColOffSet).DataType)

End If

dtClaims.Rows.Add(dr)

Return dtClaims

[2010-04-16] Update:

The performance of this is good for a form with 30 columns (20 editable fields) for about 20 rows (a couple of seconds), but pretty bad for the same form with about 300 rows (1 minute). This form was built with a couple of drop down list filters at the top of the page to filter result, so the general purpose is served. This benchmark was done on P4 single core server with 3 GB RAM with default performance setting for the web server (and SQL on the same server).