The transfer SQL string of the reconstructed path combination query

Since is the use of vb.net language to the computer room charge system reconstruction, then inevitably to solve the problem of combinatorial query, in the VB version of the implementation of this is a difficult point, but we still rely on their own or common wisdom to solve this seemingly complex problem.

Today programming language is different is on the one hand, more important is the structure of the system is not the same, using a three-tier architecture to implement the system, so that the combination of queries in the three-tier structure of the implementation of the method. Of course, the core approach to combinatorial queries is immutable, by assembling the various conditions of the query into the WHERE clause of the SQL query statement, and the question is where is the assembly of the WHERE clause completed?

If the U-layer is completed, then we pass the parameter is the SQL statement string, if the D layer is completed, then we will be the U-level input field content, operation symbols and query content as parameters to the D layer, and then the D layer to complete the assembly, Finally, call the function in SqlHelper to execute the SQL statement, return the result, it is so simple. Let's look at the solution to the SQL statement string first:

The first is the U-layer code, the U-layer we mainly completed is the user input content conversion and splicing, forming a string as a parameter down the pass. Because the user input field names and logical relationship symbols are the form of Chinese characters, and the SQL statements are in English form, must be converted, considering that many forms are to be used, so the conversion field names and logical relationship symbols encapsulated as two functions, placed in the U-layer module, convenient to call, the implementation of the following:

Two conversion functions for Modelui:

    The function of "<summary>" is mainly to convert the field names entered by the user into the name of the field in English form, that is, the corresponding field name in the database ' ' </summary> ' <param n Ame= "str" ></param> "<returns></returns>" <remarks></remarks> public functi On Convertfield (ByVal str As String) As String Dim result As String = "Select Case (str) case"                Card number "result =" CardID "Case" study number "result =" StudentID "Case" name " result = "Studentname" case "gender" result = "sex" case "professional" resu lt = "Major" Case "class" result = "ClassName" case "on machine date" result = "OnDa            TE "case" machine number "result =" Computer "cases" Machine date "result =" Offdate " Case "consumption amount" result = "consume" case "user name" result = "UserName" C              ASE "Login Date"  result = "Logindate" case "Logoff date" result = "Logoutdate" End Select Return result End function ' <summary> ' the main function of the function is to convert the user input of the form of the logical relationship of Chinese characters into the English form of the SQL language ' ' </summary> ' <p Aram Name= "str" ></param> "<returns></returns>" <remarks></remarks> public            Function convertlogic (ByVal str As String) As String Dim result As String = "" Select Case (str) Case "or" result = "or" case "with" result = "and" End select Retur N Result End Function

U-Layer Form Frmstudentinfo Click event code for Btnquery:

 Private Sub Btnquery_click (sender as Object, e as EventArgs) Handles Btnquery.click btnmodify.enabled = True Dim mylist As New List (of entity.querybalanceentity) Dim bstudent As New BLL. STUDENTBLL Dim sqlstr As String = Nothing ' stitching the SQL string, completing the assignment of the parameter sqlstr If ComboLogic1.Text.Trim = "the        n sqlstr = Convertfield (ComboField1.Text.Trim) + ComboOperator1.Text.Trim + "'" + TxtContent1.Text.Trim + "'" Else sqlstr = Convertfield (ComboField1.Text.Trim) + ComboOperator1.Text.Trim + "'" + TxtContent1.Text.Tri M + "'" _ + convertlogic (ComboLogic1.Text.Trim) + Convertfield (ComboField2.Text.Trim) + Combooperator2.text . Trim + "'" + TxtContent2.Text.Trim + "'" If comboLogic2.Text.Trim <> "then Sqlstr = Conv Ertfield (ComboField1.Text.Trim) + ComboOperator1.Text.Trim + "'" + TxtContent1.Text.Trim + "'" _ + Convertl Ogic (ComboLogic2.Text.Trim) + Convertfield (combofield2.text. Trim) + ComboOperator2.Text.Trim + "'" + TxtContent2.Text.Trim + "'" _ + convertlogic (ComboLogic2.Text.Trim        ) + Convertfield (ComboField3.Text.Trim) + ComboOperator3.Text.Trim + "'" + txtcontent3.text.trim+ "'" End If The End If ' instance object using layer B calls the function of layer B to complete the query mylist = bstudent.querystudent (sqlstr) If mylist.  Count > 0 Then datagridview1.datasource = mylist Else MsgBox ("The information you need is not retrieved, please reconfirm the information you entered is incorrect", Msgboxstyle.okonly, "hint") End If End Sub

The corresponding function code for layer B:

    Public Function querystudent (ByVal sqlstr as String) as List (of entity.querybalanceentity)        Dim istudent as Istudent
   dim fstudent as New sqlserverfactory        istudent = fstudent. Createstudent        Return istudent. Querystudent (sqlstr)    End Function

The corresponding function code for the D layer:

    Public Function querystudent (ByVal sqlstr as String) as List (of entity.querybalanceentity) Implements Istudent.querystudent        Dim studenthelper As New Sqlhelper.sqlseverhelper        Dim mylist As New List (of  entity.querybalanceentity)        Dim mydt As New DataTable        Dim sql As String = "SELECT * from  v_querybalance where" + sqlstr        mydt = studenthelper.executeselect (sql, commandtype.text)        mylist = Entity.ConvertTo.ConvertToList ( of Entity.querybalanceentity) (MYDT)        Return mylist    End Function

As for the ExecuteSelect function used in the above code to execute the SQL statement and the Converttolist function used to convert the DataTable to the list generic collection, it is not mentioned here, I believe everyone has its own encapsulated function, Here we mainly discuss how to implement the function of combinatorial query in three-layer structure.

         finally summed up this solution, personally think this method is particularly simple, also very good understanding, The idea of passing strings is what I see in the blog of Gao Ying, and simplifying the code to rewrite the code in my own form. But this method has a serious flaw, I believe that the people know that is the problem of SQL injection, of course, we can limit the input box user input, so as to prevent SQL injection problem, but in comparison, the security is worse, there is no better way?