使用預存程序配合命令

  • 發行項

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

若要呼叫預存程序，請將 Command 物件的 CommandType 設為 StoredProcedure。一旦 CommandType 設定為 StoredProcedure 後，您就可以使用 Parameters 集合來定義參數，如下列範例所示。

Note注意事項

OdbcCommand 要求您在呼叫預存程序時提供完整的 ODBC CALL 語法。

範例

' Assumes that connection is a valid SqlConnection object.

Dim salesCommand As SqlCommand = New SqlCommand( _
  "SalesByCategory", connection)
salesCommand.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = salesCommand.Parameters.Add( _
  "@CategoryName", SqlDbType.NVarChar, 15)
parameter.Value = "Beverages"

connection.Open()

Dim reader As SqlDataReader = salesCommand.ExecuteReader()

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine("{0}, ${1}", reader.GetString(0), reader.GetDecimal(1))
Loop

reader.Close()
connection.Close()

// Assumes that connection is a valid SqlConnection object.

SqlCommand salesCommand = new SqlCommand("SalesByCategory",
  connection);
salesCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = salesCommand.Parameters.Add(
  "@CategoryName", SqlDbType.NVarChar, 15);
parameter.Value = "Beverages";

connection.Open();

SqlDataReader reader = salesCommand.ExecuteReader();

Console.WriteLine(
  "{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine("{0}, ${1}", reader.GetString(0), 
    reader.GetDecimal(1));
}

reader.Close();
connection.Close();

Parameter 物件可透過 Parameter 建構函式來建立，也可由呼叫 Command 所屬的 Parameters 集合的 Add 方法來建立。Parameters.Add 會把建構函式引數或現有 Parameter 物件當成輸出。要將 Parameter 的 Value 設定為 Null 參考時，請使用 DBNull.Value。

除了 Input 參數以外的參數，您必須設定 ParameterDirection 屬性，將參數型別指定為 InputOutput、Output 或 ReturnValue。下列範例示範為各種提供者建立 Input、Output 和 ReturnValue 參數間的不同。

SqlClient 範例

' Assumes that connection is a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand("SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = command.Parameters.Add( _
  "RETURN_VALUE", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue

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

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

connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

Console.WriteLine( _
  "{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine( _
    "{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop

reader.Close()
connection.Close()

Console.WriteLine( _
  " @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine( _
  "RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)

// Assumes that connection is a valid SqlConnection object.
SqlCommand command = new SqlCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

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

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

connection.Open();

SqlDataReader reader = command.ExecuteReader();

Console.WriteLine(
  "{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine(
    "{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}

reader.Close();
connection.Close();

Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

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

connection.Open()

Dim reader As OleDbDataReader = command.ExecuteReader()

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop

reader.Close()
connection.Close()

Console.WriteLine(" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)

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;

connection.Open();

OleDbDataReader reader = command.ExecuteReader();

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}

reader.Close();
connection.Close();

Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

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

connection.Open()

Dim reader As OdbcDataReader = command.ExecuteReader()

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop

reader.Close()
connection.Close()

Console.WriteLine(" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)

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;

connection.Open();

OdbcDataReader reader = command.ExecuteReader();

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine( _
    "{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}

reader.Close();
connection.Close();

Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

使用參數配合 SqlCommand

將參數與 SqlCommand 搭配使用時，加入 Parameters 集合的參數名稱必須與預存程序中的參數標記名稱相符。SQL Server 的 .NET Framework 資料提供者會將預存程序內的參數視為具名參數，並搜尋相符的參數標記。

SQL Server 的 .NET Framework 資料提供者不支援以問號 (?) 替代符號 (Placeholder) 來傳遞參數至 SQL 陳述式或預存程序。在這種情況下，您必須使用具名參數，如下列範例所示，其中 @CustomerID 是具名參數。

SELECT * FROM Customers WHERE CustomerID = @CustomerID

使用參數配合 OleDbCommand 或 OdbcCommand

將參數與 OleDbCommand 或 OdbcCommand 搭配使用時，加入 Parameters 集合的參數順序必須與預存程序中所定義的參數順序相符。OLE DB 的 .NET Framework 資料提供者和 ODBC 的 .NET Framework 資料提供者會將預存程序內的參數視為替代符號，並依順序套用參數值。此外，傳回值參數必須是第一個加入 Parameters 集合的參數。

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

SELECT * FROM Customers WHERE CustomerID = ?

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

衍生參數資訊

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

衍生參數資訊需要加入至資料來源的存取作業，以取得資料。若在設計階段已知參數資訊，您便可以明確設定參數，改善應用程式的效能。

下列程式碼範例顯示如何使用 CommandBuilder.DeriveParameters 填入 Command 物件的 Parameters 集合。

' Assumes that connection is a valid SqlConnection object.
Dim salesCommand As SqlCommand = New SqlCommand( _
  "Sales By Year", connection)
salesCommand.CommandType = CommandType.StoredProcedure

connection.Open()
SqlCommandBuilder.DeriveParameters(salesCommand)
connection.Close()

// Assumes that connection is a valid SqlConnection object.
SqlCommand salesCommand = new SqlCommand("Sales By Year", connection);
salesCommand.CommandType = CommandType.StoredProcedure;

connection.Open();
SqlCommandBuilder.DeriveParameters(salesCommand);
connection.Close();

請參閱

概念

執行命令

其他資源

使用命令
使用 SQL Server 的 .NET Framework 資料提供者