.Net 调用存储过程取到return的返回值

2019-12-07 13:17whsnow ASP.NET教程

存储过程只能返回 int 类型，如果返回一个字符串，将会报类型转化错误，下面以示例介绍下如何取到return的值,需要的朋友可以参考下

1. 存储过程

				?

									SET ANSI_NULLS ON

									GO 

									SET QUOTED_IDENTIFIER ON

									GO 

									-- ============================================= 

									-- Author: <Author,,Name> 

									-- Create date: <Create Date,,> 

									-- Description: <Description,,> 

									-- ============================================= 

									alter PROCEDURE GetOrderLine 

									@orderId varchar(50) 

									AS

									BEGIN

									-- SET NOCOUNT ON added to prevent extra result sets from 

									-- interfering with SELECT statements. 

									SET NOCOUNT ON; 

									select * from orderLine where OrderId = @orderId; 

									return 123; 

									END

									GO

注意存储过程只能返回 int 类型，如果返回一个字符串，将会报类型转化错误

2 后台调用

				?

									DataTable dt = new DataTable(); 

									string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["BLL.Properties.Settings.ShoppingDBConnectionString"].ToString(); 

									using(SqlConnection conn= new SqlConnection(connStr)){ 

									string callName = "GetOrderLine"; 

									using (SqlCommand command = new SqlCommand(callName, conn)) 

									{ 

									command.CommandType = CommandType.StoredProcedure; 

									SqlParameter[] sps = { new SqlParameter("@orderId",SqlDbType.VarChar,50) , 

									new SqlParameter("@return",SqlDbType.Int) //注册返回值类型 

									}; 

									sps[0].Value = "43c7cf15-6b2f-4d18-92b2-dbe827f30dfc"; 

									sps[1].Direction = ParameterDirection.ReturnValue; //返回参数类型 

									command.Parameters.AddRange(sps); 

									using(SqlDataAdapter sda =new SqlDataAdapter()){ 

									sda.SelectCommand = command; 

									sda.Fill(dt); 

									//Console.WriteLine(sda.GetFillParameters()[1].Value); 

									Console.WriteLine(sps[1].Value); //取到返回的值 

									} 

									} 

									} 

									if(dt.Rows.Count>0){ 

									for (int i = 0; i < dt.Rows.Count;i++ ) 

									{ 

									Console.WriteLine(dt.Rows[i]["ProductId"]+":"+dt.Rows[i]["ProductPrice"]+":"+dt.Rows[i]["ProductCount"]); 

									} 

									} 

									Console.ReadLine();