Asp. Using stored procedure to implement fuzzy query _ practical skills in net

First, establish a stored procedure

Create a new stored procedure for the Employess table in the Northwind database in MSSQL (function by LastName for fuzzy query):

CREATE PROCEDURE employess_sel @lastname nvarchar (20)
As
Select LastName from Employees where LastName like '% ' + @lastname + '% '
Go

Second, form design

1, new ASP.net Web application, named Websql, select Save path and click OK.

2. Add a label, textbox, and button buttons to the form, and then add a DataGrid control, right-click the DataGrid control to select the property Builder, and then select the column in the open window to remove the tick before the column is automatically created. Add a bound column to the selected column to set the header to LastName and set the data field to LastName. Click OK.

Third, create intermediate data layer

Right-click the solution, select New-Project-class 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 process for querying
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 the parameters
Dim SDA as New SqlDataAdapter (scom)
Sda. SELECTCOMMAND.PARAMETERS.ADD ("@lastname", SqlDbType.NVarChar). Value = LastName
' Define a DataSet object and populate the dataset
Dim DS as New DataSet
Try
Sda. Fill (DS)
Catch ex as Exception
End Try
Return DS
End Function
End Class

Iv. Reference Intermediate data layer (class library)

Right-click on the Clasql item, select the build, then right-click on the Websql project's references, select Add Reference, select Project, add Clasql project to the selected component box, and then click OK.

Five, WebForm1.aspx form code design

Open the WebForm1.aspx file under the Websql project and double-click the button button to open the Code window, complete with the following code:

Public Class WebForm1
Inherits System.Web.UI.Page
' Form code is slightly
' Search button Events
Private Sub button1_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Button1.Click
' Define variables to store the input of the 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", "<br>")
LastName = LastName. Replace ("Chr", "<br>")

' Create a new class instance and call the query procedure to bind the data
Dim Mycla as New clasql.class1

DataGrid1.DataSource = Mycla.emp_sel (LastName)
Datagrid1.databind ()
End Sub
End Class

Six: Precautions

For security reasons, the following measures should be taken in database operations:

1, use the storage process

2, do not use the SA account

3, to use the password of the complex account

4, for data inserts, deletes and so on as far as possible uses the different account to operate, and to each different account only sets the corresponding inserts or deletes the permission

5, the operation of the database should try to encapsulate it into the middle tier (class library), so that can realize the reuse of code, but also facilitate the subsequent modification.