Use stored procedures in ASP. NET for fuzzy search

1. Create a stored procedure

Create a stored procedure for the employee table in the northwind database in MSSQL (fuzzy query by lastname ):

Create procedure employess_sel @ lastname nvarchar (20)
As
Select lastname from employees where lastname like '%' + @ lastname + '%'
Go

Ii. Form Design

1. Create an ASP. NET web applicationProgram, Name it websql, select the Save path, and click OK.

2. Add a label, A Textbox, and a button to the form, and then add a DataGrid Control. Right-click the DataGrid Control and select attribute generator. Then, select a column in the window that opens, remove the check box before automatically creating a column, add a binding column to the selected column, set the header to lastname, and set the data field to lastname. Click OK.

3. Create an intermediate data layer

Right-click solution, select "new"> "project"> "class library", name "clasql", select the Save path, and click "OK. Add the following to the Open Class Library:Code:

Imports system. Data. sqlclient
Public class class1
Dim scon as new sqlconnection ("Server = localhost; database = northwind; uid = sa; Pwd = 123 ")

'Create A Query Process
Public Function emp_sel (byval lastname as string) as Dataset
Scon. open ()
Scon. Close ()
'Define the command object and use the Stored Procedure
Dim scom as new sqlcommand
Scom. commandtype = commandtype. storedprocedure
Scom. commandtext = "employess_sel"
Scom. Connection = scon
'Define a data adapter and set parameters
Dim SDA as new sqldataadapter (scom)
SDA. selectcommand. Parameters. Add ("@ lastname", sqldbtype. nvarchar). value = lastname
'Define A DataSet object and fill the dataset
Dim ds as new dataset
Try
SDA. Fill (DS)
Catch ex as exception
End try
Return DS
End Function
End Class

4. reference the intermediate data layer (Class Library)

Right-click the clasql project, select generate, and right-click "Reference" of the websql project, select Add reference, select a project, and add the clasql project to the selected component box, click OK.

V. webform1.aspx form code design

Open the webform1.aspx file under the websql project and double-click the button to open the code window. The complete code is as follows:

Public class webform1
Inherits system. Web. UI. Page
'Form Code omitted
'Search button events
Private sub button#click (byval sender as system. Object, byval e as system. eventargs) handles button1.click
'Define the input of the variable storage text box and convert it for security purposes
Dim lastname as string = textbox1.text
Lastname = lastname. Replace ("&","&")
Lastname = lastname. Replace ("<","")
Lastname = lastname. Replace ("> ","")
Lastname = lastname. Replace ("'","'")
Lastname = lastname. Replace ("CHR (13)", "<br> ")
Lastname = lastname. Replace ("CHR (10)", "<br> ")

'Create a new class instance and call the Query Process to bind data
Dim mygroovy as new clasql. class1

Datagrid1.datasource = mylinoleic. emp_sel (lastname)
Datagrid1.databind ()
End sub
End Class

Vi. Notes

In terms of security, the following measures should be taken in database operations:

1. Use the Stored Procedure

2. Do not use the SA account

3. Use the password of a complex account

4. Try to use different accounts for data insertion and deletion, and set only the corresponding insert or delete permissions for each account.

5. database operations should be encapsulated into the middle layer (Class Library) as much as possible, so that code can be reused and further modified.