将存储过程应用到ASP.NET中

①在ASP.net中调用不带参数的存储过程

CREATE PROC select_tid AS select tid from my_test where tname='Aillo'

 protected void Page_Load(object sender, EventArgs e)   {         //不带参数的存储过程①         string constr = "database=test";         ConToDatabase ctd = new ConToDatabase(constr);         SqlConnection con = ctd.getCon();               //ConToDatabase是自己定义的类，getCon()就是自定义的连接数据库的方法。         SqlCommand cmd = new SqlCommand("select_tid", con);         cmd.CommandType = CommandType.StoredProcedure;         string result = "";         try         {             con.Open();             SqlDataReader myreader = cmd.ExecuteReader();             while (myreader.Read())             {                 if (myreader[0].ToString() != "")                 {                     Response.Write(myreader[0].ToString());                 }             }         }         catch (Exception ex)         {             result=ex.ToString();         }         finally         {             con.Close();         } }

②调用带参数的存储过程

CREATE PROCEDURE select_by_age @min_age int, @max_age int AS select cid as '编号', cname as '姓名', age as '年龄', sex as '性别' from customers where age>=@min_age and age<=@max_age

protected void Page_Load(object sender, EventArgs e) {

        //带参数的存储过程         string constr = "database=test";         ConToDatabase ctd = new ConToDatabase(constr);         SqlConnection con = ctd.getCon();         SqlDataAdapter da= new SqlDataAdapter("select_by_age", con);         da.SelectCommand.CommandType = CommandType.StoredProcedure;         SqlParameter par0, par1;         par0 = new SqlParameter("@min_age", 5);         par1 = new SqlParameter("@max_age", 18);         da.SelectCommand.Parameters.Add(par0);         da.SelectCommand.Parameters.Add(par1);         DataSet ds = new DataSet();         string result = "";         try         {             con.Open();             da.Fill(ds);             GridView1.DataSource = ds;             GridView1.DataBind();         }         catch (Exception ex)         {             result = ex.ToString();         }         finally         {             con.Close();         }    }

③调用带判断的存储过程

CREATE PROCEDURE select_if @age int AS if @age=5 begin select * from customers where age=@age end else begin select * from customers where age<>5 end

protected void Page_Load(object sender, EventArgs e) {         //带判断的存储过程，根据传递进来的参数判断执行哪条SQL语句         string constr = "database=test";         ConToDatabase ctd = new ConToDatabase(constr);         SqlConnection con = ctd.getCon();         SqlDataAdapter da = new SqlDataAdapter("select_if", con);         da.SelectCommand.CommandType = CommandType.StoredProcedure;         SqlParameter par0;         par0 = new SqlParameter("@age", 11);         da.SelectCommand.Parameters.Add(par0);         DataSet ds = new DataSet();         string result = "";         try         {             con.Open();             da.Fill(ds);             GridView1.DataSource = ds;             GridView1.DataBind();         }         catch (Exception ex)         {             result = ex.ToString();         }         finally         {             con.Close();         }    }

转载于:https://www.cnblogs.com/Fly-sky/archive/2008/11/06/1327841.html