使用Command執行儲存過程 (轉)

使用Command執行儲存過程 (轉)[@more@]

 :namespace prefix = o ns = "urn:schemas--com::office" />

  Developer's Gu

使用Command過程

 

在資料的應用中，儲存過程可以提供很多優點。使用儲存過程可以將操作封裝到一條簡單的命令中，可以效能，可以增強性。只需要按照語法在儲存過程名後面跟帶引數就可以該儲存過程。使用中Command的Parameters屬性你還可以明確的定義和使用output引數和return值。

呼叫儲存過程時，要設定Command物件的CommandType屬性為StoreProcedure。一旦設定了CommandType屬性為StoreProcedure，你就可以使用Parameters集合來定義引數，就像下面的例子。

注意 使用OcCommand呼叫儲存過程時要求提供完整的ODBC CALL語法。

SqlClient

[Visual Basic]

Dim nwindConn As SqlConnection = New SqlConnection("Data =localhost;Integrated Security=SSPI;" & _

   "Initial Catalog=northwind")

 

Dim salesCMD As SqlCommand = New SqlCommand("SalesByCategory", nwindConn)

salesCMD.CommandType = CommandType.StoredProcedure

 

Dim myPaAs SqlParameter = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15)

myParm.Value = "Beverages"

 

nwindConn.Open()

 

Dim myReader As SqlDataReader = salesCMD.ExecuteReader()

 

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

 

Do While myReader.Read()

  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1))

L

 

myReader.Close()

nwindConn.Close()

[]

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

 

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);

salesCMD.CommandType = CommandType.StoredProcedure;

 

SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);

myParm.Value = "Beverages";

 

nwindConn.Open();

 

SqlDataReader myReader = salesCMD.ExecuteReader();

 

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

 

while (myReader.Read())

{

  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));

}

 

myReader.Close();

nwindConn.Close();

OleDb

[Visual Basic]

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;" & _

  "Initial Catalog=northwind")

 

Dim salesCMD As OleDbCommand = New OleDbCommand("SalesByCategory", nwindConn)

salesCMD.CommandType = CommandType.StoredProcedure

 

Dim myParm As OleDbParameter = salesCMD.Parameters.Add("@CategoryName", OleDbType.VarChar, 15)

myParm.Value = "Beverages"

 

nwindConn.Open()

 

Dim myReader As OleDbDataReader = salesCMD.ExecuteReader()

 

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

 

Do While myReader.Read()

  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1))

Loop

 

myReader.Close()

nwindConn.Close()

[C#]

OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;" +

  "Initial Catalog=northwind");

 

OleDbCommand salesCMD = new OleDbCommand("SalesByCategory", nwindConn);

salesCMD.CommandType = CommandType.StoredProcedure;

 

OleDbParameter myParm = salesCMD.Parameters.Add("@CategoryName", OleDbType.VarChar, 15);

myParm.Value = "Beverages";

 

nwindConn.Open();

 

OleDbDataReader myReader = salesCMD.ExecuteReader();

 

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

 

while (myReader.Read())

{

  Console.WriteLine(" {0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));

}

 

myReader.Close();

nwindConn.Close();

Odbc

[Visual Basic]

Dim nwindConn As OdbcConnection = New OdbcConnection("={};Server=localhost;Trusted_Connection=yes;" & _

  "Database=northwind")

nwindConn.Open()

 

Dim salesCMD As OdbcCommand = New OdbcCommand("{ CALL SalesByCategory(?) }", nwindConn)

salesCMD.CommandType = CommandType.StoredProcedure

 

Dim myParm As OdbcParameter = salesCMD.Parameters.Add("@CategoryName", OdbcType.VarChar, 15)

myParm.Value = "Beverages"

 

Dim myReader As OdbcDataReader = salesCMD.ExecuteReader()

 

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

 

Do While myReader.Read()

  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1))

Loop

 

myReader.Close()

nwindConn.Close()

[C#]

OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;Trusted_Connection=yes;" +

  "Database=northwind");

nwindConn.Open();

 

OdbcCommand salesCMD = new OdbcCommand("{ CALL SalesByCategory(?) }", nwindConn);

salesCMD.CommandType = CommandType.StoredProcedure;

 

OdbcParameter myParm = salesCMD.Parameters.Add("@CategoryName", OdbcType.VarChar, 15);

myParm.Value = "Beverages";

 

OdbcDataReader myReader = salesCMD.ExecuteReader();

 

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

 

while (myReader.Read())

{

  Console.WriteLine(" {0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));

}

 

myReader.Close();

nwindConn.Close();

一個Parameter物件可以使用Parameter構造器建立，也可以透過呼叫Command物件的Parameters集合的Add方法建立。Parameters.Add方法的輸入引數可以和構造器相同，也可以使用一個存在的Parameter物件。用System.DBNull.Value設定Parameter的值為空。

如果要設定Parameter為非一般的輸入引數時，必須設定ParameterDirection屬性為InputOutput，Output，或者ReturnValue。下面的例子演示了建立Input, Output, 和 ReturnValue引數的差別。

SqlClient

[Visual Basic]

Dim sampleCMD As SqlCommand = New SqlCommand("SampleProc", nwindConn)

sampleCMD.CommandType = CommandType.StoredProcedure

 

Dim sampParm As SqlParameter = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)

sampParm.Direction = ParameterDirection.ReturnValue

 

sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12)

sampParm.Value = "Sample Value"

 

sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28)

sampParm.Direction = ParameterDirection.Output

 

nwindConn.Open()

 

Dim sampReader As SqlDataReader = sampleCMD.ExecuteReader()

 

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

 

Do While sampReader.Read()

  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))

Loop

 

sampReader.Close()

nwindConn.Close()

 

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

Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters("RETURN_VALUE").Value)

[C#]

SqlCommand sampleCMD = new SqlCommand("SampleProc", nwindConn);

sampleCMD.CommandType = CommandType.StoredProcedure;

 

SqlParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int);

sampParm.Direction = ParameterDirection.ReturnValue;

 

sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12);

sampParm.Value = "Sample Value";

 

sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28);

sampParm.Direction = ParameterDirection.Output;

 

nwindConn.Open();

 

SqlDataReader sampReader = sampleCMD.ExecuteReader();

 

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

 

while (sampReader.Read())

{

  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));

}

 

sampReader.Close();

nwindConn.Close();

 

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

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

OleDb

[Visual Basic]

Dim sampleCMD As OleDbCommand = New OleDbCommand("SampleProc", nwindConn)

sampleCMD.CommandType = CommandType.StoredProcedure

 

Dim sampParm As OleDbParameter = sampleCMD.Parameters.Add("RETURN_VALUE", OleDbType.Integer)

sampParm.Direction = ParameterDirection.ReturnValue

 

sampParm = sampleCMD.Parameters.Add("@InputParm", OleDbType.VarChar, 12)

sampParm.Value = "Sample Value"

 

sampParm = sampleCMD.Parameters.Add("@OutputParm", OleDbType.VarChar, 28)

sampParm.Direction = ParameterDirection.Output

 

nwindConn.Open()

 

Dim sampReader As OleDbDataReader = sampleCMD.ExecuteReader()

 

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

 

Do While sampReader.Read()

  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))

Loop

 

sampReader.Close()

nwindConn.Close()

 

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

Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters("RETURN_VALUE").Value)

[C#]

OleDbCommand sampleCMD = new OleDbCommand("SampleProc", nwindConn);

sampleCMD.CommandType = CommandType.StoredProcedure;

 

OleDbParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", OleDbType.Integer);

sampParm.Direction = ParameterDirection.ReturnValue;

 

sampParm = sampleCMD.Parameters.Add("@InputParm", OleDbType.VarChar, 12);

sampParm.Value = "Sample Value";

 

sampParm = sampleCMD.Parameters.Add("@OutputParm", OleDbType.VarChar, 28);

sampParm.Direction = ParameterDirection.Output;

 

nwindConn.Open();

 

OleDbDataReader sampReader = sampleCMD.ExecuteReader();

 

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

 

while (sampReader.Read())

{

  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));

}

 

sampReader.Close();

nwindConn.Close();

 

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

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

Odbc

[Visual Basic]

Dim sampleCMD As OdbcCommand = New OdbcCommand("{ ? = CALL SampleProc(?, ?) }", nwindConn)

sampleCMD.CommandType = CommandType.StoredProcedure

 

Dim sampParm As OdbcParameter = sampleCMD.Parameters.Add("RETURN_VALUE", OdbcType.Int)

sampParm.Direction = ParameterDirection.ReturnValue

 

sampParm = sampleCMD.Parameters.Add("@InputParm", OdbcType.VarChar, 12)

sampParm.Value = "Sample Value"

 

sampParm = sampleCMD.Parameters.Add("@OutputParm", OdbcType.VarChar, 28)

sampParm.Direction = ParameterDirection.Output

 

nwindConn.Open()

 

Dim sampReader As OdbcDataReader = sampleCMD.ExecuteReader()

 

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

 

Do While sampReader.Read()

  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))

Loop

 

sampReader.Close()

nwindConn.Close()

 

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

Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters("RETURN_VALUE").Value)

[C#]

OdbcCommand sampleCMD = new OdbcCommand("{ ? = CALL SampleProc(?, ?) }", nwindConn);

sampleCMD.CommandType = CommandType.StoredProcedure;

 

OdbcParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", OdbcType.Int);

sampParm.Direction = ParameterDirection.ReturnValue;

 

sampParm = sampleCMD.Parameters.Add("@InputParm", OdbcType.VarChar, 12);

sampParm.Value = "Sample Value";

 

sampParm = sampleCMD.Parameters.Add("@OutputParm", OdbcType.VarChar, 28);

sampParm.Direction = ParameterDirection.Output;

 

nwindConn.Open();

 

OdbcDataReader sampReader = sampleCMD.ExecuteReader();

 

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

 

while (sampReader.Read())

{

  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));

}

 

sampReader.Close();

nwindConn.Close();

 

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

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

在SqlCommand中使用引數

在SqlCommand中使用引數時，引數的名字必須和儲存過程中相應引數的名字匹配。SQL SERVER的.NET Framework Data Provider將儲存過程中的引數視為命名引數並且搜尋與之匹配的引數標記。

SQL SERVER的.NET Framework Data Provider不支援使用問號標記（?）作為佔位符來向SQL語句或者儲存過程傳遞引數。既然如此，你必須命名引數，就下面一樣：

* FROM Customers WHERE CustomerID = @CustomerID

在OleDbCommand 和OdbcCommand中使用引數

在OleDbCommand或者OdbcCommand中使用引數時，引數新增到Parameters集合中的順序必須和儲存過程中引數定義的順序匹配。OLE DB和ODBC的.NET Framework資料供應程式將儲存過程的引數視為佔位符，按照順序來給引數賦值。另外，返回引數必須是第一個被加入到Parameters集合中的引數。

OLE DB和ODBC的.NET Framework資料供應程式不提供使用命名引數來向SQL語句或儲存過程傳遞引數。為此，你必須使用問號(?)佔位符，就像下面一樣：

SELECT * FROM Customers WHERE CustomerID = ?

因此，向 Parameters 集合新增 Parameter 物件的順序必須直接對應於該引數的問號佔位符的位置。

匯出引數資訊

引數也可以使用 CommandBuilder 類從儲存過程匯出。SqlCommandBuilder 和 OleDbCommandBuilder 類都提供了靜態方法 DeriveParameters，該靜態方法將自動使用儲存過程中的引數資訊填充 Command 物件的 Parameters 集合。請注意，DeriveParameters 將改寫 Command 的任何現有引數資訊。

匯出引數資訊時需要經歷一個到資料來源的附加行程，以獲取引數資訊。如果引數資訊在設計時是已知的，則可以透過顯式設定引數來提高應用程式的。

以下程式碼示例顯示如何使用 CommandBuilder.DeriveParameters 來填充 Command 物件的 Parameters 集合。

[Visual Basic]

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;")

Dim salesCMD As SqlCommand = New SqlCommand("Sales By Year", nwindConn)

salesCMD.CommandType = CommandType.StoredProcedure

 

nwindConn.Open()

SqlCommandBuilder.DeriveParameters(salesCMD)

nwindConn.Close()

[C#]

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");

SqlCommand salesCMD = new SqlCommand("Sales By Year", nwindConn);

salesCMD.CommandType = CommandType.StoredProcedure;

 

nwindConn.Open();

SqlCommandBuilder.DeriveParameters(salesCMD);

nwindConn.Close();