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

發行項
05/20/2011

命令物件會使用參數將值傳遞至 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，這樣當程序結束時就會清除連接。 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 中的資料型別對應

共用方式為

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

提供 ParameterDirection 屬性

使用參數預留位置

指定參數的資料型別

衍生參數資訊

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

範例

使用參數配合 OleDbCommand 或 OdbcCommand

OleDb 範例

Odbc 範例

請參閱

概念

其他資源

其他資源