Computer room charge System (vb.net)--the actual storage process

The initial contact with the stored procedure is in the Jiang Jianling teacher's video, the original was just a cursory, just have an impression, know the noun; he also had SQL Server database this course, but the teacher did not speak, he did not read; The real knowledge of stored procedures comes from the Database system principle ", in the exam, know what the stored procedure is, how to write on paper, but never in the DBMS personally knocked. So the computer room charge system gave me this opportunity.
There is no more about the basic knowledge of stored procedures, just write about how the stored procedures are used in the small project of the computer room charge system.

background :
The computer room charge system has a registered function, the prototype diagram is as follows:

When registering, it is necessary to update three tables in the database (one record is added to the card table T_card, student table t_student, recharge table T_register), so when executing, it is necessary to execute three SQL statements when executing the SQL statement:

1: Add record to card table T_card

Insert into T_card (Cardnumber, balance, type, stunumber, status, isChecked) VALUES (@cardNumber, @balance, @type, @stuNumb Er, @status, @isChecked)

2: Add records to 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 a record to the recharge list T_register

Insert into Chargesystem.dbo.T_Recharge (Userid,cardnumber,rechargecash, Rechargedate, Rechargetime, isChecked) VALUES (@userID, @cardNumber, @balance, convert (Varchar,getdate (), +), convert (Varchar,getdate (), 108), ' no Checkout ')

use of stored procedures for the above requirement, if the stored procedure is used in the database, after the stored procedure is set up, it is only responsible for executing the stored procedure directly in the code, instead of connecting and manipulating the database multiple times.

First, the establishment of stored procedures There are two ways to build a stored procedure (because the system uses a database of SqlServer2008, so here's an example):
(1), manually set up stored procedures : Object Explorer: Database →chargesystem (database name) → programmability → Right-click "Stored procedure" → New stored procedure

The new stored procedure can be said to be a formed stored procedure de template, we just need to modify the stored procedure name, parameters, execute statements and other code on the OK.

(2),SQL statement Add stored procedure To create a new query directly, write the stored procedure SQL code in the Code editing window with the following basic syntax:
CREATE PROCEDURE Proc_name
@[parameter name] [type],@[parameter name] [type] ...
As
BEGIN
[Process body] .....
END

The stored procedure established by the first method is basically the same structure, after adding the corresponding parameters and the process body, the complete stored procedure is:

CREATE PROCEDURE proc_register--Define parameters @cardnumber varchar (6), @balance decimal (5,1), @type nvarchar, @status nvarchar (6), @isChecked nvarchar (30), @stuNumber varchar, @stuName nvarchar (+), @stuSex varchar (nvarchar), @stuMajor (+/-) (+)----------- , @stuGrade nvarchar, @stuClass nvarchar, @comment nvarchar (+), @userID varchar asbegin--inserting data into a table insert in To Chargesystem. 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 (Ten), GETDATE (), 120), CONVERT (Varchar,getdate (), 108), ' not checkout ') END

Note: No matter how the stored procedure is built, it needs click Execution to save to the server, it can be executed by subsequent calls, and simply save (CTRL + C) Saves the stored procedure file locally and not into the database server.

Second, call the stored procedure in the code calling a stored procedure is similar to executing a SQL statement, and it is important to note that when you execute a SQL statement, The value of the Command object's CommandType is CommandType.Text, and the value of CommandType is commandtype.storedprocedure when the stored procedure is executed.
In this example, the specific code is:

D-Tier Code:

Public Class sqlserverregisterdal:implements Idal. Iregister public Function Insert (ByVal encard as entity.cardentity, ByVal enstudent as entity.studententity, ByVal user ID as String) as Boolean Implements Idal. Iregister.insert Dim sqlHelper As New sqlHelper ' definition sqlHelper instance Dim cmdtype A                        s CommandType = CommandType.StoredProcedure ' Define database command type Dim cmdtext as String = "Proc_register" ' Database execution string Dim parameters as SqlParameter () ' defines the parameter array, which is responsible for passing the value of the variable in the stored procedure ' as a parameter Parameter one by one in the array is assigned the value parameters = {New SqlParameter ("@cardNumber", Encard.cardnumber), New SqlParameter ("@balance", encard.ba                      Lance), 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.stum ajor), New SqlParameter ("@stuGrade", Enstudent.stugrade), New SqlParameter ("@stuClass", Enstudent.stu        Class), New SqlParameter ("@comment", enstudent.stucomment), New SqlParameter ("@userID", UserID)}            ' Determine if there is a query result if Sqlhelper.executenonquery (Cmdtext, Cmdtype, parameters) then Return True Else Return False End If End FunctionEnd Class

SqlHelper Code:

Public Class SqlHelper ' gets the value of the connection string from the configuration file Dim strconnection As String = ConfigurationSettings.AppSettings ("Strconnecti    On ") ' Create database Connection Object Conn Dim conn As SqlConnection ' CREATE Database operation class cmd Dim cmd As New SqlCommand ' <summary> ' ' constructor, initializes the database connection object ' ' </summary> ' <remarks></remarks> public Sub New () conn when the class is instantiated = New SqlConnection (strconnection) End Sub ' ' <summary> ' close release SqlCommand object ' </summary> ' ' <param name= ' cmd ' > need to close the SqlCommand object </param> ' ' <remarks>cmd.        Dispose () releases the command resource directly, I do not know what to do with the system performance, first, then continue to optimize </remarks> public Sub closecommand (ByVal cmd as SqlCommand) If not IsNothing (cmd) and then cmd.    Dispose () cmd = Nothing End If end Sub ' <summary> ' Close database connection ' </summary> "' <param name=" conn "> Need to close the SqlConnection object </param>" <remarks> close the database connection, but is not released, but is stored in the connection pool, You can also open the connection &lt by using the open () method when needed;/remarks> public Sub CloseConnection (ByVal conn as SqlConnection) If is isnothing (conn) then con N.close () End If end Sub ' <summary> ' parameter additions and deletions ' ' </summary> ' <param na Me= "Cmdtext" > SQL command to execute </param> "<param name=" Cmdtype "> Executed commands, typically SQL statements, stored procedures, or tables </param> ' & Lt;param name= "sqlparameters" > Parameter array </param> ' <returns> returns the number of rows affected type is int </returns> ' ' <remark S></remarks> public Function ExecuteNonQuery (ByVal cmdtext As String, ByVal Cmdtype as String, ByVal Sqlparame Ters as SqlParameter ()) as Integer Try Conn. Open () ' Opens database connection cmd. CommandText = Cmdtext ' Sets the query statement cmd. CommandType = Cmdtype ' Sets a value that interprets the cmdtype (stored procedure is called when the value is StoredProcedure) cmd. Connection = conn ' Set connection cmd. Parameters.addrange (sqlparameters) ' Incoming parameters               Dim affectedrows as Integer affectedrows = cmd. ExecuteNonQuery return affectedrows ' Returns the number of rows affected to execute Catch ex as Exception M Sgbox (ex.            Message, Msgboxstyle.okonly, "warm Tip") return 0 ' If there is an error, 0 Finally Cmd.           Parameters.clear () ' Clear parameter call Closecommand (CMD) ' Close and release command Call CloseConnection (conn) ' Close Connection conn end Try end Function End Class

Why you use stored procedures       

Registered student card number in this system is not a large module, but this small demand, the database has to deal with the data of the three tables, and in the past operation, even three times the database, executed three SQL statements.

This frequently opens and closes the connection to the database, which consumes a lot of system resources and slows down execution. You need to consider using stored procedures instead of executing so many SQL statements.

1, the general SQL statements need to be compiled once every time, and the stored procedure is compiled only at the time of creation, and each subsequent execution does not need to compile again.

2, the stored procedure is equivalent to a number of SQL statements need to be executed together, into a SQL statement, of course, just connect and execute once to get the results.

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

4, System upgrade, maintenance more convenient.

Summarize:

★ When it comes to multiple SQL statement executions , the need to connect to the database multiple times , or when you need to process multiple tables , you can encapsulate these operations, that is, create stored procedures and invoke execution directly each time you need them. You can do all of the work and avoid opening and closing database connections multiple times.

★ When it comes to more complex requirements (such as the computer room charge system in the next machine settlement consumption can be stored process), such as sorting, calculation and so on, you can transfer data directly to the stored procedure, a series of operations in the database server, reduce the client-server data flow, It also guarantees the security of the system.

There's a problem in your head: Since having a stored procedure perform multiple tasks, what if one or several of these tasks are not completed in the process of execution? This is where the business comes in handy ... Go ahead and practice it ...