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 }