Updating and Inserting Data with ADO.NET and ASP.NET 2.0 - Using Parameterized Queries to Retrieve Data
(Page 4 of 4 )
You might have used a parameterized query while working on a SQL Server database before. A parameterized query is a query that has one or more parameters. The following query is an example.
SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID = @EmployeeID
We need to build an example that asks the user for the value of the @EmployeeID parameter and retrieves the data depending on that value. Let's see the code first, and then discuss it as usual. The following is the code for the Default.aspx page:
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="EmployeeLabel" runat="server"
Text="Enter the Employee ID"></asp:Label>
<asp:TextBox ID="EmployeeIDTextBox" runat="server"
Width="26px"></asp:TextBox><br />
<asp:Label ID="FirstNameLabel" runat="server" Text="First
Name"></asp:Label>
<asp:TextBox ID="EmployeeFirstName"
runat="server"></asp:TextBox><br />
<asp:Label ID="LastNameLabel" runat="server" Text="Last
Name"></asp:Label>
<asp:TextBox ID="EmployeeLastName"
runat="server"></asp:TextBox><br /><br />
<asp:Button ID="EmployeeGetButton" runat="server" Text="Get
Employee"
OnClick="EmployeeGetButton_Click" /><br />
<asp:Label ID="ErrorLabel" runat="server"
ForeColor="Red"></asp:Label>
</div>
</form>
</body>
</html>
We have created the page with the necessary Labels and TextBoxes in addition to the Button control that executes the data access code. The following is the code for the Default.aspx.cs file.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
// use the namespace of the ADO.NET SQL Server Data Provider
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e){
ErrorLabel.Text = String.Empty;
}
protected void EmployeeGetButton_Click(object sender, EventArgs
e){
if (EmployeeIDTextBox.Text != String.Empty){
string connectionString = "Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True";
try{
using (SqlConnection connection = new SqlConnection
(connectionString)){
string commandText = "SELECT FirstName, LastName FROM
Employees WHERE " + "EmployeeID = @EmployeeID";
SqlCommand command = new SqlCommand(commandText,
connection);
command.Parameters.AddWithValue("@EmployeeID",
EmployeeIDTextBox.Text);
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader
()){
while (dataReader.Read()){
EmployeeFirstName.Text = dataReader
["FirstName"].ToString();
EmployeeLastName.Text = dataReader
["LastName"].ToString();
}
}
}
}
catch (Exception ex){
ErrorLabel.Text = ex.Message;
}
}
else {
ErrorLabel.Text = "You must enter an Employee ID before
clicking the button";
}
}
}
Run the page and enter the value 2 as the Employee ID. You will get the first name and last name of the employee as shown in the screen shot below.
The above code is similar to the code we have used before except for the following line:
command.Parameters.AddWithValue("@EmployeeID",
EmployeeIDTextBox.Text);
The SqlCommand class has a Parameters property that is used to add SqlParameter objects to the command. Those parameters, along with their values, will be sent to the SQL Server to be executed with the T-SQL statement. So to use a parameterized query you need to write it first in T-SQL code and assign the query, as a string value, to the SqlCommand object. Then you need to create SqlParameter objects to match those in the T-SQL query.
A SqlParameter is a representation of a T-SQL parameter and its behavior. We will talk about parameters more in the next few articles, but for now you just need to know that the AddWithValue method of the SqlCommand.Parameters collection is passed the name of the parameter; you must prefix the parameter name with the @ sign as you do in T-SQL code; and the value of the parameter, and then the parameter object, will be added to the Parameters collection.
The next article will discuss in detail how to create and use Sqlparameter objects with Parameterized queries and stored procedures.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
