Data Entry application with PIVOT processing in GridView

• Download DataEntryGrid.zip - 264.7 KB

Introduction

The standard GridView does not work very well for data-entry pages. The GridView class is however very flexible and may be adapted in different ways.

This article describes building a simple Workgroup Timesheet.

By using a grid of edit boxes we simplify the GUI by removing row buttons and avoiding modes. Server processing is also reduced because roundtrips are not done for every row change.

At build time we do not know the columns needed. They must be built in code. There are some challenges with dynamic columns and the ViewState mechanism.

Some points of interest in this article are:

• Dynamic columns added at runtime.
• Edit mode for the hole grid
• PIVOT processing
• Custom handling of grid changes

All the code used is in a single file and is only about 200 lines

Using the code

Just unzip the code and open a VS2005 web site in the DataEntryGrid directory.

The sample is in the EntryGrid.aspx file and all C# code is in EntryGrid.cs

The sample database

A sample database is in the project. Expand the App_Data folder in the solution explorer and double click on TimeDb.mdf

The database schema is very simple. Each timeLog entry is stored with a date, hours and references to projects and persons.

Data access layer

A type safe data access layer was produced by creating a DataSet in VS2005. No code was needed.

TimeLog rows are retrieved by TimeLog.GetData(@Date) .

Changes are stored by the procedure cudTimeLog. It will create, update or delete rows in TimeLog.

The GridView declaration

If you remove the formatting attributes of the GridView its declaration is just:

</code><pre><asp:GridView ID="grdMain" runat="server" EnableViewState="False" 
    AutoGenerateColumns=False/> 

That is an empty grid with nothing stored in the ViewState and no columns generated automatically.

Building the grid in code

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            calendar.SelectedDate = DateTime.Now;
            LoadGrid();
        }
    }

Some highlights in LoadGrid() are:

        _dtPersons = taPersons.GetData();
        _dtProjects = taProjects.GetData();
        _dtTimeLog = taTimeLog.GetData(calendar.SelectedDate);

where _dtPersons is a DataTable and _taPersons is a TableAdapter created by the dataset designer.

We are now going to create a memory table, _dtEntry which will be bound to the grid.

The left column is the labels. It is created by:

_dtEntry.Columns.Add("ProjectName");
// Create the column in the grid
TemplateField tfProject = new TemplateField();
grdMain.Columns.Add(tfProject);
tfProject.ItemTemplate = 
new GridViewTemplate(ListItemType.Item, "ProjectName", "0", "String",true);
tfProject.HeaderTemplate = 
new GridViewTemplate(ListItemType.Header, "", "0", "String", true);

Dynamic templates and tracking updates

The class GridViewTemplate is an implementation of the ITemplate interface. With it we can instantiate new cells when the InstantiateIn member function is called.

void ITemplate.InstantiateIn(System.Web.UI.Control container)
{
    Label lbl = new Label();
    switch (_templateType)
    {
        case ListItemType.Header:
            container.Controls.Add(lbl);
            lbl.Text = _columnName;     
            break;

        case ListItemType.Item:
            if (_isLabel)
            {
                lbl.DataBinding += new EventHandler(lbl_DataBinding);
                container.Controls.Add(lbl);
            }
            else
            {
                TextBox edt = new TextBox();
                edt.ID = "edt" + _col;
                container.Controls.Add(edt);
                edt.DataBinding += new EventHandler(edt_DataBinding);
                edt.Columns = 1;
            }
            break;
    }
}

A new TextBox is created and a custom event handler is added for data binding.

We want to attach a client handler for tracking updates to the grid. When the TextBox looses focus after having its value changed, a JavaScript function is called. It stores the row, column and value to a hidden control.

void edt_DataBinding(object sender, EventArgs e)
{
    TextBox txtdata = (TextBox)sender;
    GridViewRow container = (GridViewRow)txtdata.NamingContainer;
    object dataValue = DataBinder.Eval(container.DataItem, _columnName);
    // Add JavaScript function sav(row,col,hours) which will save changes
    txtdata.Attributes.Add("onchange", "sav(" + container.RowIndex.ToString() + "," + _columnName + ",this.value)");
    if (dataValue != DBNull.Value)
    {
        txtdata.Text = dataValue.ToString();
    }
}

The function itself is very simple and is declared in the page as:

<script>
function sav(row,col,val) { 
    var hiddenChanges = document.form1.hiddenChanges;
    hiddenChanges.value += "/"+ row + "|" + col + "|" + val;
}
</script>

Create columns

We want one column for each person. Columns are created both in the grid and in the table _dtEntry

int ic = 0;
foreach (TimeDS.PersonsRow drPerson in _dtPersons)
{
    ic++;
    TemplateField tf = new TemplateField();
    tf.ItemTemplate =
        new GridViewTemplate(ListItemType.Item, drPerson.PersonId.ToString(), ic.ToString(), "Int32", false);
    tf.HeaderTemplate =
        new GridViewTemplate(ListItemType.Header, drPerson.Name.ToString(), ic.ToString(), "String", true);
    grdMain.Columns.Add(tf);
    dtEntry.Columns.Add(drPerson.PersonId.ToString());
}

Create Rows

Add a row to the table for every project and populate the first column whith the project name.

foreach (TimeDS.ProjectsRow drProject in dtProjects)
{
    DataRow r = _dtEntry.NewRow();
    _dtEntry.Rows.Add(r);
    r[0] = drProject.ProjectName.ToString();
}

Do Pivot processing

SQL Server 2005 has a PIVOT command. I did investigate but found that you need to know the columns in advance for SQL PIVOT processing. In this sample the PIVOT expansion is done in C#. We already have the rows and the columns, only positioning remains.

All TimeEntries must be moved to the correct position in dtEntry. We find the row/column position by searching dtProjects and dtPersons.

foreach (TimeDS.TimeLogRow drTimeLog in _dtTimeLog)
{
    int ie = 0;
    int ip = 0;
    foreach (TimeDS.PersonsRow drPerson in _dtPersons)
        if (drPerson.PersonId == drTimeLog.PersonId)
            break;
        else
            ie++;
    if (ie == dtPersons.Rows.Count)
        throw new Exception("Unknown Person");
    foreach (TimeDS.ProjectsRow drProject in _dtProjects)
        if (drProject.ProjectId == drTimeLog.ProjectId)
            break;
        else
            ip++;
    if (ip == dtProjects.Rows.Count)
        throw new Exception("Unknown Project");
    _dtEntry.Rows[ip][ie + 1] = drTimeLog.Hours.ToString();
}

Having created the grid columns and the table _dtEntry, we finish up by binding them with the normal:

Saving updates

The hiddenChanges control contains a string with a list of changes. Each one of these has a row, a column and a value. We need to find the person and the project.

// Changes have been stored here by JavsScript
string changes = hiddenChanges.Value;
// Retrieve tables with row and column definitions
_dtPersons = (TimeDS.PersonsDataTable)ViewState["_dtPersons"];
_dtProjects = (TimeDS.ProjectsDataTable)ViewState["_dtProjects"];

The changes string has its cells separated by '/' and its attributes by '|'

/<row>|<col>|<value>/

The tables _dtProject and _dtEntry are retrieved from the ViewState. This is needed because values may have changed since the page was sent to the client.

After parsing the string and extracting values we can call the T-SQL procedure cudTimeLog with the type safe access function:

        qta.cudTimeLog(
            calendar.SelectedDate, 
            ((TimeDS.PersonsRow)(_dtPersons.Rows[col])).PersonId,
            ((TimeDS.ProjectsRow)(_dtProjects.Rows[row])).ProjectId,
            hours);

The T-SQL code is:

/* create, update or delete  TimeLog rows */
ALTER PROCEDURE dbo.cudTimeLog(
    @Date datetime, @PersonId int,  @ProjectId int, @Hours decimal(12,2))
AS

DECLARE @TimeLogId int
SELECT @TimeLogId=NULL

SELECT @TimeLogId=TimeLogId FROM TimeLog WHERE 
    DATEDIFF(day,Date,@Date) = 0 AND PersonId=@PersonId AND ProjectId=@ProjectId 

IF @TimeLogId IS NULL AND @Hours IS NULL
    RETURN
ELSE IF @Hours IS NULL
    DELETE FROM TimeLog WHERE TimeLogId=@TimeLogId
ELSE IF @TimeLogId IS NULL 
    INSERT INTO TimeLog(Date,PersonId, ProjectId, Hours) VALUES(@Date, @PersonId, @ProjectId, @Hours)
ELSE 
    UPDATE TimeLog SET Hours = @Hours WHERE TimeLogId=@TimeLogId
RETURN

Points of Interest

Usability of data entry pages is enhanced by simplifying the GUI and removing grid buttons.

Sometimes we do not know the columns of a grid at compile time. The columns may then be created in code by using an ITemplate subclass. After a postback the grid must be recreated. This means loosing changes done by the user. Instead of trying to find a way to store the grid itself on the ViewState, I used a hidden field for tracking changes with JavaScript.

David Wheeler said: "Any problem in computer science can be solved with another layer of indirection" and he added: "But that usually will create another problem."

I am sure I have introduced some new problems with the hidden field. In practice it works well. You may want more error handling in the SaveGrid() function.

grdMain.DataSource = _dtEntry;
grdMain.DataBind();