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.

  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees
                 WHERE EmployeeID = @empId">
 
   
 


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

    autopostback="True">
  Sales Representative
  Sales Manager
  Vice President, Sales

  ConnectionString="<%$ ConnectionStrings:Northwind%>"
  SelectCommand="SELECT LastName FROM Employees WHERE Title = @Title">
 
          ControlID="DropDownList1"
      PropertyName="SelectedValue"/>
 




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#" %>

    "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



 
   


   

     

Northwind Employees

       

         
           

                            
                      
       

                              DataSourceID="EmployeesSqlDataSource"                 DataValueField="EmployeeID"                 DataTextField="FullName"                 AutoPostBack="True"                 OnSelectedIndexChanged="EmployeesDropDownList_OnSelectedIndexChanged"                 RunAt="Server" />                       Â

                                               DataSourceID="EmployeeDetailsSqlDataSource"                 AutoGenerateRows="false"                 AutoGenerateInsertbutton="true"                 AutoGenerateEditbutton="true"                 AutoGenerateDeletebutton="true"                 DataKeyNames="EmployeeID"                      Gridlines="Both"                 OnItemUpdated="EmployeeDetailsView_ItemUpdated"                 OnItemDeleted="EmployeeDetailsView_ItemDeleted"                       RunAt="server">                                   forecolor="White"/>                                                                                                                                                                                                                                                                                                                                                                                            Â

                  SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees"
          Connectionstring="<%$ ConnectionStrings:NorthwindConnection %>"
          RunAt="server">
       


                  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 LastName=@LastName, FirstName=@FirstName, Address=@Address,
                           City=@City, Region=@Region, PostalCode=@PostalCode
                         WHERE EmployeeID=@EmployeeID"

          DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

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

         
                                              Name="EmpID" Type="Int32" DefaultValue="0" />
         

         
           
           
           
           
           
           
           
         

         
           
           
           
           
           
           
           
         

         
           
         

       
     

 



 

--MSDN