.Net 调用存储过程取到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();