使用存储过程操作数据库
添加数据：
--创建一个存储过程，向表中插入一条数据
create proc usp_insertintoTbClass
@className nvarchar(50),
@classDes nvarchar(100)
as
begin
insert into TbClass values( @ClassName,@classDes)
end
exec usp_insertintoTbClass @className='初一三班',@classDes='中班'
string constr=ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
using (SqlConnection con=new SqlConnection(constr))
{
using (SqlCommand cmd=new SqlCommand("usp_insertintoTbClass", con))
{
cmd.CommandType=System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@className", txtinsertname.Text.Trim());
cmd.Parameters.AddWithValue("@classDes", txtinsertdes.Text.Trim().Trim());
con.Open();
int r=cmd.ExecuteNonQuery();
MessageBox.Show("成功插入"+r+"条数据");
}
}
修改数据：
---创建一个存储过程来修改表中的数据
create proc usp_TbClass_updateclass
@ClassId int,
@ClassName nvarchar(50),
@ClassDescription nvarchar(100)
as
begin
update TbClass set ClassName=@ClassName,ClassDescription=@ClassDescription
where ClassId=@ClassId
end
exec usp_TbClass_updateclass 2,'高一四班','重点理科班'
//1:引用配置文件，创建链接字符串
string constr=ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
//2：创建链接对象
using(SqlConnection con=new SqlConnection(constr))
{
//3：创建链接对象把存储过程作为一个参数语句传递给数据库对象
using (SqlCommand cmd=new SqlCommand("usp_TbClass_updateclass", con))
{
//4：将数据库对象设这为存储过程对象StoredProcedure
cmd.CommandType=System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ClassName",txtname.Text.Trim());
cmd.Parameters.AddWithValue("@ClassDescription", txtdsc.Text.Trim());
cmd.Parameters.AddWithValue("@ClassId",txtid.Text.Trim());
con.Open();
int r=cmd.ExecuteNonQuery();
MessageBox.Show("成功更改"+r+"条数据");
}
}
删除数据：
---创建一个存储过程删除表中的数据
create proc usp_TbClass_deleteclass
@ClassId int
as
begin
delete TbClass where ClassId=@ClassId
end
string constr=ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
using(SqlConnection con=new SqlConnection(constr))
{
using (SqlCommand cmd=new SqlCommand("usp_TbClass_deleteclass", con))
{
cmd.CommandType=System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ClassId", txtdelet.Text.Trim());
con.Open();
int r=cmd.ExecuteNonQuery();
txtdelet.Clear();
if (r!=0)
{
MessageBox.Show("成功删除"+r+"条数据");
}
else
{
MessageBox.Show("您输入的班级编号不存在");
}
}
}
读取数据：
----创建一个存储过程查询表中的数据---------
create proc usp_selectTbClass
@classId int
as
begin
select * from TbClass where ClassId=@classId
end
private void button1_Click(objectsender, EventArgse)
{
string constr=ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
SqlConnection con=new SqlConnection(constr);
using (SqlCommand cmd=new SqlCommand("usp_select TbClass", con))
{
con.Open();
cmd.CommandType=System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@classId", txtclassid.Text.Trim());
//SqlParameter p1 = new SqlParameter("@className", SqlDbType.NVarChar);
//SqlParameter p2 = new SqlParameter("@classDes", SqlDbType.NVarChar);
//p1.Direction = ParameterDirection.Output;
//p2.Direction = ParameterDirection.Output;
//cmd.Parameters.Add(p1);
//cmd.Parameters.Add(p2);
using (SqlDataReader reader=cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
txtname.Text=reader.GetString(1);
txtdsc.Text=reader.GetString(2);
//txtname.Text = reader.GetString(0).ToString();
//txtdsc.Text = reader.GetString(1).ToString();
//cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
MessageBox.Show("读取成功");
}
}
}
}
}