Transactions and stored procedures-the patron saint of data unification and the patron saint of Stored Procedures

Transactions and stored procedures-the patron saint of data unification and the patron saint of Stored Procedures

As the system grows and the amount of data increases, many operations on many tables in the database are required to implement a function. In this way, we will face the problem of data unification and consistency operations, when we want to change data for multiple tables at the same time (that is, the add, delete, and modify operations ), if some operations are incomplete due to unexpected situations, system problems, or changes in external conditions, data may be incomplete, such errors are definitely not allowed in the system. How can this problem be solved well? Next, I will introduce two common solutions for transactions and stored procedures.

Transactions

If the operations on multiple tables are written in the system, you can introduce the transaction in the developed system code, the specific operations are also described in detail in the application of "Transactions" in the charging system of the previous blog room. I will not go into details here, but there are still many flaws. In the three-tier Data Center charging system, you add transaction references to the DAL layer. This will lead to repeated Writing of many codes, which is a bad habit, we can encapsulate these methods and reference the transactions added when The BLL layer calls them.

Stored Procedure

Another way is to write down all the operations you want to implement through the stored procedure in the database, therefore, the stored procedure ensures that all SQL statements in the stored procedure are executed when you call this stored procedure, and data operations are performed in a unified manner, saving you many concerns, it is simple and convenient, so that the coders can see the hope! It is not difficult to see that the stored procedure is actually a collection of multiple statements, but this mechanism ensures the simultaneous execution of each statement!

The following is an instance of the stored procedure used in your system. This stored procedure only involves deleting two tables and sharing them with you!

Call stored procedures in the DAL layer. Note that the reference must be consistent with the declared part:

<Span style = "font-family: KaiTi_GB2312; font-size: 18px;" >/// <summary> /// Delete News (delete news comments together with news comments) /// </summary> /// <param name = "id"> News attributes </param> /// <returns> </returns> public bool Delete (string id) {// TODO: delete News (delete news comments together with news comments) bool flag = false; DataTable dt = new DataTable (); // define the datatable table SqlParameter [] paras = new SqlParameter [] // define the parameter {new SqlParameter ("@ id", id)}; int res = sqlhelper. executeNonAddDelUpd ("news_Delete", CommandType. storedProcedure); <span style = "white-space: pre"> </span> // reference the Stored Procedure news_Delete if (res> 0) {flag = true ;} return flag ;}</span>

The above is my understanding of data uniformity. I gradually realized that the mastery of technology is my understanding of the new technology. Only by understanding the new technology can we ensure the performance and functions of our own system, so I have a lot of understanding, practice, and efforts to digest it. I am a good engineer and make a good system!