.Net里怎么得到存储过程的反回值???? 就是存储过程里的return的值??? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 取 output参数的Value即可! 除了输入和输出参数之外,存储过程还可以具有返回值。以下示例阐释 ADO.NET 如何发送和接收输入参数、输出参数和返回值,其中采用了这样一种常见方案:将新记录插入其中主键列是自动编号字段的表。该示例使用输出参数来返回自动编号字段的 @@Identity,而 DataAdapter 则将其绑定到 DataTable 的列,使 DataSet 反映所生成的主键值。该示例使用以下存储过程将新目录插入 Northwind Categories 表(该表将 CategoryName 列中的值当作输入参数),从 @@Identity 中以输出参数的形式返回自动编号字段 CategoryID 的值,并提供所影响行数的返回值。CREATE PROCEDURE InsertCategory @CategoryName nchar(15), @Identity int OUTASINSERT INTO Categories (CategoryName) VALUES(@CategoryName)SET @Identity = @@IdentityRETURN @@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.StoredProcedureDim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int)myParm.Direction = ParameterDirection.ReturnValuecatDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName")myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID")myParm.Direction = ParameterDirection.OutputDim 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.StoredProcedureDim myParm As OleDbParameter = catDA.InsertCommand.Parameters.Add("@RowCount", OleDbType.Integer)myParm.Direction = ParameterDirection.ReturnValuecatDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName")myParm = catDA.InsertCommand.Parameters.Add("@Identity", OleDbType.Integer, 0, "CategoryID")myParm.Direction = ParameterDirection.OutputDim 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中如何调用存储过程http://www.5d.cn/Tutorial/webdevelop/asp/200412/1960.html 用输出的value,就return就可以了啊;或者用自定义函数,然后在程序中调用该函数 请问一定要是cmd.ExecuteNonQuery()才能够取得存储过程return的值吗????如果是cmd.ExecuteReader()是不是不能取得存储过程的return值?????????????? 执行IDbCommand 的 ExecuteXXX 方法之前, 添加一个参数名为 "@return_value" 参数类型是 System.Data.ParameterDirection.ReturnValue 的参数就可以了.在执行 ExecuteXXX 方法完毕后,获取该参数的值 (Value属性) 就可以了. 请问一定要是cmd.ExecuteNonQuery()才能够取得存储过程return的值吗????如果是cmd.ExecuteReader()是不是不能取得存储过程的return值??????????????=========================是的 cmd.ExecuteNonQuery() 运行存储过程必须使用的方法cmd.ExecuteReader()是 执行Select语句后 返回结果集的方法 cmd.Parameters.Add("returnvalue",SqlDbType.Int);cmd.Parameters["returnvalue"].Direction = rection.ReturnValue;把你声明的参数的类型定义为返回值类型.int x=Convert.ToInt32(cmd.Parameters["returnvalue"].Value);如果你要返回结果集还要返回返回值的话.你写成两个方法,调用一个存储过程.因为一个方法只能返回一个值. 如何避免注册机批量注册会员 在asp.net中,如何把TextBox控件拖到工具箱的Table控件中去?是不是不可以?我试了好久都没成功。 关于页面生命周期的通俗理解 gridview中,用一按钮绑定字段,单击按钮后,跳转其它页,并传值!!! checkbox 不能更新。数据库或对象为只读 为什么按纽没有显示出来 郁闷的问题:.net到底还有什么用? 为什么在.aspx激发不了page_load? saucer(思归),请近来拿分 请问如何检查上传的图片大小 如何用Javascript控制textbox的輸入法﹖(在線等待......)
该示例使用以下存储过程将新目录插入 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.StoredProcedureDim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValuecatDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName")myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.OutputDim 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.StoredProcedureDim myParm As OleDbParameter = catDA.InsertCommand.Parameters.Add("@RowCount", OleDbType.Integer)
myParm.Direction = ParameterDirection.ReturnValuecatDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName")myParm = catDA.InsertCommand.Parameters.Add("@Identity", OleDbType.Integer, 0, "CategoryID")
myParm.Direction = ParameterDirection.OutputDim 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;
或者用自定义函数,然后在程序中调用该函数
在执行 ExecuteXXX 方法完毕后,获取该参数的值 (Value属性) 就可以了.
=========================
是的 cmd.ExecuteNonQuery() 运行存储过程必须使用的方法
cmd.ExecuteReader()是 执行Select语句后 返回结果集的方法
cmd.Parameters["returnvalue"].Direction = rection.ReturnValue;
把你声明的参数的类型定义为返回值类型.
int x=Convert.ToInt32(cmd.Parameters["returnvalue"].Value);如果你要返回结果集还要返回返回值的话.
你写成两个方法,调用一个存储过程.
因为一个方法只能返回一个值.