建立三层结构数据库工具

Abstract

In a Web based database application, one common problem programmers face is to write similar ASP.NET pages to maintain the simple tables. A generic Database tool will save tremendous time and efforts for your project. This article demos how to use the .NET Framework to develop a generic database manipulation tool with powerful features in a clean manner. For the sake of reusability and maintainability, the application is developed according to three tire architecture.

Introduction

Two years ago, I used Classic Generic DB (www.genericdb.com) in a Web project. Classic Generic DB is a great tool with its promise of a "5 minute setup and running database" feature. But it becomes more and more difficult to manage when the user makes request to apply more complex logic. The reason is that the mix of script and Html make it difficult to change page layout or to modify the logic. Another disadvantage is that the usage of a lot of sessions in Classic Generic DB effect the performance. Now in the .NET age, those disadvantages can be overcome by creating a generic database tool using ASP.NET.

The purpose of the article is to show the Generic DB framework in the .NET architecture, so only the bare bones needed for the completion of this application is included. No extensive error handling and validation are considered in this version.

Tools and Technologies Used

The following tools and technologies are used to build this application.

.NET Framework
Visual Studio .NET
Internet Information Server 5.0
Internet Explorer 5.5
SQL Server 2000

User Requirements

Understand how to create basic ASP.NET pages
Understand the basics of RMDBS and SQL

Defining the Problem

The scope of the application is aimed at a "simple" table. The definition of simple tables is: they have a single primary key which is a unique identifier column. Handling of tables with a composite primary key is not considered in this version but it can be customized to add. One example of the "simple" table is those "codes" or "lookup" tables in any database system. From a database design point of view, every table should have a primary key and the primary key should have no business meaning and be unique. (Scott W. Ambler "Mapping objects to relational database" www.amyysof.com) In another words, system generated auto number is the best choice. It is simple: system auto number is under control and fields with business meaning subject to change.

The Application must to be able to carry out the following basic database operations to any simple table: View, Edit, Delete, Create new record. To create a system with such flexibility, we must be able to get the Database schema on the fly. The program needs to know the types of the fields while showing them, and handle the different data type appropriately.
To cater to a different environment, the application must be flexible enough to be used with different types of databases.
The application must be designed in such a way that the user is able to customize the system easily to suit their requirements.

Overview of System

The system page flow is shown as below:

In first page all the tables in the database are listed with a hyperlink that allows you to access the table selected. For a table without a primary key, you can only view the records of the selected table. You would be able to add, update and delete records for tables with a primary key. The 'Add' and 'Edit' page could be merged together, but to make it easier for the user to further customize the adding and editing individually, I separated the two.

You can also view the database table schema info from the first page. The application is divided into three layers: the presentation layer, the business layer and the data layer. The layers and their responsibilities are as following:

Using of ASP .NET and layered architecture, the application has the following plus points compared to the classic version of Generic DB:

- Separation of UI layer, business logic and data access layer, no more mixing of html and ASP script
- Can be used with any database
- Avoid using of session
- Better reusability and maintainability

Now let's go through the system layer by layer.

DataAccess Layer

There are two types of data providers in ADO.NET: the SQL Server .NET Data Provider and the OLE DB .NET Data Provider. A .NET data provider describes a collection of classes used to access a data source, such as a database. In the managed space, schema information can be obtained from your data source using both the Data Providers. The limitation of the SQL .NET provider is that it is only for SQL Server although it has better performance. The OLE DB .NET Data Provider is used in the system to make the system flexible.

ADO.NET DataSet is used as the standard format for data transfer between different tiers (Data Transfer Object) in this system as its disconnect nature.

Retreive Database Schema

The core of the problem is how to extract database schema from the database. The OLE DB .NET Data Provider exposes schema information using the GetOleDbSchemaTable method of the OleDbConnection object. GetOleDbSchemaTable takes as arguments an OleDbSchemaGuid that identifies which schema information to return and an array of restrictions on those returned columns. GetOleDbSchemaTable returns a DataTable populated with the schema information.

The DataTable returned has the same format as the OLE DB schema rowset specified by the schema parameter. The restrictions parameters are used to filter the rows to be returned in the DataTable (for example, by specifying restrictions for table name, type, owner, or schema). If you pass an empty array to restrictions, all rows (one for each table) are returned in default order.

All data access code is wrapped in DAGeneric.cs class. The details on all of a database's tables schema can be extracted out like this:

The GetDBTableColumns method returns details of the columns in a table. The datatable returned from GetDBTableColumns containing fields which describe the each database column like COLUMN_NAME and DATA_TYPE etc. The returned column data type is an integer value, so it is converted to a string by the GetDataType method of GenericDBHandler class.GenericDBHandler class containing the common utility methods for the Database operation.

Records Manipulation

SelectDBRecord method returns a dataset containing the record based on the table name and the primary key value passed.

UpdateDBRecord method will be used to update a record or insert a new record. The method makes use of the OleDb DataAdapter "update" method to update the datasource.

Biz Logic Layer

In the Business tier a set of methods are exposed to UI layer. The Biz logic objects receive data from UI layer, manipulate the data to apply the Biz rules if necessary, then talk with Data Access layer to retrieve the result and send it to UI layer. It is also important to remember that these objects can be stateful for the lifetime of that specific object instance, but not stateful for the entire application's existence.

Compared with classic "generic db", the html page is mixed with script, SQL statements and ADO data access code. The code in this application is much cleaner due to the separation of layers.

To better understand the implementation of the business logic layer, let us look at the implementation of the BzGenericDB class. The BzGenericDB class contains the following methods:

Take the InsertRecord method for example, a DataSet object containing the new record data is passed from UI layer, this method will generate the query string by looping through every column in the DataTable of the DataSet and use it to call UpdateDBRecord method in the Data Access Layer, then return the result to the UI Layer.

UI Layer

The Presentation Layer utilizes the user control (formerly known as Pagelet) namely Header.ascx. The purpose of the user control is to give the application a consistent look and feel. The following screenshot shows the page loaded from within the browser (my apologies for the simple looking of the page).

One of the best thing in ASP.NET is the predefined controls ready for you to use. These Web controls save you time and make you more productive. The DataGrid Web control is the most complex and also the most powerful. To make use of the DataGrid, all we need to do is to assign the DataSource property of the DataGrid with the DataSet returned from the data access class. Using the DataGrid to manage the sorting and paging, only few lines of code are need.

Since every table has different number of fields, the system needs to be able to display the dynamic number of fields for different tables. The PlaceHolder Web control is used for this. Placeholder is a container to store dynamically added server controls on the Web page. The PlaceHolder control does not produce any visible output and is only used as a container for other controls on the Web page.

ShowTables.aspx

The first page lists out all the tables in database. It simply calls the GetAllTable method in BZGeneric.cs and connects the returned Datatable to the Datasource property of the DataGrid.

Now the system displays all the tables in the database, it may not be desirable in practice. To limit the tables shown on the page, one approach is to store the "deny tables list" or "allow tables list" in the Web.config file and read it during the page load event. To make the sample application simple, this feature is not added in, but you can refer to the "display limited fields" in a later part which uses the same approach.

ShowRecords.aspx

From the first page, the user can click the "Show Record" to display the records of the selected table. Again, the DataGrid is used to display the contents of the table.

In the page_load event, the "field allow list" is read from the web.config file. "Field allow list" is used to limit the fields displayed in the screen, it is a string consisting of the "o" and "1", the length of the string should be the number of the fields in the table. For example, if we want to display the first three fields in the "customers" table (in Northwind database), we need to add the entry into the web.config file.

By using the ConfigurationSettings.AppSettings method in Configuration class, the allow list is read and store in the stateview bag and passed into the "BindGrid" method to do the fields filtering. The fields are filtered out by FilterDs method in the GenericDBHandler.cs class. It manipulated the DataColumnCollection of the Datatable to filter the datatable according to the allowlist.

Sorting and Paging

The DataGrid control contains built-in functionality to display its contents in page segments. The number of items on the page is determined by the PageSize property. If no value is specified for the PageSize property, the DataGrid control displays ten items on the page at a time. To enable paging, set the AllowPaging property to true and provide code to handle the PageIndexChanged event as following.

The DataGrid control supports sorting also. What we need to do is to set the DataGrid property "AllowSorting" to true, and specify the "SortExpression" field in the ButtonColumn property. The following code is also need to handle the SortCommand event.

EditRecord.aspx and AddRecord.aspx

In the "page_load" event, the DataSet containing the particular record is retrieved by using GetRecordmethod in BizGenericDB class. Next step is to loop thru each DataColumn in the returned datatable, display each field in the appropriate Web control and add the control to the placeholder.

We use getPK method to get the table's primary key and display as label, because a row's primary key should theoretically remain unique and should not change. For other fields, we display the value of each field in textbox and give the textbox id with prefix string "fld_" and the column's data-type.

Before displaying the data using the appropriate control, the program needs to know the types of fields. For the text fields, we also need to know the length of the fields to make the form elements with proper size. Both the size and types information can be obtained from the properties of DataColumn.

When the user clicks the "update" button, the code behind page will retrieve the user input and build a dataset, the dataset is passed to updateRecord method in BizGenericDB class to complete the updating process.

AddRecord.aspx is similar with the EditRecord.aspx, the difference is that the empty textbox is displayed in AddRecord.aspx page.

View and Delete a Record

To delete a record, the "ShowRecords.aspx" page receives an action parameter with a value of "delete" and a non-empty ID parameter, a SQL query is created to delete the record and redirect user to the main page.

The "ViewRecord.aspx" page is a simple page, it loads the record content in Labels dynamically to a placeholder by the ID parameter received. We won't be going through the code here as it's very similar to what we've already looked at and easy to understand.

Suggestions and Restrictions

You can further improve the tool by adding the following features:
- Handling of tables with composite Primary key
- Validation of user input

Summary

In this article, we have seen how easy it is to develop a ntiered Web application to create a generic database tool using ASP.NET and the .NET Framework. The system is quite simple, and therein lies its appeal. We are able to handle the management of any simple table in any database using this simple system. Although the solution is not very comprehensive, we have provided an excellent starting point for providing a generic database tool.