Editing a generic table using ASP .NET datagrid

The reason I'm writing this article is very simple:

"I haven't found an existing solution to this damn problem!"

What I did find are bits and pieces of code in VB and C#, referring to the events of the datagrid.

The first thing to do is add the datagrid to the aspx. Note: you'll need to hook the events and change a few properties. I did it in this way:

<asp:GridView ID="Main_grd" runat="server" AllowPaging="true" AllowSorting="true" AutoGenerateDeleteButton="true" AutoGenerateEditButton="true" Width="100%" AutoGenerateSelectButton="false" OnRowDeleting="Main_grd_Deleting" OnPageIndexChanging="Main_grd_Paging" OnRowEditing="Main_grd_Editting" OnSorting="Main_grd_Sorting" OnRowCancelingEdit="Main_grd_CancelingEdit" OnRowUpdating="Main_grd_Updating"> </asp:GridView>

Now comes the "interesting" part, which events get the whole data, and which get only partial. Unfortunately the update is the most problematic, but it will wait for a while.

The easiest to implement is "Paging", the code behind is:

string sql = "SELECT * FROM " + Request["tablename"];
Main_grd.DataSource = GetDatasetFromQuery(sql);
Main_grd.PageIndex = e.NewPageIndex;
Main_grd.DataBind();

The sorting is supposed to almost identical. I've implemented it manually, though the reasons to do so are unclear. The implementation uses 2 hidden fields, one to indicate the direction (Ascending / Descending) and the other to identify the field

string sql;
if ((LastFilter_hid.Value == e.SortExpression) && LastDirection_hid.Value == "DESC")
{
sql = "SELECT * FROM " + Request["tablename"] + " ORDER BY " + e.SortExpression + " ASC";
LastDirection_hid.Value = "ASC";
}
else
{
sql = "SELECT * FROM " + Request["tablename"] + " ORDER BY " + e.SortExpression + " DESC";
LastDirection_hid.Value = "DESC";
}
Main_grd.DataSource = GetDatasetFromQuery(sql);
LastFilter_hid.Value = e.SortExpression;
Main_grd.DataBind();

Next comes the editing

MakeWhereClause(e.NewEditIndex);
string sql = "SELECT * FROM " + Request["tablename"] + " ORDER BY " + LastFilter_hid.Value + " " + LastDirection_hid.Value;
Main_grd.DataSource = GetDatasetFromQuery(sql);
Main_grd.EditIndex = e.NewEditIndex;
Main_grd.DataBind();

Note the first line. In the update event there are fields for OldValues and NewValues, but in a generic case they don't update. Leaving a nice "Null Reference" exception for the poor programmer who needs them. In order to by pass it, I did the "where clause" on the edit event, put it in a hidden field, and used it on the update. I've also used the same "where" in the delete.

private string GetValue(System.Web.UI.WebControls.WebControl ctrl)
{
string res = "";
switch (ctrl.GetType().ToString())
{
case "System.Web.UI.WebControls.TextBox":
res = "'" + ((System.Web.UI.WebControls.TextBox)ctrl).Text + "'";
break;
case "System.Web.UI.WebControls.CheckBox":
if (((System.Web.UI.WebControls.CheckBox)ctrl).Checked)
{
res = "1";
}
else if (!((System.Web.UI.WebControls.CheckBox)ctrl).Checked)
{
res = "0";
}
break;
default:
res = "";
break;
}
if ((res == "") || (res == "''"))
{
res = "NULL";
}
return res;
}

private void MakeWhereClause(int RowNum)
{
System.Text.StringBuilder update_cmd = new System.Text.StringBuilder();
for (int i = 0; i < Main_grd.Rows[RowNum].Cells.Count; i++)
{
string FieldName = ((System.Web.UI.WebControls.DataControlFieldCell)(((System.Web.UI.WebControls.TableRow)(Main_grd.Rows[RowNum])).Cells[i])).ContainingField.SortExpression;
string txt = Main_grd.Rows[RowNum].Cells[i].Text;
if ((txt != null) && (txt != "") && (txt.ToLower() != " "))
{
update_cmd.Append(" AND " + FieldName + "='" + txt + "'");
}
}
if (update_cmd.ToString() != "")
{
Where_hid.Value = update_cmd.ToString().Substring(5);
}
else
{
Where_hid.Value ="";
}
}

The delete looks like

MakeWhereClause(e.RowIndex);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("DELETE FROM " + Request["tablename"] + " WHERE " + Where_hid.Value, GetConnection());
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
AddErrorMessage(ex.Message);
}
string sql = "SELECT * FROM " + Request["tablename"] + " ORDER BY " + LastFilter_hid.Value + " " + LastDirection_hid.Value;
Main_grd.DataSource = GetDatasetFromQuery(sql);
Main_grd.EditIndex = -1;
Main_grd.DataBind();

And the update looks like


bool HasChanges = false;
System.Text.StringBuilder update_cmd = new System.Text.StringBuilder();
update_cmd.AppendLine("UPDATE " + Request["tablename"] + " ");
for (int i = 1; i < Main_grd.Rows[e.RowIndex].Cells.Count; i++)
{
string txt = GetValue((System.Web.UI.WebControls.WebControl)(Main_grd.Rows[e.RowIndex].Cells[i].Controls[0]));
string FieldName = ((System.Web.UI.WebControls.DataControlFieldCell)(((System.Web.UI.WebControls.TableRow)(Main_grd.Rows[e.RowIndex])).Cells[i])).ContainingField.SortExpression;
if (Where_hid.Value.IndexOf(FieldName + "=" + txt) < 0)
{
if (HasChanges)
{
update_cmd.Append(", " + FieldName + "=" + txt);
}
else
{
update_cmd.Append("SET " + FieldName + "=" + txt);
HasChanges = true;
}
}
}
if (HasChanges)
{
update_cmd.AppendLine(" WHERE (");
update_cmd.AppendLine(Where_hid.Value);
update_cmd.AppendLine(")");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(update_cmd.ToString(),GetConnection());
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
AddErrorMessage(ex.Message);
}
}
string sql = "SELECT * FROM " + Request["tablename"] + " ORDER BY " + LastFilter_hid.Value + " " + LastDirection_hid.Value;
Main_grd.DataSource = GetDatasetFromQuery(sql);
Main_grd.EditIndex = -1;
Main_grd.DataBind();

Canceling the edit mode is simply by setting the edit-index to -1. Just remember to erase the "where" or you might get an unexpected result.

The code assumes that it's already initialized in some way, that's because I did the initialization in the page_load. One can implement it in whichever way he likes, just remember to do so.

I hope there aren't too many errors, I used M$ Word to write this, and even god gave up on understanding this bloody software.