If you are a veteran ASP/ADO developer who has not tested the .NET waters, you’d better get started soon. To give you a taste of .NET, we’re going to connect a Microsoft Access database (you can use a SQL Server or Oracle database instead) to the Internet and then retrieve and display some data. The example requires both Information Internet Services (IIS) and the .NET framework. You can download the .NET Framework here. If you want to try a free ASP.NET development environment, download Web Matrix.
An introduction to server controls
Active Server Pages (ASP) was one of the first Microsoft Web technologies for connecting a database and the Web. ASP.NET is a complete rewrite of that classic language. You can still use both, because .NET pages use an .aspx extension. (ASP files keep their .asp extension.)
Much of the code you write in ASP.NET will be executed on the Web server and will return only HTML to the client. Fortunately, .NET provides you with many new controls that are similar to standard HTML controls, such as drop-down lists and text boxes. Table A lists the most common server controls.
.NET server controls have the advantage of being created on the Web server as opposed to being created within the page like HTML. As a result, they’re available for processing before being sent to the client. For example, you can validate content within the page or on the server side. That means you can validate content within the page and then revalidate content on the server side.
For the most part, you can create a .NET control simply by adding this component:
to the corresponding HTML element using this syntax:
<asp:control_name id=”controlID” runat=”server” />
Some development tools are even easier to use. For instance, Visual Studio .NET lets you create a server control by dragging and dropping the control on a Web page.
In addition to the standard server-side HTML controls, ASP.NET offers a set of validation controls:
- RequiredFieldValidation requires a value.
- CompareValidator compares the values in two controls, such as validating e-mail addresses where the user is required to enter an e-mail address twice.
- RangeValidator determines that the entry falls within a set range.
- RegularExpressionValidator validates control entries using regular expressions.
- CustomValidator lets you write your own validation code.
- Validation Summary displays a list showing all the validation currently being used within a page.
You’re probably familiar with ADO, but ADO.NET is an altogether new language. But don’t let that intimidate you—there’s enough similarity so that learning how to use the new objects isn’t that difficult. Connecting to a database is a three-step process:
- Import a .NET namespace to establish a connection.
- Create an ADO.NET DataReader object to grab data.
- Create an ADO.NET Repeater object to display data.
The namespace is new to .NET, so there’s really no ADO counterpart. ADO connections are made via provider strings and a Connection or Command object. The DataReader is the ADO Recordset counterpart, and the Repeater is a server control that is used to display the data based on a template.
Creating an ADO.NET connection
To retrieve data from your database, you’ll need ADO.NET. If you’re familiar with IIS and the Web folder hierarchy, you probably don’t need any help setting up an example. You can follow ours by copying Northwind (the sample database that comes with Access) to the Inetpub\wwwroot folder on your local system. Our example is in a Web folder named nettest. Cut and paste (or enter) the code example into a text editor and save it as nettest.aspx.
Now, import the .NET namespace that allows you to work with OLEDB databases:
<%@ Import Namespace=”System.Data.OleDb” %>
The PageLoad event executes the code that connects to the Northwind database, and Server.mappath returns the physical path to the folder that contains it, as shown in Listing A.
If you wanted to connect to SQL Server using an OLEDB connection, you could use the following:
“Provider=sqloledb;Data Source=Martin;Initial Catalog=NorthWind;Integrated Security=SSPI;”
If you’re working with Oracle, you could use:
“Provider=msdaora;Data Source=OracleDataBase;User Id=YourUserName;Password=YourPassword;”
A useful resource for connection information is connectionstrings.com, which contains a connection string for every situation imaginable.
Creating the ADO.NET objects
The next step is to create a DataReader object to hold the data you want to display. The following code uses the Command object’s ExecuteReader method to create a DataReader object that will store all the records from the Northwind Customers table:
sql=”SELECT CompanyName, ContactName, Address, City FROM Customers”
This code opens the connection and defines the data-retrieving SQL statement. Then the ExecuteReader method creates the DataReader object (dbread). Note that OleDbCommand passes both the connection and the SQL statement. The DataReader control then returns a stream of read-only data to the client.
Using a Repeater control to display the data
Now you can use a Repeater control to display the data by binding the DataReader object. The Repeater control lets you construct a simple template (for example, an HTML table) that’s repeated for each row of data returned by the query.
Use a HeaderTemplate block to create the initial table structure; the data will appear within the ItemTemplate (table rows and columns) block. For example, the code below creates a table header for our customer data that refers to the fields returned by the earlier SQL statement:
<table border=”1″ width=”100%”>
Unlike other ASP.NET objects, the Repeater has no layout or styles available; you must define your own. Each row returned is displayed by the ItemTemplate block. The following script contains one cell (in the HTML table) for each field:
You can’t tell from the above example, but the template code is within the HTML body tags but outside the script definition.
Viewing the .NET page
The code in Listing B displays the customer data within the browser using an HTML table template to display the items.
Cut and paste this script into a text editor and be sure to save it with the .aspx extension. Then save or move the .aspx file to the Web root folder (wwwroot\nettest, for this example). Launch your browser and enter the appropriate address to open this file. When viewed in the browser (Figure A), the file displays a simple HTML Web page and the requested data, as defined by the SQL statement query.
You can also improve the look of the page. For example, you can alternate row colors by adding another template section. Specifically, the AlternatingItemTemplate block changes the background color of each table cell. The following script changes the cell background to yellow (FFFF00):
To affect every other row, place the AlternatingItemTemplate block after the ItemTemplate block.
.NET isn’t exactly new on the scene. But if you have delayed making the leap from classic ASP to ASP.NET, now’s the time to get started. If you have some solid experience with ASP and ADO, you should be able to make the transition fairly easily.