Build Your Own ASP.NET 3.5 Website Using C# & VB (64 page)

ADO.NET

351

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 required authentication details. A typical connection string for an 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, we’d use a connection string that included 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 little later; for now,

let’s assume that your code can access your database successfully.

Let’s put this approach into practice by creating an SqlConnection in the Page_Load

event handler. To have Visual Web Developer create an empty Page_Load event

Licensed to [email protected]

352

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

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

LearningASP\VB\AccessingData_02.aspx
(excerpt)

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs)

Dim conn As New SqlConnection("Server=localhost\SqlExpress;" & _

"Database=Dorknozzle;Integrated Security=True")

End Sub

If you're 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#

LearningASP\CS\AccessingData_02.aspx
(excerpt)

protected void Page_Load(object sender, EventArgs e)

{

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 an 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:

Licensed to [email protected]

ADO.NET

353

Visual Basic

LearningASP\VB\AccessingData_03.aspx
(excerpt)

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs)

Dim conn As New SqlConnection("Server=localhost\SqlExpress;" & _

"Database=Dorknozzle;Integrated Security=True")

Dim comm As New SqlCommand("SELECT EmployeeID, Name " & _

"FROM Employees", conn)

End Sub

C#

LearningASP\CS\AccessingData_03.aspx
(excerpt)

protected void Page_Load(object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection(

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

"Integrated Security=True");

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 the one that meets the specific needs 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 alleLicensed to [email protected] 354

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

viate 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 a specific data type depending on the kind 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.

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

LearningASP\VB\AccessingData_04.aspx
(excerpt)

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs)

Dim conn As New SqlConnection("Server=localhost\SqlExpress;" & _

"Database=Dorknozzle;Integrated Security=True")

Dim comm As New SqlCommand("SELECT EmployeeID, Name " & _

"FROM Employees", conn)

conn.Open()

Dim reader As SqlDataReader = comm.ExecuteReader()

⋮
we'll do something with the data here…

reader.Close()

conn.Close()

End Sub

Licensed to [email protected]

ADO.NET

355

C#

LearningASP\CS\AccessingData_04.aspx
(excerpt)

protected void Page_Load(object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection(

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

"Integrated Security=True");

SqlCommand comm = new SqlCommand(

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

conn.Open();

SqlDataReader reader = comm.ExecuteReader();

⋮
we'll do something with the data here…

reader.Close();

conn.Close();

}

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 ourselves that this method doesn’t produce any

output.

Immediately after we’ve done something with the data, we close the 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, it executes a SQL query, and finally closes the connection. Licensed to [email protected]

356

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Setting Up Database Authentication

Quite frequently, when using Integrated Windows Authentication (by setting "Integrated Security=True" in the connection string), programmers find that their applications are unable to access the database. If you receive a login failed error

when executing the
AccessingData.aspx
file, you’ll find the solution to the problem

in this section. If you don’t get this error, you can safely skip to the next section—come back only if you get into trouble when you’re connecting to SQL Server. Such database authentication and authorization problems can be solved easily with

SQL Server authentication. When you use this authentication method, your application doesn’t use a Windows account to log in to the SQL Server database; instead, it uses an SQL Server username and password. This is also the authentication

method you’ll use when you deploy applications to a production server.

To be able to use SQL Server authentication with your database, you first need to

enable the feature in SQL Server by setting the
Server authentication
mode to SQL

Server and Windows Authentication mode
as we did in the section called “In-

stalling SQL Server Management Studio Express” in Chapter 1
. You then need to create an SQL Server username that has access to the Dorknozzle database. To do

that, start SQL Server Management Studio, expand the
Security
>
Logins
node in
Object Explorer
, right-click the
Logins
node, and select
New Login…
. In the dialog that displays, select
SQL Server authentication
and type
dorknozzle
as the username and
dorknozzle
as the password. Deselect the
Enforce password policy
checkbox. Though these options are very important in a real-world scenario, we’re deactivating them

for the exercises in this book. Finally, change the
Default database
to Dorknozzle.

The required settings are shown in
Figure 9.2
.

Licensed to [email protected]