設定參數和參數資料類型

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

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

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

提供 ParameterDirection 屬性

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

使用參數預留位置

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

指定參數的資料類型

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

Parameter 物件的 .NET Framework Data Provider 類型是從 Parameter 物件 Value 的 .NET Framework 型別，或是 Parameter 物件的 DbType 推斷而來。 下列表格說明根據以 Parameter 值或指定之 Parameter 來傳遞的物件而推斷出的 DbType型別。

衍生參數資訊

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

如需詳細資訊，請參閱使用 CommandBuilder 產生命令。

搭配 SqlCommand 與預存程序使用參數

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

將參數與 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 並從預存程序傳回結果集，在主控台視窗中顯示輸出。

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

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

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

        // Open the connection and execute the reader.
        connection.Open();
        using (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();
        }
    }
}

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()
        Using 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 Using
End Sub

使用參數配合 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 概觀 \(部分機器翻譯\)