使用存储过程修改数据
存储过程可以接受数据作为输入参数并可以返回数据作为输出参数、结果集或返回值。 下面的示例演示 ADO.NET 如何发送和接收输入参数、输出参数及返回值。 该示例将一条新记录插入到一个表中,该表中的主键列为 SQL Server 数据库中的标识列。
备注
如果您要通过 SQL Server 存储过程使用 SqlDataAdapter 来编辑或删除数据,请确保不要在存储过程定义中使用 SET NOCOUNT ON。 这将使返回的受影响的行数为零,DataAdapter
会将其解释为并发冲突。 在这种情况下,将引发 DBConcurrencyException。
示例
此示例使用以下存储过程将一个新类别插入到 Northwind“类别”表。 存储过程获取“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
存储过程作为 InsertCommand 的 SqlDataAdapter 的来源。 如果在将记录插入到数据库后调用 @Identity
的 DataSet 方法,Update
中将会反映出 SqlDataAdapter 输出参数。 此代码还会检索返回值。
备注
在使用 OleDbDataAdapter 时,必须在其他参数之前先使用 ReturnValue 的 ParameterDirection 指定参数。
using (SqlConnection connection = new(connectionString))
{
// Create a SqlDataAdapter based on a SELECT query.
SqlDataAdapter adapter = new("SELECT CategoryID, CategoryName FROM dbo.Categories", connection)
{
// Create a SqlCommand to execute the stored procedure.
InsertCommand = new SqlCommand("InsertCategory", connection)
{
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();
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.
var rowCount = (int)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]);
}
}
Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Module Class1
Sub Main()
Dim connectionString As String = _
GetConnectionString()
ReturnIdentity(connectionString)
' Console.ReadLine()
End Sub
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 Function GetConnectionString() As String
Throw New NotImplementedException()
End Function
End Module