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

来源：互联网发布：棉花云数据编辑：程序博客网时间：2024/06/16 14:28

1. 存储过程

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================alter PROCEDURE GetOrderLine @orderId varchar(50)ASBEGIN-- 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;ENDGO

注意存储过程只能返回 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();

0 0