存储过程

--存储过程

create proc stuinfo

@stuid int

as

select a.* from Student a where stuid=@stuid

 

--执行存储过程

exec stuinfo 1002

 

--删除存储过程

drop proc stuinfo

 

//在VS中执行存储过程stuinfo

SqlConnection con = new SqlConnection();

        con.ConnectionString = "server=.;database=CRM;uid=sa;pwd=123";

        con.Open();

 

        SqlCommand cmd = new SqlCommand();

        cmd.Connection = con;

        //指定命令对象要执行存储过程

        cmd.CommandType = CommandType.StoredProcedure;

        //存储过程名称

        cmd.CommandText = "stuinfo";

        //存储过程参数

        SqlParameter spid = new SqlParameter("@stuid", SqlDbType.Int);

        spid.Value = 1002;

        cmd.Parameters.Add(spid);

 

        SqlDataAdapter ads = new SqlDataAdapter();

        ads.SelectCommand = cmd;

 

        DataSet ds = new DataSet();

        ads.Fill(ds);

 

        GridView1.DataSource = ds;

        GridView1.DataBind();

        con.Close();

 

--存储过程2

create proc stuinsert

@stuname varchar(20),

@stusex char(2),

@stubirthday datetime

as

insert into Student

values(@stuname,@stusex,@stubirthday)

 

SqlConnection con = new SqlConnection();

        con.ConnectionString = "server=.;database=CRM;uid=sa;pwd=123";

        con.Open();

 

        SqlCommand cmd = new SqlCommand();

        cmd.Connection = con;

        //指定命令对象要执行存储过程

        cmd.CommandType = CommandType.StoredProcedure;

        //存储过程名称

        cmd.CommandText = "stuinsert";

        //存储过程参数1

        SqlParameter spname = new SqlParameter("@stuname", SqlDbType.VarChar);

        spname.Value = TextBox1.Text.Trim();

        cmd.Parameters.Add(spname);

        //存储过程参数2

        SqlParameter spsex = new SqlParameter("@stusex", SqlDbType.Char);

        ton1spsex.Value = RadioButton1.Checked ? "男" : "女";

// RadioButton1与RadioButton2的GroupName相同

        cmd.Parameters.Add(spsex);

        //存储过程参数3

        SqlParameter spbir = new SqlParameter("@stubirthday", SqlDbType.VarChar);

        spbir.Value = TextBox2.Text.Trim();

        cmd.Parameters.Add(spbir);

 

        int count = cmd.ExecuteNonQuery();

        if (count > 0)

        {

            Response.Write("sg");

        }

        con.Close();

 

--存储过程,新增一行数据并返回该行数据的stuid

create proc stuinsert2

@stuname varchar(20),

@stusex char(2),

@stubirthday datetime,

@stuid int output

as

insert into Student

values(@stuname,@stusex,@stubirthday)

set @stuid=@@identity;

 

SqlConnection con = new SqlConnection();

        con.ConnectionString = "server=.;database=CRM;uid=sa;pwd=123";

        con.Open();

 

        SqlCommand cmd = new SqlCommand();

        cmd.Connection = con;

        //指定命令对象要执行存储过程

        cmd.CommandType = CommandType.StoredProcedure;

        //存储过程名称

        cmd.CommandText = "stuinsert2";

        //存储过程参数1

        SqlParameter spname = new SqlParameter("@stuname", SqlDbType.VarChar);

        spname.Value = TextBox1.Text.Trim();

        cmd.Parameters.Add(spname);

        //存储过程参数2

        SqlParameter spsex = new SqlParameter("@stusex", SqlDbType.Char);

        spsex.Value = RadioButton1.Checked ? "男" : "女";

// RadioButton1与RadioButton2的GroupName相同

        cmd.Parameters.Add(spsex);

        //存储过程参数3

        SqlParameter spbir = new SqlParameter("@stubirthday", SqlDbType.VarChar);

        spbir.Value = TextBox2.Text.Trim();

        cmd.Parameters.Add(spbir);

 

        SqlParameter spid = new SqlParameter("@stuid", SqlDbType.Int);

        spid.Direction = ParameterDirection.Output;

        cmd.Parameters.Add(spid);

 

        int count = cmd.ExecuteNonQuery();

        if (count > 0)

        {

            Response.Write(cmd.Parameters["@stuid"].Value);

        }

        con.Close();