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

ADO.NET

357

Figure 9.2. Setting up SQL Server authentication

We also want our new user to have full access to the Dorknozzle database. You can

modify this user permission when you’re creating the new user, or after the fact.

To make the user dorknozzle the owner of the Dorknozzle database, select
User

Mapping
from the
Select a page
pane, check the
Dorknozzle
table in the list, and check the
db_owner
role, as depicted in Figure 9.3
. To return to this page after you create the user, right-click the
dorknozzle
user in SQL Server Management Studio and select

Properties
.

Licensed to [email protected]

358

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

Figure 9.3. Setting the database owner

Once the new user is in place, you can use this user account to connect to your

database. The code to do this in VB and C# is shown below. Be sure to replace

ServerName
,
InstanceName
,
DatabaseName
,
Username
, and
Password
with the appropriate details for your server:

Visual Basic

Dim conn As New SqlConnection("Server=
ServerName
\
InstanceName
;" & _

"Database=
DatabaseName
;User ID=
Username
;" & _

"Password=
Password
")

C#

SqlConnection conn = new SqlConnection(

"Server=
ServerName
\\
InstanceName
;" +

"Database=
DatabaseName
;User ID=
Username
;" +

"Password=
Password
");

Licensed to [email protected]

ADO.NET

359

Reading the Data

Okay, so you’ve opened the connection and executed the command. Let’s do

something with the returned data!

A good task for us to start with is to display the list of employees we read from the

database. To do so, we’ll simply use a While loop to add the data to a Label control

that we’ll place in the form. Start by adding a Label named employeesLabel to the

AccessingData.aspx
web form. We’ll also change the title of the page to
Using ADO.NET
:
LearningASP\VB\AccessingData_05.aspx
(excerpt)

⋮

⋮

Now, let’s use the SqlDataReader’s Read method to loop through the data items

held in the reader; we’ll display them by adding their text to the employeesLabel

object as we go:

Visual Basic

LearningASP\VB\AccessingData_06.aspx
(excerpt)

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs)

⋮
create the SqlConnection and SqlCommand objects…

conn.Open()

Dim reader As SqlDataReader = comm.ExecuteReader()

While reader.Read()

employeesLabel.Text &= reader.Item("Name") & "
"
End While

reader.Close()

conn.Close()

End Sub

Licensed to [email protected]

360

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

C#

LearningASP\CS\AccessingData_06.aspx
(excerpt)

protected void Page_Load(object sender, EventArgs e)

{

⋮
create the SqlConnection and SqlCommand objects…

conn.Open();

SqlDataReader reader = comm.ExecuteReader();

while (reader.Read())

{

employeesLabel.Text += reader["Name"] + "
";

}

reader.Close();

conn.Close();

}

Figure 9.4. Displaying the list of employees

We already know that the SqlDataReader class reads the data row by row, in a forward-only fashion. Only one row can be read at any moment. When we call reader.Read, our SqlDataReader reads the next row of data from the database. If

there’s data to be read, it returns True; otherwise—if we’ve already read the last

record returned by the query—the Read method returns False. If we view this page

in the browser, we’
ll see something like Figure 9.4
.

Using Parameters with Queries

What if the user doesn’t want to view information for all employees, but instead,

wants to see details for one specific employee?

To get this information from our Employees table, we’d run the following query,

replacing
EmployeeID
with the ID of the employee in which the user was interested:

Licensed to [email protected]

ADO.NET

361

SELECT EmployeeID, Name, Username, Password

WHERE EmployeeID =
EmployeeID

Let’s build a page like the one shown in
Figure 9.5 to display this information.

Figure 9.5. Retrieving the details of a specific employee

Create a new web form called
QueryParameters.aspx
and alter it to reflect the code

shown here:

LearningASP\VB\QueryParameters_01.aspx
(excerpt)

<%@ Page Language="VB" %>

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

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

With these amendments, we’ve added a Textbox control into which users can enter

the ID of the employee whose information they want to see. We’ve also added a

Button that will be used to submit the form and retrieve the data.

Next, we need to add a Click event handler to the Button control. When this button

is clicked, our web form will need to execute the following tasks:

1.

Read the ID typed by the user in the idTextBox control.

2.

Prepare an SQL query to retrieve data about the specified employee.

3.

Execute the query and read the results.

Now, we
could
perform this query using the following code:

Visual Basic

comm = New SqlCommand( _

"SELECT EmployeeID, Name, Username, Password " & _

"FROM Employees WHERE EmployeeID = " & idTextBox.Text , conn)

If the user entered the number
5
into the text box and clicked the button, the following query would be run: SELECT EmplyeeID, Name, Username, Password

WHERE EmployeeID = 5

The database would run this query without complaint, and your program would

execute as expected. However, if—as is perhaps more likely—the user entered an

employee’s name, your application would attempt to run the following query:

Licensed to [email protected]

ADO.NET

363

SELECT EmployeeID, Name, Username, Password

WHERE EmployeeID = Zac Ruvalcaba

This query would cause an error in the database, which would, in turn, cause an

exception in your web form. As a safeguard against this eventuality, ADO.NET allows

you to define parameters in your query, and to give each of those parameters a type.

Inserting parameters into your query is a reasonably simple task:

Visual Basic

comm = New SqlCommand( _

"SELECT EmployeeID, Name, Username, Password " & _

"FROM Employees WHERE EmployeeID =
@EmployeeID
", conn)

We’ve added a placeholder for our parameter to the query above; it comprises the

@ symbol, followed by an identifier for the parameter (in this case, we’ve used EmployeeID). Next, we need to add this parameter to the SqlCommand object, and give it a value:

Visual Basic

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int)

comm.Parameters("@EmployeeID").Value = idTextBox.Text

C#

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int);

comm.Parameters["@EmployeeID"].Value = idTextBox.Text

Here, we call the Add method of comm.Parameters, passing in the name of the

parameter (EmployeeID) and the parameter’s type; we’ve told ADO.NET that we’re

expecting an int to be passed to the database, but we could specify any of the SQL

Server data types here.

One of the most common SQL Server data types is nvarchar. If your query involved

an nvarchar parameter named @Username, for example, you could set its value with

the following code:

Licensed to [email protected]

364

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

Visual Basic

comm.Parameters.Add("@Username", Data.SqlDbType.NVarChar, 50)

comm.Parameters("@Username").Value = username

C#

comm.Parameters.Add("@Username", SqlDbType.NVarChar, 50);

comm.Parameters["@Username"].Value = username;

Notice that we’ve included an additional parameter in our call to the Add method.

This optional parameter tells the SqlCommand object the maximum allowable size

of the nvarchar field in the database. We’ve given the Username field in our Employees table a maximum size of 50 characters, so our code should reflect this limit. For a list of all the types you can use when calling conn.Parameters.Add, see the

entry on System.Data.SqlDbType enumeration in the .NET Framework’s SDK

Documentation.

Let’s put parameters into action in
QueryParameters.aspx
. First, create a Click event

handler for the Button control by double-clicking it in Design view. Next, fill the

event handler with the code shown below:

Visual Basic

LearningASP\VB\QueryParameters_02.aspx
(excerpt)

Protected Sub submitButton_Click(ByVal sender As Object,

➥ ByVal e As System.EventArgs)

Dim conn As SqlConnection

Dim comm As SqlCommand

Dim reader As SqlDataReader