WinForm Page Control update integrated SQLite database paging

In WinForm development, has been inseparable from the paging processing, good pagination control package, can save a lot of time and tedious work for the development of pagination control has been improved and improved, but also one of my interests. Pagination controls have always had minor updates, but they are basically enough to satisfy most usage scenarios. One day, a friend told me: Our project is using the SQLite database to do the project, this paging control can support the implementation of paging? Because paging controls do not directly access data, you need to construct different paging statements based on different databases, because you have heard of it before but have not studied the SQLite database, and of course there is no support for it.

Since the friend needs, it has to study, and provide relevant support, after a little modification, you can complete the content of the SQLite paging support. Before giving the relevant pagination implementation examples, let's explain why projects use SQLite rather than Access databases, and they have those characteristics.

1. Access Features

When we do small projects in particular, the smaller MIS system will also use the database to save data, most of the small system is used Access database, access is really very convenient, but also convenient for data management and maintenance advantages, copy the past can be used. But the Access database itself also has a lot of problems: the performance is not safe, users can open the database file directly with access to modify data, even after encryption can be directly cracked; Access has a large amount of data, the size of the file itself is extremely large ; Access database files are easily corrupted, and so on.

2, SQLite characteristics

SQLite is a small C program library, the implementation of a stand-alone, embeddable, 0 configuration of the SQL database engine, SQLite used a very extensive, web applications are using it, PHP5 built-in SQLite extensions, so SQLite is desktop lightweight database preferred.

The

transaction operations are atomic, consistent, isolated, and persistent (ACID) even after a system crash and power failure.
• 0 configuration-no installation and administration is required. The
• implements the vast majority of SQL92 standards.
• The entire database is stored in a single file. The
• database files can be freely shared between machines of different byte sequences.
• supports databases up to 2T maximum. (241 bytes)
  The
• string and blob type have a maximum size of up to 2G bytes (231 bytes).
• Small code: a fully configured less than 250KB, ignoring some optional features of less than 150KB.
• is faster than the popular client/server database engine for most common operations.
• A simple, Easy-to-use API. The TCL binding is built in
• . Also provides bindings that can be used in many other languages. The
• has good annotated source code, 95% tested.
• Standalone: no external dependencies. The
• Source code is in a public domain. Can be used for any purpose.

Friend Introduction Operation SQLite Use tool sqlitespy, in fact, this tool does not support the introduction of databases (such as Access database), accidentally found that there are sqlite developer such a tool, management is very convenient, the operation diagram as shown below, the use of discovery is more rich, Support for direct modifications to table field definitions, editing data, SQL queries, creating, compressing, backing up databases, and so on, is also good for Chinese support.

After the introduction of some sqlite, continue to explain how pagination controls implement SQLite paging.

The normal version of the paging control rendering effect is shown below.

The DevExpress style version's paging control effect is as follows (all support SQLite paging)

The example code for pagination controls based on the Sqllite database is as follows:

The code is as follows

Copy Code

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;//Display line number this.winGridViewPager1.PagerInfo.PageSize = 20;//Page size This.winGridViewPager1.EventRowBackColor = color.lightcyan;//interval color Binddata (); } private void Wingridviewpager1_onrefresh (object sender, EventArgs e) { Binddata (); } private void Wingridviewpager1_ondeleteselected (object sender, EventArgs e) { if (messageutil.showyesnoandtips) (Are you sure you deleted the selected records?) ") = = 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 Query Auxiliary functions <summary> Executes the SQL query statement, returning the first field of all records for the query result, separated by commas. </summary> <param name= "SQL" >sql statement </param> <returns> Returns the first field of all records for the query result, separated by commas. </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> Executes a SQL query statement that returns a DataTable collection of all records. </summary> <param name= "SQL" >sql query statement </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> Standard Record Query function </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)//To display specific information, you need to set the total number of records DataTable dt = sqltable (datasql); return DT; } <summary> Construct query statements based on query criteria </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 Add alias Resolution The displaycolumns is consistent with the displayed field name or entity attributes, is case insensitive, and sequentially represents the display order, separated by commas or | This.winGridViewPager1.DisplayColumns = "Number,name,type,area,company,address,telephone1,telephone2,telephone3, Telephone4,telephone5,createdate,note,lastupdated "; This.winGridViewPager1.AddColumnAlias ("id", "number"); This.winGridViewPager1.AddColumnAlias ("number", "Customer ID"); This.winGridViewPager1.AddColumnAlias ("name", "Customer Name"); This.winGridViewPager1.AddColumnAlias ("type", "Customer Type"); This.winGridViewPager1.AddColumnAlias ("area", "Customer region"); This.winGridViewPager1.AddColumnAlias ("Company", "Customer Unit"); This.winGridViewPager1.AddColumnAlias ("Address", "customer"); This.winGridViewPager1.AddColumnAlias ("Telephone1", "Telephone 1"); This.winGridViewPager1.AddColumnAlias ("Telephone2", "Telephone 2"); This.winGridViewPager1.AddColumnAlias ("Telephone3", "Telephone 3"); This.winGridViewPager1.AddColumnAlias ("Telephone4", "Telephone 4"); This.winGridViewPager1.AddColumnAlias ("Telephone5", "Telephone 5"); This.winGridViewPager1.AddColumnAlias ("CreateDate", "Account opening date"); This.winGridViewPager1.AddColumnAlias ("shop_id", "Branch ID"); This.winGridViewPager1.AddColumnAlias ("note", "remark"); This.winGridViewPager1.AddColumnAlias ("lastupdated", "Update Date"); #endregion string where = Getsearchsql (); This.winGridViewPager1.DataSource = Findtodatatable (where, this.winGridViewPager1.PagerInfo); } private void btnSearch_Click (object sender, EventArgs e) { Binddata ();         }

The key points of the above examples are 3

1. Database string

The code is as follows	Copy Code
connectionString = string. Format (@ "Data source={0}orderwater.db; version=3; ", Application.startuppath);

2., query data operation

The data query implements the data access operation by invoking the System.Data.SQLite.DLL assembly, as shown below.

The code is as follows	Copy Code
Public DataTable sqltable (String sql) { DataSet ds = new DataSet (); Sqlitedataadapter adpater = new Sqlitedataadapter (sql, connectionString); Adpater. Fill (DS); Return DS. Tables[0];         }

3, SQLite paging statement call generation

Call the built-in class to implement the build of the paging statement, as shown in the following code.

          

code is as follows

copy code

 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);             &NBSP String datasql = helpe R.getpagingsql (WHC. Pager.wincontrol.databasetype.sqlite, false);