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
{
connection = new
OracleConnection(ConfigurationSettings.AppSettings["connectionString"]);
connection.Open();
...
}
catch (Exception ex)
{
...
}
finally
{
...
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)
{
string modifiedOrderby = "ZIP ASC";
if (orderby.Equals("ZIP"))
modifiedOrderby = "ZIP " + direction;
else
modifiedOrderby = orderby + " " + direction + ", ZIP ASC";
try
{
connection = new
OracleConnection(ConfigurationSettings.AppSettings["connectionString"]);
connection.Open();
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;
command.BindByName = false;
command.Parameters.Add(new OracleParameter("ROWNUM",
OracleDbType.Decimal, ParameterDirection.Input));
command.Parameters["ROWNUM"].Value = (1 + pageindex
* rowsperpage + rowsperpage);
command.Parameters.Add(new OracleParameter("RNUM",
OracleDbType.Decimal, ParameterDirection.Input));
command.Parameters["RNUM"].Value = (1 + pageindex * rowsperpage);
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)
{
string direction;
if (CurrentSortDirection == "ASC" && CurrentSortColumn == e.SortExpression)
direction = "DESC";
else
direction = "ASC";
CurrentSortColumn = e.SortExpression;
CurrentSortDirection = direction;
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)
{
DATAGRID.CurrentPageIndex = e.NewPageIndex;
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)
{
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)
{
ROWSPERPAGE.BackColor = Color.FromArgb(235, 0, 235);
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)
{
string[] ar_zip = new string[DATAGRID.PageSize];
string[] ar_name = new string[DATAGRID.PageSize];
int index = 0;
foreach ( DataGridItem row in DATAGRID.Items)
{
ar_zip[index] = DATAGRID.Items[row.ItemIndex].Cells[0].Text;
ar_name[index] = ((TextBox)row.FindControl("NAME")).Text;
++index;
}
int[] p_zip_bind = new int[index];
for (int i=0; i<index; ++i)
p_zip_bind[i] = 5;
int[] p_name_bind = new int[index];
for (int i=0; i<index; ++i)
p_name_bind[i] = 40;
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;
try
{
connection = new
OracleConnection(ConfigurationSettings.AppSettings["connectionString"]);
connection.Open();
command = new OracleCommand("GRIDPAGER.SaveChanges", connection);
command.CommandType = CommandType.StoredProcedure;
command.BindByName = false;
command.Parameters.Add(p_zip);
command.Parameters.Add(p_name);
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.ExecuteNonQuery();
transaction.Commit();
}
catch(Exception ex)
{
if(transaction != null)
transaction.Rollback();
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
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
PROCEDURE SaveChanges ( p_zip in t_zip,
p_name in t_name ) IS
BEGIN
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)
{
writer.AddAttribute("colspan", "7");
writer.AddAttribute("class", "header");
writer.RenderBeginTag("td");
writer.Write("US ZIP Codes");
writer.RenderEndTag();
writer.RenderEndTag();
writer.AddAttribute("class", "subheader");
writer.RenderBeginTag("tr");
writer.AddAttribute("colspan", "3");
writer.RenderBeginTag("td");
writer.Write("Location");
writer.RenderEndTag();
writer.AddAttribute("colspan", "2");
writer.RenderBeginTag("td");
writer.Write("Coordinates");
writer.RenderEndTag();
writer.AddAttribute("colspan", "2");
writer.RenderBeginTag("td");
writer.Write("Population");
writer.RenderEndTag();
writer.RenderEndTag();
writer.RenderBeginTag("tr");
foreach(Control control in ctl.Controls)
control.RenderControl(writer);
}
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.
