ASP.NET - 对 SqlDataSource 控件使用参数

 

      通常，SQL 语句和存储过程包括运行时计算的参数。使用参数编写的 SQL 语句称作参数化 SQL 语句。

使用 SqlDataSource 控件时，可以指定使用参数的 SQL 查询和语句。通过根据运行时计算的值读写数据库信息，这有助于提升数据绑定环境的灵活性。可以从各种源获取参数值。这些源包括 ASP.NET 应用程序变量、用户标识和用户选择的值。可以使用参数执行下列操作：提供用于数据检索的搜索条件；提供要在数据存储区中插入、更新或删除的值；提供用于排序、分页和筛选的值。

 

一、使用参数

同所有的数据源控件一样，SqlDataSource 控件在运行时接受输入参数，并在参数集合中对参数进行管理。每一项数据操作都有一个相关的参数集合。对于选择操作，可以使用 SelectParameters 集合；对于更新操作，可以使用 UpdateParameters 集合，依此类推。

可以为每个参数指定名称、类型、方向和默认值。对于从特定对象（例如，控件、会话变量或用户配置文件）获取值的参数，需要设置其他属性。例如，ControlParameter 要求设置 ControlID 以标识要从中获取参数值的控件，以及设置 PropertyName 属性以指定包含参数值的属性。

另外，SqlDataSource 控件将基于支持自动更新、插入和删除操作的数据绑定控件（例如 GridView 或 FormView 控件）所传递的值自动创建参数。

 

二、在命令中指定参数

使用 SqlDataSource 控件时，可以将该控件的命令属性设置为参数化 SQL 语句或存储过程的名称。如果指定命令的存储过程，必须将该命令的命令类型指定为 StoredProcedure。

2.1、参数名称

‏      SqlDataSource 控件可以将 ParameterPrefix 属性的值添加到所有参数名称的开头。‎（默认的前缀为“@”。）

如果 GridView 控件等数据绑定控件绑定到 SqlDataSource 控件，在执行更新或删除操作期间，该数据绑定控件会同时将当前记录值和原始记录值传递到 SqlDataSource 控件。当前值传递到 Values 字典中。原始值传递到 Keys 或 OldValues 字典中。对于给定的数据操作，这些字典的内容将会追加到基础 DbCommand 对象的 Parameters 集合中。

在 SqlDataSource 控件的 SQL 命令中，使用命名约定使参数占位符与传递到该命令的原来的值相匹配。通过设置 SqlDataSource 控件的 OldValuesParameterFormatString 属性，可以建立该占位符名称的格式。将 OldValuesParameterFormatString 属性设置为一个字符串。该字符串包含的“{0}”是该字段名称的占位符。例如，如果将 OldValuesParameterFormatString 属性设置为“old_{0}”，原始值参数的名称将会解析为前缀为“@old_”的字段名称。考虑对名为 LastModifiedDate 的字段执行更新操作。该字段的当前值传递到 Values 字典中，而该字段的原始值传递到 OldValues 字典中。此时，将会创建名为 @LastModifiedDate 参数，用来传递当前值，同时会创建名为 @old_LastModifiedDate 参数，用来传递原始值。此后，可以将这两个参数都包括在 SQL 语句中，以便区别该字段的当前值和原始值，如以下示例所示：

UPDATE Table1 SET LastModifiedDate = @LastModifiedDate

  WHERE Key = @Key AND LastModifiedDate = @old_LastModifiedDate

      执行开放式并发检查或使用可以修改主键的数据源时，必须可以分隔命令中的当前值和原始值。

 

2.2、对 SqlClient 提供程序使用参数

默认情况下，SqlDataSource 控件通过 System.Data.SqlClient 数据提供程序将 SQL Server 用作数据源。System.Data.SqlClient 提供程序支持作为占位符的命名参数，如以下示例所示：

SELECT * FROM Employees WHERE LastName = @LastName

  AND FirstName = @FirstName

      使用命名参数，在命令的参数集合中指定参数的顺序并不重要。但是，必须确保在 SQL 命令中使用的参数名称与相关集合中的参数名称相对应。

下面的示例演示如何将 SQL 命令中的命名参数用于使用 System.Data.SqlClient 提供程序的 SqlDataSource 控件。

<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource"

  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

 

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName);

                 SELECT @EmpID = SCOPE_IDENTITY()"

  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName

                   WHERE EmployeeID=@EmployeeID"

  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

 

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"

  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"

  RunAt="server">

 

  <SelectParameters>

    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />

  </SelectParameters>

 

  <InsertParameters>

    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />

  </InsertParameters>

 

</asp:sqlDataSource>

 

2.3、对 OleDb 和 Odbc 提供程序使用参数

如果要连接到 OLE 数据库或 ODBC 数据源，可以对 SqlDataSource 控件进行配置，使其通过 System.Data.OleDb 或 System.Data.Odbc 提供程序分别使用数据源。System.Data.OleDb 和 System.Data.Odbc 提供程序只支持由“?”字符标识的定位参数，如以下示例所示：

SELECT * FROM Employees WHERE LastName = ? AND FirstName = ?

      将 System.Data.OleDb 和 System.Data.Odbc 提供程序与参数化 SQL 语句结合使用时，参数占位符的指定顺序必须与相关参数集合中的参数顺序匹配。可以控制参数的顺序，方法是在执行相关数据操作的集合（如相关 UpdateCommand 的 UpdateParameters 集合）中显式指定这些参数。为通过数据绑定控件传递的值自动创建的参数显式创建参数集合时，显式创建的参数将会改写自动生成的所有参数。这样，可以确保参数按照所需的顺序进行传递。如果调用了可返回值的存储过程，则必须将 Direction 值为 ReturnValue 的参数指定为命令参数集合中的第一个参数。

      说明： 默认情况下，数据绑定控件中基于绑定字段的参数将按照下列顺序从参数字典添加到命令中：Values、Keys 和 OldValues。对于删除操作，只使用 Keys 字典。对于插入操作，只使用 Values 字典。

      下面的示例演示如何为使用 System.Data.OleDb 提供程序的 SqlDataSource 控件指定参数。为了确保集合中的参数顺序与 SQL 语句中占位符的顺序匹配，可以显式指定这些参数。

<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:TemplateField HeaderText="Birth Date">

    <ItemTemplate>

      <asp:Label ID="BirthDateLabel" Runat="Server"

                 Text='<%# Eval("BirthDate", "{0:d}") %>' />

    </ItemTemplate>

    <InsertItemTemplate>

      <asp:Calendar ID="InsertBirthDateCalendar" Runat="Server"

                    SelectedDate='<%# Bind("BirthDate") %>' />

    </InsertItemTemplate>

    <EditItemTemplate>

      <asp:Calendar ID="EditBirthDateCalendar" Runat="Server"

                    VisibleDate='<%# Eval("BirthDate") %>'

                    SelectedDate='<%# Bind("BirthDate") %>' />

    </EditItemTemplate>

  </asp:TemplateField>                   

</Fields>