Combined Query of Data room charging systems-inheritance of forms and Combined Query of charging systems

Combined Query of Data room charging systems-inheritance of forms and Combined Query of charging systems

Do you still remember the combined query function when I first performed the data center? At that time, I started to judge one by one. At the beginning, the link box and the condition box behind it were not available. I decided whether the link box could be used based on the condition box filling conditions. This step by step ......

After the second data center reconstruction, I found that a lot of code was repeated and I did so much useless work! As a good programmer, we should try to reduce the amount of our code so that our code can be reused. Here we need to understand that reuse is not a copy!

In the IDC room, the four forms of basic student information maintenance, computer status viewing, computer information statistics, and operator work records are almost the same except for field names, therefore, we used form inheritance when performing the combined query function.

The inheritance of the form is to write the public part in the parent form. If there is any inconsistency, you can write a virtual method, and then ask the subclass to rewrite the virtual method. In this way, we only need a U layer, a B layer, an interface, and an abstract factory. A D layer can implement the functions of the four forms.

The U layer is mainly used to determine whether the input boxes and selection boxes are empty, pass values to the object parameters and B-layer methods, define the virtual method for converting database fields and the virtual method for obtaining table names.

<Span style = "font-size: 18px; "> /************************************* * *********** 'Author: yu 'Team: 'Description: Combined Query template' created on: 5.5.8.9' version: '*************************************** */Imports System. windows. formsPublic Class frmGroupQuery defines a variable of the Protection Type and can be accessed through a subform. The following is another way to write 'protected groupcheck As JFEntity. groupEntity = New JFEntity. groupEntity () Protected groupcheck As New JFEntity. groupEntity Private Sub frmGroupQu Ery_Load (sender As Object, e As EventArgs) Handles MyBase. load 'operator part. Because the operator remains unchanged, you can Load cmbOperator1.Items in the parent form. add (">") cmbOperator1.Items. add ("<") cmbOperator1.Items. add ("=") cmbOperator1.Items. add ("<>") cmbOperator2.Items. add (">") cmbOperator2.Items. add ("<") cmbOperator2.Items. add ("=") cmbOperator2.Items. add ("<>") cmbOperator3.Items. add (">") cmbOperator3.Items. add ("<") cmbOperator3.Items. add ("=") cm BOperator3.Items. add ("<>") 'link cmbRelations1.Items. add ("and") cmbRelations1.Items. add ("or") cmbRelations2.Items. add ("and") cmbRelations2.Items. add ("or") 'is selected. The datagridview control selects the row DataGridView1.SelectionMode = DataGridViewSelectionMode. fullRowSelect Dim I As Integer For I = 0 To DataGridView1.Columns. count-1 maid (I ). width = maid. allCells Next End Sub ''' <summary> '''Clear query conditions and results ''' </summary> ''' <param name = "sender"> </param> ''' <param name = "e "> </param> ''' <remarks> </remarks> Private Sub btnclear_Click (sender As Object, e As EventArgs) Handles btnclear. click cmbFiled1.Text = "" cmbFiled2.Text = "" cmbFiled3.Text = "" character = "" txtContent1.Text = "" txtContent2.Text = "" txtContent3.Text = "cmb" Relations1.Text = "" cmbRelations2.Text = "" DataGridView1.DataSource = "" End Sub ''' <summary> ''' exit ''' </summary> ''' <param name = "sender "> </param> ''' <param name =" e "> </param> ''' <remarks> </remarks> Private Sub btncancel_Click (sender As Object, e As EventArgs) Handles btncancel. click Me. dispose () End Sub Private Sub btninquire_Click (sender As Object, e As EventArgs) Handles btninquire. click 'Judge that the combo box is not empty If cmbRelations1.Text = "" then' If the first combo box is empty If cmbFiled1.Text = "Or cmbOperator1.Text =" "Or txtContent1.Text =" "Then MsgBox (" the first row query condition cannot be blank! "," Prompt ") exit Sub End If cmbRelations1.Text <> "" then' If the first composite relation is selected If cmbFiled2.Text = "Or cmbOperator2.Text =" Or txtContent2.Text = "" Then MsgBox (" the query condition for the second row cannot be blank! "," Prompt ") exit Sub End If Else If cmbRelations2.Text <> "" Then "selects the second composite relationship If cmbFiled1.Text =" "Or cmbOperator1.Text =" "Or txtContent1.Text =" "Or cmbFiled2.Text =" Or cmbFiled2.Text = "" Or txtContent2.Text = "" Or cmbFiled3.Text = "" Or cmbFiled3.Text = "" Or txtContent3.Text = "" Then MsgBox ("Row 3 query condition cannot be blank ",, "prompt") Exit Sub End If 'assigns groupcheck to the object. getTable = GetTable () gro Upcheck. cmbField1 = GetDBName (cmbFiled1.Text. trim () groupcheck. cmbField2 = GetDBName (cmbFiled2.Text. trim () groupcheck. cmbField3 = GetDBName (cmbFiled3.Text. trim () groupcheck. cmbOperator1 = cmbOperator1.Text. trim () groupcheck. cmbOperator2 = cmbOperator2.Text. trim () groupcheck. cmbOperator3 = cmbOperator3.Text. trim () groupcheck.txt Content1 = txtContent1.Text. trim () groupcheck.txt Content2 = txtConte Nt2.Text. trim () groupcheck.txt Content3 = txtContent3.Text. trim () groupcheck. cmbRelation1 = GetDBName (cmbRelations1.Text. trim () groupcheck. cmbRelation2 = GetDBName (cmbRelations2.Text. trim () 'transmits the Dim dt As New DataTable Dim Ugroupcheck As New Facade to the appearance layer method. groupCheckFacade dt = Ugroupcheck. FGroupCheck (groupcheck) If (dt. rows. count = 0) Then MsgBox ("no matching record exists! Set query conditions again! "," Prompt ") maid = Nothing Else 'maid 1. dataSource = dt Call Todatagridview () End If End Sub 'when the content of the first composite relationship box changes, determine whether the Private Sub cmbRelations1_SelectedIndexChanged (sender As Object, e As EventArgs) handles export cmbFiled2.Enabled = True cmbOperator2.Enabled = True txtContent2.Enabled = True cmbRelations2.Enabled = True End Sub 'when the content of the second combo box changes, the second row query box can be used to determine whether the Private Sub branch (sender As Object, e As EventArgs) Handles handler 'limits the control selection cmbFiled3.Enabled = True cmbOperator3.Enabled = True txtContent3.Enabled = TrueEnd Sub ''' <summary> ''' defines the virtual function GetDBName, obtain the field names of different databases ''' </summary> ''' <returns> </returns> ''' <remarks> </remarks> Public Overridable Function GetDBName (ByVal control string) as String Return "" End Function "defines the virtual Function GetTable and obtains the table name Protected Overridable Function GetTable () of different databases () as String Return "" End Function ''' <summary> ''': displays the table in the dview ''' </summary> ''' <remarks> </remarks> Protected overridable Sub Todatagridview () dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode. allCells End Sub ''' <summary> ''' calls the method in the module, export the Excel table ''' </summary> ''' <param name = "sender"> </param> ''' <param name = "e"> </param> ''' <remarks> </remarks> Private Sub groupbox‑enter (sender As Object, e As EventArgs) Call ExportExcel (DataGridView1) End SubPrivate Sub btnExcel_Click (sender As Object, e As EventArgs) Handles btnExcel. click Call ExportExcel (DataGridView1) End Sub </span>

Layer B

<Span style = "font-size: 18px; "> '/************************************ * *** 'Class Name: groupCheckBLLvb 'namespace: jfbll' Creation Time: 16:46:57 'Author: xiaoyu' group: 'modification time: 'modifier:' version number: v1.0.0 '************************************** * *** Imports IDALImports JFEntityPublic Class GroupCheckBLLvb ''' <summary> implements the query by using the ''' combination of factory and interface methods, parent form ''' </summary> ''' <param name = "group"> </param> ''' <returns> </returns> ''' <remarks> </remarks> Public Function GroupCheck (ByVal group As JFEntity. groupEntity) As DataTable Dim Igroupcheck As IDAL. IGroupCheckDAL Dim table As New DataTable Igroupcheck = Factory. loginFactory. groupCheck table = Igroupcheck. IGroupCheck (group) If table. rows. count = 0 Then 'Return Nothing 'Else Return table End If Return table End FunctionEnd Class </span>

Layer D:

<Span style = "font-size: 18px; "> '/************************************ * *** 'Class Name: sqlGroupCheckDAL 'namespace: jfdal' Creation Time: 16:42:43 'Author: Xiaoyu 'group: 'modification time:' modifier: 'version number: v1.0.0 '************************************** * *** Imports System. data. sqlClientImports IDALPublic Class SqlGroupCheckDAL: Implements IGroupCheckDAL 'instantiate sqlHelper Private SqlHelper As SQLHelper. sqlHelper = New SQLHelper. sqlHelper () Public Function IGroupCheck (ByVal groupcheck As JFEntity. groupEntity) As DataTable Implements IGroupCheckDAL. IGroupCheck Dim sqlparam As SqlParameter () = {New SqlParameter ("@ cmbFiled1", groupcheck. cmbField1), New SqlParameter ("@ cmbFiled2", groupcheck. cmbField2), New SqlParameter ("@ cmbFiled3", groupcheck. cmbField3), New SqlParameter ("@ cmbOperator1", groupcheck. cmbOperator1), New SqlParameter ("@ cmbOperator2", groupcheck. cmbOperator2), New SqlParameter ("@ cmbOperator3", groupcheck. parameters), New SqlParameter ("@ txtContent1", groupcheck.txt Content1), New SqlParameter ("@ txtContent2", groupcheck.txt Content2), New SqlParameter ("@ txtContent3", groupcheck.txt Content3 ), new SqlParameter ("@ cmbRelation1", groupcheck. cmbRelation1), New SqlParameter ("@ cmbRelation2", groupcheck. cmbRelation2), New SqlParameter ("@ tableName", groupcheck. getTable)} 'sets the parameter Dim strSql As String = "PROC_GroupQuery"' to call the Stored Procedure table = helper. getDataTable (strSQL, CommandType. storedProcedure, prams) Return table End FunctionEnd Class </span>

Stored Procedure:

<Span style = "font-size: 18px; "> -- ============================================ =========-- Author: xiaoyu -- Create date: 2015/7/27 -- Description: composite query -- =============================================== ========= alter procedure [dbo]. [PROC_GroupQuery] -- Add the parameters for the stored procedure here @ cmbFiled1 varchar (10), @ cmbOperator1 varchar (10), @ txtContent1 varchar (10), @ cmbFiled2 varchar (10 ), @ cmbOperator2 varchar (10), @ txtC Ontent2 varchar (10), @ cmbFiled3 varchar (10), @ cmbOperator3 varchar (10), @ txtContent3 varchar (10), @ cmbRelation1 varchar (10 ), @ cmbRelation2 varchar (10), @ tableName varchar (20) AS declare @ TempSql varchar (500) -- temporarily store SQL statements -- BEGIN--SET @ TempSql = 'select * from' + @ tableName + 'where' + @ cmbFiled1 + @ cmbOperator1 + char (39) + @ txtContent1 + char (39) -- if @ cmbRelation1! = ''-- BEGIN-SET @ TempSql = @ TempSql + @ cmbRelation1 + CHAR (32) + @ cmbFiled2 + @ cmbOperator2 + CHAR (39) + @ txtContent2 + CHAR (39) -- if @ cmbRelation2! = ''-- BEGIN-SET @ TempSql = @ TempSql + @ cmbRelation2 + CHAR (32) + @ cmbFiled3 + @ cmbOperator3 + CHAR (39) + @ txtContent3 + char (39) -- end -- EXECUTE (@ TempSql) begin set @ TempSql = 'select * from' + @ tableName + 'where' + @ cmbFiled1 + @ cmbOperator1 + char (39) + @ txtContent1 + char (39) if (@ cmbRelation1! = '') Begin set @ TempSql = @ TempSql + @ cmbRelation1 + CHAR (32) + @ cmbFiled2 + @ cmbOperator2 + CHAR (39) + @ txtContent2 + CHAR (39) if (@ cmbRelation2 is not null) begin set @ TempSql = @ TempSql + @ cmbRelation2 + CHAR (32) + @ cmbFiled3 + @ cmbOperator3 + CHAR (39) + @ txtContent3 + CHAR (39) end execute (@ TempSql) END </span>

Abstract Factory

 

<Span style = "font-size: 18px; "> '/************************************ * *** 'Class Name: loginFactory 'namespace: Factory 'Creation Time: 16:18:59' Author: Xiaoyu 'group: 'modification time: 'modifier: 'version number: v1.0.0 '************************************** * *** Imports System. configuration 'Add a reference to the Configuration file Imports System. reflection 'add the application Imports IDALImports System for Reflection. data ''' <summary> ''' instantiate the GroupCheck table class ''' </summary> ''' <returns> </returns> ''' <remarks> </remarks> Public Shared Function GroupCheck () as IGroupCheckDAL Dim className As String = AssemblyName + ". "+ db +" GroupCheckDAL "Dim Igroupcheck As IDAL. IGroupCheckDAL Igroupcheck = CType (Assembly. load (AssemblyName ). createInstance (className), IGroupCheckDAL) Return Igroupcheck End Function </span>

After editing the parent form, add the child form that we inherit.

Step 1: add an inherited form:

Step 2: select the inherited form:

Finally, rewrite the virtual method of the parent class in the subclass:

<Span style = "font-size: 18px; "> /************************************* * *********** 'Author: yu 'Team: 'Description: Combined Query template' created on: 5.5.8.9' version: '*************************************** * ******/Public Class frmStuInfo 'override the method for converting to a database Field Public Overrides Function GetDBName (control As String) as String Select Case (control) case "card" Return "cardID" Case "student ID" Return "studentID" Case "name" Return "studentName" Case "gender" Return "sex" Case "don't" Return "department" case "grade" Return "grade" Case "class" Return "class" Case "and" Return "and" Case "or" Return "or" Case Else Return "" End Select End function 'Restore table name methodprotected Overrides Function GetTable () as String groupcheck. getTable = "Student_Info" Return groupcheck. getTable End Function Private Sub frmStuInfo_Load (sender As Object, e As EventArgs) Handles MyBase. load cmbFiled1.Items. add ("student ID") cmbFiled1.Items. add ("name") cmbFiled1.Items. add ("gender") cmbFiled1.Items. add ("don't") cmbFiled1.Items. add ("Grade") cmbFiled1.Items. add ("class") cmbFiled2.Items. add ("student ID") cmbFiled2.Items. add ("name") cmbFiled2.Items. add ("gender") cmbFiled2.Items. add ("don't") cmbFiled2.Items. add ("Grade") cmbFiled2.Items. add ("class") cmbFiled3.Items. add ("student ID") cmbFiled3.Items. add ("name") cmbFiled3.Items. add ("gender") cmbFiled3.Items. add ("don't") cmbFiled3.Items. add ("Grade") cmbFiled3.Items. add ("class") End Sub Protected Overrides Sub Todatagridview () Dim table As New DataTable Dim frmGroupQuery As New frmGroupQuery Dim FacadeGroupQuery As New Facade. groupCheckFacade Try table = FacadeGroupQuery. FGroupCheck (groupcheck) If table. rows. count = 0 Then table. clear () maid () Else maid = Nothing maid = table 'maid 1. columns (0 ). visible = False DataGridView1.Columns (0 ). headerText = "student ID" Maid (1 ). headerText = "name" Maid (2 ). headerText = "don't" DataGridView1.Columns (3 ). headerText = "don't" DataGridView1.Columns (4 ). headerText = "Grade" Maid (5 ). headerText = "class" End If Catch ex As Exception MsgBox (ex. message, vbOKOnly, "prompt") End Try End SubEnd Class </span>

The logic of the Combined Query is to first make the condition boxes of the first row available, and the relationship boxes and other condition boxes unavailable. Only when the condition boxes of the first row are filled up, the first link box can be used. The condition box of the second line can be used only after the first link box selects the link. And so on ...... In this way, we have saved many judgment statements, fewer code, and clearer logic.

Every time we make a decision, we are trying to stop our progress. The only thing we do is the source of discovery!

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.