C # Desktop Office applications-Payroll management System Series Seven

C # Desktop Office applications-Payroll management System Series Seven

Next to Payroll Management System Series Six, this article describes the query module functionality in C # WinForm applications. Among them, including comprehensive, fuzzy query and paging query, it is worth noting that the comprehensive query, in fact, is a combination of multiple conditions of query, but I changed a not strict address! Combining multiple conditions and using fuzzy Query method to implement query function; For paged query, I think, have been involved in the enterprise projects or some practical projects of Bo friends, are not unfamiliar, like me, I was engaged in Java development, the company's database is MySQL, So I am familiar with the paging query used in the front-end pages of the project. I also write a blog post about our company's projects using the asynchronous request paging query . OK, let's introduce the two small functions below!

For the combination of conditions, fuzzy query, I think, in fact, the core lies in SQL, such as I used in the payroll management system is MS SQL Server, so the query SQL statement is like this:

Select Id,name,address,age from Tb_employee where name "%AA% ' and address like 'BB% '

The above AA and BB are the variables that need to be retrieved from the front-end database field name and address respectively! ---Actually, this is the essence, for the paging query is actually the same (this is what I realized, so that sometimes I would like to find a page plug-in on the Internet, but encountered a lot of trouble, and finally just make a page control of their own!! Hehe, there is no way, "Make me mad is this kind of fate")!

Below, the above a blog introduction of the Payroll Management System Employee Management module For example, below, the implementation of the Staff Management module query function bar! The model of the employee and the database table corresponding to the database have been designed in the previous article!

The first is the design and implementation of the interface:

The content of the rest of the page is already described in the previous article! The following is the Employee Query button event code (where you can also press the "enter" query after entering the information in a query criteria input box, i.e. I have implemented the function of " enter Query ")

        Employee Query Event private void Buttonquery_click (object sender, EventArgs e) {try {                Isbuttonquery = true;                String empname = TextBoxEmpName.Text.Trim ();                String Emppart = TextBoxEmpPart.Text.Trim ();                String Empcardno = TextBoxEmpCardNo.Text.Trim ();                String Empjobtype = TextBoxEmpJobType.Text.Trim ();                String empsex = ComboBoxEmpSex.Text.Trim ();                Cmmpage.pageno = 1;                Cmmpage.pagesize = PageSize; String stremployeeselectsql = "SELECT Top" +cmmpage.pagesize+ "empId as ' employee number ', loginName as ' login user name ', powername as ' user rights ', EM PName as ' employee name ', age as ' ages ', sex as ' gender ', partname as ' affiliation ', Idcardno as ' id ', jobType as ' job type ', jobdate as ' entry time ', Imagepos Ition as ' picture position ', Tb_employee.memo as ' Memo info ' from Tb_employee,tb_powertype,tb_part where tb_employee.powerid=tb_ Powertype.powerid and Tb_employee.partid=tb_part.partid and empname like '% ' +empname+ '% ' and sex like '% ' +empsex+ '% ' and partname like '% ' +emppart+ '% ' and idcardno like '% ' +empcardno+ '% ' and jobType like '% ' +                empjobtype+ "% ' and empId not in (select Top" +cmmpage.start+ "EmpId from Tb_employee Order by empId) Order by empId"; String getquerycountsql = "Select COUNT (*) from Tb_employee,tb_powertype,tb_part where tb_employee.powerid=tb_p Owertype.powerid and Tb_employee.partid=tb_part.partid and empname like '% ' + empname + "% ' and sex like '%" + empsex + "%                ' and partname like '% ' + emppart + '% ' and idcardno like '% ' + empcardno + '% ' and ' jobType ' as '% ' + empjobtype + '% ' ";                RecordCount = Employeeservice.getcount (Getquerycountsql);                PageCount = Commonmessage.gettotalpage (pageSize, RecordCount);                Bindpaginationquery (Stremployeeselectsql,cmmpage,recordcount, PageCount, sender, E);                This.textBoxCurrentPage.Text = "1";            if (DataGridViewEmployeeInfo.Rows.Count = = 0) {        Clearemployeeinfo (); }} catch (System.Exception ex) {MessageBox.Show ("query department error: \ n" +ex.            Message, "Hint", Messageboxbuttons.okcancel, MessageBoxIcon.Information); }}//Enter query private void Textboxempname_keydown (object sender, KeyEventArgs e) {if            (E.keycode = = Keys.enter)            {Buttonquery_click (sender, E); }}//Enter query private void Comboboxempsex_keydown (object sender, KeyEventArgs e) {if            (E.keycode = = Keys.enter)            {Buttonquery_click (sender, E); }}//Enter query private void Textboxemppart_keydown (object sender, KeyEventArgs e) {if            (E.keycode = = Keys.enter)            {Buttonquery_click (sender, E);   }}//Enter query private void Textboxempcardno_keydown (object sender, KeyEventArgs e) {         if (E.keycode = = Keys.enter) {Buttonquery_click (sender, E);            }}//Enter query private void Textboxempjobtype_keydown (object sender, KeyEventArgs e) {            if (E.keycode = = Keys.enter) {Buttonquery_click (sender, E); }        }

The code that corresponds to the Reset query event:

        Reset query condition        private void Buttonreset_click (object sender, EventArgs e)        {            resetqueryinfo ();        }        Reset query Information        private void Resetqueryinfo ()        {            textboxempname.text = "";            Textboxemppart.text = "";            Textboxempcardno.text = "";            Textboxempjobtype.text = "";            Comboboxempsex.text = null;        }

here are the effects:

The following describes the "paging query" function, actually paged query, I think it is not so difficult, its nature I think ultimately due to SQL or JDBC SQL writing: Different databases have different wording, like the MySQL limit, Oracle's RowNumber I've tried, I'm using MS SQL Server, mostly top, and its original paging statement is this:

Top 10: That is to take 10 pieces of data, that is, each page want to display 10 records, and 0 is such a sub-calculated (1-1) *10, the first 1 is the meaning of the page, 10 is the previous 10 records, the result is "data record start number." Principle I do not say much, in addition, it is worth noting that the performance of this method is compared to the bottom! For SQL paging queries on MS SQL Server, you can refer to this blog post: Four ways of implementing SQL Server paging queries .

Well, the effect of paged query, you can look at the picture above!

My implementation is this way, the properties of the pagination are encapsulated into a class, the properties of this class include: Pageno,pagesize,start three properties, as follows (I tested, no problem!) ）

Using system;using system.collections.generic;using system.linq;using system.text;namespace SMS.cmmMessage{    // Paging wrapper class public class    Commonpage    {        private long pageno;        Private long pageSize;        Private long start;        Public long PageNo        {            get {return pageno;}            set {PageNo = value;}        }        Public long PageSize        {            get {return PageSize;}            set {pageSize = value;}        }        Public long Start        {            get {return pageSize * (pageNo-1);}        }        Public Commonpage () {} public        commonpage (Long pageno,long pageSize)        {            This.pageno = PageNo;            This.pagesize = PageSize;}}    }

The following is the self-made pagination control, ugly is ugly point, will point it! (Note: The 1000 above is just a label, not the real data!) And, when you click on "Home", "last", "previous" and "Next", you need to be aware of the relevant actions!

Here is the event code for "Home", "prev", "Next", "Last", "Enter page and jump":

        Control input for "page text box" Only enter the number private void Textboxcurrentpage_keypress (object sender, KeyPressEventArgs e)        {Cmmutils.onlyinputdigitnumber (sender, E); }//The jump of the paging query private void Toolstripbutton1_click (object sender, EventArgs e) {//Get "the first page of the article            The number of the box "String currpage = TextBoxCurrentPage.Text.Trim ();            if (currpage== "") {return;            } int currentpage = Convert.ToInt32 (currpage);            if (currentpage<=0 | | currentpage>pagecount) {return;            } Cmmpage.pageno = CurrentPage;            Cmmpage.pagesize = PageSize;        Paginationfunction (cmmpage, sender, E);            }//Enter the first few pages in the text box return can be paged query private void Textboxcurrentpage_keydown (object sender, KeyEventArgs e) {            if (E.keycode = = Keys.enter) {Toolstripbutton1_click (sender, E); }        }       Home event private void Buttonfirstpage_click (object sender, EventArgs e) {if (pagecount==0)            {return;            } Cmmpage.pageno = 1;            Cmmpage.pagesize = PageSize;        Paginationfunction (cmmpage, sender, E); }//Home (arrow) event private void Tofirstpage_click (object sender, EventArgs e) {Buttonfirstpage_        Click (sender, E); }//Last event private void Buttonlastpage_click (object sender, EventArgs e) {if (PageCount = =            0) {return;            } Cmmpage.pageno = PageCount;            Cmmpage.pagesize = PageSize;        Paginationfunction (cmmpage, sender, E); }//Last (arrow) event private void Tolastpage_click (object sender, EventArgs e) {BUTTONLASTPAGE_CL        Ick (sender, E);    }//prev event private void Buttonprepage_click (object sender, EventArgs e) {        Cmmpage.pageno = cmmpage.pageno-1 <= 0?            Cmmpage.pageno = 1:cmmpage.pageno-= 1;            Cmmpage.pagesize = PageSize;        Paginationfunction (cmmpage, sender, E); }//prev (arrow) event private void Toprepage_click (object sender, EventArgs e) {buttonprepage_cli        CK (sender, E);  }//Next event private void Buttonnextpage_click (object sender, EventArgs e) {Cmmpage.pageno = Cmmpage.pageno >= PageCount?            Cmmpage.pageno = PageCount:cmmPage.PageNo + = 1;            Cmmpage.pagesize = PageSize;        Paginationfunction (cmmpage, sender, E); }//Next page (arrow) event private void Tonextpage_click (object sender, EventArgs e) {Buttonnextpage_c        Lick (sender, E);        }//Paging event: cmmpage-instance of the page wrapper class private void Paginationfunction (Commonpage cmmpage,object sender, EventArgs e)              {try {String employeesql = "";  if (!isbuttonquery) {employeesql = "select top" + cmmpage.pagesize + "EmpId as" employee compilation Number ', loginName as ' login user name ', powername as ' user Rights ', empname as ' employee name ', age as ' old ', sex as ' gender ', partname as ' affiliation ', Idcardno as ' ID number ', jobType as ' job type ', jobdate as ' entry time ', imageposition as ' picture location ', Tb_employee.memo as ' Memo info ' from Tb_employee,tb_powertype, Tb_part where Tb_employee.powerid=tb_powertype.powerid and Tb_employee.partid=tb_part.partid and EmpId not in (select                    Top "+ Cmmpage.start +" empId from Tb_employee Order by empId) Order by empId ";                RecordCount = Employeeservice.getcount ("Select COUNT (*) from Tb_employee");                    } else {String empname = TextBoxEmpName.Text.Trim ();                    String Emppart = TextBoxEmpPart.Text.Trim ();                    String Empcardno = TextBoxEmpCardNo.Text.Trim ();                    String Empjobtype = TextBoxEmpJobType.Text.Trim (); String empsex = ComboBoxEmpSex.Text.Trim (); Employeesql = "SELECT top" + cmmpage.pagesize + "empId as ' employee number ', loginName as ' login user name ', powername as ' user Rights ', empname as ' employee Name ', age as ' old ', sex as ' gender ', partname as ' affiliation ', Idcardno as ' id ', jobType as ' job type ', jobdate as ' entry time ', imageposition as ' picture Location ', Tb_employee.memo as ' Memo info ' from Tb_employee,tb_powertype,tb_part where Tb_employee.powerid=tb_powertype.powerid and Tb_employee.partid=tb_part.partid and empname like '% ' + empname + '% ' and sex like '% ' + empsex + '% ' and PartName Li Ke '% ' + emppart + '% ' and idcardno like '% ' + empcardno + '% ' and jobType like '% ' + empjobtype + '% ' and empId not in (s                    Elect Top "+ Cmmpage.start +" empId from Tb_employee Order by empId) Order by empId "; String getquerycountsql = "Select COUNT (*) from Tb_employee,tb_powertype,tb_part where tb_employee.powerid=tb_ Powertype.powerid and Tb_employee.partid=tb_part.partid and empname like '% ' + empname + '% ' and sex like '% ' + empsex +'% ' and partname like '% ' + emppart + '% ' and idcardno like '% ' + empcardno + '% ' and ' jobType like '% ' + empjobtype + '% ' "                    ;                RecordCount = Employeeservice.getcount (Getquerycountsql);                } PageCount = Commonmessage.gettotalpage (cmmpage.pagesize, RecordCount);            Bindpaginationquery (Employeesql,cmmpage,recordcount,pagecount, sender, E); } catch (System.Exception ex) {MessageBox.Show ("Paged Query employee information error: \ n" + ex.)            Message);  }}//The function that binds the paging query private void Bindpaginationquery (String sql,commonpage cmmpage,long Totalrecord, long Totalpage, object sender, EventArgs e) {Employeeservice.bindsqlresulttodatagridview (Datagridviewemploy            Eeinfo, SQL);            This.labelTotalRecordCount.Text = Totalrecord.tostring ();            This.labelPageCount.Text = Totalpage.tostring (); This.labelCurrentPageRecordTotal.Text = DatagridviewemPloyeeInfo.Rows.Count.ToString ();        This.textBoxCurrentPage.Text = CmmPage.PageNo.ToString (); }

here are the effects:

(1) The following is the initialization of loading data, the default is required to have paging data, I am here 10! You can also see how many data are in total, how many data are currently there, and so on (this is my own idea!). ）

(2) Click on the previous page: will be automatically judged, if it is already home, it shows the home page! Click on the next page:

It is worth noting that the implementation of paging query function must be combined with comprehensive fuzzy query, in the Enterprise project, also the fact that!

Well, the blog is introduced here, want to communicate with me, you can leave a message below!

C # Desktop Office applications-Payroll management System Series Seven