Chapter 9: ADO.NET

If you get sick of typing quotes, ampersands, and underscores, you can combine the three bold strings in the above code into a single string. However, I’ll continue to present connection strings as above throughout this book—not only are they more readable that way, but they fit on the page, too!

If you’re using C#, your code should look like this:

C#	File: AccessingData.aspx (excerpt)

protected void Page_Load(object sender, EventArgs e)

{

// Define database connection SqlConnection conn = new SqlConnection(

"Server=localhost\\SqlExpress;Database=Dorknozzle;" + "Integrated Security=True");

}

Be aware that, in C#, the backslash (\) character has a special meaning when it appears inside a string, so, when we wish to use one, we have to use the double backslash (\\) shown above.

Preparing the Command

Now we’re at step three, in which we create a SqlCommand object and pass in our SQL statement. The SqlCommand object accepts two parameters: the first is the SQL statement, and the second is the connection object that we created in the previous step.

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")
'	Create	command
Dim comm		As	New SqlCommand( _
	"SELECT		EmployeeID, Name FROM Employees", conn)
End	Sub
C#			File: AccessingData.aspx (excerpt)

protected void Page_Load(object sender, EventArgs e)

{

// Define database connection SqlConnection conn = new SqlConnection(

"Server=localhost\\SqlExpress;Database=Dorknozzle;" +

336

Executing the Command

"Integrated Security=True");

// Create command

SqlCommand comm = new SqlCommand(

"SELECT EmployeeID, Name FROM Employees", conn);

}

Executing the Command

When we’re ready to run the query, we open the connection and execute the command. The SqlCommand class has three methods that we can use to execute a command; we simply choose between them depending on the specifics of our query. The three methods are as follows:

ExecuteReader

ExecuteReader is used for queries or stored procedures that return one or more rows of data. ExecuteReader returns an SqlDataReader object that can be used to read the results of the query one by one, in a forward-only, read-only manner. Using the SqlDataReader object is the fastest way to retrieve records from the database, but it can’t be used to update the data or to access the results in random order.

The SqlDataReader keeps the database connection open until all the records have been read. This can be a problem, as the database server will usually have a limited number of connections—people who are using your application simultaneously may start to see errors if you leave these connections open. To alleviate this problem, we can read all the results from the SqlDataReader object into an object such as a DataTable, which stores the data locally without needing a database connection. You’ll learn more about the DataTable object in Chapter 12.

ExecuteScalar

ExecuteScalar is used to execute SQL queries or stored procedures that return a single value, such as a query that counts the number of employees in a company. This method returns an Object, which you can convert to specific data types depending on the kinds of data you expect to receive.

ExecuteNonQuery

ExecuteNonQuery is an oddly-named method that’s used to execute stored procedures and SQL queries that insert, modify, or update data. The return value will be the number of affected rows.

337

Chapter 9: ADO.NET

As we’re reading a list of employees, we’ll be using ExecuteReader. After we execute this method, we’ll follow standard practice, reading the data from the returned SqlDataReader as quickly as possible, then closing both the SqlDataReader and the SqlConnection, to ensure we don’t keep any database resources tied up for longer than is necessary.

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")
' Create	command
Dim comm	As	New SqlCommand( _
"SELECT		EmployeeID, Name FROM Employees", conn)

'Open connection conn.Open()

'Execute the command

Dim reader As SqlDataReader = comm.ExecuteReader()

'TODO: Do something with the data

'Close the reader and the connection reader.Close()

conn.Close()

End Sub

C#	File: AccessingData.aspx (excerpt)

protected void Page_Load(object sender, EventArgs e)

{

//Define database connection SqlConnection conn = new SqlConnection(

"Server=localhost\\SqlExpress;Database=Dorknozzle;" + "Integrated Security=True");

//Create command

SqlCommand comm = new SqlCommand(

"SELECT EmployeeID, Name FROM Employees", conn);

//Open connection conn.Open();

//Execute the command

SqlDataReader reader = comm.ExecuteReader();

//TODO: Do something with the data

//Close the reader and the connection reader.Close();

conn.Close();

}

338

Setting up Database Authentication

Let’s take a look at a few of the methods that are being introduced here. Before we can query our database, a connection must be opened, so we need to call the Open method of our SqlConnection object conn. Once the connection is opened, we call the ExecuteReader method of our SqlCommand object comm to run our query. ExecuteCommand will retrieve a list of all employees and return the list in an open SqlDataReader object.

At this point, we would usually do something with the data in reader, but for now, we’ve left a comment to remind us that this method doesn’t produce any output.

Immediately after we’ve done something with the data, we close our SqlDataReader and SqlConnection objects using their Close methods. Keeping the connection open for longer than necessary can waste database resources, which can be an issue in real-world applications where hundreds or more users might be accessing the same database at once. As such, it’s best practice to keep the connection open for the minimum time.

The code above doesn’t have any “real” functionality, as it doesn’t actually display anything for the user; however, it does open a connection to your database.

Setting up Database Authentication

If you’re running your ASP.NET Web Application through IIS and connecting to SQL Server using Integrated Windows Authentication (by setting "Integrated Security=True" in the connection string), you’ll probably get an exception when you try to run AccessingData.aspx. If you don’t get an error, you can skip to the next section—come back only if you get in trouble when connecting to SQL Server.

Provided everything else is as it should be, the error will look like the one shown in Figure 9.2 when you run the code without debugging; it will look like Figure 9.3 if you run the code with debugging using Visual Web Developer.

When you run Dorknozzle using Visual Web Developer’s integrated web server, all code is executed as if it were being run by you. In the background, every time your code requests a system resource, Windows checks to make sure that you have access to that resource. If your program were accessing files on the hard drive, for example, Windows would check that you have permission to access those files before allowing the program to proceed. Windows also checks to make sure that you have access to the database. As the user who installed the database

339

Chapter 9: ADO.NET

Figure 9.2. A database connection error trapped by the ASP.NET runtime

Figure 9.3. A database connection error trapped by Visual Web Developer

340

Setting up Database Authentication

in the first place, you have free reign over its features—you’re allowed unfettered access to all areas.

When you run Dorknozzle through IIS, on the other hand, the code is executed as if it were being run by a special user called ASPNET (or Network Service in Windows Server 2003). As such, you need to give this ASPNET user access to your Dorknozzle database. Let’s do that now.

Start by opening SQL Server Management Studio and connecting to your SQL

Server instance. Click the New Query button to open a new query window. Now, type the code below into the query window, replacing the MachineName section with the name of your machine:

Comments

The lines starting with -- are comments, so you may prefer not to type them.

--Add the ASPNET account to SQL Server EXEC sp_grantlogin 'MachineName\ASPNET'

--Give the ASPNET account access to Dorknozzle USE Dorknozzle

EXEC sp_grantdbaccess 'MachineName\ASPNET'

--Give the ASPNET account full privileges to Dorknozzle EXEC sp_addrolemember 'db_owner', 'MachineName\ASPNET'

What’s the Name of my Machine?

If you’re not sure of the name of your machine, right-click on My Computer, which you can find either on the desktop or in the Start menu, and select

Properties. Under the Computer Name tab, you’ll see the Full computer name listed.

Now, when you’re running the application through IIS, you can connect to SQL Server from your web application using Integrated Windows Authentication. Execute AccessingData.aspx; this time, you shouldn’t see any connectivity errors.

Using SQL Server Authentication

Alternatively, you can connect using SQL Server Authentication by supplying a username and password as part of the connection string. This is the connection mode you’ll most likely use when connecting to remote SQL Server instances using the username and password provided to you by that database’s administrator. The code to do this in VB and C# is shown below. Replace

341