資料配接器命令中的參數

發行項
08/21/2008

更新：2007 年 11 月

資料配接器的命令，也就是定義在 SelectCommand、InsertCommand、UpdateCommand 和 DeleteCommand 物件之 CommandText 屬性中的命令，通常都與參數有關。在執行階段時，參數是用來將值傳遞至以命令表示的 SQL 陳述式或預存程序。

注意事項：
在舊版本的 Visual Studio 中，資料配接器用於應用程式和資料庫之間的通訊。雖然資料配接器仍然是 .NET Framework 資料提供者 (ADO.NET)的主要元件，但 TableAdapter 是設計工具產生的元件，它可以簡化在應用程式和資料庫之間的資料移動程序。如需使用 TableAdapter 的詳細資訊，請參閱 TableAdapter 概觀。

使用參數的情況有兩個：

選取參數 - 在實際執行應用程式中，您經常只會擷取資料庫中的資料子集。若要這麼做，您可以使用 SQL 陳述式或預存程序，其中包含 WHERE 子句以及您在執行階段取得當做選取準則的參數。除此之外，當您更新或刪除資料錄時，可以使用能精確找出要變更之資料錄的 WHERE 子句。使用在 WHERE 子句的值通常是在執行階段時衍生的。

更新參數 - 當您更新現有資料錄或插入新的資料錄時，已變更或新增之資料錄中資料行的值會在執行階段時建立。除此之外，還使用參數來建立在開放式並行存取檢查時所使用的值。

注意事項：
對於 Oracle 而言，在 SQL 陳述式或預存程序中使用具名參數時，必須在參數名稱前加上冒號 (:)。然而，當在程式碼的其他地方參考具名參數時 (例如，呼叫 Add 時)，則不可在具名參數前加上冒號 (:)，因為資料提供者會自動加上冒號。如需詳細資訊，請參閱 OracleParameter 類別。

選取參數

當選取資料錄來填滿資料集時，您通常會在 WHERE 子句中包含一個或多個參數，如此就可以在執行階段時指定要擷取的資料錄。例如，使用者可能會在 Web 網頁輸入特定標題關鍵字來搜尋書籍資料庫。若要允許這樣的處理方式，您可能需要指定 SQL 陳述式，就像下列 SelectCommand 的 CommandText 屬性。參數則會利用替代符號 (Placeholder) (問號) 或具名參數變數來表示。與 OleDbCommand 和 OdbcCommand 物件有關的查詢之參數會使用問號；使用 SqlCommand 物件的查詢則是使用以 @ 符號開頭的具名參數，而 OracleCommand 物件則是使用以冒號 (:) 開頭的具名參數。

使用替代符號的查詢看起來可能如下：

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE ?)

使用 SqlCommand 具名參數的查詢看起來可能如下：

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE @title)

使用 OracleCommand 具名參數的查詢看起來可能如下：

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE :title)

在應用程式中，您會提示使用者輸入標題關鍵字，接著設定參數值並執行命令。

注意事項：
有時您可能想要取得整個資料庫資料表的內容，例如可能正在設定查閱資料表 (Lookup Table)，但通常只擷取需要的資料以保持應用程式的效率。

在 Visual Studio 中，您可以使用 [查詢產生器] 來建置 (Build) 具有參數的 SQL 陳述式。如果您從 [伺服器總管] 拖曳項目，Visual Studio 在某些情況下能夠設定參數，但在其他情況下就需要手動完成設定。

更新參數

不論配接器的 SelectCommand 物件是否包含參數型命令，UpdateCommand、InsertCommand 和 DeleteCommand 屬性的命令一定會包含。

UpdateCommand 和 InsertCommand 屬性的命令需要參數，以便使資料庫中的每一個資料行進行更新。此外，UpdateCommand 和 DeleteCommand 陳述式需要參數型 WHERE 子句，以識別要更新的資料錄，這與一般設定 SelectCommand 物件的方式類似。

假設現在有個能讓使用者購買書籍的應用程式。當使用者購物時，他們維護實作為資料表的購物車。在 ShoppingCart 資料表中，使用者維護他們所要購買書籍的資料錄，並將書籍 ID 和客戶 ID 當做是購物車資料錄的索引鍵 (Key)。

當使用者將一本書加入至他們的購物車時，應用程式可能會叫用 SQL INSERT 陳述式。在配接器中，陳述式的語法看起來可能如下：

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (?, ?, ?)

三個問號代表的是參數替代符號，在執行階段時會以客戶 ID、書籍 ID 和數量的值填滿。如果您使用具名參數，則相同的查詢可能看起來如下：

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (@bookid, @custid, @quantity)

如果使用者決定變更購物車中的項目，例如變更數量，則應用程式可能會叫用 SQL UPDATE 陳述式。陳述式的語法可能如下：

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

或者如果您使用具名參數，語法則可能如下：

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

在這個陳述式中，SET 子句裡的參數是填入變更資料錄的更新值。WHERE 子句中的參數會識別要更新的資料錄，然後以資料錄的原始值填滿。

使用者也可能會從購物車中移除項目。在這種情況下，應用程式可能會叫用 SQL DELETE 陳述式，如果您使用參數替代符號，語法則如下所示：

DELETE FROM ShoppingCart
WHERE (BookId = ? AND CustId = ?)

如果使用具名參數，則語法如下：

DELETE FROM ShoppingCart
WHERE (BookId = @bookid AND CustId = @custid)

參數集合和參數物件

為了讓您能夠在執行階段傳遞參數值，資料配接器的四個命令物件都支援 Parameters 屬性。這個屬性包含個別參數物件的集合，其中個別參數物件與陳述式中的替代符號一對一對應。

以下資料表顯示每個資料配接器所對應的參數集合：

資料配接器	參數集合
SqlDataAdapter	SqlParameterCollection
OleDbDataAdapter	OleDbParameterCollection
OdbcDataAdapter	OdbcParameterCollection
OracleDataAdapter	OracleParameterCollection

注意事項：
對於 Oracle 而言，在 SQL 陳述式或預存程序中使用具名參數時，必須在參數名稱前加上冒號 (:)。然而，當在程式碼的其他地方參考具名參數時 (例如，呼叫 Add 時)，則不可在具名參數前加上冒號 (:)，因為 .NET Framework Data Provider for Oracle 會自動加上冒號。

藉由使用參數集合，您省去了手動將 SQL 命令建構為具有執行階段值之字串的麻煩；除此之外，您也可以藉此在參數中使用型別檢查 (Type Checking)。

如果您使用 [資料配接器組態精靈] 來設定配接器，則四個配接器命令的參數集合都會自動完成設定。如果您從 [伺服器總管] 拖曳項目到表單或元件上，Visual Studio 可執行以下設定：

如果您將資料表或一些資料行拖曳至設計工具上，Visual Studio 會產生不含參數的 SelectCommand 物件 (特別是 SQL SELECT 陳述式) 以及參數型 UpdateCommand、InsertCommand 和 DeleteCommand 物件。如果您希望 SelectCommand 物件陳述式具有參數，則必須手動進行設定。
如果您將預存程序拖曳至設計工具上，Visual Studio 會產生 SelectCommand 物件以及預存程序所需的參數。但是，如果您需要 UpdateCommand、InsertCommand 和 DeleteCommand 物件，則必須自行設定該物件及其參數。

一般來說，如果您要建立配接器的參數型查詢，您應使用 [資料配接器組態精靈]。但如果有需要，您可以使用 [屬性] 視窗來手動設定參數。

參數集合的結構

命令參數集合中的項目是與對應命令物件所需的參數一對一對應。如果命令物件是 SQL 陳述式，則集合中的項目會與陳述式中的替代符號 (問號) 對應。下列 UPDATE 陳述式需有五個參數項目的集合：

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

以下是使用具名參數的相同陳述式：

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

如果命令物件參考預存程序，則集合中的參數項目數量是由程序本身決定。參數可能不會正好與 SQL 陳述式中的替代符號對應。

在預存程序中也可以具名參數。在這種情況下，參數在集合中的位置就不是很重要。反而是集合中的每個參數項目都具有 ParameterName 屬性，可用來對應至預存程序中的對應參數。

如果您手動設定參數集合，您必須知道預存程序所需的正確參數為何。許多預存程序都會傳回值，而這個值會傳回到您應用程式的參數集合中，所以您必須考慮到這點。此外，有些預存程序包含多個 SQL 陳述式，而您必須確定參數集合會反映傳遞至程序中所有陳述式的所有值。

如果不具名參數 (如在預存程序中)，則集合中的項目會依位置對應至命令所需的參數。如果命令是預存程序並傳回值，則集合當中的第一個項目 (項目零) 會保留給這個傳回值。

因此您可以藉由集合中的索引位置來參考個別參數物件。但是，參數物件也支援可不依順序參考參數的 ParameterName 屬性。例如，下列兩個陳述式就可能相等 (假設集合中的第二個參數名為 Title_Keyword)：

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters(1).Value = titleKeyword
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword

// Encloses the keyword in SQL wildcard characters.
string titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters[1].Value = titleKeyword;
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;

和利用索引值參考參數相比，使用參數名稱通常是較佳的程式設計應用，因為如此一來您就不需要在參數數目變更時進行維護，也不需要記住預存程序是否會傳回值。雖然以名稱參考參數會比用索引值稍微增加額外負荷，但這卻可從程式設計的便利性以及應用程式的可維護性兩方面來加以彌補。

建立參數值

建立參數值的方式有兩種：

明確設定參數的 Value 屬性。
將參數對應至資料集資料表中的資料行，如此可在需要時從資料列擷取值。

您可在填滿資料集或呼叫命令時，明確設定參數值，也就是選取參數。例如，在以上搜尋書籍的範例中，應用程式可能具有讓使用者輸入標題關鍵字的文字方塊。接著，您會在呼叫配接器的 Fill 方法之前，將參數值明確設定為文字方塊中的文字。這項作業的程式碼可能看起來像是在填滿資料集之前，將文字方塊的內容建立為參數，如下所示：

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword
OleDbDataAdapter1.Fill(dsAuthors1)

// Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;
this.OleDbDataAdapter1.Fill(dsAuthors1);

更新期間會使用對應的參數值。當您呼叫配接器的 Update 方法時，此方法會逐筆檢查資料集資料表中的資料錄，個別為每一筆資料錄進行適當的更新 (更新、插入、刪除)。在這種情況下，參數值可做為資料集資料錄中的資料行。例如，當更新處理序 (Process) 在資料集資料表中找到新的資料錄 (必須呼叫資料庫中 INSERT 陳述式的資料錄) 時，就能夠直接從資料錄讀出 INSERT 陳述式的 VALUE 子句值。

以上是一般案例，但並不是唯一的情況。預存程序有時會使用 out 參數，或透過程序的傳回值來傳回資料。在這種情況下，應將傳回值對應至資料集資料表當中的資料行。

您也可以明確設定更新參數。配接器支援 RowUpdating 事件，每次更新資料列時都會呼叫此事件。您可以建立這個事件的處理常式，並設定參數值。這讓您能夠精確地控制參數值，並允許您在將其寫入資料庫資料錄之前，執行像是動態建立參數值的處理序。