经典存储过程，（.NET存储过程分页）
|
create proc proUserInfo
as select * from T_UserInfo; go exec proUserInfo --创建带输入参数的存储过程 create proc proUserInfo1 @name varchar(20) as select * from T_UserInfo WHERE Name=@name go --调用存储过程 exec proUserInfo1 '郭阳' --创建带2个输入参数的存储过程 create proc proUserInfo2 @name varchar(20), @age int as select * from T_UserInfo where Name=@name or Age=@age go exec proUserInfo2 '李哲',18 --带输出参数的存储过程 create proc proUserInfo3 @name varchar(20), @age int output as select @age=age from T_UserInfo WHERE Name=@name GO begin declare @age int exec proUserInfo3 '李哲',@age output select @age end ---------------------分页 select * from (select ROW_NUMBER() over (order by UserId)as ?rownum,* from T_UserInfo) t where rownum>3 and rownum<=6 ?-------------------存储过程分页 ? create proc profenye ?@pagesize int, ?@pageindex int ?as ?select * from (select ROW_NUMBER() over(order by UserId)as ?rownum,* from T_UserInfo)t where ?rownum>(@pageindex-1)*@pagesize ?and ?rownum<=@pageindex*@pagesize ?go ?--调用 ?exec profenye 3,1 --索引 删除重复数据的方法： ? select distinct * into newtable from T_GRDA ?--删除原表 ?delete from T_GRDA WHERE 1=1 ?--将临时表数据导入到原表中 ?insert into T_GRDA ?SELECT * FROM newtable ?//删除临时表 ?drop table newtable ------------------------------------------------------------ public partial class 存储过程分页 : Form ??? { ??????? int pagesize = 4; ??????? int pageindex = 1; ??????? int recordcount = 0; ??????? int totalsize = 0; ??????? public 存储过程分页() ??????? { ??????????? InitializeComponent(); ??????? } ??????? private void btnFirst_Click(object sender,EventArgs e) ??????? { ??????????? pageindex = 1; ??????????? DataBinds(pageindex); ??????? } ??????? private void btnPre_Click(object sender,EventArgs e) ??????? { ??????????? if (pageindex > 1) ??????????? { ??????????????? pageindex--; ??????????? } ?????????? ? ??????????? DataBinds(pageindex); ??????? } ??????? private void btnNext_Click(object sender,EventArgs e) ??????? { ??????????? if (pageindex < totalsize) ??????????? { ??????????????? pageindex++; ??????????? }?????????? ? ??????????? DataBinds(pageindex); ??????? } ??????? private void btnLast_Click(object sender,EventArgs e) ??????? { ??????????? DataBinds(totalsize); ??????? } ??????? private void 存储过程分页_Load(object sender,EventArgs e) ??????? { ??????????? recordcount= GetCount(); ??????????? if (recordcount % pagesize != 0) ??????????? { ??????????????? totalsize = recordcount / pagesize + 1; ??????????? } ??????????? else ??????????? { ??????????????? totalsize = recordcount / pagesize; ??????????? } ??????????? DataBinds(pageindex); ??????? } ??????? private void DataBinds(int pageindex) ??????? { ??????????? SqlConnection conn = new SqlConnection(@"Data Source=YHB-PCSQLSERVER2008;Initial Catalog=MyFirst;Persist Security Info=True;User ID=sa;Password=yhb@163"); ??????????? SqlCommand cmd = new SqlCommand(); ??????????? cmd.Connection = conn; ??????????? conn.Open(); ??????????? cmd.CommandText = "profenye"; ??????????? cmd.CommandType = CommandType.StoredProcedure; ??????????? cmd.Parameters.AddWithValue("@pageindex",pageindex); ??????????? cmd.Parameters.AddWithValue("@pagesize",pagesize); ??????????? SqlDataAdapter adapter = new SqlDataAdapter(cmd); ??????????? DataTable dt = new DataTable(); ??????????? adapter.Fill(dt); ??????????? this.dataGridView1.DataSource = dt; ??????????? conn.Close(); ??????????? conn.Dispose(); ??????? } ??????? //获取记录数量 ??????? private int GetCount() ??????? { ??????????? SqlConnection conn = new SqlConnection(@"Data Source=YHB-PCSQLSERVER2008;Initial Catalog=MyFirst;Persist Security Info=True;User ID=sa;Password=yhb@163"); ??????????? SqlCommand cmd = new SqlCommand(); ??????????? cmd.Connection = conn; ??????????? conn.Open(); ??????????? cmd.CommandText = "select count(*) from T_UserInfo"; ??????????? int count=Convert.ToInt32(cmd.ExecuteScalar()); ??????????? cmd.Dispose(); ??????????? conn.Close(); ??????????? conn.Dispose(); ??????????? return count; ??????? } ??? } （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |