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.
SELECT EmployeeID, Name, Username, Password
FROM Employees
WHERE EmployeeID = EmployeeID
Let’s build a page like the one shown in Figure 9.5 to display this information.
Figure 9.5. Retrieving details of a specific employee
Create a new web form called QueryParameters.aspx and alter it to reflect the
code shown here:
File: QueryParameters.aspx (excerpt)
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
344
Chapter 9: ADO.NET
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Using Query Parameters</title>
</head>
<body>
<form id="form1" runat="server">
<div>
User ID:
<asp:TextBox ID="idTextBox" runat="server" />
<asp:Button ID="submitButton" runat="server"
Text="Get Data" /><br />
<asp:Label ID="userLabel" runat="server" />
</div>
</form>
</body>
</html>
With these amendments, we’ve added a Textbox control into which users can
type in 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:
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
FROM Employees
WHERE EmployeeID = 5
345
Using Parameters with Queries
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:
SELECT EmployeeID, Name, Username, Password
FROM Employees
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 para-
meters a type. Inserting parameters into your query is a pretty simple task:
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. To do so, we
add the @ symbol, followed by an identifier for our 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 conn.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 in-
volved an nvarchar parameter named @Username, for example, you could set its
value with the following code:
Visual Basic
comm.Parameters.Add("@Username", Data.SqlDbType.NVarChar, 50)
comm.Parameters("@Username").Value = username
346
Chapter 9: ADO.NET
Get Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
