Chapter 9: ADO.NET

how to execute SQL queries and retrieve their results using ADO.NET

how to display data that is read from a database

how to handle data access errors

Introducing ADO.NET

In previous chapters, we learned how to use Visual Web Developer and SQL Management Studio to connect to a database and execute SQL queries. Now, it’s time to apply this knowledge. Within our web application, we’ll use ADO.NET’s classes to connect to the database; we’ll then use that connection to execute SQL queries.

ADO.NET 2.0 and Generic Data Access

ADO.NET is able to use different types of data connections, depending on the kind of database to which the application is trying to connect. The ADO.NET classes whose names start with Sql (such as the previously mentioned SqlConnection, SqlCommand, etc.) are specifically built to connect to SQL Server.

Similar classes are provided for other databases—for example, if you’re working with Oracle, you can use classes such as OracleConnection, OracleCommand, and so on. If, on the other hand, you’re working with database systems for which such classes are not specifically designed, you can use generic low-level interfaces; most databases can be accessed through the OLE DB interface (using classes such as OleDbConnection and OleDbCommand), or the older ODBC interface (using classes such as

OdbcConnection and OdbcCommand).

In this book, we’ll use only the Sql classes, but it’s good to know that you have options!

In order to use ADO.NET, we must first decide which kind of database we’ll use, and import those namespaces containing classes that work with the database. Since we’re using SQL Server, you’ll need to import the System.Data.SqlClient namespace. This contains all the required Sql classes, the most important of which are:

SqlConnection

This class exposes properties and methods for connecting to an SQL Server database.

332

Importing the SqlClient Namespace

SqlCommand

This class holds data about the SQL queries and stored procedures that you intend to run on your SQL Server database.

SqlDataReader

Data is returned from the database in an SqlDataReader class. This class comes with properties and methods that let you iterate through the data it contains. Traditional ASP developers can think of the SqlDataReader as being similar to a forward-only RecordSet, in which data can only be read forward, one record at a time, and we cannot move back to the beginning of the data stream.

The System.Data.SqlClient namespace exposes many more than the few classes listed above. We’ll discuss some of the more advanced classes in the next few chapters.

Once you’re ready to begin working with ADO.NET, the task of establishing a link between the database and your application is a straightforward, six-step process:

1.Import the necessary namespaces.

2.Define a connection to your database with an SqlConnection object.

3.When you’re ready to manipulate your database, set up the appropriate query in an SqlCommand object.

4.Open the connection and execute the SQL query to return the results into a SqlDataReader object.

5.Extract relevant database data from the SqlDataReader object and display it on your web page.

6.Close the database connection.

Let’s walk through this process, discussing each step.

Importing the SqlClient Namespace

It’s been a while since we’ve written some VB or C# code! Let’s fire up our old friend, Visual Web Developer, and load the Learning project. We’ll use this application to create a few simple scripts; then we’ll move to Dorknozzle, where we’ll add more functionality to the project site.

333

Chapter 9: ADO.NET

After opening the Learning project, go to File > New File… to create a new file.

Select the Web Form template, and name it AccessingData.aspx. Uncheck the

Place code in separate file and Select master page checkboxes, as shown in Figure 9.1.

Figure 9.1. Creating the AccessingData.aspx web form

After the form is created, we can import the SqlClient namespace:

File: AccessingData.aspx (excerpt)

<%@ Page Language="VB" %>

<%@ Import Namespace = "System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Defining the Database Connection

With our import of the SqlClient namespace complete, we can create a new instance of the SqlConnection, which will facilitate our connection to the database. To initialize this connection, we need to specify a connection string—a string in which we specify the database we want to connect to, and provide any

334

Defining the Database Connection

required authentication details. A typical connection string for a SQL Server Express database looks like this:

Server=computer\SqlExpress;Database=database;User ID=username;

Password=password

The connection string must specify the name of the computer on which the database is located (you can always use localhost to refer to the local machine) and the name assigned to the database server instance (SqlExpress is the default for SQL Server Express). Also required are the name of the database (such as Dorknozzle), the user ID, and the password for that user account.

SQL Server supports two methods of authentication: SQL Server Authentication and Windows Authentication. The form of authentication we’ve used in previous chapters to connect to SQL Server was Windows Authentication, which doesn’t require you to supply a SQL Server name and password, but instead uses the credentials of your Windows user account. To tell SQL Server that we’re logging in using Windows Authentication, our connection string would include Integrated Security=True, rather than a username and password, as shown here:

Server=computer\SqlExpress;Database=database;Integrated Security=True

SQL Server Authentication

Be aware that, when the ASP.NET web application is run by ASP.NET through IIS, it authenticates to SQL Server using a special account named ASPNET. We’ll discuss more about configuring SQL Server authentication a bit later; for now, let’s assume that your code can access your database successfully.

Let’s put this into practice by creating an SqlConnection in the Page_Load event handler. To have Visual Web Developer create an empty Page_Load event handler for you, switch to Design View, and double-click somewhere within the form. This should take you back to Source View where you can see the Page_Load method that was created for you. If you’re using VB, enter the code shown in bold below:

Visual Basic	File: AccessingData.aspx (excerpt)
Protected	Sub Page_Load(ByVal sender As Object, _
ByVal	e As System.EventArgs)

' Define database connection

Dim conn As New SqlConnection("Server=localhost\SqlExpress;" & _ "Database=Dorknozzle;Integrated Security=True")

End Sub

335