Reconstruction path Combined Query: Passing parameters + stored procedure, rebuilding Combined Query
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 '+ @ field1 + @ operator1 + char (39) + @ content1 + char (39) if (@ relation1 is not null) set @ tempSql = @ tempSql + @ relation1 + char (32) + @ field2 + @ operator2 + char (39) + @ content2 + char (39) if (@ relation2 is not null) set @ tempSql = @ tempSql + @ relation2 + char (32) + @ field3 + @ operator3 + char (39) + @ content3 + 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.
Small SQL statement problem: Passing parameters in Stored Procedures
Do not use double quotation marks
The following code is defined before the stored procedure:
-----------------------------
@ Content nvarchar (50 ),
------------------------------
Select * from zswy_shop_GoodsInfo where GoodsName like '%' + @ content + '%'
Question about passing parameters in Oracle stored procedures?
First, I will tell you that there is an error in this process. In the stored procedure, the select statement cannot be used like this. The complete process is as follows:
Create or replace procdeure xxx (tablename in varchar2)
As
Begin
Select * into tablename1 from tablename;
End xxx;
Tablename1 is a tablename copy table.