Diving Into Data
Last time round, I showed you some real-life ASP.NET examples of how the technology can be used in one of the most common applications of a Web development language - form input. I gave you a rundown on how ASP.NET can be used to extract and use data from HTML forms, and how it can be used to programmatically generate forms using server controls for text fields, drop-downs and check boxes.
This time, let’s take things a step further, by visiting another of the things ASP.NET is good for - database interaction. As noted earlier, this is one of the more common things you will be doing in your sojourn through the ASP.NET landscape - so pay attention, and let’s get the show on the road!
Out With The Old…
Before we get into the code, a little theory to smoothen the way. Classic ASP programmers may remember how overjoyed they were at the advent of ADO, which made database communication a breeze and significantly reduced development times. However, with the ascension of .NET, ADO, together with other ASP staples, has been given a new lease of life in the form of ADO.NET
Nope, it’s not just a name change hiding the same tired skeleton. According to the .NET development team, ADO.NET (like ADO) is a set of libraries designed to help developers access data from different RDBMSs, including Microsoft Access, SQL Server and Oracle 8. It does this through an object model supporting a wide range of object classes for connecting to a database, adding new information, updating or deleting existing records, and so on.
If this sounds familiar, hang on a minute - unlike plain-vanilla ADO, ADO.NET uses a new, more efficient object model which supports XML to simplify the transfer of data between two applications. Additionally, it allows “disconnected access” to data, wherein developers cache data on the local machine, work on it and send the changes back to the database on an as-needed basis.
New DataSet objects allow data from different sources to be merged together and treated as one - this makes it possible, for example, to have an online shopping store running off SQL Server and an offline accounting system running off Microsoft Access, and create composite reports using both systems without needing any complex import/export routines. Finally, ADO.NET allows far greater flexibility when it comes to manipulating record sets, and also lets you run multiple SQL statements simultaneously.
Simply put, ADO.NET rocks!
Dumped!
Now that you’re all excited about ADO.NET, lets get our hands dirty with some code, shall we?
First, some knowledge of SQL will come in handy over the next few pages. In case you don’t know SQL, don’t worry - it’s extremely simple, and a few minutes with the “Speaking SQL” tutorial at https://www.melonfire.com/archives/trog/article/speaking-sql-part-1 will have you executing queries like an expert.
Next, we need some data to play with. For the following examples, I shall be using a Microsoft SQL Server 2000 database called “pubs” and a table named “starwars” containing a list of characters that play vital roles in the epic Star Wars series. Here’s the SQL code to create the table (you can use the Query Analyzer tool included with SQL Server 2000 to execute them):
CREATE TABLE starwars (
id int IDENTITY (1, 1) NOT NULL ,
name varchar (50) NULL ,
homeworld varchar (50) NULL ,
species varchar (50) NULL ,
gender varchar (50) NULL ,
affiliation varchar (50) NULL
)
INSERT INTO starwars(name, homeworld, species, gender, affiliation)
VALUES('Darth Maul','Iridonia','Zabrak','Male','Sith')
INSERT INTO starwars(name, homeworld, species, gender, affiliation)
VALUES('Obi-Wan Kenobi','NA','Human','Male','Jedi')
INSERT INTO starwars(name, homeworld, species, gender, affiliation)
VALUES('Qui-Gon Jinn','NA','Human','Male','Jedi')
INSERT INTO starwars(name, homeworld, species, gender, affiliation)
VALUES('C-3PO','Tatooine','Droid','NA','Rebel Alliance')
INSERT INTO starwars(name, homeworld, species, gender, affiliation)
VALUES('Luke Skywalker','Tatooine','Human','Male','Jedi')
INSERT INTO starwars(name, homeworld, species, gender, affiliation)
VALUES('Darth Vader','Tatooine','Human','Male','Empire')
Once the table has been created, execute the following query in the Query Analyzer to test whether the data has been inserted properly:
SELECT * FROM starwars
If you see a list of records, as below, you can be rest assured that the data was inserted properly.
With all that out of the way, let’s write some code, shall we?
Hello Database!
Let’s start by looking at a simple ASP.NET script that simply connects to the database. If the connection is successful, it will display a success message.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script Language="C#" runat="server">
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
output.Text = "Opening connection...<br /><br />";
// open the connection
objConn.Open();
// display success message if connection opens successfully
output.Text = output.Text + "Connection successful!<br /><br />";
output.Text = output.Text + "Closing connection...<br /><br />";
// close the connection
objConn.Close();
// display success message if connection closes successfully
output.Text = output.Text + "Connection closed successfully!<br />";
}
</script>
<html>
<head><title>Database Connection Test</title></head>
<body>
<asp:label id="output" runat="server" />
</body>
</html>
If all goes well, you should see the following output.
There’s a lot to learn in this one simple example. Let’s take a look!
As you know, the .NET framework come packed with a whole set of pre-defined classes that can be easily reused in your code. ADO.NET is no different - when you install the .NET package, you will have access to a whole set of libraries (“assemblies”, in .NET lingo) which you can use to access a variety of databases. I’ve used SQL Server 2000 above, but if you’re using a database other than SQL Server 2000, fear not - ADO.NET supports all major databases, and you can use the techniques described over the next few pages to talk to other databases too. You’ll probably need to consult your database vendor’s manual or Web site for information on how to obtain the necessary drivers to access the database.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
Say hello to the “Import” page directive!
In order to create any object, the .NET framework must know the exact location of the required classes. If you were to skip the above lines, you would need to give the full path to the objects in question. For example, to create the SqlConnection object in the example, I’ve only used the name “SqlConnection”. If I hadn’t imported the namespaces above, I would instead have had to specify the fully-qualified class name “System.Data.SqlClient.SqlConnection” each time I referenced the object.
You can use this page directive to import user-defined assemblies (the class libraries) into an ASP.NET script as required.
<%
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
%>
Next, comes the connection string. This simple little “strConn” variable stores the parameters required to access the database. These include the username and password for accessing the database (in our case, “john” and “secret” respectively), the database to be used (the de-facto “pubs” database) and the name of the server (i.e. “tatooine” in our example).
Note that the format of the connection string varies from database to database, so it’s wise to consult the database manuals to obtain the right connection string to use with the database of your choice.
<%
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
%>
The SqlConnection object allows me to connect to the database. In order to make a connection, the connection string “strConn” must be passed to the database. If all the information provided in the connection string is valid, the object will be successfully created for use.
<%
output.Text = "Opening connection...<br /><br />";
// open the connection
objConn.Open();
// display success message if connection opens successfully
output.Text = output.Text + "Connection successful!<br /><br />";
output.Text = output.Text + "Closing connection...<br /><br />";
// close the connection
objConn.Close();
// display success message if connection closes successfully
output.Text = output.Text + "Connection closed successfully!<br />";
%>
That’s a lot of code, but it doesn’t do much. The Open() method of the SqlConnection object is used to open a database connection. A message is printed to the display once the connection successfully opened. And since programming standards recommend that all objects be closed when they are no longer required, the end of the script uses the Close() method of the SqlConnection object to free up valuable memory resources by deleting the object from memory.
Wonder what will happen if you use an incorrect user name or password, or if the connection can’t be made? ASP.NET will spit this ugly error message at you:
Going All The Way
All working? Good. Now, let’s use our new SqlConnection object to do something interesting - fire a SELECT query at the database, and display the results in an HTML page.
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<html>
<title>Star Wars</title>
<script Language="C#" runat="server">
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand("SELECT * FROM starwars;", objConn);
// open the connection
objConn.Open();
// populate a SqlDataReader object
SqlDataReader objReader = objCommand.ExecuteReader();
// output some HTML code
Response.Write("<table border=\"1\" cellspacing=\"2\" cellpadding=\"2\">");
Response.Write("<tr>");
// display the names of the columns of the "starwars" table
for(int count = 0; count < objReader.FieldCount; count++)
{
Response.Write("<td><b>" + objReader.GetName(count).ToUpper() + "</b></td>");
}
Response.Write("</tr>");
// read each record from the resultset and display in the table
while(objReader.Read())
{
Response.Write("<tr>");
Response.Write("<td>" + objReader.GetValue(0) + "</td>");
Response.Write("<td>" + objReader.GetValue(1) + "</td>");
Response.Write("<td>" + objReader.GetValue(2) + "</td>");
Response.Write("<td>" + objReader.GetValue(3) + "</td>");
Response.Write("<td>" + objReader.GetValue(4) + "</td>");
Response.Write("<td>" + objReader.GetValue(5) + "</td>");
Response.Write("</tr>");
}
Response.Write("</table>");
// clear up memory by closing all objects
objReader.Close();
objConn.Close();
}
</script>
</html>
Here’s the output.
How about dissecting the code?
<%
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
%>
After the mandatory import of the required ADO.NET assemblies, a connection object is created as explained earlier.
<%
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand("SELECT * FROM starwars;", objConn);
%>
Here, I have created an instance of the SqlCommand object. True to its name, this object allows you to execute commands against the database and possibly return records (in case of a SELECT query). Using this object, you can then navigate through the records in your ASP.NET code.
The constructor (a method having the same name as that of the class and executed each time an instance is created) of this object requires two parameters - the SQL statement to be executed and the connection object to be used.
<%
// open the connection
objConn.Open();
// populate a SqlDataReader object
SqlDataReader objReader = objCommand.ExecuteReader();
%>
After opening the connection to the database, an instance of the SqlDataReader object is created. As the name suggests, this object is used to store a read-only set of records and, therefore, can only be used for display purposes. The ExecuteReader() method of the SqlCommand object is then used to populate the SqlDataReader object with the database’s response to the command - in this case, a recordset. Note that when using the SqlDataReader object, records can be read in the forward direction only; you cannot access a record once you have moved past it.
Note also that in this approach, the connection is open throughout the execution of the code and processing of the recordset. This might not seem important at the moment, but remember it because it’ll become a big deal soon.
<%
// display the names of the columns of the "starwars" table
for(int count = 0; count < objReader.FieldCount; count++)
{
Response.Write("<td><b>" + objReader.GetName(count).ToUpper() + "</b></td>");
}
%>
The GetName() method of the SqlDataReader object can be used, with a “for” loop, to obtain a list of field names from the recordset, with the FieldCount property exposing the total number of fields.
<%
// read each record from the resultset and display in the table
while(objReader.Read())
{
Response.Write("<tr>");
Response.Write("<td>" + objReader.GetValue(0) + "</td>");
Response.Write("<td>" + objReader.GetValue(1) + "</td>");
Response.Write("<td>" + objReader.GetValue(2) + "</td>");
Response.Write("<td>" + objReader.GetValue(3) + "</td>");
Response.Write("<td>" + objReader.GetValue(4) + "</td>");
Response.Write("<td>" + objReader.GetValue(5) + "</td>");
Response.Write("</tr>");
}
%>
Next, the Read() method of the SqlDataReader object can be used to iterate over the resultset and process each record in a “while” loop - all you need to do is pass the field index to the GetValue() method to retrieve the corresponding field value. The “while” loop will terminate automatically as soon as the SqlDataReader object runs out of records.
Finally, once the entire set of records has been displayed, it’s a good idea to free up used memory resources.
<%
// clear up memory by closing all objects
objReader.Close();
objConn.Close();
%>
Since the SqlDataReader object is read-only and can only be used in one direction, it’s fast and ensures minimal load on the server - always a good thing in the Web world!
Grid Lock
Now, that was a long and complex example, loaded with a large number of objects. Take a breather, and let me show you a simpler (though less flexible) way to accomplish the same thing using the DataGrid server control. As the name suggests, this control allows you to display a database result set in a neat little grid without needing too much code. Here’s an example:
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<script Language="C#" runat="server">
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand("SELECT * FROM starwars;", objConn);
// open the connetion
objConn.Open();
// populate a SqlDataReader object
SqlDataReader objReader = objCommand.ExecuteReader();
// set the source of the "starwars" datagrid
starwars.DataSource = objReader;
// bind the data to the grid
starwars.DataBind();
// clear up memory by closing all objects
objReader.Close();
objConn.Close();
}
</script>
<html>
<title>Star Wars</title>
<body>
<asp:datagrid id="starwars" runat="server"/>
</body>
</html>
And this is the output.
I’m sure that you will not fail to notice the drastic reduction in the amount of code I’ve written - in one flawless maneuver, I have removed all the complicated “for” and “while” loops of the earlier example. Most of this is due to the DataGrid server control, which takes care of converting the resultset into an HTML table.
The first step is to define the server control, as below:
<asp:datagrid id="starwars" runat="server"/>
As before, after creating the mandatory connection object, I have used the SqlCommand and SqlReader objects to retrieve the results of the SELECT query from the database. However, this time round, I have not bothered iterating through the resultset. Instead, I’ve used the “DataSource” property of the server control to point it to a populated SqlDataReader object that serves as the source for the data to be displayed in the grid.
<%
// populate a SqlDataReader object
SqlDataReader objReader = objCommand.ExecuteReader();
// set the source of the "starwars" datagrid
starwars.DataSource = objReader;
// bind the data to the grid
starwars.DataBind();
%>
The DataBind() method does the rest, binding the data to the individual elements of the grid and generating the output shown above. Simple, huh?
And that’s about it for today! In this article, I gave you a quick introduction to ADO.NET and how it differs vastly from its predecessor, together with an example of how to get a connection up and running between an ASP.NET page and a SQL Server 2000 RDBMS. This was followed with another example explaining the objects used to retrieve and iterate over a recordset from the database, and an explanation of the ASP.NET DataGrid server control for quick-and-dirty data display.
What’s cooking for next week, you ask? Lots, say I - first, an introduction to more objects of the ADO.NET family, including the DataSet object for disconnected data access. So make sure you don’t miss the next episode of this tutorial!
Note: Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!
This article was first published on 24 Oct 2003.