Read Build Your Own ASP.NET 3.5 Website Using C# & VB Online
Authors: Cristian Darie,Zak Ruvalcaba,Wyatt Barnett
Tags: #C♯ (Computer program language), #Active server pages, #Programming Languages, #C#, #Web Page Design, #Computers, #Web site development, #internet programming, #General, #C? (Computer program language), #Internet, #Visual BASIC, #Microsoft Visual BASIC, #Application Development, #Microsoft .NET Framework
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
child tag
—is located within the
control’s main
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