将存储过程应用到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();
        }
   }