ASP.NET USES stored procedures to implement fuzzy queries

1. Establish the stored procedure

Create a new stored procedure for the employess 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

2. Form design

1. Create ASP.NET Web application, name it WebSql, select the save path and click ok.

2. Add an Label, an Textbox, and an Button button to the form, then add an DataGrid control, right click on the DataGrid control to select the property generator, then select a column in the open window, remove the check before automatically creating the column, then add a bound column to the selected column to set the header to LastName and the data field to LastName. Click ok.

Create the intermediate data layer

Right-click the solution, select new - project - library, name ClaSQL, select save path and click ok. Add the following code to the open class library:

Imports System.Data.SqlClient
Public Class Class1
Dim scon As New SqlConnection("server=localhost; database = northwind; uid = sa;

pwd = 123 ")

'create a procedure to query
Public Function Emp_Sel(ByVal lastname As String) As DataSet

scon. Open ()
scon. Close () 'defines the command object and USES 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 the parameter
Dim sda As New SqlDataAdapter(scom)
sda. SelectCommand. Parameters. Add (" @ lastname, "SqlDbType. NVarChar). Value = lastname
'defines a dataset object and populates 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 intermediate data layer (class library)

Right-click on the ClaSql project, select generate, then right-click on the "references" of the WebSql project, select add references, then select project, add the ClaSql project to the selected component box, and then click ok.

1.aspx form code design

Open the WebForm1.aspx file under the WebSql project. Double-click the Button button to open the code window. The complete code is as follows:

Public Class WebForm1
Inherits System. Web. UI Page
'form code
'search button event
Private Sub Button1_Click(ByVal sender System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'defines the variable that stores the input to the text box and converts 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 > ")

'creates a new class instance and invokes the query procedure binding data
Dim myCla As New ClaSql.Class1

DataGrid1. DataSource = myCla. Emp_Sel
(lastname)
DataGrid1. DataBind () End Sub
End Class

Six: precautions

For security and other reasons, the following measures should be taken as far as possible in database operations:

1. Use the storage process

2. Do not use SA account

3. Use the password

for complex accounts

4. For data insertion and deletion, try to use different accounts for operation, and only set the corresponding permissions

such as insertion or deletion for each different account

5, the operation of the database should try to encapsulate it into the middle layer (class library), so that the code can be reused, but also convenient for future modification.