ADO绑定SQL数据库过程
来源:互联网 发布:淘宝上的官换机能买吗 编辑:程序博客网 时间:2024/05/08 14:26
--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();
}
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();
}
- ADO绑定SQL数据库过程
- ADO 访问SQL数据库
- VC ADO dataGrid 数据库动态绑定
- VC ADO dataGrid 数据库动态绑定
- Treeview绑定SQL数据库
- ado连接sql server 数据库
- VC ADO连接SQL数据库
- ADO连接SQL数据库 C++
- ADO连接SQL Server数据库
- SQL 2008 ADO数据库使用指南
- SQL 2008 ADO数据库使用指南
- ado连不上sql server数据库
- Sql server 数据库ADO连接
- ado 连接sql server数据库
- ADO技术连接SQL数据库
- ADO对Sql的存储过程操作
- 数据库及ADO.NET-----存储过程 触发器
- 用ADO.NET访问数据库过程概述
- U盘启动以及int 13h扩展读取U盘内容
- php学习 第十八节
- wordpress登录代码分析
- (独家)cocos2d-x与excel通过xml文件读写双向交互(下)
- openURL的使用(iOS调用系统电话、浏览器、地图、邮件等)
- ADO绑定SQL数据库过程
- MFC消息的分类
- GIS算法的一点理解
- php学习 第十九节
- js获取时间
- 第四次周总结---在路上
- A+B Problem III
- Vss与Svn 的对比
- php学习 第二十节