ADO绑定SQL数据库过程

--1 更新特定表中记录的存储过程并在WEBpage中使用ADO调用。
use MySchool
select * from Class
insert into Class values('net','优秀班级体')
create proc usp_T_Class_update
@classId int,
@cName varchar(50),
@cDescription varchar(50)
as
begin
update Class set cName=@cName, cDescription=@cDescription where clsId=@classId
end

exec usp_T_Class_update 7,'NET','超级班'

--ASP中前台代码
<form id="form1" runat="server">
<div>
请输入班号:<asp:TextBox ID="txtClassId" Text="" runat="server"></asp:TextBox><br />
请输入班级:<asp:TextBox ID="txtClass" Text="" runat="server"></asp:TextBox><br />
请输入荣誉:<asp:TextBox ID="txtRongYu" Text="" runat="server"></asp:TextBox><br />
<asp:Button ID="btn_update" runat="server" Text="更新"
onclick="btn_update_Click" />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</div>
</form>

--ASP中后台代码

protected void btn_update_Click(object sender, EventArgs e)
{
int classId=Convert.ToInt32(txtClassId.Text);
string cname=txtClass.Text;
string cDescription=txtRongYu.Text;

string connStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
string usp_name = "usp_T_Class_update";
cmd.CommandText = usp_name;
SqlParameter prm1=new SqlParameter("@classId",classId);
SqlParameter prm2=new SqlParameter("@cName",cname);
SqlParameter prm3=new SqlParameter("@cDescription",cDescription);
cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);
cmd.Parameters.Add(prm3);
int i= cmd.ExecuteNonQuery();
if (i > 0)
{
Label1.Text = "更新成功";
}
else
{
Label1.Text = "更新失败";
}
}
}
}

--2 写一个查询表中记录的存储过程，并在winform中应用他实现显示student表中的
--所有记录（app.config,引用，ClassModel类，List泛型绑定，dataGradView1的数据源）。

create proc usp_T_Class_Select
as
begin
select * from Class
end

exec usp_T_Class_Select


private void button1_Click(object sender, EventArgs e)
{
List<ClassModle> list = new List<ClassModle>();
string connStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
string usp_name = "usp_T_Class_Select";
cmd.CommandText = usp_name;
using (SqlDataReader read = cmd.ExecuteReader())
{
if (read.HasRows)
{
while (read.Read())
{
ClassModle modle = new ClassModle();
modle.clsName = read.IsDBNull(read.GetOrdinal("cName")) ? string.Empty : read.GetString(read.GetOrdinal("cName"));
modle.clsDesc = read.IsDBNull(read.GetOrdinal("cDescription")) ? string.Empty : read.GetString(read.GetOrdinal("cDescription"));
list.Add(modle);

}

}
}
}
}
dataGridView1.DataSource = list;

}
--3 写一个对特定表进行分页显示的存储过程，要求有两个参数一个是每页显示的记录的
--条数（@pagesize），第二个是显示第几页（@pageIndex）
select * from Class
create proc usp_T_Class_GetDateByPageIndex
@pagesize int,
@pageIndex int
as
begin
select * from
(select *,ROW_NUMBER() over(order by clsId) as rowIndex from Class ) as tb
where tb.rowIndex between (@pagesize*(@pageIndex-1)+1) and (@pagesize*@pageIndex)
end

exec usp_T_Class_GetDateByPageIndex 3,2

--4 使用存储过程、事务、webpage实现转账。
--思路1要有存储过程，存储过程中包含事务。参数应该有3个（转入账号，转出账号，金额）
select * from bank
create proc usp_T_bank_update
@outputNumber int,
@inputNumber int,
@moneyNumber money
as
begin
begin tran
begin try
update bank set balance=balance-@moneyNumber where sName=@outputNumber
update bank set balance=balance+@moneyNumber where sName=@inputNumber
commit
end try
begin catch
rollback
end catch
end

exec usp_T_bank_update '001','002',20

private void button1_Click(object sender, EventArgs e)
{
List<ClassModle> list = new List<ClassModle>();
string connStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
string usp_name = "usp_T_Class_Select";
cmd.CommandText = usp_name;
using (SqlDataReader read = cmd.ExecuteReader())
{
if (read.HasRows)
{
while (read.Read())
{
ClassModle modle = new ClassModle();
modle.clsName = read.IsDBNull(read.GetOrdinal("cName")) ? string.Empty : read.GetString(read.GetOrdinal("cName"));
modle.clsDesc = read.IsDBNull(read.GetOrdinal("cDescription")) ? string.Empty : read.GetString(read.GetOrdinal("cDescription"));
list.Add(modle);

}

}
}
}
}
dataGridView1.DataSource = list;

}


--5 通过ADO实现事务
-- 5.1把它写在控制台应用程序中
static void Main(string[] args)
{

string connStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "delete from bank where sName='0003'";
cmd.Transaction = tran;
int i = cmd.ExecuteNonQuery();

tran.Commit();
Console.WriteLine("提交成功");

//tran.Rollback();
//Console.WriteLine("回滚成功");
}
}

Console.ReadKey();

}