winform DataGridView+Sqlserver2005 存储过程分页控件
C#控件代码
SQLSever2005分页控件代码
Winform页面调用控件的代码
主要代码下载:https://files.cnblogs.com/qiaojun/DataGridView%E5%88%86%E9%A1%B5%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E4%B8%BB%E8%A6%81%E4%BB%A3%E7%A0%81.zip
using System; using System.Collections.Generic; using System.ComponentModel; using System.Drawing; using System.Data; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.Threading; namespace GpsControl { public partial class UCPageTableProc : UserControl { public UCPageTableProc() { InitializeComponent(); } public EtyPager etyPager = null; /// <summary> /// 初始化分页控件 /// </summary> public void InitControl() { if (DataGrid == null) { MessageBox.Show("您未设置参数DataGridView", "消息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } BindData(); if (dataTable == null) { return; } if (etyPager.TotalRecord == 0) { return; } // Even if there is no records, there is no exception BindSource(DataGrid); toolStripTextBoxPageSize.Text = etyPager.PageSize.ToString(); // Set the status of the BindingNavigator control if (etyPager.PageCount == 0 || etyPager.PageCount == 1) { bindngrDemo.MoveFirstItem.Enabled = false; bindngrDemo.MoveLastItem.Enabled = false; bindngrDemo.MoveNextItem.Enabled = false; bindngrDemo.MovePreviousItem.Enabled = false; } else { bindngrDemo.MoveFirstItem.Enabled = false; bindngrDemo.MoveLastItem.Enabled = true; bindngrDemo.MoveNextItem.Enabled = true; bindngrDemo.MovePreviousItem.Enabled = false; bindngrDemo.PositionItem.Text = "1"; bindngrDemo.CountItem.Text = "of {" + etyPager.PageCount.ToString() + "}"; } } private void BindData() { if (etyPager == null) return; Guanzhou.SQLServer.AdoHelper ado = Guanzhou.SQLServer.AdoHelper.CreateHelper(Guanzhou.SQLServer.AdoHelper.oldassembly, Guanzhou.SQLServer.AdoHelper.oldtype); List<SqlParameter> listParameter = new List<SqlParameter>(); SqlParameter parameter = new SqlParameter("@Tablename", etyPager.TableName); parameter.SqlDbType = SqlDbType.VarChar; parameter.Size = 50; listParameter.Add(parameter); parameter = new SqlParameter("@ReFieldsStr", etyPager.ReFieldsStr); parameter.SqlDbType = SqlDbType.VarChar; parameter.Size = 200; listParameter.Add(parameter); parameter = new SqlParameter("@OrderString", etyPager.OrderString); parameter.SqlDbType = SqlDbType.VarChar; parameter.Size = 200; listParameter.Add(parameter); parameter = new SqlParameter("@WhereString", etyPager.WhereString); parameter.SqlDbType = SqlDbType.VarChar; parameter.Size = 500; listParameter.Add(parameter); parameter = new SqlParameter("@PageSize", etyPager.PageSize); parameter.SqlDbType = SqlDbType.Int; parameter.Size = 4; listParameter.Add(parameter); parameter = new SqlParameter("@PageIndex", etyPager.PageIndex); parameter.SqlDbType = SqlDbType.Int; parameter.Size = 4; listParameter.Add(parameter); SqlParameter pPageIndex = new SqlParameter(); pPageIndex.ParameterName = "@TotalRecord"; pPageIndex.SqlDbType = SqlDbType.Int; pPageIndex.Direction = ParameterDirection.Output; listParameter.Add(pPageIndex); DataSet ds = ado.ExecuteDataset(Guanzhou.SQLServer.AdoHelper.sqlconnection, CommandType.StoredProcedure, "Sp_Page", listParameter.ToArray()); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0] == null) return; dataTable = ds.Tables[0]; etyPager.TotalRecord = (int)pPageIndex.Value; ds.Dispose(); } /// <summary> /// 加载数据 /// </summary> /// <param name="pageIndex"></param> /// <param name="dataGridViewDemo"></param> private void BindSource(DataGridView dataGridViewDemo) { BindData(); dataGridViewDemo.DataSource = dataTable; dataGridViewDemo.Refresh(); } /// <summary> /// 数据源 /// </summary> private DataTable dataTable; /// <summary> /// 要操作的DataGridView /// </summary> public DataGridView DataGrid; #region 事件和处理 private void bindingNavigatorMoveNextItem_Click(object sender, EventArgs e) { int currentpageindex = Convert.ToInt32(bindngrDemo.PositionItem.Text); if (currentpageindex < etyPager.PageCount) { etyPager.PageIndex = currentpageindex + 1; bindngrDemo.PositionItem.Text = etyPager.PageIndex.ToString(); BindSource(DataGrid); if (etyPager.PageIndex == etyPager.PageCount) { bindngrDemo.MoveNextItem.Enabled = false; bindngrDemo.MoveLastItem.Enabled = false; } if (etyPager.PageIndex >= 2) { bindngrDemo.MovePreviousItem.Enabled = true; bindngrDemo.MoveFirstItem.Enabled = true; } } else { MessageBox.Show("已经是最后一页", "消息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } private void bindingNavigatorMovePreviousItem_Click(object sender, EventArgs e) { int currentpageindex = Convert.ToInt32(bindngrDemo.PositionItem.Text); if (currentpageindex >= 2) { etyPager.PageIndex = currentpageindex - 1; bindngrDemo.PositionItem.Text = etyPager.PageIndex.ToString(); BindSource(DataGrid); if (etyPager.PageIndex == 1) { bindngrDemo.MovePreviousItem.Enabled = false; bindngrDemo.MoveFirstItem.Enabled = false; } if (etyPager.PageIndex <= etyPager.PageCount) { bindngrDemo.MoveNextItem.Enabled = true; bindngrDemo.MoveLastItem.Enabled = true; } } else { MessageBox.Show("已经是第一页", "消息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } private void bindingNavigatorMoveLastItem_Click(object sender, EventArgs e) { etyPager.PageIndex = etyPager.PageCount; BindSource(DataGrid); bindngrDemo.PositionItem.Text = etyPager.PageCount.ToString(); bindngrDemo.MoveLastItem.Enabled = false; bindngrDemo.MoveNextItem.Enabled = false; bindngrDemo.MovePreviousItem.Enabled = true; bindngrDemo.MoveFirstItem.Enabled = true; } private void bindingNavigatorMoveFirstItem_Click(object sender, EventArgs e) { etyPager.PageIndex = 1; BindSource(DataGrid); bindngrDemo.PositionItem.Text = "1"; bindngrDemo.MoveFirstItem.Enabled = false; bindngrDemo.MovePreviousItem.Enabled = false; bindngrDemo.MoveNextItem.Enabled = true; bindngrDemo.MoveLastItem.Enabled = true; } #endregion private void ribbonMenuButton_PageSize_Click(object sender, EventArgs e) { if (String.IsNullOrEmpty(toolStripTextBoxPageSize.Text)) { MessageBox.Show("页数不能为空。", "消息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } try { etyPager.PageSize = Convert.ToInt32(toolStripTextBoxPageSize.Text); } catch { MessageBox.Show("页数不是有效数字。", "消息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } bindngrDemo.PositionItem.Text = "1"; bindingNavigatorMoveFirstItem_Click(sender, e); bindngrDemo.CountItem.Text = "of " + etyPager.PageCount.ToString(); if (etyPager.PageSize >= etyPager.TotalRecord) { bindngrDemo.MoveNextItem.Enabled = false; bindngrDemo.MoveLastItem.Enabled = false; } } private void ribbonMenuButton_PageIndex_Click(object sender, EventArgs e) { if (String.IsNullOrEmpty(toolStripTextBoxPageIndex.Text)) { MessageBox.Show("页码不能为空。", "消息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } int currentPageIndex = 1; try { currentPageIndex = Convert.ToInt32(toolStripTextBoxPageIndex.Text); } catch { MessageBox.Show("页码不是有效数字。", "消息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (currentPageIndex <= 1) { bindingNavigatorMoveFirstItem_Click(sender, e); return; } else if (currentPageIndex >= etyPager.PageCount) { bindingNavigatorMoveLastItem_Click(sender, e); return; } etyPager.PageIndex = currentPageIndex; bindngrDemo.MoveFirstItem.Enabled = true; bindngrDemo.MovePreviousItem.Enabled = true; bindngrDemo.MoveNextItem.Enabled = true; bindngrDemo.MoveLastItem.Enabled = true; bindngrDemo.PositionItem.Text = currentPageIndex.ToString(); BindSource(DataGrid); } } }
if exists(Select name from sysobjects where name='Sp_Page') drop proc Sp_Page go Create PROCEDURE Sp_Page ( @TableName varchar(50), --表名 @ReFieldsStr varchar(200) = '*', --字段名(全部字段为*) @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N'', --条件语句(不用加where) @PageSize int, --每页多少条记录 @PageIndex int = 1 , --指定当前为第几页 @TotalRecord int output --返回总记录数 ) AS BEGIN --处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar(500); Declare @SqlString nvarchar(2000); set @StartRecord = (@PageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句 -- IF (@WhereString! = '' or @WhereString!=null) BEGIN SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString; SET @SqlString =@SqlString+ ' where '+ @WhereString; END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord)); Exec(@SqlString) END go
private void FormDGMore_Load(object sender, EventArgs e) { Guanzhou.SQLServer.AdoHelper.sqlconnection = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"; GpsControl.EtyPager ety = new GpsControl.EtyPager(); ety.OrderString = "ID"; ety.PageIndex = 1; ety.PageSize = 10; ety.ReFieldsStr = "*"; ety.TableName = "qiaojun"; ety.WhereString = ""; ucPageTableProc1.DataGrid = dgMore; ucPageTableProc1.etyPager = ety; ucPageTableProc1.InitControl(); }