輸入參數、輸出參數和傳回值

預存程序除了有輸入和輸出參數，還可有傳回值。下列範例使用將新資料錄插入資料表 (該資料表的主索引鍵資料行是自動編號欄位) 的常見案例，示範 ADO.NET 如何傳送和接收輸入參數、輸出參數和傳回值。這個範例使用輸出參數傳回自動編號欄位的 @@Identity，而 DataAdapter 則將它繫結至 DataTable 的資料行，如此 DataSet 即可反映結果主索引鍵的值。

這個範例使用下列預存程序，將新類別插入 Northwind Categories 資料表，該資料表採用 CategoryName 資料行的值當作輸入參數，並從 @@Identity 將自動編號識別欄位 CategoryID 的值當成輸出參數傳回，且具有受到影響資料列的傳回值。

CREATE PROCEDURE InsertCategory
  @CategoryName nchar(15),
  @Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = @@Identity
RETURN @@ROWCOUNT

下列範例將 InsertCategory 預存程序當作 DataAdapter 中 InsertCommand 的來源。指定 CategoryID 資料行做為 @Identity 輸出參數的 SourceColumn，產生的 Autonumber 值便會在呼叫 DataAdapter 的 Update 方法時，於記錄已插入至資料庫後在 DataSet 中反映。

至於 OleDbDataAdapter，ParameterDirection 為 ReturnValue 的參數必須在其他參數前先行指定。

SqlClient

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

Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)

catDA.InsertCommand = New SqlCommand("InsertCategory" , nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure

Dim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValue

catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName")

myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output

Dim catDS As DataSet = New DataSet()
catDA.Fill(catDS, "Categories")

Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)

catDA.Update(catDS, "Categories")

Dim rowCount As Int32 = CInt(catDA.InsertCommand.Parameters("@RowCount").Value)
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;" +
                                            "Initial Catalog=northwind");

SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);

catDA.InsertCommand = new SqlCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
myParm.Direction = ParameterDirection.ReturnValue;

catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");

myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;

DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");

DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);

catDA.Update(catDS, "Categories");

Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value;

OleDb

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

Dim catDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories", _
                                                     nwindConn)

catDA.InsertCommand = New OleDbCommand("InsertCategory" , nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure

Dim myParm As OleDbParameter = catDA.InsertCommand.Parameters.Add("@RowCount", OleDbType.Integer)
myParm.Direction = ParameterDirection.ReturnValue

catDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName")

myParm = catDA.InsertCommand.Parameters.Add("@Identity", OleDbType.Integer, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output

Dim catDS As DataSet = New DataSet()
catDA.Fill(catDS, "Categories")

Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)

catDA.Update(catDS, "Categories")

Dim rowCount As Int32 = CInt(catDA.InsertCommand.Parameters("@RowCount").Value)
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" + 
                                                "Integrated Security=SSPI;Initial Catalog=northwind");

OleDbDataAdapter catDA = new OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);

catDA.InsertCommand = new OleDbCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;

OleDbParameter myParm = catDA.InsertCommand.Parameters.Add("@RowCount", OleDbType.Integer);
myParm.Direction = ParameterDirection.ReturnValue;

catDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName");

myParm = catDA.InsertCommand.Parameters.Add("@Identity", OleDbType.Integer, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;

DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");

DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);

catDA.Update(catDS, "Categories");

Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value;

請參閱

使用 .NET Framework 資料提供者存取資料