Data center billing system Personal Edition-stored procedure + transaction rollback

I. Stored Procedures (storedprocedure) are a set of SQL statements for specific functions in a large database, which are compiled and stored in the database, you can specify the name of a stored procedure and provide parameters (if the procedure contains parameters) to execute

Line.

1. Advantages of stored procedures:

1) The stored procedure is compiled only at creation time, and does not need to be re-compiled every time the stored procedure is executed. Generally, the SQL statement is compiled every time it is executed, therefore, using storage improves the execution speed of the database.

2) When performing complex operations on the database (for example, performing update, insert, quey, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the data provided by the data.

3) stored procedures can be reused to reduce the workload of database developers.

4) high security. You can set that only one user has the right to use the specified stored procedure.

 

2. databasetransaction refers to a series of operations performed as a single logical unit of work. Combine a group of related operations into a unit that either succeeds or fails.

1. Transaction attributes:

1) atomicity: either both are executed, or none are executed. They are inseparable.

2) Consistency: after the transaction is completed, all data must be consistent. Maintain the integrity of database data!

3) isolation: modifications made by concurrent transactions must be isolated from those made by any other concurrent transactions. The status of the data when the transaction is viewing the data is either the status before the transaction is modified or the status after the transaction is modified. The transaction does not view the data in the intermediate status.

4) durability: after the transaction is completed, its impact on the system is permanent. This modification will remain even if a fatal system failure occurs.

 

2. Advantages of transactions:

1) Use transactions: simplifies error recovery and makes applications more reliable.

2) especially when banking transactions are involved, transactions are used to ensure the stability and predictability of transactions.

 

The theoretical knowledge is briefly introduced here. The following describes the application of stored procedures and transactions in the Personal Edition of the data center toll system. Please provide more guidance:

Taking recharge as an example, when I recharge a student, I need to do these jobs. 1) it needs to accumulate the value of the charge with the balance in the t_student table; 2) you also need to insert the card number, Recharge Amount, date and time of the filled value into t_andmoney.

The program design concept in this example: Pass the parameter -------> facade -----> BLL ------> factory + idal ------> Dal (CallThe stored procedure is used to perform multi-Table operations. If the operation is successful, return true; otherwise, an exception is caught.) ------->Layer-by-layer returnBoolean Type until the UI returns a success message based on the returned value.

Dal code:

Public class sqlserverstudent: Implements idal. studetinfo 'use the configuration file to set the connection string dim strconnstr as string = configurationmanager. deleetask( "connstr") dim conn as sqlconnection = new sqlconnection (strconnstr) 'Recharge amount public function findstu2 (byval enstudent as entity. studententity) as Boolean implements idal. studetinfo. findstu2 'assigns the stored procedure name to the string dim strsql as string = "pro_andmoney"' to instantiate a cmd command dim cmd as new S Qlcommand (strsql, Conn) 'assigns cmd to the parameter. commandtype = commandtype. storedprocedure cmd. parameters. add (New sqlparameter ("@ cardno", enstudent. cardno) cmd. parameters. add (New sqlparameter ("@ addmoney", enstudent. money) cmd. parameters. add (New sqlparameter ("@ describe", enstudent. describe) cmd. parameters. add (New sqlparameter ("@ date", enstudent. adddate) cmd. parameters. add (New sqlparameter ("time", enstudent. A Ddtime) Try conn. open () 'to determine whether execution is successful. Cmd. executenonquery () catch ex as exception 'if a transaction rollback occurs in the stored procedure, an exception is caught. Msgbox (ex. Message) end try return true end Function

Stored procedures and transactions in the database:

Use [charge3] gocreate procedure [DBO]. [pro_andmoney] (@ cardno varchar (8), @ addmoney varchar (10), @ describe varchar (50), @ date varchar (10), @ time varchar (8 )) as begin -- thing set nocount on; -- set xact_abort on when enabled; -- when the xac_abort parameter is on, it can detect whether the error is returned. if the error is returned, it will not be submitted, instead, all operations will be rolled back. Begin tran -- start a transaction -- update the field update t_student set money = cast (@ addmoney as INT) + Cast (money as INT) as varchar) in the student table) where cardno = @ cardno -- insert a record (data type conversion, cast (variable, conversion type) into the recharge table insert into t_addmoney (cardno, addmoney, adddate, addtime) values (@ cardno, @ addmoney, @ date, @ time) -- if there is no error in the data operation if @ error = 0 commit tran -- submit the transaction elserollback tran -- roll back the transaction end

The return value of layer D can be fed back to the UI Layer to complete the operation.

Knowledge supplement:Set the transaction retention point

You can set the save point or mark in the transaction. Save point definition if a part of the transaction is canceled conditionally, the transaction can return

Location. If the transaction is rolled back to the storage point, it is required (if necessary, use more Transact-SQL statements and commit

Transaction statement) to continue to complete the transaction, or the transaction must be completely canceled (by rolling back the transaction to its starting point. To

To cancel the entire transaction, use the rollback transactiontransaction_name format. This will cancel all the statements and

Process.

Code:

Create procedure myprocedureasbeginset nocount on; Set xact_abort on; begin Tran OK -- start a transaction OK Delete from rxqz where qz = 'rx015' -- delete data save Tran BCD -- save a transaction point named bcdupdate SZ set name = 'Lili s' where name = 'lily' -- modify the data if @ error <> 0 -- determine whether there is an error in modifying the data begin -- if the error rollback Tran BCD -- roll back the transaction to the BCD restore point commit Tran OK -- commit transaction end else -- no error commit Tran OK -- commit transaction end

Note: 1. @ error indicates whether an error exists. If it is 0, no error is returned. However, it cannot be captured for that major error, and @ error indicates that only the previous SQL statement can take effect.

Note: Cast (variable, conversion type)

Be careful to share with you:

1. Just think about it, don't implement it, just in vain!

2. Almost all of the answers we want are online. It's no big deal to overcome fear!

3. Be calm in learning! Impetuousness can only weaken your enthusiasm, waste time, and reduce efficiency!