Publishing a database through a web application is an immensely useful technique, both for internal networks and for the Internet. To do this the .NET way requires bringing together two large pieces of the Framework, ASP.NET for a dynamic web site, and ADO.NET for data access. Microsoft did a good job with the design of these frameworks, but with one caveat. It is easier than it should be to create disturbingly inefficient applications. There is a trade-off between quick and easy design, and fast, lean performance. This Masterclass shows how to build a quick but fairly inefficient application, which is fine when you just want to be up and running fast, but also offers tips for improving database performance.

For this example, we are using the Northwind database that comes with Access and SQL Server. It will work equally well with any supported database. The first step is to create a new Delphi 2005 web application, using either the Cassini web server or Internet Information Server. When the default page opens, find the Document object in the object inspector and change the PageLayout property to FlowLayout instead of GridLayout, as this makes page layout easier. Then go back to the Design surface and drag a BdpConnection, a BdpDataAdapter and a DBWebDataSource onto the designer. Next step is to add two visual components, a DBWebGrid and a DBWebNavigator.

So what do all these components do? The BdpConnection and BdpDataAdapter are there to connect to the database and generate a result set, in the form of an ADO.NET Dataset. The BdpDataAdapter is also able to update the source database from a Dataset modified by your application. The DBWebGrid and DBWebNavigator are web controls that bind to a result set. You would expect that to be a Dataset, but Delphi 2005 has a powerful intermediate component that simplifies your code. This is the DBWebDataSource, which connects to a Dataset as its input and then supplies this data to the grid and navigation controls. The DBWebDataSource keeps a record of the current row, and maintains a list of changes made to the data during the current session. When the application needs to update the database, the DBWebDataSource is able to recreate the original dataset including any updates, insertions and deletions. It also enables a neat undo function. These features are accessible via the DBWebNavigator. Overall, it makes working with data much the same as how it would be working with a traditional Windows application.

Hooking up the components
Start with the BdpConnection. Right-click and choose Connection Editor. In the Connection Editor, click 'Add' and select the MSSQL provider. Call the driver PCPlusNorthwind and click 'OK'. Still in the Connection Editor, set the Database property to Northwind, and the HostName, OSAuthentication, Password and Username as needed on your system. Next, click 'Test' to make sure the connection works, and OK to close the Connection Editor.

Now select the BdpDataAdapter. Right-click and choose Configure Data Adapter. In the configuration dialog, choose the connection you have just set up. Select a table such as Products, and click Generate SQL. Add an order by clause to the Select statement, such as Order By ProductName. Click the Preview Data tab and Refresh to test the query. Then click the DataSet tab, select New Dataset and click 'OK'. A dataset object will appear in the designer.

All that remains is to hook up the other controls. Set the BdpDataAdapter's Active property to 'True'. Select the DBWebDataSource and set its DataSource property to the dataset you have generated. Select the DBWebGrid and set the DBDataSource to the DBWebDataSource, and the TableName to Products. The grid will fill with data. Do the same with the DBWebNavigator.

The web application is almost complete. There is one bit of code you need to add to get edit and update working. Select the DBWebDataSource and handle the OnApplyChangesRequest event. Afterwards, enter the following code:

self.BdpDataAdapter1.AutoUpdate; 

That's it. Now you can run the application. Using the navigator, you can change the active row in the grid, and type into the active row to make changes. You can also add and delete rows, undo changes, and update the database.

What's wrong with this picture?
It is a good start, but there is plenty of scope to improve this application. You can work on the design by editing the properties of the grid, modifying the DBWebNavigator or using different controls for some of its functions, and editing the .aspx page. However, there are some more serious issues. If you have SQL Server, it is interesting to run the profiler and watch what SQL is sent to the server. Although the grid only shows 10 rows, the entire table is requested on each page refresh. It's hard to manage concurrency issues, such as where another user changes a row while you are editing the same row during your session. Another problem is that auto-update will not work where you have a multi-table query.

All these issues can be solved, but the solutions require additional code. The right strategy also depends on the likely usage pattern. If you expect to have just a few users then the convenience of working with large datasets may be worthwhile. On the other hand, with large numbers of users, you will want to minimize the amount of data held in session state as well as reducing the number of queries sent to the database server. It will be tempting to abandon the DBWebDataSource and go for a leaner approach based on a Datareader, or a Dataset cached at the application level. If you do stick with DBWebDataSource, you will want to limit the number of rows returned, using a restrictive 'where' clause or one of the SQL extensions that let you specify how many rows to return. You also boost performance and get more control by avoiding the BdpDataAdapter's AutoUpdate feature. AutoUpdate generates SQL behind the scenes. As an alternative, you can specify the SQL to use for inserts, deletions and updates, and call the BdpDataAdapter's Update method instead of AutoUpdate. This means it will use your SQL code rather than generating its own, allowing you to tune performance.

Protecting access with a logon page
It is likely that you will want to secure your online database with at least a username and password scheme. Here is how to do it. First, amend the authentication element of the web.config file at the root of your ASP.NET application folder. You can use the one already in your Delphi project, although it's unlikely to be suitable for deployment as is. Amend the web.config file to look like this:

<authentication mode="Forms">
 <forms name="LoginForm" loginUrl="login. aspx" protection="All">
 <credentials passwordFormat="SHA1"> 
<user name="PCPlus" password="HFDWE..." /> 
</credentials> 
</forms>
 </authentication>

This does two things. It specifies the URL of a login page, and it defines a user called 'PCPlus' authenticated by a password hash. How can you find the hashed value? Easy, just create a Windows Forms application, add a reference to System.Web, add System.Web.Security to the uses clause and call the following function:

FormsAuthentication.HashPasswordForStoringInConfigFile('yourpassword', 'sha1') 

This returns the hashed value for the given password. Next, edit the web.config file in the folder you want to protect. This might be the same as the root folder, or it could be elsewhere but part of the same application. Create or edit the authorization element as follows:

<authorization>
 <allow users="PCPlus"></allow>
 <deny users="*" /> <!-- Deny all users - ->
 </authorization>

At runtime, ASP.NET will allow access to the PC Plus user, but deny any other user. All that remains is to add the login page. Add a new ASP.NET page to the project and rename it to 'login.aspx'. Add textboxes for username and password, setting the TextMode property to 'Password' for the second of these. Add a label called 'lbWarning' and set its visible property to 'False'. Then add a button and double-click it to open its click event handler. At the top of the code, add 'System.Web.Security' to the uses clause. Here is the code:

if (FormsAuthentication. 
Authenticate(txtUsername.Text, 
txtPassword.Text)) then 
FormsAuthentication.RedirectFromLoginPag 
e(txtUserName.Text, False) 
Else begin 
lbWarning.Text := 'Login failed, please 
retry'; 
lbWarning.Visible := True; 
end; 

Now any user who navigates to a page in this folder will get the login page. After logging in, they will be redirected to the page first requested. What if you wanted to maintain a database of users and hashed passwords, rather than using web.config? Just put the following in the authorization:

<deny users="?" />
 <!-- Deny anonymous user --> 
<allow users="*"></allow>

This allows any authenticated user. You can authenticate however you wish in your code. When the user is validated, call FormsAuthentication.RedirectFromLoginPage. Don't forget that sending passwords as plain text is insecure. You can overcome this by connecting to the login page with HTTPS. Your login page can check the isSecureConnection property of the Request object to verify secure connections.