1. 存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 后台调用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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(); |