Winform分页控件更新之集成Sqlite分页-技术开发专区

Winform分页控件更新之集成Sqlite分页

作者：伍华聪编辑：王玉圆 2011-11-25 00:05 来源：博客

　　基于SqlLite数据库的分页控件使用例子代码如下：

        string connectionString = "";

        public FrmCustomer()
        {
            InitializeComponent();

            connectionString = string.Format(@"Data Source={0}\OrderWater.db;Version=3;", Application.StartupPath);
        }

        private void FrmCustomer_Load(object sender, EventArgs e)
        {
            this.winGridViewPager1.OnPageChanged += new EventHandler(winGridViewPager1_OnPageChanged);
            this.winGridViewPager1.OnStartExport += new EventHandler(winGridViewPager1_OnStartExport);
            this.winGridViewPager1.OnEditSelected += new EventHandler(winGridViewPager1_OnEditSelected);
            this.winGridViewPager1.OnDeleteSelected += new EventHandler(winGridViewPager1_OnDeleteSelected);
            this.winGridViewPager1.OnRefresh += new EventHandler(winGridViewPager1_OnRefresh);
            this.winGridViewPager1.OnAddNew += new EventHandler(winGridViewPager1_OnAddNew);
            this.winGridViewPager1.AppendedMenu = this.contextMenuStrip1;
            this.winGridViewPager1.ShowLineNumber = true;//显示行号
            this.winGridViewPager1.PagerInfo.PageSize = 20;//页面大小
            this.winGridViewPager1.EventRowBackColor = Color.LightCyan;//间隔颜色

            BindData();
        }

        private void winGridViewPager1_OnRefresh(object sender, EventArgs e)
        {
            BindData();
        }

        private void winGridViewPager1_OnDeleteSelected(object sender, EventArgs e)
        {
            if (MessageUtil.ShowYesNoAndTips("您确定删除选定的记录么？") == DialogResult.No)
            {
                return;
            }

            DataGridView grid = this.winGridViewPager1.dataGridView1;
            if (grid != null)
            {
                foreach (DataGridViewRow row in grid.SelectedRows)
                {
                    //BLLFactory<Customer>.Instance.Delete(row.Cells[0].Value.ToString());
                }
                BindData();
            }
        }

        private void winGridViewPager1_OnEditSelected(object sender, EventArgs e)
        {
            DataGridView grid = this.winGridViewPager1.dataGridView1;
            if (grid != null)
            {
                foreach (DataGridViewRow row in grid.SelectedRows)
                {
                    FrmEditCustomer dlg = new FrmEditCustomer();
                    dlg.ID = row.Cells[0].Value.ToString();
                    if (DialogResult.OK == dlg.ShowDialog())
                    {
                        BindData();
                    }

                    break;
                }
            }
        }

        private void winGridViewPager1_OnStartExport(object sender, EventArgs e)
        {
            string where = GetSearchSql();
            PagerInfo info = new PagerInfo();
            info.CurrenetPageIndex = 1;
            info.PageSize = int.MaxValue;
            this.winGridViewPager1.AllToExport = FindToDataTable(where, info);
        }

        private void winGridViewPager1_OnPageChanged(object sender, EventArgs e)
        {
            BindData();
        }

        #region 查询辅助函数

        /// <summary>
        /// 执行SQL查询语句，返回查询结果的所有记录的第一个字段,用逗号分隔。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>
        /// 返回查询结果的所有记录的第一个字段,用逗号分隔。
        /// </returns>
        public string SqlValueList(string sql)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand(sql, connection);

            connection.Open();
            StringBuilder result = new StringBuilder();
            using (SQLiteDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    result.AppendFormat("{0},", dr[0].ToString());
                }
            }

            string strResult = result.ToString().Trim(',');
            return strResult;
        }

        /// <summary>
        /// 执行SQL查询语句，返回所有记录的DataTable集合。
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataTable SqlTable(string sql)
        {
            DataSet ds = new DataSet();
            SQLiteDataAdapter adpater = new SQLiteDataAdapter(sql, connectionString);
            adpater.Fill(ds);

            return ds.Tables[0];
        }

        /// <summary>
        /// 标准的记录查询函数
        /// </summary>
        /// <param name="where"></param>
        /// <param name="pagerInfo"></param>
        /// <returns></returns>
        private DataTable FindToDataTable(string where, PagerInfo pagerInfo)
        {
            WHC.Pager.WinControl.PagerHelper helper = new WHC.Pager.WinControl.PagerHelper("All_Customer", "*", "LastUpdated", pagerInfo.PageSize, pagerInfo.CurrenetPageIndex, true, where);
            string countSql = helper.GetPagingSql(WHC.Pager.WinControl.DatabaseType.SQLite, true);
            string dataSql = helper.GetPagingSql(WHC.Pager.WinControl.DatabaseType.SQLite, false);

            string value = SqlValueList(countSql);
            pagerInfo.RecordCount = Convert.ToInt32(value);//为了显示具体的信息，需要设置总记录数
            DataTable dt = SqlTable(dataSql);
            return dt;
        }

        /// <summary>
        /// 根据查询条件构造查询语句
        /// </summary>
        /// <returns></returns>
        private string GetSearchSql()
        {
            SearchCondition condition = new SearchCondition();
            condition.AddCondition("Number", this.txtNumber.Text, SqlOperator.Like)
                .AddCondition("Name", this.txtName.Text, SqlOperator.Like)
                .AddCondition("Type", this.cmbType.Text, SqlOperator.Like)
                .AddCondition("Area", this.cmbArea.Text, SqlOperator.Like)
                .AddCondition("Address", this.txtAddress.Text, SqlOperator.Like)
                .AddCondition("Company", this.txtCompany.Text, SqlOperator.Like)
                .AddCondition("Note", this.txtNote.Text, SqlOperator.Like)
                .AddCondition("Telephone1", this.txtTelephone.Text, SqlOperator.Like, true, "Telephone")
                .AddCondition("Telephone2", this.txtTelephone.Text, SqlOperator.Like, true, "Telephone")
                .AddCondition("Telephone3", this.txtTelephone.Text, SqlOperator.Like, true, "Telephone")
                .AddCondition("Telephone4", this.txtTelephone.Text, SqlOperator.Like, true, "Telephone")
                .AddCondition("Telephone5", this.txtTelephone.Text, SqlOperator.Like, true, "Telephone");

            if (chkUseDate.Checked)
            {
                condition.AddCondition("CreateDate", dateTimePicker1.Value.ToString("yyyy-MM-dd"), SqlOperator.MoreThanOrEqual, true)
                    .AddCondition("CreateDate", dateTimePicker2.Value.AddDays(1).ToString("yyyy-MM-dd"), SqlOperator.LessThanOrEqual, true);

            }
            string where = condition.BuildConditionSql().Replace("Where", "");
            return where;
        }
        #endregion

        private void BindData()
        {
            #region 添加别名解析
            //DisplayColumns与显示的字段名或者实体属性一致，大小写不敏感，顺序代表显示顺序，用逗号或者|分开
            this.winGridViewPager1.DisplayColumns = "Number,NAME,type,Area,Company,Address,Telephone1,Telephone2,Telephone3,Telephone4,Telephone5,CreateDate,Note,LastUpdated";
            this.winGridViewPager1.AddColumnAlias("ID", "编号");
            this.winGridViewPager1.AddColumnAlias("Number", "客户编号");
            this.winGridViewPager1.AddColumnAlias("Name", "客户名称");
            this.winGridViewPager1.AddColumnAlias("Type", "客户类型");
            this.winGridViewPager1.AddColumnAlias("Area", "客户地区");
            this.winGridViewPager1.AddColumnAlias("Company", "客户单位");
            this.winGridViewPager1.AddColumnAlias("Address", "客户地址");
            this.winGridViewPager1.AddColumnAlias("Telephone1", "电话1");
            this.winGridViewPager1.AddColumnAlias("Telephone2", "电话2");
            this.winGridViewPager1.AddColumnAlias("Telephone3", "电话3");
            this.winGridViewPager1.AddColumnAlias("Telephone4", "电话4");
            this.winGridViewPager1.AddColumnAlias("Telephone5", "电话5");
            this.winGridViewPager1.AddColumnAlias("CreateDate", "开户日期");
            this.winGridViewPager1.AddColumnAlias("Shop_ID", "分店ID");
            this.winGridViewPager1.AddColumnAlias("Note", "备注");
            this.winGridViewPager1.AddColumnAlias("LastUpdated", "更新日期");

            #endregion

            string where = GetSearchSql();
            this.winGridViewPager1.DataSource = FindToDataTable(where, this.winGridViewPager1.PagerInfo);
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            BindData();
        }

　　以上例子关键的地方有3个

　　1、数据库字符串

　　connectionString = string.Format(@"Data Source={0}\OrderWater.db;Version=3;", Application.StartupPath);

　　2.、查询数据操作

　　数据查询通过调用 System.Data.SQLite.DLL 的程序集实现数据访问操作，如下所示。

        public DataTable SqlTable(string sql)
        {
            DataSet ds = new DataSet();
            SQLiteDataAdapter adpater = new SQLiteDataAdapter(sql, connectionString);
            adpater.Fill(ds);

            return ds.Tables[0];
        }

　　3、 SQLite分页语句调用生成

　　调用内置的类实现分页语句的生成，代码如下所示。

            WHC.Pager.WinControl.PagerHelper helper = new WHC.Pager.WinControl.PagerHelper("All_Customer", "*", "LastUpdated", pagerInfo.PageSize, pagerInfo.CurrenetPageIndex, true, where);
            string countSql = helper.GetPagingSql(WHC.Pager.WinControl.DatabaseType.SQLite, true);
            string dataSql = helper.GetPagingSql(WHC.Pager.WinControl.DatabaseType.SQLite, false);

　　完整可运行例子如下下载即可：

　　http://files.cnblogs.com/wuhuacong/TestPager_SqlLite.rar