使用預存程序修改資料 (ADO.NET)
更新： November 2007
預存程序 (Stored Procedure) 可以接受資料做為輸入參數，也可以將資料以輸出參數、結果集 (Result Set) 或傳回值的形式傳回。下列範例說明 ADO.NET 如何傳送及接收輸入參數、輸出參數和傳回值。此範例會將新記錄插入資料表 (該資料表的主索引鍵資料行是 SQL Server 資料庫中的識別欄位)。
.gif "注意事項") 注意事項： |
|---|
如果您要使用 SQL Server 預存程序搭配 SqlDataAdapter 來編輯或刪除資料，請務必不要在預存程序定義中使用 SET NOCOUNT ON。因為這樣會讓傳回的受影響資料列計數成為零，而 DataAdapter 會將它解譯為並行衝突。在此事件中，系統會擲回 DBConcurrencyException。 |
範例
此範例使用下列的預存程序將新類別插入至 Northwind 的 Categories 資料表。此預存程序使用 CategoryName 資料行中的值做為輸入參數，然後使用 SCOPE_IDENTITY() 函式來擷取識別欄位 CategoryID 的新值，並以輸出參數加以傳回。RETURN 陳述式使用 @@ROWCOUNT 函式傳回插入的資料列數。
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
下列程式碼範例使用以上所示的 InsertCategory 預存程序做為 SqlDataAdapter 的 InsertCommand 的來源。在呼叫 SqlDataAdapter 的 Update 方法而將記錄插入至資料庫之後，@Identity 輸出參數將反映在 DataSet 中。程式碼也會擷取傳回值。
| 注意事項： |
|---|
使用 OleDbDataAdapter 時，必須在其他參數前指定 ParameterDirection 為 ReturnValue 的參數。 |
Private Sub ReturnIdentity(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create a SqlDataAdapter based on a SELECT query.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connection)
' Create a SqlCommand to execute the stored procedure.
adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure
' Create a parameter for the ReturnValue.
Dim parameter As SqlParameter = _
adapter.InsertCommand.Parameters.Add( _
"@RowCount", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
' Create an input parameter for the CategoryName.
' You do not need to specify direction for input parameters.
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NChar, 15, "CategoryName")
' Create an output parameter for the new identity value.
parameter = adapter.InsertCommand.Parameters.Add( _
"@Identity", SqlDbType.Int, 0, "CategoryID")
parameter.Direction = ParameterDirection.Output
' Create a DataTable and fill it.
Dim categories As DataTable = New DataTable
adapter.Fill(categories)
' Add a new row.
Dim newRow As DataRow = categories.NewRow()
newRow("CategoryName") = "New Category"
categories.Rows.Add(newRow)
' Update the database.
adapter.Update(categories)
' Retrieve the ReturnValue.
Dim rowCount As Int32 = _
CInt(adapter.InsertCommand.Parameters("@RowCount").Value)
Console.WriteLine("ReturnValue: {0}", rowCount.ToString())
Console.WriteLine("All Rows:")
Dim row As DataRow
For Each row In categories.Rows
Console.WriteLine(" {0}: {1}", row(0), row(1))
Next
End Using
End Sub
private static void ReturnIdentity(string connectionString)
{
using (SqlConnection connection =
new SqlConnection(connectionString))
{
// Create a SqlDataAdapter based on a SELECT query.
SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT CategoryID, CategoryName FROM dbo.Categories",
connection);
// Create a SqlCommand to execute the stored procedure.
adapter.InsertCommand = new SqlCommand("InsertCategory", connection);
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
// Create a parameter for the ReturnValue.
SqlParameter parameter = adapter.InsertCommand.Parameters.Add(
"@RowCount", SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
// Create an input parameter for the CategoryName.
// You do not need to specify direction for input parameters.
adapter.InsertCommand.Parameters.Add(
"@CategoryName", SqlDbType.NChar, 15, "CategoryName");
// Create an output parameter for the new identity value.
parameter = adapter.InsertCommand.Parameters.Add(
"@Identity", SqlDbType.Int, 0, "CategoryID");
parameter.Direction = ParameterDirection.Output;
// Create a DataTable and fill it.
DataTable categories = new DataTable();
adapter.Fill(categories);
// Add a new row.
DataRow categoryRow = categories.NewRow();
categoryRow["CategoryName"] = "New Beverages";
categories.Rows.Add(categoryRow);
// Update the database.
adapter.Update(categories);
// Retrieve the ReturnValue.
Int32 rowCount =
(Int32)adapter.InsertCommand.Parameters["@RowCount"].Value;
Console.WriteLine("ReturnValue: {0}", rowCount.ToString());
Console.WriteLine("All Rows:");
foreach (DataRow row in categories.Rows)
{
{
Console.WriteLine(" {0}: {1}", row[0], row[1]);
}
}
}
}