1. 存储过程
01 SET ANSI_NULLS ON
02 GO
03 SET QUOTED_IDENTIFIER ON
04 GO
05 -- =============================================
06 -- Author: <Author,,Name>
07 -- Create date: <Create Date,,>
08 -- Description: <Description,,>
09 -- =============================================
10 alter PROCEDURE GetOrderLine
11 @orderId varchar(50)
12 AS
13 BEGIN
14 -- SET NOCOUNT ON added to prevent extra result sets from
15 -- interfering with SELECT statements.
16 SET NOCOUNT ON;
17
18 select * from orderLine where OrderId = @orderId;
19
20 return 123;
21 END
22 GO
注意 存储过程只能返回 int 类型，如果返回一个字符串 ，将会报类型转化错误
2 后台调用
01 DataTable dt = new DataTable();
02 string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["BLL.Properties.Settings.ShoppingDBConnectionString"].ToString();
03 using(SqlConnection conn= new SqlConnection(connStr)){
04 string callName = "GetOrderLine";
05 using (SqlCommand command = new SqlCommand(callName, conn))
06 {
07 command.CommandType = CommandType.StoredProcedure;
08 SqlParameter[] sps = { new SqlParameter("@orderId",SqlDbType.VarChar,50) ,
09 new SqlParameter("@return",SqlDbType.Int) //注册返回值类型
10 };
11
12 sps[0].Value = "43c7cf15-6b2f-4d18-92b2-dbe827f30dfc";
13 sps[1].Direction = ParameterDirection.ReturnValue; //返回参数类型
14
15 command.Parameters.AddRange(sps);
16 using(SqlDataAdapter sda =new SqlDataAdapter()){
17 sda.SelectCommand = command;
18 sda.Fill(dt);
19 //Console.WriteLine(sda.GetFillParameters()[1].Value);
20 Console.WriteLine(sps[1].Value); //取到返回的值
21 }
22
23 }
24 }
25
26 if(dt.Rows.Count>0){
27 for (int i = 0; i < dt.Rows.Count;i++ )
28 {
29 Console.WriteLine(dt.Rows[i]["ProductId"]+":"+dt.Rows[i]["ProductPrice"]+":"+dt.Rows[i]["ProductCount"]);
30 }
31 }
32 Console.ReadLine();
更多信息请查看IT技术专栏