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>