asp.net中C#调用存储过程

创建存储过程：

 1 create procedure houseCount
 2 (
 3   @house_state nvarchar(20),
 4   @house_count int output
 5 )
 6 as
 7 select @house_count=COUNT(*) from house where house_state=@house_state
 8 
 9 sql执行存储过程：
10 declare @house_count int
11 execute houseCount '空房',@house_count output select @house_count

C#调用存储过程：

 1 protected void Page_Load(object sender, EventArgs e)
 2     {
 3         string count_house = ProcedureCount("空房");
 4         Response.Write(string.Format(count_house));
 5     }
 6     //调用存储过程
 7     public string ProcedureCount(string house) 
 8     {
 9         string count_house = "";
10         string strConnection = "user id=sa;password=sa;initial catalog=houseState;Server=127.0.0.1;Connect Timeout=30";
11         using (SqlConnection conn = new SqlConnection(strConnection))
12         {
13             conn.Open();
14             using (SqlCommand sqlComm = conn.CreateCommand())
15             {
16                 //设置要调用的存储过程的名称  
17                 sqlComm.CommandText = "houseCount";
18                 //指定SqlCommand对象传给数据库的是存储过程的名称而不是sql语句  
19                 sqlComm.CommandType = CommandType.StoredProcedure;
20 
21                 SqlParameter username = sqlComm.Parameters.Add(new SqlParameter("@house_state", SqlDbType.VarChar, 20));
22                 //指明"@username"是输入参数  
23                 username.Direction = ParameterDirection.Input;
24                 //为“@username”参数赋值  
25                 username.Value =house;
26 
27                 SqlParameter password = sqlComm.Parameters.Add(new SqlParameter("@house_count", SqlDbType.Int));
28                 //指定"@password"为输出参数  
29                 password.Direction = ParameterDirection.Output;
30                 //执行  
31                 sqlComm.ExecuteNonQuery();
32                 //得到输出参数的值,把赋值给name,注意,这里得到的是object类型的,要进行相应的类型轮换  
33                 count_house = sqlComm.Parameters["@house_count"].Value.ToString();
34                 
35             }
36         }
37         return count_house;
38     }

 

转载于:https://www.cnblogs.com/ToFlying/p/3140532.html