1. Create a stored procedure. Create
a new stored procedure for the employees table in the Northwind database in MSSQL (function is fuzzy query based on LastName):
CREATE PROCEDURE Employess_Sel @lastname nvarchar (20)
AS
select lastname from Employees where lastname like '%' + @lastname + '%'
GO
2. Form design
1. Create a new ASP.NET Web application, name it WebSql, select the save path and click OK.
2. Add a Label, a Textbox and a Button button to the form, then add a DataGrid control, right-click the DataGrid control and select Property Generator, then select the column in the window that opens, and remove the check mark before automatically creating the column. , then add a bound 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 the solution, select New-Project-Class Library, name it ClaSQL, select the save path and click OK. Add the following code to the opened class library:
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 Dimscom 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
'Define a data set object and populate the data set 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 on the ClaSql project, select Generate, then right-click on the "Reference" of the WebSql project, select Add Reference, and then select the project to add the ClaSql project to the selected Components box and click OK.
5. WebForm1.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 omitted'Search button eventPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Define a variable 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(13)", "<br>")
lastname = lastname.Replace("chr(10)", "<br>")
'Create a new class instance and call the query process to bind data Dim myCla As New ClaSql.Class1
DataGrid1.DataSource = myCla.Emp_Sel(lastname )
DataGrid1.DataBind()
End Sub
End Class
Six: Notes
: For security reasons, the following measures should be taken when operating the database:
1. Use stored procedures
2. Do not use SA accounts
3. Use passwords for complex accounts
4. For data insertion, Try to use different accounts for deletion and other operations, and only set corresponding insertion or deletion permissions for each different account.
5. Database operations should be encapsulated into the middle layer (class library) as much as possible, so that the code can be implemented The reuse also facilitates future modifications.