.Net里怎么得到存储过程的返回值

最新推荐文章于 2015-05-21 08:57:43 发布

weixin_34383618

最新推荐文章于 2015-05-21 08:57:43 发布

阅读量91

点赞数

文章标签： c# 数据库

本文介绍如何使用ADO.NET在.NET中调用存储过程，演示了如何发送和接收输入参数、输出参数及返回值。示例中使用了InsertCategory存储过程，通过DataAdapter将其绑定到DataTable，并反映了所生成的主键值。

摘要由CSDN通过智能技术生成

除了输入和输出参数之外，存储过程还可以具有返回值。以下示例阐释 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，当调用 DataAdapter 的 Update 方法时，所生成的自动编号值将在该记录插入数据库后在 DataSet 中得到反映。
对于 OleDbDataAdapter，必须在指定其他参数之前先指定 ParameterDirection 为 ReturnValue 的参数。
SqlClient
[Visual Basic]
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
[Visual Basic]
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;