A sorting and paging DataGrid loading data on-demand, saving changes in bulk from and to an Oracle database, in ASP.NET

• Download source - 660 Kb

Introduction

When developing an Enterprise Application, there is always a need to present data in a grid, for a user to review or modify it.

Microsoft provides a very powerful tool with Visual Studio .NET, the DataGrid Web Component. There are many components out there that come in different shapes, colors and features. This example is using the standard implementation provided by Microsoft with an Oracle database.

The motivation behind this article is based on these facts:

• A client of mine wanted to use Oracle on AIX 5.2 as a back-end.
• After looking at the different options, we made the choice of using ASP.NET because it provides a wonderful tool called the DataGrid for presenting business data.
• As a technical manager, I had the responsibility to put together a working sample program to help developers get started on the project.
• Most (if not all) articles in MSDN or on web sites such as Code Project describe the implementation of a DataGrid with SQL Server, thus, this example had to be completely written from scratch.
• Paging through the resultset is usually done by either loading the whole set in the memory of the ASP.NET process or by using a complex technique that needs a temporary table. This was not an option as the system needed to support 200+ simultaneous users.
• The application should avoid transferring data with the IIS Web Server as much as possible, since one of the major constraints of the system to be used in production (at my client's 200 sites) is data transfer through a satellite connection which has a high latency.

This example shows how to:

• Create and populate a sample table in Oracle with SQL Loader (scripts are provided in the Oracle folder).
• Write a query to retrieve only the data needed from Oracle (also known as retrieving rows n through m).
• Use parameterized queries with bind variables, to improve performance and prevent "SQL injection" attacks.
• Connect to Oracle and set up a connection pool with ASP.NET.
• Send a query to Oracle from C# code.
• Sort the result, ascending or descending.
• Page through the resultset.
• Change the number of rows per page.
• Update data "in bulk" in PL/SQL to improve performance.
• Call a PL/SQL procedure in Oracle.
• Use some CSS to modify the rendering of the DataGrid.
• Customize the output of the grid by using tricks to use the DataGrid.

This example does not show how to:

• Set up an Oracle database.
• Install the ODP.NET component provided by Oracle.

Prerequisites

Before being able to run this sample, there are a few pieces that need to be put together on your machine.

• Visual Studio .NET 2003 installed on your PC.
• You need to have access to an Oracle database server (version 9 or 10, this sample has not been tested on 8), running on Windows or Unix. This sample has been tested with Oracle 9 & 10 running on Windows XP and IBM AIX 5.2.
• In order to connect your machine to the Oracle database (either locally or remotely), you need to download the Oracle Data Provider developed by Oracle. The ODP.NET (as it is called) can be downloaded here. If you don't have any specific requirements, you should download version 10. Although the default Oracle Data Provider shipped with VS.NET may work, it has not been tested.

In this example, it is also assumed that the reader knows how to use the standard DataGrid Web Control shipped with VS.NET 2003. Many articles can be found on CodeProject that demonstrate how to use this component. The objective here is to focus on the Oracle side and how to leverage its capability.

Installation of the database part

In order to query the sample table, one must first create the table and populate it. This sample uses a simple table that stores information about the US ZIP codes. Scripts are provided in the archive in the folder called Oracle.

First, we need to create the table. You should use a "normal" Oracle user, avoid using sys or system as these are used for administrative tasks. In order to execute the script, you should issue the following command:

sqlplus orauser/orapasswd@INSTANCE

where <orauser> and <orapasswd> are the user name and password that your DBA gave you (don't forget the "slash" in between) and INSTANCE is the name of the instance you want to logon to, as defined in your tnsnames.ora file.

If your connection was successful, you should see a message like this one (Oracle 9i on Windows):

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>

You can now execute the script (assuming your ran the sqlplus program in the folder where the table.sql script can be found).

@table.sql

At this point, you should be able to see the table in your database. Try the following command, to see if it exists:

desc ZIPCODE;

You should see something similar to the following:

Name                                  Null?    Type
------------------------------------- -------- --------------

ZIP                                   NOT NULL VARCHAR2(5)
STATEID                               NOT NULL CHAR(2)
NAME                                           VARCHAR2(40)
LONGITUDE                                      NUMBER
LATITUDE                                       NUMBER
POPULATION                                     NUMBER
ALLOCFACTOR                                    NUMBER

Now that we have created the table, we need to populate it. Since we are creating a table with thousands of records, we are going to use a tool called SQL Loader that can take a file as an input and populate a table in Oracle directly. There are many options to parameterize this tool and everything is based on a control file. In the Oracle folder, there are two files provided. These two files will be used to load data into the table. These two files are:

• zipcode.dat that contains the data.
• zipcode.ctl that contains the information needed by SQL Loader to do its job.

Now, we need to issue the following command to actually trigger the load. This assumes you are calling sqlldr from the directory where the .dat and the .ctl files reside. If this is not the case, just provide the full path.

sqlldr orauser/orapass@INSTANCE control=zipcode.ctl data=zipcode.dat

where <orauser> and <orapasswd> are the user name and password that your DBA gave you (don't forget the "slash" in between) and INSTANCE is the name of the instance you want to logon to, as defined in your tnsnames.ora file.

Once this is complete, you should be able to query the table and see if it contains data. Log on with sqlplus once again:

sqlplus orauser/orapasswd@INSTANCE

And issue the following command:

SQL> SELECT ZIP, NAME FROM ZIPCODE WHERE ROWNUM < 11;

ZIP   NAME
----- ----------------------------------------
01001 AGAWAM
01002 CUSHMAN
01005 BARRE
01007 BELCHERTOWN
01008 BLANDFORD
01010 BRIMFIELD
01011 CHESTER
01012 CHESTERFIELD
01013 CHICOPEE
01020 CHICOPEE

10 rows selected.

The rownum < 11 in the where clause is used to limit the output to 10 rows.

Now, if you ask Oracle how many rows it has in this table, you should get exactly the same result as:

SQL> select count(1) from zipcode;

  COUNT(1)
----------
     29467

At this point, you can exit sqlplus, the setup of the table is complete.

Before going any further, a quick comment about the table we just created. If you look at the table.sql script, you can see that the declaration of the structure of the table contains the following:

organization index

This tells Oracle that the data in this table should be physically organized as an index on the disk, using the key fields as the fields to organize the index on. In this case, the primary key (that ensures uniqueness) is the ZIP field. The objective here is to reduce I/O needs, since this kind of a table is very probably going to be a "look-up" table, where data is rarely written to and is read from most of the time. This is not a structure we would use for a table that is involved in heavy OLTP activity. For a look-up table, such a physical organization is very attractive, because we probably need to look-up data by providing the ZIP and retrieve the information associated with it. If you query the table without providing any where clause or any order by clause, the data will be sorted on the ZIP, since this is how the data is physically organized on the disk.

Installation of the ASP.NET part

This project is a regular web project in Visual Studio .NET. Copy the datagrid folder from the Zip archive to your hard drive and create a virtual folder in IIS that points to this folder.

Then, modify the Web.config file to actually point your IIS to your Oracle instance. The three parameters to modify are Source, User ID and Password.

<appSettings>
 <add key="connectionString" value="Data Source=XXXX;Pooling=true; 
    User ID=XXXX;Password=XXXX; Min Pool Size=3;Max Pool Size=25;
    Connection Lifetime=7200;Connection 
    Timeout=15;Incr Pool Size=3;Decr Pool Size=1;" />
</appSettings>

Retrieving rows n through m

As promised at the beginning of this article, we will now see how one can retrieve rows n through m from a set. A set can either be a table, the result set generated by a view, or any result from any SQL query. To the best of my knowledge, there is no SQL command understandable by Oracle such as:

Select X, Y, Z
From Table
Where ...
And ROWNUM BETWEEN 21 AND 40

that would be able to return rows 21 through 40 only. Be careful here, there is a trap. If the set is not explicitly fully sorted, extracting rows 21 through 40 is not guaranteed to mean the same thing every time. More on this later.

When looking for top notch information about Oracle, one of my favorite sites is Tom Kyte's web site. The question about retrieving rows n through m is a very popular one and you can read more about it here.

The whole idea is to use the "hidden" column provided by Oracle with every result set, called ROWNUM. This pseudo column is actually an ordinal number that Oracle gives us if we want to use it. Here is an example. Let's assume we want to retrieve rows 21 through 40 because we are on page 2 and there are 20 rows displayed per page. Let's take a look at this query:

SELECT P.*, ROWNUM RNUM
FROM (SELECT ZIP, STATEID, NAME, LONGITUDE, 
             LATITUDE, POPULATION, ALLOCFACTOR
      FROM   ZIPCODE
      ORDER BY ZIP) P
WHERE ROWNUM < 41;

The result of this query is:

ZIP   ST NAME          LONGITUDE   LATITUDE POPULATION ALLOCFACTOR     RNUM
----- -- ------------ ---------- ---------- ---------- ----------- --------

01001 MA AGAWAM         72.622739  42.070206     15338     .002549        1

01002 MA CUSHMAN        72.51565   42.377017     36963     .006144        2

01005 MA BARRE          72.108354  42.409698      4546     .000756        3

...

ZIP   ST NAME          LONGITUDE   LATITUDE POPULATION ALLOCFACTOR     RNUM
----- -- ------------ ---------- ---------- ---------- ----------- --------
01081 MA WALES         72.204592  42.062734      1732     .000288       40

40 rows selected.

This is fine for half of our problem, but we get 40 rows when we would like to get only 20. The solution comes from using this resultset as a source for an outer query that will "trim" it.

SELECT ZIP, STATEID, NAME, LONGITUDE, LATITUDE, POPULATION, ALLOCFACTOR
FROM (SELECT P.*,
             ROWNUM RNUM
      FROM (SELECT ZIP, STATEID, NAME, LONGITUDE, 
                   LATITUDE, POPULATION, ALLOCFACTOR
            FROM   ZIPCODE
            ORDER BY ZIP) P
      WHERE ROWNUM < 41)
WHERE RNUM > 20;

This time, we use a second ROWNUM pseudo-column to filter on ROWNUM >= 20. Be careful when using ROWNUM as even though it has the same name for all the tables in a query, it doesn't have the same meaning. The ROWNUM column is dynamically generated for every set.

Now, the result of the query is:

ZIP   ST NAME                  LONGITUDE   LATITUDE POPULATION ALLOCFACTOR
----- -- -------------------- ---------- ---------- ---------- -----------
01036 MA HAMPDEN               72.431823  42.064756      4709     .000783

01038 MA HATFIELD              72.616735   42.38439      3184     .000529

01039 MA HAYDENVILLE           72.703178  42.381799      1387     .000231

...

ZIP   ST NAME                  LONGITUDE   LATITUDE POPULATION ALLOCFACTOR
----- -- -------------------- ---------- ---------- ---------- -----------
01080 MA THREE RIVERS          72.362352  42.181894      2425     .000403

01081 MA WALES                 72.204592  42.062734      1732     .000288

20 rows selected.

The major risk with this kind of a query is to give a ORDER BY clause in the inner most query that does not unambiguously sort the set we are "trimming". Since the ZIP field is the key of the table, it is sufficient. However, in our grid, we want to allow the user to sort on any column, both ascending and descending. We need to make sure the sort used is sufficient to be unambiguous.

Parameterized queries: Use bind variables, to improve performance and prevent "SQL injection" attacks

In the query above, there are three parameters we would like to define to use in our code. Obviously, the limits (20 and 41) are needed but also the sorting column(s).

In order to pass parameters to a query, the first approach often taken is to concatenate strings to regenerate the SQL text "on the fly". This could look like this:

...
strSQL += "WHERE ROWNUM < ";
strSQL += (lowerLimit).ToString();
...

We are not going to talk about this a lot. Just don't do it! If you do something like this, every parameterized query is unique (or almost) from an ASCII string perspective. This will force Oracle to parse these queries every time, in order to select an execution plan. Instead of this, use bind variables. To do this, we just need to rewrite the query in a more generic format and put the equivalent of place holders to let the Oracle parser know where we need to use variables. Here is the query, rewritten in a more generic format:

SELECT ZIP, STATEID, NAME, LONGITUDE, LATITUDE, POPULATION, ALLOCFACTOR
FROM (SELECT P.*,
             ROWNUM RNUM
      FROM (SELECT ZIP, STATEID, NAME, LONGITUDE, 
                   LATITUDE, POPULATION, ALLOCFACTOR
            FROM   ZIPCODE
            ORDER BY ZIP) P
      WHERE ROWNUM < :1)
WHERE RNUM > :2;

Note the place holders in bold and the colon symbol used to define variable 1 and 2.

By using these "bind variables" as Oracle calls them, the ASCII string sent to the database is always the same, thus, the engine can cache its execution plan and reuse it over and over again. This greatly improves the speed and scalability of the system.

Another benefit of these bind variables is the impossibility to trick the system with a SQL injection attack, since the execution plan is already defined by the engine and it will not reparse the query. If we try to pass wrong parameters (after a SQL injection attack for instance), Oracle will just raise an exception telling the calling code that the parameters are of a wrong type, but it will not execute the modified query.

Connecting to Oracle and setting up a connection pool with ASP.NET

Remember the connection string we had to modify in the Web.config file? This string is used by the ASP Work Process to define a connection pool, based on a byte for byte matching. This is why it is defined directly in Web.config. This way, we are sure that we won't have a typo (thus ensuring that the same connection pool is used for all threads) and it is much easier for maintenance when we need to point the whole application to another database server. There are a few parameters to define of course:

Pooling=true;
Min Pool Size=3;
Max Pool Size=25;
Connection Lifetime=7200;
Connection Timeout=15;
Incr Pool Size=3;
Decr Pool Size=1;

These parameters are self-explanatory, except "Connection Lifetime" and "Connection Timeout" that are a bit confusing. Here is the official documentation for these two parameters, taken from the documentation installed by the ODP.NET component:

Connection Lifetime: Maximum life time (in seconds) of the connection. This attribute specifies the lifetime of the connection in seconds. Before the connection is placed back into the pool, the lifetime of the connection is checked. If the lifetime of the connection exceeds this property value, the connection is closed and disposed. If this property value is 0, the connection lifetime is never checked. Connections that have exceeded their lifetimes are not closed and disposed of, if doing so brings the number of connection in the pool below the Min Pool Size.

Connection Timeout: Maximum time (in seconds) to wait for a free connection from the pool This attribute specifies the maximum amount of time (in seconds) that the Open() method can take to obtain a pooled connection before it terminates the request. This value comes into effect only if no free connection is available from the connection pool and the Max Pool Size is reached. If a free connection is not available within the specified time, an exception is thrown. Connection Timeout does not limit the time required to open new connections.

This attribute value takes effect for pooled connection requests and not for new connection requests.

For those of you who have already installed ODP.NET, here is the link to find details about all the possible parameters to use in the connection string: ms-help://MS.VSCC.2003/Oracle.DataAccess.1033/DataProviderHelp/ HTML/ConnectionConnectionString.htm#i1000291.

From now on, every time we need to open a connection to Oracle, we are going to do it like this:

try
{
    // The "connectionString" is defined in the Web.config file
    connection = new 
      OracleConnection(ConfigurationSettings.AppSettings["connectionString"]);
    connection.Open();
    ...
}
catch (Exception ex)
{
    ...
}
finally
{
    // Clean up
    ...
    if (connection != null)
    {
        connection.Close();
        connection.Dispose();
    }
}

As you can see, we retrieve the connectionString string from the settings of the application at runtime.

Another detail to notice is the invocation of Close() and Dispose() in the finally section in order to give the connection back to the pool. If we do not do it, the pool would be "leaking" and we would have to wait until the connections time out to have them available again in the pool.

Sending a query to Oracle from the C# code

We now have a table with data defined in our database and we know how to get a connection from the pool and give it back once we are done with it. Let's see how we can actually do something useful by sending a query to Oracle and retrieving the result set.

Here is an example that shows how we can use the query we looked at earlier. The general pattern is always the same.

private void DataBind(string orderby, string direction, 
                        int pageindex, int rowsperpage)
{
    // An important thing to note here:
    // When we select data one page at a time,
    // we need to fully qualify the inner most "ORDER BY"
    // clause, so that the data is explicitly
    // fully sorted. If not, then there is no guarantee
    // as to the order which implies that we may see
    // strange things like having the same entry duplicated
    // and showed on different pages.

    // It is the responsibility of this method
    // to do the "magic" and organize the "ORDER BY" clause
    // sent to Oracle to prevent this problem from occurring.

    // This needs to be modified for
    // all queries that would be processed like this.
    // The objective is to generate
    // a modified "ORDER BY" clause that FULLY sorts
    // UNAMBIGUOUSLY the result set before Oracle trims it.

    // Use a default sorting.
    string modifiedOrderby = "ZIP ASC";

    if (orderby.Equals("ZIP"))
        // Note the space after the column name.
        // ZIP being the key, the result set is guaranteed
        // to be fully sorted by using it alone.
        modifiedOrderby = "ZIP " + direction;
    else
        // Note the space and the comma after and before the column names.
        // Note also that we choose to sort on
        // the user's choice and then on the ZIP ascending.
        // The reason for this is to guarantee
        // that the result set is unambiguously sorted.
        // The ZIP column being the key of the table,
        // the sorting is guaranteed to be unique.
        modifiedOrderby = orderby + " " + direction + ", ZIP ASC";

    try
    {
        // Same as above. Get the connection string from the Web.config file.
        // IMPORTANT:
        // The way the ODP.NET code can
        // identify the connection pool is by matching
        // Connection strings. Thus, one should
        // only retrieve such a string from the
        // Web.config file to avoid typos and simplify everything when we need
        // to redirect to another DB server.
        connection = new 
          OracleConnection(ConfigurationSettings.AppSettings["connectionString"]);
        connection.Open();

        // Note in the following query that we are
        // using place holders where parameters will be used.
        // These place holders will be bound to variables, thus ensuring:
        // - Security: SQL-Injection is impossible with bind variables.
        // - Performance: We greatly improve the performance
        //           and scalability of the application,
        // by simplifying Oracle's job. The text
        // of the query will be recognized by the Oracle parser
        // and fetched from its library cache, thus avoiding "hard parses".
        string SQLString =    "SELECT ZIP, STATEID, NAME," + 
                    " LONGITUDE, LATITUDE, POPULATION, ALLOCFACTOR " +
                    "FROM (SELECT P.*, " +
                    "             ROWNUM RNUM " +
                    "      FROM (SELECT ZIP, STATEID, NAME," + 
                    " LONGITUDE, LATITUDE, POPULATION, ALLOCFACTOR " +
                    "            FROM   ZIPCODE " +
                    "            ORDER BY " + modifiedOrderby + ") P " +
                    "      WHERE ROWNUM < :1) " +
                    "WHERE RNUM >= :2";

        command = new OracleCommand( SQLString, connection);
        command.CommandType = CommandType.Text;
        // This is used to tell the ODP.NET code that the
        // binding should be done using the sequence the parameters
        // are passed in.
        command.BindByName = false;

        // The first parameter to use is defined
        // as a "Decimal" (Oracle datatype) and as "input" only (from
        // Oracle's perspective)
        command.Parameters.Add(new OracleParameter("ROWNUM", 
             OracleDbType.Decimal, ParameterDirection.Input));
        // Set the actual value of the parameter, based on the value passed
        command.Parameters["ROWNUM"].Value = (1 + pageindex 
                              * rowsperpage + rowsperpage);

        // The second parameter to use is defined
        // as a "Decimal" (Oracle datatype) and as "input" only (from
        // Oracle's perspective)
        command.Parameters.Add(new OracleParameter("RNUM", 
                OracleDbType.Decimal, ParameterDirection.Input));
        // Set the actual value of the parameter, based on the value passed
        command.Parameters["RNUM"].Value = (1 + pageindex * rowsperpage);

        // Execute the query and bind the grid to the result set.
        DATAGRID.DataSource = command.ExecuteReader();
        DATAGRID.DataBind();

    }
    catch(Exception ex)
    {
        ERROR.Visible = true;
        ERROR.Text += ex.Message;
    }
    finally
    {
        if (dataReader != null)
        {
            dataReader.Close();
            dataReader.Dispose();
        }

        if (command != null)
            command.Dispose();

        if (connection != null)
        {
            connection.Close();
            connection.Dispose();
        }
    }
}

The only "trick" here is that we use a string concatenation to pass "ASC" or "DESC" in order to sort ascending or descending. There is no way to pass this information through a bind variable. However, this is not a problem here because these queries are actually different, since the sorting clause forces Oracle to generate different execution plans. This approach is not contradicting the recommendation to use bind variables.

One last thing about this code snippet: The values passed to Oracle for the paging are dynamically calculated, based on the number of rows per page the user wants to see and the page index which indicates what page number the grid is currently looking at.

Finally, the actual call is done by calling ExecuteReader() which returns an OracleDateReader object that provides methods to parse the resultset. In this example, we chose to pass the OracleDataReader object returned directly to the DataGrid object for binding.

Sorting the result, ascending or descending

Once the method to retrieve pages of data is built, sorting the data is a snap. We need to add a handler to the DATAGRID_SortCommand event triggered when the user clicks on a column header. The implementation here sorts ascending when first clicked, then descending, then ascending... In order to do this, we need to save the column the data is currently sorted on and the direction. This is done by saving these parameters in the ViewState.

private void DATAGRID_SortCommand(object source, 
         System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{

    // This event is triggered when the user
    // clicks on a column header, thus requesting
    // that the data be sorted, according to the selected column.

    string direction;

    // Check if this is the second time the same column
    // name is selected (this means we need to sort descending)
    // Otherwise, the sort should be done ascending since
    // this is a new column (compared to the one saved in the
    // view state) that was selected.
    if (CurrentSortDirection == "ASC" && CurrentSortColumn == e.SortExpression)
        direction = "DESC";
    else
        direction = "ASC";

    // Remember what column the data is sorted by.
    CurrentSortColumn = e.SortExpression;

    // Make sure the page remembers the sorting direction.
    CurrentSortDirection = direction;

    // Reselect the set of data to show. Note that the page index is reset to 0.
    DATAGRID.CurrentPageIndex = 0;
    DataBind(e.SortExpression, direction, 0, DATAGRID.PageSize);
}

The logic is straightforward. When the event is triggered, the handler checks the column clicked on by the user against the column the data is sorted on. If they are the same, the sorting direction is switched, if not, the direction is reset to ASC. Then, we save the new parameters in the ViewState, reset the current page the DataGrid is looking at (otherwise, it would be confusing for the user), and call the method to fetch the new page in order to rebind the grid.

Paging through the result set

The same approach is used for paging. We use a handler to catch the event, recognize what page index the grid would present, fetch the new page and bind. This is done in the method DATAGRID_PageIndexChanged() as follows:

private void DATAGRID_PageIndexChanged(object source, 
        System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
    // Let the grid know that it needs to update the pager at the bottom
    DATAGRID.CurrentPageIndex = e.NewPageIndex;
    // Retrieve a new subset to refresh the grid.
    DataBind(CurrentSortColumn, CurrentSortDirection, 
                  e.NewPageIndex, DATAGRID.PageSize);
}

Changing the number of rows per page

The same technique is used to change the number of rows the grid is showing per page. This is done in the CHANGEROWSPERPAGE_Click method as follows:

protected void CHANGEROWSPERPAGE_Click(object sender, System.EventArgs e)
{
    // Assume the number entered is valid
    ROWSPERPAGE.BackColor = Color.FromArgb(255, 255, 255);

    try
    {
        DATAGRID.PageSize = int.Parse((ROWSPERPAGE).Text);
        DATAGRID.CurrentPageIndex = 0;
        DataBind(CurrentSortColumn, 
                 CurrentSortDirection, 0, DATAGRID.PageSize);
    }
    catch (Exception ex)
    {
        // There was a problem when trying to parse the number passed.
        // Let the user know about it.
        ROWSPERPAGE.BackColor = Color.FromArgb(235, 0, 235);

        // Let the user know about the problem
        ERROR.Visible = true;
        ERROR.Text = "Please enter a valid integer" + 
                     " to change the number of rows per page.";
    }
}

Updating data "in bulk" in PL/SQL to improve performance

We haven't looked at the GridPager.aspx file yet, because it was assumed that the reader was familiar with ASP.NET. However, there is one particular piece of interest in it:

...
<asp:TemplateColumn SortExpression="NAME" HeaderText="Name">
    <ItemTemplate>
        <asp:TextBox id="NAME" CssClass="textbox" 
                runat="server" 
                Text='<%# DataBinder.Eval(Container, "DataItem.NAME") %>'>
        </asp:TextBox>
    </ItemTemplate>
</asp:TemplateColumn>
...

The important piece is the use of a "Template column" in order to provide a text box where the user can modify the content. This text box is populated at the time the grid is data bound by retrieving the NAME field from the data source built on the OracleDataReader object returned by the query.

Once the user is done with the changes, these are being saved in "bulk" into the database. In order to do this, we use a handler to answer the event triggered by clicking on "Save Changes". The approach is as follows:

• Build an array that contains the ZIP code values as they represent the key of the table. We need them to update the database.
• Build an array that contains the NAME values, by retrieving the changes the user may have made. We don’t try to figure out if the values were changed or not here. It is far much faster to just update the whole subset in bulk.
• Make sure the indices between these two arrays match.
• Call a PL/SQL procedure, by passing these two arrays and let PL/SQL do the heavy lifting.
• Commit the changes if everything is okay or roll back the changes otherwise.

Let's look at the handler SAVE_Click to answer the click on the button:

protected void SAVE_Click(object sender, System.EventArgs e)
{

    // The objective here is to build arrays that contain:
    // - The ZIP code (key of the table)
    // - The names as entered on the page
    // Since the number of rows is dynamic,
    //    these arrays should be dynamic as well
    string[] ar_zip     = new string[DATAGRID.PageSize];
    string[] ar_name    = new string[DATAGRID.PageSize];

    int index = 0;

    foreach ( DataGridItem row in DATAGRID.Items)
    {
        //Need to get the ZIP code and the new name out of the grid
        ar_zip[index]  = DATAGRID.Items[row.ItemIndex].Cells[0].Text;
        ar_name[index] = ((TextBox)row.FindControl("NAME")).Text;
        ++index;
    }

    // These two arrays will be passed
    // to the parameters objects to let the ODP know
    // about the size of the varchar2 variables
    // defining the fields in the table.

    // In the ZIPCODE table, the ZIP field is defined as VARCHAR2(5)
    int[] p_zip_bind = new int[index];
    for (int i=0; i<index; ++i)
        p_zip_bind[i] = 5;

    // In the ZIPCODE table, the NAME field is defined as VARCHAR2(40)
    int[] p_name_bind = new int[index];
    for (int i=0; i<index; ++i)
        p_name_bind[i] = 40;

    // Oracle parameters declarations.
    OracleParameter p_zip     = new OracleParameter("p_zip", 
          OracleDbType.Varchar2, ParameterDirection.Input);
    p_zip.CollectionType      = OracleCollectionType.PLSQLAssociativeArray;
    p_zip.Value               = ar_zip;
    p_zip.ArrayBindSize       = p_zip_bind;
    p_zip.Size                = index;

    OracleParameter p_name    = new OracleParameter("p_name", 
          OracleDbType.Varchar2, ParameterDirection.Input);
    p_name.CollectionType     = OracleCollectionType.PLSQLAssociativeArray;
    p_name.Value              = ar_name;
    p_name.ArrayBindSize      = p_name_bind;
    p_name.Size               = index;

    // Save the new values.
    // Note: We don't attempt to determine what row has been changed.
    // We just update the whole subset, it is way easier.
    try
    {
        connection = new 
          OracleConnection(ConfigurationSettings.AppSettings["connectionString"]);
        connection.Open();

        command = new OracleCommand("GRIDPAGER.SaveChanges", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.BindByName = false;

        // add the parameters to the command object
        command.Parameters.Add(p_zip);
        command.Parameters.Add(p_name);

        // Start a transaction
        transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

        // execute the procedure
        command.ExecuteNonQuery();

        // Commit the changes
        transaction.Commit();
    }
    catch(Exception ex)
    {
        // There could be a problem in the transaction itself
        // Check if we have a valid transaction object first.
        if(transaction != null)
            transaction.Rollback();

        // Since this is a sample program, we need
        // to get some feedback in case there
        // was an exception raised when calling Oracle
        ERROR.Visible = true;
        ERROR.Text = ex.Message;
    }
    finally
    {

        if (transaction != null)
            transaction.Dispose();

        if (command != null)
            command.Dispose();

        if (connection != null)
        {
            connection.Close();
            connection.Dispose();
        }
    }

}

The three major points in the preceding code that are worth noticing:

• The use of the ArrayBindSize property of the OracleParameter object for our parameters. This is required because the parameters are defined as VARCHAR2 in the database, and we need to let the ODP.NET know about the actual maximum length. This is done by passing a reference to an array that contains the length of each ZIP code field and each NAME field. We are not trying to be tricky here, we just pass the maximum length as defined in the database.
• The use of a transaction to define a commit or rollback point.
• The call to a PL/SQL procedure is done by passing the name of the package that contains it dot the actual name of the procedure itself, like this: command = new OracleCommand("GRIDPAGER.SaveChanges", connection);. Obviously, the Oracle user used to connect to Oracle (the one defined in Web.config) must be allowed to execute the procedure.

The whole point here is to pass all the rows to the database in one shot. Let's now have a look at what the PL/SQL procedure is doing in order to process this data in "bulk".

PL/SQL procedure in Oracle

For those of you who are new to PL/SQL development in Oracle, I highly recommend using PL/SQL developer as it is the de facto standard to do it. You can find more information about it here. The procedure we are calling is defined in the package called "GRIDPAGER" and its name is "SaveChanges". Here is the content. First, let's look at the package definition:

create or replace package common.GRIDPAGER is

  -- Author  : Patrice Borne (patriceborne@yahoo.com)
  -- Created : 06/19/2005 15:50:27
  -- Purpose : This package contains all the PL/SQL
  --           procedures used by the GridPager.aspx sample.

  -- Public type declarations
    type t_zip  is table of ZIPCODE.ZIP%TYPE  index by pls_integer;
    type t_name is table of ZIPCODE.NAME%TYPE index by pls_integer;

    PROCEDURE SaveChanges ( p_zip   in  t_zip,
                            p_name  in  t_name);

end GRIDPAGER;

Then, let's look at the actual implementation:

create or replace package body common.GRIDPAGER is


   ---------------------------------------------------------------------
   -- This procedure updates the changes sent by the C# code
   -- Note that there is no point in checking if something was changed
   -- or not since it is way faster to just update "in bulk" using the
   -- FORALL construction.
   ---------------------------------------------------------------------
   PROCEDURE SaveChanges ( p_zip   in  t_zip,
                           p_name  in  t_name ) IS

   BEGIN

     -------------------------------------------------------------------------
     -- The parameters passed are PL/SQL associative arrays and are declared
     -- as such in the calling code.
     -- The loop here is implicit with the FORALL statement.
     -- Note 1: There is no commit nor exception handling code in here
     -- since we want the calling code to be fully in charge.
     -- Note 2: In order to update the entries
     -- into the table, we need to receive
     -- the ZIP code as this is the key of the table.
     -------------------------------------------------------------------------
     FORALL i IN p_zip.FIRST..p_zip.LAST
      UPDATE ZIPCODE
      SET    NAME  = p_name(i)
      WHERE  ZIP   = p_zip(i);

   END;


end GRIDPAGER;

The points to notice in the preceding code are:

• All the procedures must be defined in the package declaration and must match the signature of the actual implementation.
• Defining the type t_zip and t_name tells the PL/SQL engine that it should retrieve the actual definition from the Oracle data dictionary, based on the types of the fields in the table.
• The FORALL construction is specific to PL/SQL and allows us to call the SQL engine to do the updates in just one call, passing all the parameters at once (better performance).
• There is no error handling or transaction management (no commit or rollback command) as we want the caller (C# code) to be fully in charge for these two points (to send feedback to the user and to manage the "bigger picture" this code may be part of).

If you don't have access to PL/SQL developer, you can still install the package from the command line:

sqlplus <orauser>/<orapassword>@INSTANCE @GRIDPAGER.pck

Using some CSS to modify the rendering of the data grid

As you can see on the screenshot at the beginning of this article, the rendering of the grid in IE is customized using Cascading Style Sheets. The first thing to do is to add a line to the GridPager.aspx source in the <head> section:

<head>
    ...
    <style type="text/css" 
      media="all">@import url( css/grid.css ); </style>
</head>

This will instruct IE to download the grid.css file from the server and use it for rendering.

There is nothing special in this CSS file, it is just plain vanilla CSS. A more interesting thing, though, is to look at how we can alter the generated HTML when the DataGrid is rendered by the framework and how we can use the CSS classes defined in the CSS file.

Interesting tricks to use the DataGrid

An important method during the rendering of the grid is the DATAGRID_Created method that is handling the OnItemCreated event. This method is called by the framework on every major step of the rendering process. In order to know where the grid is at in its rendering process, the Framework is passing a DataGridItemEventArgs object. One key member of this object is DataGridItemEventArgs.Item.ItemType that tells us what is currently being rendered. We just need to compare its value to the enumerated values of ListItemType.

In this example, we check against two values:

• ListItemType.Header happens when the header is about to be rendered. We then set a delegate to our own RenderTitle method.
• ListItemType.Pager happens when the pager (bottom of the grid) is about to be rendered. We then set a delegate to our own RenderBottom method.

Both RenderTitle and RenderBottom are built on the same pattern. They both receive a HtmlTextWriter and a Control reference. The HtmlTextWriter gives us direct access to the HTML output. The reference to the Control object gives us access to the collection of controls that belong to the grid and need to be rendered during our own processing.

These two methods are simply generating HTML that is sent directly to the output, in order to create extra rows (for the header) to render a title to the grid, or to set a CSS class. This is the code of the RenderTitle method:

protected virtual void RenderTitle(HtmlTextWriter writer, Control ctl)
{
    // TR is on the stack writer's stack at this point...
    writer.AddAttribute("colspan", "7");
    writer.AddAttribute("class", "header");
    writer.RenderBeginTag("td");
    writer.Write("US ZIP Codes");
    writer.RenderEndTag(); // Writes </td>
    writer.RenderEndTag(); // Writes </tr>

    // New Row
    writer.AddAttribute("class", "subheader");
    writer.RenderBeginTag("tr");

    writer.AddAttribute("colspan", "3");
    writer.RenderBeginTag("td");
    writer.Write("Location");
    writer.RenderEndTag(); // Writes </td>

    writer.AddAttribute("colspan", "2");
    writer.RenderBeginTag("td");
    writer.Write("Coordinates");
    writer.RenderEndTag(); // Writes </td>

    writer.AddAttribute("colspan", "2");
    writer.RenderBeginTag("td");
    writer.Write("Population");
    writer.RenderEndTag(); // Writes </td>

    writer.RenderEndTag(); // Writes </tr>

    writer.RenderBeginTag("tr");
    //Render the cells for the header row.
    foreach(Control control in ctl.Controls)
        control.RenderControl(writer);

    // We don't need to write the </tr>.
    // The grid will do that for us.
}

The CSS class we intend IE to use are passed through a call to the AddAttibute() method. Note also how we "open" and "close" <tr> tags with the RenderBeginTag() and RenderEndTag() methods.

Finally, we need to render the controls that the grid needs to render as part of its normal process (meaning what should happen if we didn't hijack the rendering process) with a foreach loop.

Conclusion: Limitations and room for improvements

This article is a bit long for sure. However, it was written for those who need to get started as quickly as possible with ASP.NET using Oracle as a database.

There are still many things to be demonstrated with the ODP.NET component, like using REF Cursors from Oracle (they work really well) or use multiple REF Cursors returned by a PL/SQL procedure that need to be kept in sync while parsing the result sets. Another great approach to populating a DataGrid is to build a DataView object on the fly and use it as the source for the DataGrid. Finally, this code does not show how to use safer updates to the database by using one of Oracle's greatest features (in my opinion): its locking mechanism. As we are building a Web application and we are using a connection pool, we cannot simply lock the rows while the user is looking at the data in the grid. We would need to implement the so-called "optimistic locking" mechanism. These points are good subjects for future articles!

References

For those who are interested in getting more information about Oracle, here are a few links:

• AskTom: This is the reference on Oracle.
• The Oracle library: contains all the documentation. This is a lot of information though.
• Download Oracle 10g Release 2 here.
• Download the ODP.NET installer here
• ODP.NET forum: Ask questions and help others to use ODP.NET.
• Get more information about PL/SQL developer here.

History

• August 2005: First version.