Combined Query of Data room charging system: Basic String concatenation method and Stored Procedure Implementation

It takes a lot of effort to query the Combined Query. The two main ideas are: String concatenation and the use of stored procedures.

 

The first method isImplemented through the simplest method. The three-tier architecture is for decoupling, and it is not very good for combined queries. The following is my processing process,

Program interface:

U layer (interface layer): mobile phone data, transmitted to the entity

''' <Summary> ''' after obtaining the data value, it is passed to the object, and the data is displayed to the user ''' </Summary> ''' <Param name = "sender"> </param> ''' <Param name = "E"> </param> ''' <remarks> </remarks> private sub into query_click (sender as object, e As eventargs) handles fetch query. click dim enselectstring as new entity. selectstring dim table as new able 'replaces the dim F1 as string dim F2 as string dim F3 as string' variable and converts it to the select case cbofielda field recognized by the database. text case "student ID" f1 = "studentid" case "name" f1 = "studentname" case "gender" f1 = "sex" case "don't" f1 = "department" case "Grade "f1 =" Grade "case" class "f1 =" stuclass "case" Remarks "f1 =" studentstyle "case" card number "f1 =" cardid "case else F1 =" "End select select case cbofieldb. text case "student ID" f2 = "studentid" case "name" f2 = "studentname" case "gender" f2 = "sex" case "department" f2 = "department" case "Grade "f2 =" Grade "case" class "f2 =" stuclass "case" Remarks "f2 =" studentstyle "case" card number "f2 =" cardid "case else F2 =" "End select dim F4 as string select case cborelationa. text case "or" F4 = "or" case "and" F4 = "and" case else F4 = "" End select case cbofieldc. text case "student ID" F3 = "studentid" case "name" F3 = "studentname" case "gender" F3 = "sex" case "department" F3 = "department" case "Grade "F3 =" Grade "case" class "F3 =" stuclass "case" Remarks "F3 =" studentstyle "case" card number "F3 =" cardid "case else F3 =" "End select to pass the parameter to the entity enselectstring. work_cbofielda = F1 enselectstring. work_cbofieldb = F2 enselectstring. work_cbofieldc = F3 enselectstring. work_cbooperatora = cbooperatora. text enselectstring. work_cbooperatorb = cbooperatorb. text enselectstring. work_cbooperatorc = cbooperatorc. text enselectstring. work_cborelationa = F4 enselectstring. work_cborelationb = cborelationb. text enselectstring. work_txtconditiona = txtconditiona. text enselectstring. work_txtconditionb = txtconditionb. text enselectstring. work_txtconditionc = txtconditionc. text' displays the query result to the user dim inquirestudentinfofa as new facade. studentmanagefa table = inquirestudentinfofa. inquirestudentinfobll (enselectstring) dgw. datasource = TABLE 'data binding 'is displayed as the data dgw you need. columns (0 ). headertext = "student ID" dgw. columns (1 ). headertext = "name" dgw. columns (2 ). headertext = "card number" dgw. columns (3 ). headertext = "" dgw. columns (4 ). headertext = "don't" dgw. columns (5 ). headertext = "Grade" dgw. columns (6 ). headertext = "class" dgw. columns (7 ). headertext = "gender" dgw. columns (8 ). headertext = "status" dgw. columns (9 ). headertext = "Remarks"

 

Methods In the entity Layer

''' <Summary> ''' generates a string ''' </Summary> ''' <Param name = "table"> names of tables accessing different databases </param> ''' <Param name = "SS"> </param> ''' <returns> string </returns> ''' <remarks> </remarks> Public Function selectstring (byval table as string, byval ss as selectstring) as string dim SQL as string try SQL = "select * from" & table & "where" & SS. cbofielda & "& SS. work_cbooperatora & "'" & SS. work_txtconditiona & "'" If ss. work_cborelationa = "" Then return SQL else SQL = SQL & "" & SS. work_cborelationa & "& SS. work_cbofieldb & "& SS. work_cbooperatorb & "'" & SS. work_txtconditionb & "'" If ss. work_cborelationb = "" Then return SQL else SQL = SQL & "" & SS. work_cborelationb & "& SS. work_cbofieldc & "& SS. work_cbooperatorc & "'" & SS. work_txtconditionc & "'" Return SQL end if catch ex as exception' SQL = "" Return SQL end try

Layer B: logic sorting

''' <Summary> ''' query student information ''' </Summary> ''' <Param name = "selectstring"> entity selection string </param>'' '<returns> returned table </returns> ''' <remarks> </remarks> Public Function inquirestudentinfo (byval selectstring as entity. selectstring) as datatable dim factory as new dbfactory 'instantiate factory dim istudent as istudent 'define interface istudent = factory. createstudentinfo 'create interface dim dT as new datatable try' to obtain the information you need through the view dt = istudent. getinfobystring ("v_querystudentinfo", selectstring) return DT catch ex as exception return DT end try end Function

D: Use a string to obtain information.

''' <Summary> ''' obtains information using strings ''' </Summary> ''' <Param name = "table"> database table </param>'' '<Param name = "selectstring"> entity selection string </param> ''' <returns> <return table/returns> ''' <remarks> </remarks> Public Function getinfobystring (table as string, selectstring as selectstring) as datatable implements istudent. getinfobystring dim dT as new able 'Declares dim selectstring1 as new entity. selectstring dim SQL as string 'defines the string SQL = selectstring. selectstring (table, selectstring) Try 'Call sqlhelper to execute dt = sqlhelper. dbhelper. getdatatable (SQL, commandtype. text) return DT catch ex as exception return DT end try end Function

The selectstring method is used to synthesize an SQL statement based on input parameters, including the table name. In this way, different functions can be combined for query, however, only the data queried by this function is in one table or in the same view.

The combined queries processed in this way comply with the requirements of the three-tier architecture and achieve the goal of decoupling.

 

The second method is: No longer use String concatenation as described above. After all, this method is easy to handle errors. I will ask the teacher to learn and think about how to implement the transfer of only three parameters regardless of how to fill in the conditions, in addition, if the select clause is not used, you need to set the default relation relationship to the and clause. Therefore, only the if clause is used to determine whether the relation is or not. When there is only one condition, then, the three parameters are assigned the same value, that is, the query and relationship for the three identical conditions. The advantage of doing so is to avoid the use of judgment statements to splice strings and directly input parameters in the stored procedure.

Alter procedure [DBO]. [proc_query] @ cbofielda varchar (10), @ cbooperatora varchar (10), @ txtconditiona varchar (10), @ cborelationa varchar (10), @ cbofieldb varchar (10 ), @ cbooperatorb varchar (10), @ txtconditionb varchar (10), @ cborelationb varchar (10), @ cbofieldc varchar (10), @ cbooperatorc varchar (10 ), @ txtconditionc varchar (10) -- table name or view name asdeclare @ tempsql varchar (500) -- temporarily store SQL statements -- char (32) is a space, char (39) single quotes beginset @ tempsql = 'select * From v_studentinfo where '+ char (32) + @ cbofielda + @ cbooperatora + char (39) + @ txtconditiona + char (39) + char (32) + @ cborelationa + char (32) + @ cbofieldb + @ cbooperatorb + char (39) + @ txtconditionb + char (39) + + char (32) + @ cborelationb + char (32) + @ cbofieldc + @ cbooperatorc + char (39) + @ txtconditionc + char (39) execute (@ tempsql) End

 

This method requires a lot of practices in the future. It requires a lot of Logical Analysis for such combined queries. We hope to deepen our understanding of this method during cooperative development.