Using Parameters in Queries

Certify and Increase Opportunity.
Be
Govt. Certified ASP.NET Programmer

Using Parameters in Queries
The SqlDataSource control’s Configure Data Source wizard offers three avenues for defining the command to execute to retrieve database records:

  • By picking the columns from an existing table or view,
  • By entering a custom SQL statement, or
  • By choosing a stored procedure

When picking columns from an existing table or view, the parameters for the WHEREclause must be specified through the Add WHEREClause dialog box. When creating a custom SQL statement, however, you can enter the parameters directly into the WHEREclause (using @parameterNameto denote each parameter). A stored procedure consists of one or more SQL statements, and these statements can be parameterized. The parameters used in the SQL statements, however, must be passed in as input parameters to the stored procedure.

The SqlDataSource and AccessDataSource controls enable you to specify parameter placeholders in an SQL statement, such as the SelectCommand. The ObjectDataSource control uses parameters to determine the appropriate method signature to call for a particular data operation, such as the SelectMethod.

Data source controls typically include a parameter collection for each data operation. When selecting data, you can specify a SelectParameters collection, when updating a data item you can specify an UpdateParameters collection, and so on. The contents of the parameters collection for a particular action are then used to supply values to the underlying data source. When inserting, updating, or deleting data, the data source control creates parameters for bound fields, combines them with the explicitly specified parameters collection (if any), and then passes the resulting collection to the data source.

The following example shows a SqlDataSource control that retrieves information based on a value from a QueryString field.

<asp:SqlDataSource id=”Employees1″ runat=”server”
ConnectionString=”<%$ ConnectionStrings:Northwind %>”
SelectCommand=”SELECT EmployeeID, LastName, FirstName FROM Employees
WHERE EmployeeID = @empId”>
<SelectParameters>
<asp:QueryStringParameter Name=”empId” QueryStringField=”empId” />
</SelectParameters>
</asp:SqlDataSource>

The following example shows a SqlDataSource control that retrieves information based on a value from another control on the page.

<asp:DropDownList id=”DropDownList1″ runat=”server”
autopostback=”True”>
<asp:listitem selected>Sales Representative</asp:listitem>
<asp:listitem>Sales Manager</asp:listitem>
<asp:listitem>Vice President, Sales</asp:listitem>
</asp:DropDownList></p>

<asp:SqlDataSource id=”Employees” runat=”server”
ConnectionString=”<%$ ConnectionStrings:Northwind%>”
SelectCommand=”SELECT LastName FROM Employees WHERE Title = @Title”>
<SelectParameters>
<asp:ControlParameter Name=”Title”
ControlID=”DropDownList1″
PropertyName=”SelectedValue”/>
</SelectParameters>
</asp:sqldatasource>

The following example shows a SqlDataSource control that uses parameterized commands to query and modify data from a data-bound control. Parameters are explicitly specified in order to strongly type parameter values and to specify output parameters.
C#
VB

<%@ Page language=”C#” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<script runat=”server”>

void EmployeesDropDownList_OnSelectedIndexChanged(Object sender, EventArgs e)
{
EmployeeDetailsView.DataBind();
}

void EmployeeDetailsView_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e)
{
EmployeesDropDownList.DataBind();
EmployeesDropDownList.SelectedValue = e.Keys[“EmployeeID”].ToString();
EmployeeDetailsView.DataBind();
}

void EmployeeDetailsView_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e)
{
EmployeesDropDownList.DataBind();
}

void EmployeeDetailsSqlDataSource_OnInserted(Object sender, SqlDataSourceStatusEventArgs e)
{
System.Data.Common.DbCommand command = e.Command;
EmployeesDropDownList.DataBind();
EmployeesDropDownList.SelectedValue =
command.Parameters[“@EmpID”].Value.ToString();
EmployeeDetailsView.DataBind();
}

</script>

<html xmlns=”http://www.w3.org/1999/xhtml” >
<head runat=”server”>
<title>Northwind Employees</title>
</head>
<body>
<form id=”form1″ runat=”server”>

<h3>Northwind Employees</h3>

<table cellspacing=”10″>

<tr>
<td valign=”top”>
<asp:DropDownList ID=”EmployeesDropDownList”
DataSourceID=”EmployeesSqlDataSource”
DataValueField=”EmployeeID”
DataTextField=”FullName”
AutoPostBack=”True”
OnSelectedIndexChanged=”EmployeesDropDownList_OnSelectedIndexChanged”
RunAt=”Server” />
</td>

<td valign=”top”>
<asp:DetailsView ID=”EmployeeDetailsView”
DataSourceID=”EmployeeDetailsSqlDataSource”
AutoGenerateRows=”false”
AutoGenerateInsertbutton=”true”
AutoGenerateEditbutton=”true”
AutoGenerateDeletebutton=”true”
DataKeyNames=”EmployeeID”
Gridlines=”Both”
OnItemUpdated=”EmployeeDetailsView_ItemUpdated”
OnItemDeleted=”EmployeeDetailsView_ItemDeleted”
RunAt=”server”>

<HeaderStyle backcolor=”Navy”
forecolor=”White”/>

<RowStyle backcolor=”White”/>

<AlternatingRowStyle backcolor=”LightGray”/>

<EditRowStyle backcolor=”LightCyan”/>

<Fields>
<asp:BoundField DataField=”EmployeeID” HeaderText=”Employee ID” InsertVisible=”False” ReadOnly=”true”/>
<asp:BoundField DataField=”FirstName”  HeaderText=”First Name”/>
<asp:BoundField DataField=”LastName”   HeaderText=”Last Name”/>
<asp:BoundField DataField=”Address”    HeaderText=”Address”/>
<asp:BoundField DataField=”City”       HeaderText=”City”/>
<asp:BoundField DataField=”Region”     HeaderText=”Region”/>
<asp:BoundField DataField=”PostalCode” HeaderText=”Postal Code”/>
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>

<asp:SqlDataSource ID=”EmployeesSqlDataSource”
SelectCommand=”SELECT EmployeeID, LastName + ‘, ‘ + FirstName AS FullName FROM Employees”
Connectionstring=”<%$ ConnectionStrings:NorthwindConnection %>”
RunAt=”server”>
</asp:SqlDataSource>

<asp:SqlDataSource ID=”EmployeeDetailsSqlDataSource”
SelectCommand=”SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
FROM Employees WHERE EmployeeID = @EmpID”

InsertCommand=”INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
VALUES (@LastName, @FirstName, @Address, @City, @Region, @PostalCode);
SELECT @EmpID = SCOPE_IDENTITY()”

UpdateCommand=”UPDATE Employees SET [email protected], [email protected], [email protected],
[email protected], [email protected], [email protected]
WHERE [email protected]

DeleteCommand=”DELETE Employees WHERE [email protected]

ConnectionString=”<%$ ConnectionStrings:NorthwindConnection %>”
OnInserted=”EmployeeDetailsSqlDataSource_OnInserted”
RunAt=”server”>

<SelectParameters>
<asp:ControlParameter ControlID=”EmployeesDropDownList” PropertyName=”SelectedValue”
Name=”EmpID” Type=”Int32″ DefaultValue=”0″ />
</SelectParameters>

<InsertParameters>
<asp:Parameter Name=”LastName”   Type=”String” />
<asp:Parameter Name=”FirstName”  Type=”String” />
<asp:Parameter Name=”Address”    Type=”String” />
<asp:Parameter Name=”City”       Type=”String” />
<asp:Parameter Name=”Region”     Type=”String” />
<asp:Parameter Name=”PostalCode” Type=”String” />
<asp:Parameter Name=”EmpID” Direction=”Output” Type=”Int32″ DefaultValue=”0″ />
</InsertParameters>

<UpdateParameters>
<asp:Parameter Name=”LastName”   Type=”String” />
<asp:Parameter Name=”FirstName”  Type=”String” />
<asp:Parameter Name=”Address”    Type=”String” />
<asp:Parameter Name=”City”       Type=”String” />
<asp:Parameter Name=”Region”     Type=”String” />
<asp:Parameter Name=”PostalCode” Type=”String” />
<asp:Parameter Name=”EmployeeID” Type=”Int32″ DefaultValue=”0″ />
</UpdateParameters>

<DeleteParameters>
<asp:Parameter Name=”EmployeeID” Type=”Int32″ DefaultValue=”0″ />
</DeleteParameters>

</asp:SqlDataSource>
</form>
</body>
</html>

–MSDN

Get industry recognized certification – Contact us

Menu