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

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

"Database=Dorknozzle;Integrated Security=True")

comm = New SqlCommand( _

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

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

Dim employeeID As Integer

If (Not Integer.TryParse(idTextBox.Text, employeeID)) Then

userLabel.Text = "Please enter a numeric ID!"

Else

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

comm.Parameters("@EmployeeID").Value = employeeID

Licensed to [email protected]

ADO.NET

365

conn.Open()

reader = comm.ExecuteReader()

If reader.Read() Then

userLabel.Text = "Employee ID: " & _

reader.Item("EmployeeID") & "
" & _

"Name: " & reader.Item("Name") & "
" & _

"Username: " & reader.Item("Username") & "
" & _

"Password: " & reader.Item("Password")

Else

userLabel.Text = _

"There is no user with this ID: " & employeeID

End If

reader.Close()

conn.Close()

End If

End Sub

C#

LearningASP\CS\QueryParameters_02.aspx
(excerpt)

protected void submitButton_Click(object sender, EventArgs e)

{

SqlConnection conn;

SqlCommand comm;

SqlDataReader reader;

conn = new SqlConnection("Server=localhost\\SqlExpress;" +

"Database=Dorknozzle;Integrated Security=True");

comm = new SqlCommand(

"SELECT EmployeeID, Name, Username, Password " +

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

int employeeID;

if (!int.TryParse(idTextBox.Text, out employeeID))

{

userLabel.Text = "Please enter a numeric ID!";

}

else

{

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

comm.Parameters["@EmployeeID"].Value = employeeID;

conn.Open();

reader = comm.ExecuteReader();

if (reader.Read())

{

userLabel.Text = "Employee ID: " +

reader["EmployeeID"] + "
" +

Licensed to [email protected]

366

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

"Name: " + reader["Name"] + "
" +

"Username: " + reader["Username"] + "
" +

"Password: " + reader["Password"];

}

else

{

userLabel.Text =

"There is no user with this ID: " + employeeID;

}

reader.Close();

conn.Close();

}

}

Now, when the user clicks the button, the Click event is raised, and the event

handler is executed. In that method, we grab the Employee ID from the Text property

of the TextBox control, and check that it’s a valid integer. This check can be done

with the Integer.TryParse method in VB, or the int.TryParse method in C#:

Visual Basic

LearningASP\VB\QueryParameters_02.aspx
(excerpt)

⋮

Dim employeeID As Integer

If (Not
Integer.TryParse(idTextBox.Text, employeeID)
) Then

⋮

C#

LearningASP\CS\QueryParameters_02.aspx
(excerpt)

⋮

int employeeID;

if (!
int.TryParse(idTextBox.Text, out employeeID)
)

{

⋮

This method verifies whether or not the string we pass as the first parameter can

be cast to an integer; if it can, the integer is returned through the second parameter.

Note that in C#, this second parameter is an out parameter.
Out parameters
are

parameters that are used to retrieve data from a function, rather than send data to

that function. Out parameters are similar to return values, except that we can supply

Licensed to [email protected]

ADO.NET

367

multiple out parameters to any method. The return value of TryParse is a Boolean

value that specifies whether or not the supplied value could be properly converted.

If the ID that’s entered isn’t a valid number, we notify the user, as
Figure 9.6 illus-

trates.

Figure 9.6. Invalid input data generating a warning

We want also to notify the user if the query doesn’t return any results. This feature

is simple to implement, because reader.Read only returns True if the query returns

a record:

Visual Basic

LearningASP\VB\QueryParameters_02.aspx
(excerpt)

⋮

If
reader.Read()
Then

userLabel.Text = "Employee ID: " & reader.Item("EmployeeID") & _

⋮

C#

LearningASP\CS\QueryParameters_02.aspx
(excerpt)

⋮

if (
reader.Read()
)

{

userLabel.Text = "Employee ID: " + reader["EmployeeID"] +

⋮

Figure 9.7 shows the message you
’ll see if you enter an ID that doesn’t exist in the database.

Licensed to [email protected]

368

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

Figure 9.7. An invalid ID warning

There are still a couple of details that we could improve in this system. For example,

if an error occurs in the code, the connection will never be closed. Let’s look at this

problem next.

Bulletproofing Data Access Code

Right now, the code in
QueryParameters.aspx
seems to be perfect, right? Well, not

quite. While the code does its job most of the time, it still has one important weakness: it doesn’t take into account potential errors that could occur in the data access code. It’s very good practice to enclose such code in Try-Catch-Finally blocks, and

always to use the Finally block to close any open data objects. We learned about

Try-Catch-Finally in
Chapter 5; now we
’re going to use that theory in a real-world scenario.

Take a look at the following code samples:

Visual Basic

LearningASP\VB\QueryParameters_03.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

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

"Database=Dorknozzle;Integrated Security=True")

comm = New SqlCommand( _

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

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

Dim employeeID As Integer

If (Not Integer.TryParse(idTextBox.Text, employeeID)) Then

userLabel.Text = "Please enter a numeric ID!"

Else

Licensed to [email protected]

ADO.NET

369

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

comm.Parameters("@EmployeeID").Value = employeeID

Try

conn.Open()

reader = comm.ExecuteReader()

If reader.Read() Then

userLabel.Text = "Employee ID: " & _

reader.Item("EmployeeID") & "
" & _

"Name: " & reader.Item("Name") & "
" & _

"Username: " & reader.Item("Username") & "
" & _

"Password: " & reader.Item("Password")

userLabel.Text = _

"There is no user with this ID: " & employeeID

End If

reader.Close()

Catch

userLabel.Text = "Error retrieving user data."

Finally

conn.Close()

End Try

End If

End Sub

C#

LearningASP\CS\QueryParameters_03.aspx
(excerpt)

protected void submitButton_Click(object sender, EventArgs e)

{

SqlConnection conn;

SqlCommand comm;

SqlDataReader reader;

conn = new SqlConnection("Server=localhost\\SqlExpress;" +

"Database=Dorknozzle;Integrated Security=True");

comm = new SqlCommand(

"SELECT EmployeeID, Name, Username, Password " +

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

int employeeID;

if (!int.TryParse(idTextBox.Text, out employeeID))

{

userLabel.Text = "Please enter a numeric ID!";

}

else

{

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

Licensed to [email protected]

370

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

comm.Parameters["@EmployeeID"].Value = employeeID;

try

{

conn.Open();

reader = comm.ExecuteReader();

if (reader.Read())

{

userLabel.Text = "Employee ID: " +

reader["EmployeeID"] + "
" +

"Name: " + reader["Name"] + "
" +

"Username: " + reader["Username"] + "
" +

"Password: " + reader["Password"];

}

{

userLabel.Text =

"There is no user with this ID: " + employeeID;

}

reader.Close();

}

catch

{

userLabel.Text = "Error retrieving user data.";

}

finally

{

conn.Close();

}

}

}

So, what’s new in this version of the event handler, apart from the fact that it’s become larger? First of all—and most importantly—we have the Try-Catch-Finally block in place. Everything that manipulates the database is in the Try block. If an

error arises, we display a message for the user through the Catch block. In the Finally block, which is always guaranteed to execute, we close the database connection.
Using the Repeater Control

The .NET Framework comes bundled with a few controls that can help us to display

more complex lists of data: Repeater, DataList, GridView, DetailsView, and

Licensed to [email protected]

ADO.NET

371

FormView. These controls allow you to format database data easily within an

ASP.NET page.

In this chapter, you’ll learn how to work with the Repeater; we’ll cover the other

controls in the next few chapters. Note that these controls aren’t part of ADO.NET,

but we’re presenting them together with ADO.NET because they’re frequently used

in work with databases.

The Repeater control is a lightweight ASP.NET control that allows the easy

presentation of data directly from a data source, usually in just a handful of lines

of code. Let’s look at a quick example of how a Repeater control can be added to

a page:

<%# Eval("Name") %>

As you can see, the Repeater control looks a little different from the other web

controls we’ve used thus far. The difference with this control is that an

subtag—otherwise known as a
child tag
—is located within the

control’s main tag, or
parent tag
. This child tag contains a code render block that specifies the particular data item that we want to appear in the

Repeater. However, before this data can be displayed, we have to bind an

SqlDataReader object (which contains the results of an SQL query) to the Repeater

control using the process known as
data binding
. This task is achieved from a code