VB. NET & amp; (three-layer + generic) Realize Combined Query

VB. NET & amp; (three-layer + generic) for Combined Query

For Combined Query, it is really "difficult". Of course, it is just a joke. Let's talk about how to splice strings by yourself!

First, we need to clearly declare an object Layer, define a composite query as an object class, and set "Fields, operators, and composite relationships to the attributes of the object class combinQuery respectively.

'Field 1 Private _ ComboFileName1 As String Public Property ComboFileName1 () As String Get Return _ ComboFileName1 End Get Set (value As String) _ ComboFileName1 = value End Set End properties' Field 2 Private _ ComboFileName2 As String Public Property ComboFileName2 () As String Get Return _ ComboFileName2 End Get Set (value As String) _ ComboFileName2 = value End Set End Property ............

For the U layer, We need to assign values to parameters and display the records in the DataGridView dview. At this stage, we use a generic set, converts the datatable type to a set and displays it.

Look at the U layer:

Private Sub btnQuery_Click (sender As Object, e As EventArgs) Handles btnQuery. click 'combinquery _ m, which defines an Entity Dim combinQuery_m As IList (Of Entity. registerEntity) 'defines the generic set Dim combinQuery_bll As New BLL. stusInfoMainBLL 'defines a layer B interface Dim combinQuery As New Entity. combinQueryEntity 'defines an object As the table used by the Dim table As String = "T_student"' parameter. It is associated with the student table Dim arrayControl (2) as Control, assign a value to the parameter With combinQuery to assign the value of the query condition Field 1 to the 1ComboFileName1 attribute of the combinQuery_m object. comboFileName1 = ComboFileName1.Text. trim (). comboFileName2 = ComboFileName2.Text. trim (). comboFileName3 = ComboFileName3.Text. trim () ''assigns the value of operator 1 to the 1ComboSign1 attribute of the combinQuery_m operator. comboSign1 = ComboSign1.Text. trim (). comboSign2 = ComboSign2.Text. trim (). comboSign3 = ComboSign3.Text. trim () 'assigns the value of content 1 to the 1txtInqure1 attribute of the object combinQuery_m. txtInqure1 = txtInqure1.Text. trim (). txtInqure2 = txtInqure2.Text. trim (). txtInqure3 = txtInqure3.Text. trim () 'assigns the value of composite 1 to the composite 1ComboRelation1 attribute of combinQuery_m. comboRelation1 = ComboRelation1.Text. trim (). comboRelation2 = ComboRelation2.Text. trim () End WithcombinQuery_m = combinQuery_bll.StusInfo (table, combinQuery) DataGridView1.DataSource = combinQuery_m'

The above code mainly implements the process of feeding back the queried set to the DataGridView.
We also need to convert strings and assign values to fields, operators, and composite relations.

The query field (, 3) corresponds to the field (eg: cardNo) in the "table", the operator corresponds to the relational operator, and the composite relationship corresponds to the logical operator (or/and)

See some code:

'String' assigns Field 1 A Select Case (combinQuery. comboFileName1) Case "card number" combinQuery. comboFileName1 = "cardNo" Case "student ID" combinQuery. comboFileName1 = "studentNo" Case "name" combinQuery. comboFileName1 = "studentName" Case "gender" combinQuery. comboFileName1 = "sex" Case "Grade" combinQuery. comboFileName1 = "Grade" Case "class" combinQuery. comboFileName1 = "sclass" End select' operator 1 Select Case combinQuery. comboSign1 Case "=" combinQuery. comboSign1 = "=" Case ">" combinQuery. comboSign1 = ">" Case "<" combinQuery. comboSign3 = "<" Case "<>" combinQuery. comboSign1 = "<>" End Select 'combination 1 Select Case combinQuery. comboRelation1 Case "or" combinQuery. comboRelation1 = "or" Case "and" combinQuery. comboRelation1 = "and" End Select

In the whole system process, we involved four combined queries, So we defined a string-type Table to achieve code reusability. The following describes how to concatenate strings:

''''''Generate A Combined Query SQL statement-concatenate an SQL string '''''''''CombinQuery entity'''
 '''
 Public Function CombinsqlQuery (ByVal table As String, ByVal combinQuery As Entity. combinQueryEntity) As String first, the first query condition is effective Dim SQL As String = "select * from" & table & "where" & combinQuery. comboFileName1 & "" & combinQuery. comboSign1 & "" & combinQuery.txt Inqure1.Trim () If combinQuery. comboRelation1 = "" then' if the first combination is empty, the first query condition returns SQL Else. If the first combination is not empty, the first two query conditions are valid: SQL = SQL & "& combinQuery. comboRelation1 & "" & combinQuery. comboFileName2 & "" & combinQuery. comboSign2 & "" & combinQuery.txt Inqure2.Trim () If combinQuery. comboRelation2 = "" then' if the first composite link is not empty and the second composite link is empty, only the first two query conditions are valid. Return SQL Else 'If the first and second combinations are not empty, all three query conditions are valid. SQL = SQL & "& combinQuery. comboRelation2 & "" & combinQuery. comboFileName3 & "" & combinQuery. comboSign3 & "" & combinQuery.txt Inqure3.Trim () Return SQL End If combinQuery. comboRelation1 <> "" And combinQuery. comboRelation2 = "" then' if the first combination is not empty and the second combination is empty, the first two query conditions are valid. SQL = SQL & "" & combinQuery. comboRelation1 & "" & combinQuery. comboFileName2 & "" & combinQuery. comboSign2 & "" & combinQuery.txt Inqure2.Trim () ElseIf combinQuery. comboRelation1 <> "" And combinQuery. comboRelation2 <> "" then' If the first and second composite relations are not empty, the Three query conditions are valid. SQL = SQL End If End Function

Because it is implemented by layer D, I defined this process in the form of a Module in layer D. the most critical part of layer D is as follows:

Private clsSqlhelper As DAL. sqlhelper = New DAL. sqlhelper () 'declare and instantiate ''''''Generic set, Combined Query ''''''''''''
 '''
 Public Function StuInfoQuery (ByVal table As String, ByVal combinQuery As Entity. combinQueryEntity) As IList (Of Entity. registerEntity) Implements IDAL. IStusInfoMainDAL. stuInfoQuery Dim dt As New DataTable Dim myList As IList (Of Entity. registerEntity) 'Save the converted generic set' call query statement' SQL statement. Call the combinQuery method of the Model layer CombinQuerySql () method and return the SQL statement Dim strsql As String = CombinQueryModule. combinsqlQuery (table, combinQuery) dt = clsSqlhelper. query (strsql, CommandType. text) 'execute query' converts dt to the generic set myList = EntityHelper. converToList (Of RegisterEntity) (dt) Return myList End Function

The entire process tries to define the Factory class and interface, so layer B directly declares a variable and calls the interface returned by the Factory method.

'Declare and instantiate the Factory As the DataAccess class Private ReadOnly factory As DataAccess = New DataAccess Public Function StusInfo (ByVal data As String, ByVal StusQuery As Entity. combinQueryEntity) As IList (Of Entity. registerEntity) 'declares and instantiates the variable InterfaceUser: Call Factory. IUser Dim comboQuery As IStusInfoMainDAL = factory. stusInfoMain () Dim StusMain As IList (Of Entity. registerEntity) 'defines a generic set StusMain = comboQuery. stuInfoQuery (data, StusQuery) Return StusMain End Function

In this way, the whole process of Combined Query is completed, and the implementation of the whole process is well compliant with the requirements of the three-tier architecture, achieving the goal of decoupling. Of course, in addition to concatenating strings, there is also a method to implement the stored procedure, so that there is no need to assign a large number of values, and there is no need to splice strings, all of these are directly defined in the stored procedure, then, you can directly input parameters in the stored procedure. Although you understand the implementation of the entire process, there is still a lack of practice. I hope to be proficient in using various skills in the cooperative development process.