Reconstruction path Combined Query-passing parameters + Stored Procedure

Labels: VB.net SQL Server Reconstruction

In the previous article, we discussed a method for implementing composite queries. That is, at the U layer, we assembled the WHERE clause of the SELECT statement and assigned it to a string variable, upload to layer D and then form a complete SQL statement with the select clause. then execute the statement and return the query result. This is so simple, but there is still a question at the end of the blog, the security of this method is a little poor. Is there a better solution?

The answer is yes. Let's take a look at another method I have implemented this time. First of all, I would like to give you a brief introduction to the idea of this method, which is actually relatively simple. At first I wanted to write SQL query statements in the program code, then, the values of the conditions in the Combined Query are passed into the SQL statement as the object parameters (now the entity layer defines an object for the combined query), and then the SQL statement is executed to return the result. However, I couldn't figure out how to assemble SQL statements. So I asked someone else if I could use a Combined Query method to pass Parameters. As a result, I used a stored procedure. I thought: Why not?

Next we will look at the specific implementation code of this method by querying the combined query function of user work logs:

First, we need to define an entity class for Combined Query at the Entity layer entity:

Public Class QueryWorklog    Private _field1 As String    Private _field2 As String    Private _field3 As String    Private _operatorchar1 As String    Private _operatorchar2 As String    Private _operatorchar3 As String    Private _content1 As String    Private _content2 As String    Private _content3 As String    Private _relation1 As String    Private _relation2 As String    Public Property Field1 As String        Get            Return _field1        End Get        Set(value As String)            _field1 = value        End Set    End Property    Public Property Field2 As String        Get            Return _field2        End Get        Set(value As String)            _field2 = value        End Set    End Property    Public Property Field3 As String        Get            Return _field3        End Get        Set(value As String)            _field3 = value        End Set    End Property    Public Property Operatorchar1 As String        Get            Return _operatorchar1        End Get        Set(value As String)            _operatorchar1 = value        End Set    End Property    Public Property Operatorchar2 As String        Get            Return _operatorchar2        End Get        Set(value As String)            _operatorchar2 = value        End Set    End Property    Public Property Operatorchar3 As String        Get            Return _operatorchar3        End Get        Set(value As String)            _operatorchar3 = value        End Set    End Property    Public Property Content1 As String        Get            Return _content1        End Get        Set(value As String)            _content1 = value        End Set    End Property    Public Property Content2 As String        Get            Return _content2        End Get        Set(value As String)            _content2 = value        End Set    End Property    Public Property Content3 As String        Get            Return _content3        End Get        Set(value As String)            _content3 = value        End Set    End Property    Public Property Relation1 As String        Get            Return _relation1        End Get        Set(value As String)            _relation1 = value        End Set    End Property    Public Property Relation2 As String        Get            Return _relation2        End Get        Set(value As String)            _relation2 = value        End Set    End PropertyEnd Class

Then, write the stored procedure in SQL Server to implement the Combined Query:

Use [chargesystemdb] Go/****** object: storedprocedure [DBO]. [proc_queryworklog] script Date: 08/17/2014 22:39:39 *****/set ansi_nulls ongoset quoted_identifier ongo -- ================ =================================-- Author: <Lian jiangwei> -- create Date: <August 17, 2014> -- Description: <used for Combined Query of system user work logs> -- ========================== ==================== alter procedure [DBO]. [proc_queryworklog] -- add the parameters for the stored procedure here @ field1 char (20), @ field2 char (20), @ field3 char (20), @ operator1 char (20 ), @ operator2 char (20), @ operator3 char (20), @ content1 char (20), @ content2 char (20), @ content3 char (20 ), @ relation1 char (20), @ relation2 char (20) asdeclare @ tempsql varchar (500) begin -- set nocount on added to prevent extra result sets from -- interfering with select statements. set nocount on; -- insert statements for procedure hereif (@ relation1 = '') set @ relation1 = nullif (@ relation2 ='') set @ relation2 = NULL -- char (32) is a space, char (39) is a single quotation mark set @ tempsql = 'select * From t_worklog where '[email protected] [email protected] + char (39) + @ content1 + char (39) if (@ relation1 is not null) set @ tempsql [email protected] [email protected] + char (32) [email protected] [email protected] + char (39) [email protected] + char (39) if (@ relation2 is not null) set @ tempsql [email protected] [email protected] + char (32) [email protected] [email protected] + char (39) [email protected] + char (39) execute (@ tempsql) End

Then, write functions at Layer D, call and execute the stored procedure, and pass the required parameters in:

 Public Function QueryWorklog(worklog As QueryWorklog) As List(Of Entity.WorklogEntity) Implements IWorklog.QueryWorklog        Dim mylist As List(Of Entity.WorklogEntity)        Dim mydt As New DataTable        Dim sql As String = "PROC_QueryWorklog"        Dim paras As SqlParameter() = {New SqlParameter("@field1", worklog.Field1),                                      New SqlParameter("@field2", worklog.Field2),                                      New SqlParameter("@field3", worklog.Field3),                                       New SqlParameter("@operator1", worklog.Operatorchar1),                                       New SqlParameter("@operator2", worklog.Operatorchar2),                                       New SqlParameter("@operator3", worklog.Operatorchar3),                                       New SqlParameter("@content1", worklog.Content1),                                       New SqlParameter("@content2", worklog.Content2),                                       New SqlParameter("@content3", worklog.Content3),                                       New SqlParameter("@relation1", worklog.Relation1),                                       New SqlParameter("@relation2", worklog.Relation2)}        mydt = workloghelper.ExecuteSelect(sql, CommandType.StoredProcedure, paras)        mylist = Entity.ConvertTo.ConvertToList(Of Entity.WorklogEntity)(mydt)        Return mylist    End Function

Then, in layer B, the interface is used to call the combined query function of layer D:

   Public Function QueryWorklog(ByVal worklog As Entity.QueryWorklog) As List(Of WorklogEntity)        iworklog = fworklog.CreateWorklog        Return iworklog.QueryWorklog(worklog)    End Function

Finally, at the U layer, the user input content in the interface is assigned to the corresponding attributes in the object parameters:

Private sub btnquery_click (sender as object, e as eventargs) handles btnquery. click dim mylist as List (of entity. worklogentity) dim worklog as new entity. queryworklog dim bworklog as new BLL. worklogbll worklog. field1 = convertfield (combobox1.text. trim) worklog. field2 = convertfield (combobox2.text. trim) worklog. field3 = convertfield (combobox3.text. trim) worklog. operatorchar1 = combobox4.text. trim wo Rklog. operatorchar2 = combobox5.text. trim worklog. operatorchar3 = combobox6.text. trim worklog. content1 = textbox1.text. trim worklog. content2 = textbox2.text. trim worklog. content3 = textbox3.text. trim worklog. relation1 = combobox7.text. trim worklog. relation2 = combobox8.text. trim mylist = bworklog. queryworklog (worklog) If mylist. count> 0 then datagridview1.datasource = mylist else msgbox (" Data. Please confirm the query conditions again! ", Msgboxstyle. okonly," prompt ") end if end sub

Finally, let's summarize the implementation of the combined query function: simply looking at this function looks very advanced, you can combine your filtering conditions at will, but its implementation is actually very simple, the core of the statement is to concatenate and assemble the restrictions entered by the user to form the WHERE clause of the SELECT statement. Then, the system function is called to execute this SQL statement and return the result, the difficulty lies in the processing of details, that is, how to put the restrictions entered by users into SQL statements. This is my understanding of combined queries. In fact, code implementation is not the most important. It is important to think about ideas. How to simplify the problem and implement it is the core of our learning.