Click here to Skip to main content
Click here to Skip to main content

Data Entry application with PIVOT processing in GridView

, 12 Apr 2007
Rate this:
Please Sign up or sign in to vote.
Describes building a WorkGroup TimeSheet with GridView
Screenshot - EntryPage.png

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.

Screenshot - DbSchema.png

Data access layer

Screenshot - DataSet.png

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();

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Ole E
Web Developer
Norway Norway
My name is Ole Eirik Øren
 
I am an independent programmer and consultant living in Oslo, Norway. The last three years I have worked with ASP.NET, developing systems for handling time and attendence data.
 

Comments and Discussions

 
QuestionUnable to debug the application Pinmemberramsai197321-Jun-12 1:07 
GeneralMy vote of 5 PinmemberMember 381279314-Apr-12 0:12 
QuestionNarrowing results with use of dropdowns/textboxes PinmemberMember 78517002-Apr-12 11:09 
GeneralMy vote of 5 Pinmembermanoj kumar choubey7-Feb-12 1:13 
GeneralGenius Pinmemberallwebdesign19-Apr-11 11:42 
GeneralMy vote of 5 Pinmemberkdio7-Oct-10 21:40 
GeneralExcelent Pinmemberericsk826-Sep-08 12:36 
GeneralHi..there are some problems occur while downloading... Pinmemberbrundavanamhari1233-Jun-08 1:54 
GeneralNice Stuff, i have an improvised version. Pinmemberjosephsj10-Oct-07 21:51 
GeneralRe: Nice Stuff, i have an improvised version. Pinmembertonkster200313-Nov-07 16:53 
GeneralRe: Nice Stuff, i have an improvised version. Pinmemberdan.hadrian23-Jun-08 5:54 
GeneralRe: Nice Stuff, i have an improvised version. Pinmembercraibuch7-Jul-08 16:54 
Generalfile download Pinmemberyan1945412-Sep-07 5:07 
GeneralRe: file download PinmemberOle E12-Sep-07 5:22 
QuestionDataEntryGrid with Ajax? Pinmemberajrite20-Jun-07 8:12 
Questiondynamic Dropdown? PinmemberUmer Khan15-Apr-07 19:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 13 Apr 2007
Article Copyright 2007 by Ole E
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid