CREATE PROCEDURE [dbo].[PageTest] @PageSize INT, @PageIndex INT, @TotalCount INT OUTPUT AS BEGIN --总记录数 SELECT @TotalCount=COUNT(1) FROM MF_MO --记录返回(使用动态SQL绕开参数嗅探问题，效率大幅度提升。) DECLARE @SQL NVARCHAR(1000) SET @SQL= 'SELECT TOP ('+CONVERT(VARCHAR(32),@PageSize)+') MO_NO,MRP_NO,QTY,BIL_NO '+ 'FROM MF_MO A '+ 'WHERE NOT EXISTS (SELECT 1 FROM (SELECT TOP ('+CONVERT(VARCHAR(32),(@PageIndex-1)*@PageSize)+') MO_NO FROM MF_MO ORDER BY MO_NO) B WHERE A.MO_NO=B.MO_NO) '+ 'ORDER BY MO_NO' EXEC (@SQL) END
4、新建一个WinForm程序，命名为Main，并拖入一个DataGridView控件及上面新建的PageControl控件，代码如下：
private void Main_Load(object sender, EventArgs e) { pageControl1.PageSize = 20; pageControl1.PageIndex = 0; pageControl1.BindPageEvent += BindPage; pageControl1.SetPage(); } /// <summary> /// 绑定页 /// </summary> /// <param>每页显示记录数</param> /// <param>页序号</param> /// <param>总记录数</param> private void BindPage(int pageSize, int pageIndex, out int totalCount) { SqlConnection conn = null; SqlCommand cmd = null; totalCount = 0; #region 连接数据库测试 try { //数据库连接 conn = new SqlConnection("server=.;database=DB_TEST;Uid=sa;pwd=********;"); conn.Open(); //SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "PageTest"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter[] param = { new SqlParameter("@PageSize",SqlDbType.Int), new SqlParameter("@PageIndex",SqlDbType.Int), new SqlParameter("@TotalCount",SqlDbType.Int) }; param[0].Value = pageSize; param[1].Value = pageIndex; param[2].Direction = ParameterDirection.Output; cmd.Parameters.AddRange(param); //DataTable DataTable dt = new DataTable("MF_MO"); dt.Columns.Add(new DataColumn("MO_NO", typeof(String))); dt.Columns.Add(new DataColumn("MRP_NO", typeof(String))); dt.Columns.Add(new DataColumn("QTY", typeof(Decimal))); dt.Columns.Add(new DataColumn("BIL_NO", typeof(String))); #region 方法一：SqlDataReader SqlDataReader dr = cmd.ExecuteReader(); dt.Load(dr, LoadOption.PreserveChanges); dr.Close(); totalCount = (int)param[2].Value; dataGridView1.DataSource = dt; #endregion #region #方法二：SqlDataAdapter //SqlDataAdapter da = new SqlDataAdapter(); //da.SelectCommand = cmd; //dt.BeginLoadData(); //da.Fill(dt); //dt.EndLoadData(); //totalCount = (int)param[2].Value; //dataGridView1.DataSource = dt; #endregion } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { conn.Close(); cmd.Dispose(); } #endregion }
5、执行程序：
总结
以上所述是小编给大家介绍的DataGridView使用自定义控件实现简单分页功能，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
您可能感兴趣的文章: