存储过程的应用实例

一、创建存储过程
      要使用存储过程,我们就必须先创建存储过程。存储过程要创建的在数据库的服务器中。您可以通过下面的方法创建存储过程:
①企业管理器→具体的数据库→存储过程→右击→"新建存储过程"→在弹出的对话框中定义存储过程。你可以通过create proc [用户名].[存储过程名] ... 指定该存储过程的所有者,比如create proc test.selectAll ... 。如果未指定所有者,则默认地使用 dbo 作为所有者。


②在SQL 查询分析器中直接通过执行代码来实现。
创建完存储过程,您可以给改存储过程设置权限。企业管理器→具体的数据库→存储过程→找到刚创建的存储过程→右击属性→"权限"进行权限的设置。

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

留言

Your email address will not be published. Required fields are marked *