存储过程的使用方法

-------------------建表------------------

CREATE TABLE [tab_cJ] (

 [id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,

 [name] [varchar] (50) ,

 [age] [int] NULL ,

 [info] [varchar] (200)

)

 

------------------建存储过程-------------------------

CREATE PROCEDURE QueryInfoByName

   @name varchar(50),

   @age int

AS

select info from tab_cj where [name]=@name and age=@age

GO

--------------------调用-------------------------------

protected void GetInfoByName(string txt_name, int txt_age)

    {

        SqlConnection conn = new SqlConnection("server=localhost;database=dbDemo;uid=sa");

        SqlCommand cmd = conn.CreateCommand();

        cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作

        cmd.CommandText = "QueryInfoByName"; //存储过程名称

        //对应存储过程QueryInfoByName的第一个参数@name

        SqlParameter parName = new SqlParameter("@name", SqlDbType.VarChar, 50);

        //指定参数@name要转入的值

        parName.Value = txt_name;

        //对应存储过程QueryInfoByName的第二个参数@age

        SqlParameter parAge = new SqlParameter("@age", SqlDbType.Int);

        //指定参数@age要转入的值

        parAge.Value = txt_age;

 

        //这一步非常重要，一定将设置好的两个参数类型添加到Command对象的参数集合里

        cmd.Parameters.Add(parName);

        cmd.Parameters.Add(parAge);

 

        //方式一，查询回来的结果需要显示在DataGrid之类的控件上

        DataSet ds = new DataSet();

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);

        adapter.Fill(ds);

 

        //方式二，按单个值读取

        conn.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.HasRows)

        {

            while (reader.Read())

            {

               Response.Write(reader.GetString(0));

            }

        }

        conn.Close();

    }

 8