ADO.NET

Easing Transition From ASP and ADO to ASP.NET and ADO.Net Part 1/2

You shouldn't have to learn six objects beginning with the word "Data" to start using ADO.NET. More importantly, you shouldn't have to depend on learning a brand new object model for Microsoft's DataGrid object to customize the HTML you're sending to the end user. In this article Ruffin compares classic ASP and ADO to Microsoft's ASP.NET and ADO.NET technologies, easing the transition to ADO.NET for current ADO developers.

Easing Transition From ASP and ADO to ASP.NET and ADO.Net Part 1/2 - Creating Column Headers(Page 4 of 5 )

The first goal when creating a visual representation of our data is to create the column headers. I'm going to demonstrate how to retrieve data if the column names are known beforehand, allowing us to hardcode the field names in HTML, as well as how we can go about creating a generic, customized DataGrid replacement.

If you know the column headers at design time the you'll be able to hardcode the first line of your HTML table and place it right into the strReturn string variable, as shown in the following example:

... and so on for all of the nine column names. See hardCodedColNames.aspx in the support material for more detail.

To make our code more portable, it's preferable to grab the column headers at runtime. This will allow us to use this same code no matter what table schema our DataReader object contains. We'll first check how many columns there are in the table and then loop through them and grab their names, adding the column headers to the strReturn variable. This code can be found in genericInline.aspx:

Voila! We've used the DataReader's GetName(int Index) method to determine the name of each of its columns and used the ToString() method to convert that value into a string. So this line of code:

readPubs.GetName(i).ToString();

... replaces what we used to do in classic ASP, which would've looked like this:

rsPubs.Fields(Index).Name

Do note that we've used <th> tags and not <td> tags in our tables. The W3C appreciates this as it helps identify column headers from regular table cells. Many browsers display the column header cells slightly differently than regular cells (often bolding and centering the column header text), giving these tables increased accessibility for some users:

I went ahead and threw two lines of cascading style sheet code into the page's header to give different colored backgrounds to the <th> and <td> tags, as seen in the screenshot above. The table also has a few pixels of cellspacing and cellpadding defined to make each cell easier to read. We're already a few steps ahead of a simple implementation of Microsoft's DataGrid object and we haven't even created that much code!

I'm getting ahead of myself at the moment, so let's look at how we can grab the data from our database to fill out table.

While not rsPubs.EOF

We're finally ready to grab the data from our database. In classic ASP, typically we would've used code similar to that shown below to read data from an ADO recordset:

rsPubs.Open cmdPubs, ,1 ,1

If rsPubs.BOF And rsPubs.EOF Then

Response.Write "No matching record.<br>" & vbnewline

else

rsPubs.MoveFirst

while not rsPubs.EOF

‘write out data

wend

end if

rsPubs.Close

The way that we accomplish this with ADO.NET is quite similar. Here's one solution, assuming that we know the column names for our DataReader object at design time:

while(readPubs.Read())

{

strReturn += "\t<tr>";

strReturn += "<td>" + readPubs["au_id"].ToString() + "</td>";

strReturn += "<td>" + readPubs["au_lname"].ToString() + "</td>";

strReturn += "<td>" + readPubs["phone"].ToString() + "</td>";

strReturn += "<td>" + readPubs["address"].ToString() + "</td>";

strReturn += "<td>" + readPubs["city"].ToString() + "</td>";

strReturn += "<td>" + readPubs["state"].ToString() + "</td>";

strReturn += "<td>" + readPubs["zip"].ToString() + "</td>";

strReturn += "<td>" + readPubs["contract"].ToString() + "</td>";

}

strReturn += "</table>";

Doing the same thing in ASP.NET with ADO.NET is a more portable, generic, and reusable, and requires use of the FieldCount property of the DataReader object that we used to increment the intFieldCount variable when we wrote out the table headers.

We can extract the data from our database using a numerical index just as easily as we could with the field's name (just like we could in classic ADO). Here's the code:

while (readPubs.Read())

{

strReturn += "\t<tr>";

for (int i=0;i<=readPubs.FieldCount - 1;i++)

{

strReturn += "<td>" + (readPubs[i].ToString() + "</td>");

}

strReturn += "</tr>\n";

}

strReturn += "</table>";

The last step is to use the Response.Write method to output strReturn to the browser. We've now replaced and upgraded the functionality of the DataGrid object with just a few lines of our own C# code, managing to keep doing business in ASP.NET much like we used to in classic ASP.