Office Test - Tutorial 4 - Page 2

This is the second page (Page 1) of the 4th tutorial. This section covers the C# coding portion of the BillingTypeEditGrid web page.

4. Editable GridView - acts more like a spreadsheet.

In the previous page we already looked at setting up the Markup/HTML code necessary to create the editable GridView. Now its time to look at the actual code.

C# Code
We'll go over each function 1 at a time and discuss its purpose in the overall design.

Page_Load


protected void Page_Load(object sender, EventArgs e) {
  if (!this.IsPostBack) {
    Session["BillingTypeID"] = -1;
    Populate();
  }
}
   

This routine is run each time the page is loaded but IsPostBack will only be true after the first load of the page. Thus on the first load I set our local session variable for BillingTypeID to -1 and then populate the grid.

BillingTypeID is used here as a local primary key allowing us to access each grid row uniquely. Its only used for newly added rows until they get inserted into the database. I keep it negative so I know its not a real primary key. This value is only required if you use the gvBillingType_RowCommand method below instead of the cmdDelete_Click method.

Populate


protected void Populate() {
  DataTable billingTypes = new DataTable("BillingType");
  billingTypes.Columns.Add(new DataColumn("BillingTypeID", Type.GetType("System.Int32")));
  billingTypes.Columns.Add(new DataColumn("BillingTitle", Type.GetType("System.String")));
  billingTypes.Columns.Add(new DataColumn("BillingDesc", Type.GetType("System.String")));
  billingTypes.Columns.Add(new DataColumn("Status", Type.GetType("System.String")));

  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  FbCommand cmd = new FbCommand("SELECT \"BillingTypeID\",\"BillingTitle\",\"BillingDesc\" FROM \"BillingType\"", con);
  try {
    con.Open();
    FbDataReader reader = cmd.ExecuteReader();
    try {
      billingTypes.BeginLoadData();
      billingTypes.Load(reader);
      billingTypes.EndLoadData();
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
  CompleteChanges(billingTypes);
}
   

Populate is only called from the Page_Load and only called once when the page is first loaded. This method create a new DataTable object and defines on it the columns we need to store the data and any additional information for our grid. You could accomplish the same results as this example using any data storage method. Even just a simple object container with a list of a BillingType class you create would also work. I decided to use the DataTable because it works like a DataSet, it's very simple to use and supports multiple views of the data (original, current, proposed values).

A FbConnection and FbCommand are used to manually select the data from the Firebird database and then fill the DataTable. I used BeginLoadData/EndLoadData so that the DataTable doesn't think I'm adding new rows. This way the rows will not be flagged as new or modified. One advantage of using DataTable is that it works well with other dataset type objects such as FbDataReader in this case. Simply calling Load(reader) is enough code to fill the entire DataTable.

CompleteChanges


protected void CompleteChanges(DataTable billingTypes) {
  gvBillingType.DataSource = billingTypes;
  gvBillingType.DataBind();
  SaveDataTable(billingTypes);
}
   

Complete changes does 2 basic tasks. First it assigns the DataTable to the DataSource property of our GridView. Second it saves the DataTable results. This brings up another advantage of using a DataTable. It is compatible with a DataSet or DataSource which means it can be directly assigned to the DataSource property of our grid. The DataBind() call tells the grid to loop through the datasource and bind all the values to the fields according to the binding settings in the HTML tags properties for the grid.

SaveDataTable


protected void SaveDataTable(DataTable table) {
  Session["DataTable-BillingType"] = table;
}
   

This method just saves a copy of the DataTable in the Session. This means there is 1 copy for each user on the site, which is what we want for this grid. We have to save the DataTable this way because web pages and their associated classes are stateless and therefore a global class variable would be lost between posts.

GetDataTable


protected DataTable GetDataTable() {
  DataTable table = (DataTable)Session["DataTable-BillingType"];
  return table;
}
   

GetDataTable is just the reverse of the SaveDataTable. It retrieves the copy of DataTable from the user's Session cache.

cmdAddNewRow_Click


protected void cmdAddNewRow_Click(object sender, EventArgs e) {
  DataTable billingTypes = GetDataTable();
  DataRow newRow = billingTypes.NewRow();
  //newRow["BillingTypeID"] = GetNextBillingTypeID();
  newRow["BillingTypeID"] = DBNull.Value;
  newRow["BillingTitle"] = DBNull.Value;
  newRow["BillingDesc"] = DBNull.Value;
  newRow["Status"] = "Added";
  billingTypes.Rows.Add(newRow);
  CompleteChanges(billingTypes);
}
   

This is the event handler for clicking on the "Add New Row" button. All the fields are set to NULL for the new row and the DataTable is again saved to the user's session.

Notice the line that is commented out. This line should be used in conjunction with the gvBillingType_RowCommand method for deleting. It assigns a unique temporary negative primary key to the BillingTypeID. This provides an alternative way of identifying the current row.

cmdDelete_Click


protected void cmdDelete_Click(object sender, EventArgs e) {
  Button button = (Button)sender;
  GridViewRow gvRow = (GridViewRow)button.Parent.Parent;
  int index = gvRow.RowIndex;
  DataTable billingTypes = GetDataTable();
  DataRow row = billingTypes.Rows[index];
  row.Delete();
  CompleteChanges(billingTypes);
}
   

This is the simple method (as oppose to the gvBillingType_RowCommand method) for handling deletes. The sender parameter for this method corresponds the cmdDelete button. Keep in mind that while there is button per row in the HTML there is really only 1 button class and 1 button event handler.

We can determine the actual row by getting the grandparent of the button. The row which is a DataRow object has a RowIndex property that tells us which row from the top of the grid we are on. Keep in mind that this is relative to the actual visibly rendered grid, not the data source. If we were supporting paging or sorting the RowIndex would not necessary match the row in the data source. Since we are not using paging or sorting we can be certain the RowIndex is also the data source index (or can we?). There is actually a bug in this code. The bug only occurs if we delete a row other than the last row and then try to delete another row below it. The reason this bug occurs is because Deleted rows are not visible after they are deleted but they remain in the DataTable marked as deleted until the change is accepted (committed). This means that row 8 in the grid visually might be row 9 in the data source if a row further up was already deleted and the changes were not yet applied. We need to take this into account. See the TextChanged method which does account for this correctly. This is one of those reasons why the gvBillingType_RowCommand method for handling deletes is more consistent.

NOTE: This does not actually delete the row. It just marks it for deletion. If you cancel the changes on the DataTable the row will return. If you apply the changes the row is then deleted permanently. This is why no SQL is done at this point to delete it from the database. When the changes are applied we will delete it then and accept the changes.

cmdApplyChanges_Click1


protected void cmdApplyChanges_Click1(object sender, EventArgs e) {
  ApplyChanges();
}
   

The event handler for the cmdApplyChanges click just calls the internal method ApplyChanges().

TextChanged


protected void TextChanged(object sender, EventArgs e) {
  TextBox textBox = (TextBox)sender;
  GridViewRow gvRow = (GridViewRow)textBox.Parent.Parent;
  string colName = textBox.ID.Substring(3);
  int index = gvRow.RowIndex;

  DataTable billingTypes = GetDataTable();
  int rowIndex = 0;
  foreach (DataRow row in billingTypes.Rows) {
    if (row.RowState != DataRowState.Deleted) {
      if (index == rowIndex) {
        row.BeginEdit();
        if (textBox.Text == "")
          row[colName] = DBNull.Value;
        else
          row[colName] = textBox.Text;
        row["Status"] = "Modified";
        row.EndEdit();
        break;
      }
      rowIndex++;
    }
  }
}
   

This is the OnTextChanged event handler which we tied to all the edit fields in the grid. Normally this even would be called immediately after leaving an edit field if this was a Win32 application. Since this is a web page and that would be inefficient the calls to the event handler are actually Queued up and all sent at once during a postback. In our case the post back will occur when any of the buttons are pressed.

This method actually handles all the textboxes so we need to determine the corresponding field from the textboxes' ID. We names the ID using the format txt so by using a SubString to remove the "txt" portion we can determine the correct field name.

Notice in this routine when we loop through the Rows looking for an RowIndex match we need to skip deleted rows. This code adjusts for the problem mentioned in the cmdDelete_Click method above. The RowIndex does not always match the DataTable index once a row has been deleted and the changes not applied.

To fix this problem we should really make a GetRow routine to use in the delete and text change handlers. Review the following changes to the existing code:

protected DataRow GetRow(GridViewRow gridRow) {
  DataTable billingTypes = GetDataTable();
  int rowIndex = 0;
  foreach (DataRow row in billingTypes.Rows) {
    if (row.RowState != DataRowState.Deleted) {
      if (gridRow.RowIndex == rowIndex) {
        return row;
      }
      rowIndex++;
    }
  }
  return null;
}
protected void cmdDelete_Click(object sender, EventArgs e) {
  Button button = (Button)sender;
  GridViewRow gvRow = (GridViewRow)button.Parent.Parent;
  DataRow row = GetRow(gvRow);
  row.Delete();
  DataTable billingTypes = GetDataTable();
  CompleteChanges(billingTypes);
}
protected void TextChanged(object sender, EventArgs e) {
  TextBox textBox = (TextBox)sender;
  GridViewRow gvRow = (GridViewRow)textBox.Parent.Parent;
  string colName = textBox.ID.Substring(3);

  DataRow row = GetRow(gvRow);
  row.BeginEdit();
  if (textBox.Text == "")
    row[colName] = DBNull.Value;
  else
    row[colName] = textBox.Text;
  row["Status"] = "Modified";
  row.EndEdit();
}
     

These changes fix the problem with the cmdDelete_Click and also centralizes the GetRow loop so it can be used in other places such as the TextChanged event handler.

ApplyChanges


protected void ApplyChanges() {
  DataTable billingTypes = GetDataTable();

  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();

    lblChangeLog.Text = "<b>Change Log:<b></br>";
    int rowIndex = 0;
    while (rowIndex < billingTypes.Rows.Count) {
      DataRow row = billingTypes.Rows[rowIndex];
      if (row.RowState == DataRowState.Deleted) {
        FbCommand cmd = new FbCommand("DELETE FROM \"BillingType\" WHERE \"BillingTypeID\" = @BillingTypeID", con);
        cmd.Parameters.Clear();
        cmd.Parameters.Add("@BillingTypeID", row["BillingTypeID", DataRowVersion.Original]);
        cmd.ExecuteNonQuery();
        lblChangeLog.Text += "Deleted Row: BillingTypeID=" + row["BillingTypeID", DataRowVersion.Original].ToString()+ "</br>";
        row.AcceptChanges();
      } else if (row.RowState == DataRowState.Modified) {
        if ((row["BillingTypeID"] == DBNull.Value) || (row["BillingTitle"] == DBNull.Value)) {
          row["Status"] = "BillingTypeID and BillingTitle cannot be NULL";
          rowIndex++;
        } else {
          FbCommand cmd = new FbCommand("UPDATE \"BillingType\" SET \"BillingTitle\" = @BillingTitle, \"BillingDesc\" = @BillingDesc " +
                                        "WHERE \"BillingTypeID\" = @BillingTypeID", con);
          cmd.Parameters.Clear();
          cmd.Parameters.Add("@BillingTypeID", row["BillingTypeID"]);
          // Need to trim Char(NN) fields because they pad blank spaces automatically
          cmd.Parameters.Add("@BillingTitle", row["BillingTitle"].ToString().TrimEnd());
          cmd.Parameters.Add("@BillingDesc", row["BillingDesc"]);
          cmd.ExecuteNonQuery();
          lblChangeLog.Text += "Updated Row: BillingTypeID=" + row["BillingTypeID"].ToString() + "</br>";
          row.AcceptChanges();
          row["Status"] = DBNull.Value;
          rowIndex++;
        }
      } else if (row.RowState == DataRowState.Added) {
        if ((row["BillingTypeID"] == DBNull.Value) || (row["BillingTitle"] == DBNull.Value)) {
          row["Status"] = "BillingTypeID and BillingTitle cannot be NULL";
          rowIndex++;
        } else {
          FbCommand cmd = new FbCommand("INSERT INTO \"BillingType\"(\"BillingTypeID\",\"BillingTitle\",\"BillingDesc\") " +
                                        "VALUES(@BillingTypeID,@BillingTitle,@BillingDesc)", con);
          cmd.Parameters.Clear();
          cmd.Parameters.Add("@BillingTypeID", row["BillingTypeID"]);
          // Need to trim Char(NN) fields because they pad blank spaces automatically
          cmd.Parameters.Add("@BillingTitle", row["BillingTitle"].ToString().TrimEnd());
          cmd.Parameters.Add("@BillingDesc", row["BillingDesc"]);
          cmd.ExecuteNonQuery();
          lblChangeLog.Text += "Inserted Row: BillingTypeID=" + row["BillingTypeID"].ToString() + "</br>";
          row.AcceptChanges();
          row["Status"] = DBNull.Value;
          rowIndex++;
        }
      } else
        rowIndex++;
    }
    CompleteChanges(billingTypes);
  } finally {
    con.Close();
  }
}
   

As I already mentioned the changes made in the DataTable are in memory only and not actually applied until you call this method. The DataTable is handy because it tracks multiple states of the data. This gives you access to row state (new, modified, deleted) and the current data and the original data as well. Here we loop through the DataTable as check if a row was modified, added or deleted and correspondingly update, insert or delete it from the database. After each row is changed row.AcceptChanges() is called. This tells the DataTable to accept the changes and the state is cleared and the current data becomes the original data.

You may notice that during deletes DataRowVersion.Original version is used to get the primary key. This is required because deleted rows do not maintain a current version of the data which is the default view of the data.

Another thing to keep in mind is the loop index. Notice the loop is incremented only if we update or insert a row. The obvious reason for this is that deleted row are removed from the DataTable when row.AcceptChanges() is called which means our current rowIndex becomes the next row automatically. You should also remember that newly added rows that are deleted before the changes are applied are deleted instantly because they never really got added.

RowCommand: Alternative delete method

The following 3 methods as well as some of the comments code is earlier routines demonstrate an alternative way of handling the row delete. This method requires a little more code and complexity up front and requires the management of a temporary unique identifier. The advantage of this method really comes down to making the accessing the row more transparent. Remember in the other method how just taking deleted rows into account complicated getting access to the correct row from the data source. Imagine if we decide to handle paging, sorting and filter in the grid. More and more code would need to be added to handle determining where the DataTable row is compared to the grid's row. The following methods avoid all those problems.

GetNextBillingTypeID


protected int GetNextBillingTypeID() {
  // This was added to work with GetRow and gvBillingType_RowCommand for deleting
  int BillingTypeID = (int)Session["BillingTypeID"];
  BillingTypeID--;
  Session["BillingTypeID"] = BillingTypeID;
  return BillingTypeID;
}
     

This method generates a temporary unique ID for newly added rows until they are applied. The session tracks the next BillingTypeID to use and they are always negative so we don't have to worry about overlap with keys from the database itself.

Another way to handle this would be to add a separate column in the DataTable and in the grid (it could be hidden in the grid). This column would just be there to provide a unique id separate from the BillingTypeID. The column value could be generated using a GUID or a hash value. Alternatively since we are using Firebird in this example I could simply ask the database for a Generator value and use that instead. In fact the generated value could be the final primary key not just a temporary one.

GetRow


protected DataRow GetRow(DataTable billingTypes, string value) {
  foreach (DataRow row in billingTypes.Rows) {
    if (row.RowState == DataRowState.Deleted) {
      if (row["BillingTypeID", DataRowVersion.Original].ToString() == value)
        return row;
    } else {
      if (row["BillingTypeID"].ToString() == value)
        return row;
    }
  }
  return null;
}
     

This is the method for finding the row in the DataTable that matches the row returned from the RowCommand. This routine can find deleted rows as well as any other. Although generally there is no reason to lookup deleted rows. This is a really simple piece of code and it is independent of how the data is displayed. Thus deleted rows, sorting, filtering, paging etc. do not affect this routine and it never needs to get any more complicated.

gvBillingType_RowCommand


protected void gvBillingType_RowCommand(object sender, GridViewCommandEventArgs e) {
  if (e.CommandName != "") {
    DataTable billingTypes = GetDataTable();
    DataRow row = GetRow(billingTypes, e.CommandArgument.ToString());
    string rowHash = e.CommandArgument.ToString();
    if (row != null) {
      if (e.CommandName == "DeleteRow") {
        row.Delete();
        CompleteChanges(billingTypes);
      }
    }
  }
}
     

RowCommand is sort of a top level event on the grid. Whenever an event on a internal sub object (like the delete button) is sent a RowCommand is also sent if the CommandName is set in the Markup code.
<asp:Button ID="cmdDelete" runat="server" CommandArgument='<%# Eval("BillingTypeID") %>'
  CommandName="DeleteRow" Text="Delete" OnClick="cmdDelete_Click" />
       

Looking here at the HTML for this button the OnClick is not necessary if we are only using the RowCommand method. The CommandName is assigned directly here to allow us to differentiate this RowCommand from another if we had other buttons also using RowCommand. The CommandArgument using the Eval method to return the BillingTypeID back to this routine through the GridViewCommandEventArgs.CommandArgument. This BillingTypeID will be either the real primary key or our temporary negative key but in either case it uniquely identifies which row sent the RowCommand.

This is a very simple way to make an editable GridView. Obviously things like error detection and quick navigation of cells using the cursor keys is missing.

If anyone is interested in a more generic version of this code that uses the RowCommand method only I could whip something up and post it.

see next tutorial...Employee: Stored Procedure/Trigger web page which performs inserts that :1. allow a trigger to generate the primary key and 2. use a stored procedure.