共用方式為


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

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

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

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

提供 ParameterDirection 屬性

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

成員名稱

描述

Input

這是輸入參數, 此為預設值。

InputOutput

這個參數可執行輸入和輸出。

Output

這是輸出參數。

ReturnValue

此參數代表預存程序 (Stored Procedure)、內建函式或使用者定義函式等作業的傳回值。

使用參數預留位置

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

資料提供者

參數命名語法

System.Data.SqlClient

以格式 @parametername 使用具名參數。

System.Data.OleDb

使用由問號 (?) 表示的位置參數標記。

System.Data.Odbc

使用由問號 (?) 表示的位置參數標記。

System.Data.OracleClient

以格式 :parmname (或 parmname) 使用具名參數。

指定參數的資料型別

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

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

.NET Framework 型別

DbType

SqlDbType

OleDbType

OdbcType

OracleType

bool

Boolean

Bit

Boolean

Bit

Byte

byte

Byte

TinyInt

UnsignedTinyInt

TinyInt

Byte

byte[]

Binary

VarBinary。如果位元組陣列超過 VarBinary 的最大大小 (8000 個位元組),則這項隱含轉換將會失敗。若要使用超過 8000 個位元組的位元組陣列,請明確設定 SqlDbType

VarBinary

Binary

Raw

char

 

不支援從 char 推斷 SqlDbType

Char

Char

Byte

DateTime

DateTime

DateTime

DBTimeStamp

DateTime

DateTime

DateTimeOffset

DateTimeOffset

SQL Server 2008 中的 DateTimeOffset。 SQL Server 2008 之前的 SQL Server 版本不支援從 DateTimeOffset 推斷 SqlDbType

DateTime

Decimal

Decimal

Decimal

Decimal

Numeric

Number

double

Double

Float

Double

Double

Double

float

Single

Real

Single

Real

Float

Guid

Guid

UniqueIdentifier

Guid

UniqueIdentifier

Raw

Int16

Int16

SmallInt

SmallInt

SmallInt

Int16

Int32

Int32

Int

Int

Int

Int32

Int64

Int64

BigInt

BigInt

BigInt

Number

object

Object

Variant

Variant

不支援從 Object 推斷 OdbcType。

Blob

string

String

NVarChar。 如果字串超過 NVarChar 的最大大小 (4000 個字元),則這項隱含轉換將會失敗。 若要使用超過 4000 個字元的字串,請明確設定 SqlDbType

VarWChar

NVarChar

NVarChar

TimeSpan

Time

SQL Server 2008 中的 Time。 SQL Server 2008 之前的 SQL Server 版本不支援從 TimeSpan 推斷 SqlDbType

DBTime

Time

DateTime

UInt16

UInt16

不支援從 UInt16 SqlDbType 推斷。

UnsignedSmallInt

Int

UInt16

UInt32

UInt32

不支援從 UInt32 推斷SqlDbType

UnsignedInt

BigInt

UInt32

UInt64

UInt64

不支援從 UInt64 推斷 SqlDbType

UnsignedBigInt

Numeric

Number

 

AnsiString

VarChar

VarChar

VarChar

VarChar

 

AnsiStringFixedLength

Char

Char

Char

Char

 

Currency

Money

Currency

不支援從 Currency 推斷 OdbcType。

Number

 

Date

SQL Server 2008 中的 Date。 SQL Server 2008 之前的 SQL Server 版本不支援從 Date 推斷 SqlDbType

DBDate

Date

DateTime

 

SByte

不支援從 SByte 推斷 SqlDbType

TinyInt

不支援從 SByte 推斷 OdbcType。

SByte

 

StringFixedLength

NChar

WChar

NChar

NChar

 

Time

SQL Server 2008 中的 Time。 SQL Server 2008 之前的 SQL Server 版本不支援從 Time 推斷 SqlDbType

DBTime

Time

DateTime

 

VarNumeric

不支援從 VarNumeric 推斷 SqlDbType

VarNumeric

不支援從 VarNumeric 推斷 OdbcType。

Number

注意事項注意事項

將十進位值轉換為其他型別的過程稱為窄化轉換,此類轉換會將十進位值向零的方向取整數。如果目的型別無法代表此項轉換的結果,則會擲回 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,這樣當程序結束時就會清除連接。 SqlCommandSqlParameter 物件會建立,其屬性也會設定。 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

將參數與 OleDbCommandOdbcCommand 搭配使用時,加入至 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 中的資料型別對應