Winform分页控件更新之集成Sqlite数据库分页

         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();
        } 

         public DataTable SqlTable( string sql)
        {
            DataSet ds =  new DataSet();
            SQLiteDataAdapter adpater =  new SQLiteDataAdapter(sql, connectionString);
            adpater.Fill(ds);
            
             return ds.Tables[ 0];
        } 

            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);