使用 GridView 处理的 Data Entry 应用程序

• 下载 DataEntryGrid.zip - 264.7 KB

引言

标准 GridView 在数据录入页面上的表现不太好。然而，GridView 类非常灵活，可以以不同的方式进行调整。

本文描述了如何构建一个简单的 Workgroup Timesheet。

通过使用编辑框的网格，我们通过删除行按钮并避免模式来简化 GUI。服务器处理也减少了，因为没有为每次行更改进行往返。

在构建时，我们不知道需要的列。它们必须在代码中构建。动态列和 ViewState 机制存在一些挑战。

本文的一些重点是

• 运行时添加的动态列。
• 整个网格的编辑模式
• PIVOT 处理
• 自定义处理网格更改

使用的所有代码都在一个文件中，大约只有 200 行

使用代码

只需解压代码并在 DataEntryGrid 目录中打开 VS2005 网站。

示例在 EntryGrid.aspx 文件中，所有 C# 代码都在 EntryGrid.cs 中

示例数据库

项目中有示例数据库。展开解决方案资源管理器中的 App_Data 文件夹，双击 TimeDb.mdf

数据库模式非常简单。每个 timeLog 条目都存储了日期、小时以及对项目和人员的引用。

数据访问层

通过在 VS2005 中创建一个 DataSet，生成了一个类型安全的数据访问层。不需要代码。

TimeLog 行通过 TimeLog.GetData(@Date) 检索。

更改由过程 cudTimeLog 存储。它将在 TimeLog 中创建、更新或删除行。

GridView 声明

如果删除 GridView 的格式属性，它的声明就是

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