設定參數和參數資料型別 (ADO.NET)

更新： November 2007

命令物件會使用參數將值傳遞至 SQL 陳述式或預存程序 (Stored Procedure)，以提供型別檢查及驗證。與命令文字不同的是，參數輸入會被視為常值 (Literal)，而非可執行程式碼。這有助於防衛「SQL 插入式」攻擊，在此類攻擊中，攻擊者會將危害伺服器安全的命令插入 SQL 陳述式中。

參數型命令 (Parameterized Command) 也可以改善查詢執行效能，因為它們可以協助資料庫伺服器正確地比對內送命令與正確快取的查詢計畫。如需詳細資訊，請參閱《SQL Server 線上叢書》中的＜執行計畫快取與重複使用＞和＜參數和執行計畫的重複使用＞。除了安全性和效能的優點以外，參數型命令也提供方便的方法，可讓您安排傳遞至資料來源的值。

DbParameter 物件可透過其建構函式建立，或者透過呼叫 DbParameterCollection 集合的 Add 方法，將其加入 DbParameterCollection 來建立。Add 方法會將建構函式引數或現有的參數物件當做輸出，依資料提供者而定。

提供 ParameterDirection 屬性

在加入參數時，您必須為不是輸入參數的參數提供 ParameterDirection 屬性。下表所顯示的 ParameterDirection 值是可以與 ParameterDirection 列舉一起使用的。

使用參數預留位置

參數預留位置的語法會隨資料來源而有所不同。.NET Framework 資料提供者對於命名和指定參數及參數預留位置的處理方式各有不同。這個語法是特定資料來源專用的，如以下資料表所述：

指定參數的資料型別

參數的資料型別是 .NET Framework 資料提供者特有的。指定型別會使 Parameter 的值在傳遞給資料來源前，先轉換成 .NET Framework 資料提供者型別。您也可以使用一般方式指定 Parameter 的型別，方法是將 Parameter 物件的 DbType 屬性設為特定的 DbType。

Parameter 物件的 .NET Framework 資料提供者型別是從 Parameter 物件之 Value 的 .NET Framework 型別，或是 Parameter 物件的 DbType 推斷而來。下列表格顯示根據當做 Parameter 值或指定之 DbType 來傳遞的物件而推斷出的 Parameter 型別。

注意事項：
將十進位值轉換為其他型別的過程稱為窄化轉換，此類轉換會將十進位值向零的方向取整數。如果目的型別無法代表此項轉換的結果，則會擲回 OverflowException。

注意事項：
當傳送 null 參數值到伺服器時，您必須指定 DBNull，而不是 null (在 Visual Basic 中則為 Nothing)。系統中的 Null 值是沒有任何值的空物件。DBNull 會用於代表 Null 值。如需資料庫 null 值的詳細資訊，請參閱處理 Null 值 (ADO.NET)。

衍生參數資訊

您也可以使用 DbCommandBuilder 類別 (Class) 從預存程序衍生參數。SqlCommandBuilder 和 OleDbCommandBuilder 類別都能提供靜態方法 (DeriveParameters)，該方法會在使用預存程序之參數資訊的命令物件，自動填入參數集合。請注意，DeriveParameters 將會覆寫命令所有的現有參數資訊。

注意事項：
衍生參數資訊會造成效能降低，因為這項作業需要對資料來源進行額外的來回行程才能擷取資訊。若在設計階段已知參數資訊，您便可以明確設定參數，改善應用程式的效能。

如需詳細資訊，請參閱使用 CommandBuilders 產生命令 (ADO.NET)。

使用參數配合 SqlCommand 和預存程序

預存程序對資料驅動應用程式有許多好處。藉由使用預存程序，資料庫作業可以封裝在單一命令中、最佳化為最佳效能，並且可進一步提升安全性。雖然只要將後接參數引數的預存程序名稱當成 SQL 陳述式傳遞即可呼叫預存程序，但是使用 ADO.NET DbCommand 物件的 Parameters 集合，可以讓您更明確地定義預存程序參數，以及存取輸出參數和傳回值。

注意事項：
參數化陳述式能在伺服器上執行，都是透過允許查詢計畫重複使用的 sp_executesql,。呼叫 sp_executesql 的批次無法見到 sp_executesql 批次中的本機資料指標或變數。資料庫內容中的變更只會持續到 sp_executesql 陳述式結束。如需詳細資訊，請參閱《SQL Server 線上叢書》。

將參數與 SqlCommand 搭配使用以執行 SQL Server 預存程序時，加入 Parameters 集合的參數名稱必須與預存程序中的參數標記名稱相符。.NET Framework Data Provider for SQL Server 不支援使用問號 (?)預留位置將參數傳遞給 SQL 陳述式或預存程序。它會將預存程式中的參數視為具名參數，並搜尋相符的參數標記。例如，CustOrderHist 預存程序是使用名為 @CustomerID 的參數所定義的。則當您的程式碼執行預存程序時，也必須使用名為 @CustomerID 的參數。

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

範例

此範例會示範如何在 Northwind 範例資料庫中呼叫 SQL Server 預存程序。預存程序的名稱為 dbo.SalesByCategory，而且具有名為 @CategoryName 的輸入參數 (資料型別為 nvarchar(15))。此程式碼會在 Using 區塊中建立新的 SqlConnection，這樣當程序結束時就會清除連接。SqlCommand 和 SqlParameter 物件會建立，其屬性也會設定。SqlDataReader 會執行 SqlCommand 並從預存程序傳回結果集，在主控台視窗中顯示輸出。

注意事項：
與其建立 SqlCommand 和 SqlParameter 物件，然後再以個別的陳述式設定屬性，您可以選擇使用其中一個多載建構函式 (Constructor)，以單一的陳述式設定多個屬性。

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub

static void GetSalesByCategory(string connectionString, 
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection. 
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

使用參數配合 OleDbCommand 或 OdbcCommand

將參數與 OleDbCommand 或 OdbcCommand 搭配使用時，加入至 Parameters 集合的參數順序必須與預存程序中所定義的參數順序相符。.NET Framework Data Provider for OLE DB 和 .NET Framework Data Provider for ODBC 會將預存程序內的參數視為預留位置，並依順序套用參數值。此外，傳回值參數必須是第一個加入至 Parameters 集合的參數。

.NET Framework Data Provider for OLE DB 和 .NET Framework Data Provider for ODBC 資料提供者不支援以具名參數來傳遞參數至 SQL 陳述式或預存程序。這種情況下，您必須使用問號 (?) 預留位置，如下列範例所示。

SELECT * FROM Customers WHERE CustomerID = ?

所以，Parameter 物件加入至 Parameters 集合的順序，必須直接對應至參數的 ? 預留位置。

OleDb 範例

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

OleDbParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Odbc 範例

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

請參閱

概念

DataAdapter 參數 (ADO.NET)

其他資源

命令和參數 (ADO.NET)

ADO.NET 中的資料型別對應