Data room Charging System (VB. NET)-storage process practice, charging system vb.net

Data room Charging System (VB. NET)-storage process practice, charging system vb.net

I first came into contact with the stored procedure in a video from instructor Yan jianling. I had a rough time, but I had an impression that I knew this term; when I was a sophomore, I also had the SqlServer database course, but the teacher did not speak about it and did not read it. The real understanding of the stored procedure came from the "database system principles" in the self-study exam. During the exam, I know what the stored procedure is, how to write it on paper, but I have never personally typed it in DBMS. The data room charging system gave me this opportunity.
I will not describe much about the basic knowledge of Stored Procedure Germany here. I will only explain how to use the stored procedure in the small project of the data center charging system.

Background:
The IDC charging system has a registration function. The prototype is shown below:

During registration, you must update the three tables in the database (add a record to the T_Card, T_Student, and T_Register tables, if you use the SQL statement execution method, you need to execute three SQL statements:

1: Add a record to the T_Card table

 insert into T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )

2: Add a record to the student table T_Student

 insert into T_Student (stuNumber,stuName,stuSex,stuMajor,stuGrade,stuClass  ,comment ) values (@stuNumber ,@stuName ,@stuSex ,@stuMajor ,@stuGrade ,@stuClass,@comment )

3: Add records to the recharge table T_Register

Insert into chargesystem. dbo. t_Recharge (userID, cardNumber, rechargeCash, rechargeDate, rechargeTime, isChecked) values (@ userID, @ cardNumber, @ balance, CONVERT (varchar, getdate (), 120), CONVERT (varchar, GETDATE (), 108), 'unsettled check ')

The use of stored procedures for the above requirements, if the use of stored procedures, after the establishment of a stored procedure in the database, in the Code is only responsible for directly executing this stored procedure, instead of connecting to and operating the database multiple times in a row.

1. There are two ways to establish a stored procedure (because the database used by the system is SqlServer2008, here we take this as an example ):
(1) manually create a stored procedure: In the object Resource Manager: Database → ChargeSystem (Database Name) → programmability → right-click "Stored Procedure" → create a stored procedure

The new stored procedure can be said to be a formed stored procedure template. You only need to modify the stored procedure name, parameters, execution statements, and other code on the template.

(2) create a query directly when adding a stored procedure to an SQL statement, and write the Stored Procedure SQL code in the code editing window. The basic syntax is:
Create procedure PROC_NAME
@ [Parameter name] [type], @ [parameter name] [type]…
AS
BEGIN
[Process body] ......
END

The stored procedure created using the first method is basically the same structure. After corresponding parameters and process bodies are added, the complete stored procedure is:

Create procedure PROC_Register -- Define the parameter @ cardNumber varchar (6), @ balance decimal (5, 1), @ type nvarchar (20), @ nvstatus archar (50 ), @ isChecked nvarchar (10), @ stuNumber varchar (18), @ stuName nvarchar (10), @ stuSex varchar (6), @ stuMajor nvarchar (30 ), @ stuGrade nvarchar (20), @ stuClass nvarchar (20), @ comment nvarchar (100), @ userID varchar (18) ASBEGIN -- insert into ChargeSystem into the table. dbo. t_Card (cardNumber, balance, type, stuNumber, status, isChecked) values (@ cardNumber, @ balance, @ type, @ stuNumber, @ status, @ isChecked) insert into ChargeSystem. dbo. t_Student (stuNumber, stuName, stuSex, stuMajor, stuGrade, stuClass, comment) values (@ stuNumber, @ stuName, @ stuSex, @ stuMajor, @ stuGrade, @ stuClass, @ comment) insert into chargesystem. dbo. t_Recharge (userID, cardNumber, rechargeCash, rechargeDate, rechargeTime, isChecked) values (@ userID, @ cardNumber, @ balance, CONVERT (char (10), getdate (), 120 ), CONVERT (varchar, GETDATE (), 108), 'uncheck') END

Note:: No matter which method is used to create a stored procedure, you need to click execute to save it to the server for future invocation. simply save it (ctrl + C) it only saves the Stored Procedure file locally instead of the database server.

When a stored procedure is created, you can create a new query and execute "exec stored procedure name @ parameter 1 = value 1, @ parameter 2 = value 2 ......" To verify whether the stored procedure is correct.

2. Calling a stored procedure in the Code is similar to executing an SQL statement. Note that when executing an SQL statement, the CommandType value of the Command object is CommandType. while the CommandType value is CommandType when the stored procedure is executed. storedProcedure.
In this example, the specific code is:

Layer D code:

Public Class SqlServerRegisterDAL: Implements IDAL. IRegister Public Function Insert (ByVal enCard As Entity. cardEntity, ByVal enStudent As Entity. studentEntity, ByVal userID As String) As Boolean Implements IDAL. IRegister. insert Dim sqlHelper As New SqlHelper 'defines SqlHelper instance Dim partition type As CommandType = CommandType. storedProcedure 'defines the database command type Dim plain text As String = "PROC_Register" 'database execution String Dim parameters As SqlParameter ()' to define the parameter array, assign parameters = {New SqlParameter ("@ cardNumber", enCard. cardNumber), New SqlParameter ("@ balance", enCard. balance), New SqlParameter ("@ type", enCard. cardType), New SqlParameter ("@ status", enCard. status), New SqlParameter ("@ isChecked", enCard. isChecked), New SqlParameter ("@ stuNumber", enStudent. stuNumber), New SqlParameter ("@ stuName", enStudent. stuName), New SqlParameter ("@ stuSex", enStudent. stuSex), New SqlParameter ("@ stuMajor", enStudent. stuMajor), New SqlParameter ("@ stuGrade", enStudent. stuGrade), New SqlParameter ("@ stuClass", enStudent. stuClass), New SqlParameter ("@ comment", enStudent. stuComment), New SqlParameter ("@ userID", userID)} 'determines whether a query result exists If sqlHelper. executeNonQuery (plain text, argument type, parameters) Then Return True Else Return False End If End FunctionEnd Class

SqlHelper code:

Public Class SqlHelper get the connection String value Dim strConnection As String = ConfigurationSettings from the configuration file. deleetask( "strConnection") 'creates a database connection object conn Dim conn As SqlConnection' create a database operation class cmd Dim cmd As New SqlCommand ''' <summary> ''' constructor, when the class is instantiated, the database connection object ''' </summary> ''' <remarks> </remarks> Public Sub New () conn = New SqlConnection (strConnection) is initialized) end Sub ''' <summary> ''' close the SqlCommand object ''' </summary> ''' <param name = "cmd"> SqlCommand object to be closed </ param> ''' <remarks> cmd. dispose () directly releases command resources. I don't know how this will do for the system performance. First, I will continue to optimize it. </remarks> Public Sub CloseCommand (ByVal cmd As SqlCommand) if Not IsNothing (cmd) Then cmd. dispose () cmd = Nothing End If End Sub ''' <summary> ''' close database connection ''' </summary> ''' <param name = "conn"> sqlConnection object </param> ''' <remarks> to close the database connection, but it is not released, but stored in the connection pool. When needed, you can use the Open () method to Open the connection </remarks> Public Sub CloseConnection (ByVal conn As SqlConnection) if Not IsNothing (conn) Then conn. close () end If End Sub ''' <summary> ''' has the parameter addition, deletion, modification operation ''' </summary> ''' <param name = "plain text"> to be executed </param> ''' <param name = "primitive type">, it is generally an SQL statement, stored procedure, or table </param> ''' <param name = "sqlParameters"> parameter array </param> ''' <returns> to return the affected number of rows. type: integer </returns> ''' <remarks> </remarks> Public Function ExecuteNonQuery (ByVal plain text As String, byVal parameter type As String, ByVal sqlParameters As SqlParameter () As Integer Try conn. open () 'Open the database connection cmd. commandText = plain text 'sets the query statement cmd. commandType = primitive type 'sets a value to explain the stored type (if the value is StoredProcedure, the stored procedure is called) cmd. connection = conn' sets the Connection cmd. parameters. addRange (sqlParameters) 'input parameter Dim affectedRows As Integer affectedRows = cmd. executeNonQuery Return affectedRows 'returns the number of affected lines in the execution. Catch ex As Exception MsgBox (ex. message, MsgBoxStyle. okOnly, "tip") Return 0' if an error occurs, 0 Finally cmd is returned. parameters. clear () 'clear parameter Call CloseCommand (cmd) 'close and release Command Call CloseConnection (conn)' close connection conn End Try End Function End Class

Why use stored procedures?

The student registration card number is not a big module in this system, but this small requirement requires dealing with the data of the three tables in the database. In the past, three databases were connected, three SQL statements are executed.

In this way, frequent access to and disconnection from the database consume a large amount of system resources to reduce the execution speed. In this case, you need to consider usingStored ProcedureInstead of executing so many SQL statements.

1. Generally, the SQL statement needs to be compiled every time it is executed, while the stored procedure only compiles at the time of creation and does not need to be compiled every time it is executed.

2. The stored procedure is equivalent to combining multiple SQL statements to be executed and turning them into an SQL statement. Of course, you only need to connect and execute the statement once to get the result.

3. High security. You can specify the right to use stored procedures to prevent SQL injection.

4. Easy system upgrade and maintenance.

Summary:

★When it comesExecute Multiple SQL statements, RequiredConnect to the Database Multiple times, Or you needProcess multiple tablesThese operations can be encapsulated together, that is, the creation of a stored procedure. You can directly call and execute each time you need to execute all the operations to avoid opening or closing the database connection multiple times.

★When it comesComplex requirements(For example, the stored procedure can be used for offline settlement and consumption amount in the data room charging system), such as sorting and computing, data can be directly transferred to the stored procedure, and a series of operations can be performed on the database server, reduces the data traffic between the client and the server, and ensures the security of the system.

At this point, you may have a problem in your mind: Since you want a stored procedure to execute multiple tasks, in case one or more of these tasks are not completed during execution, what should I do? At this time, the transaction will come in handy... Go to practice...

How does vbnet use stored procedures?

Stored procedures combine one or more T-SQL statements into one logical unit and save them as an object in the SQL Server database. After a stored procedure is created, its T-SQL definition is stored in the sys. SQL _module system directory view. When the stored procedure is executed for the first time, SQL Server creates an execution plan and stores it in the Plan memory cache. Then, SQL Server can execute a reuse plan for the stored procedure. The stored procedure is faster and more reliable than an equivalent instant query that is not compiled and not prepared. You can also create a stored procedure for the. net public Language Runtime (CLR) assembly. There are a lot of advantages to using stored procedures, and there is no harm: helping to aggregate T-SQL code at the data layer. It helps large instant queries reduce network traffic. Promotes code reusability. Describe how to obtain data. Unlike views, stored procedures can use Liu control technology, temporary tables, and table variables. The impact of stored procedures on query response time is relatively stable. The stored procedure can be used as a control layer to eliminate potential security risks caused by direct access to the SQL Server instance and its database tables. Stored procedures can be used for a variety of activities, including simple SELECT, INSERT, UPDATE, and DELETE. T-SQL activities can be mixed in a single stored procedure, or create stored procedures in modular form, creating multiple stored procedures for each or a group of tasks. The basic syntax of a stored PROCEDURE without parameters is as follows: create procedure [Schema_name.] procedure_nameAS {<SQL _statement> [... n]} a stored procedure with parameters can receive up to 2100 external input parameters. Syntax: CREATE {PROC | PROCEDURE} [Schema_name.] procedure_name [; number] [{@ parameter [type_schema_name.] data_type} [VARYING] [= default] [OUT | OUTPUT] [READONLY] [,... n] [WITH <procedure_option> [,... n] [for replication] AS {SQL _statement> [;] [... n] | <method_specifier>}
 
How does vbnet obtain the returned values of stored procedures?

Dim cmd As New SqlCommand () cmd. commandType = CommandType. storedProcedure cmd. commandText = "proc name" 'parameter name can be arbitrary, but it is better to be consistent with the return value of the stored procedure' default value: Dim ret As New SqlParameter ("ret", 0) 'The returned value type has the following ret. direction = ParameterDirection. returnValue cmd. parameters. add (ret) 'Other parameters are no different from general SQL statements '... cmd. executeNonQuery () 'gets ret