SQL transactions and instance demos

SQL transactions and instance demos

Introduction

Transaction. The English name is transaction. Is a logical unit in the Process of database management operations, composed of a limited operation sequence. In fact, this concept is well understood. A simple understanding is that a transaction is an operation in the database, and a complete transaction is formed by putting some operations together. For example, taking a train. If there are only two actions to take a train, buy a ticket and get on the bus, then two operations are required to take the train. Buy a ticket and get on the bus, now, you can buy tickets and get on the train. In this way, you can call the train operation to complete the train operation. Taking the train here is a transaction. Another situation here is that the ticket is successfully purchased but the train is not catching up. At this time, I need to return the ticket, the refund operation is called rollback.

Features

What is database transaction? The four features of database transactions are generated. Through these four features, we can better understand transactions and, in turn, identify what kind of operations are transactions.

In simple terms, database transactions are characterized by ACID.

Translation of A (Atomic) into Chinese is atomicity

Atoms are inseparable (as we understood in the initial period ). Therefore, we can understand that a transaction is a whole. Once executed, the entire transaction is executed or not executed.

C (Consistency) Consistency

After the transaction is executed, make sure that all data in the database is in one state to another. Using the above example, you can understand that you have been on the train and sat down, and your feet have to be on or put on the train (a little cautious ). But this is the truth.

I (Isolation) isolated rows

This is the complementary interference between transactions during execution. Here we will refer to the concept of concurrency control. What is concurrency control? Concurrency Control is the control of data operation exceptions for concurrent operations. For example, I bought a train ticket. Both Kobayashi and Mr. Li went to the Internet to buy a ticket. Five tickets were left, and they both bought the same one at the same time at 11: 11, so that two transactions could access the train ticket at the same time, change the remaining parts to 4. at this time, I actually sold two, but there were actually three, and four in the database, which caused data exceptions. How can this problem be solved? One way is to lock the object when you process it and unlock it after the transaction is completed ). The object can be accessed only when its status is unlock. A little more. Just scale it out. Hey

D (Durability), persistence

This is easier to understand. Simply put, your operations on the database must be permanently stored in the data.

Through the above explanation, we have briefly understood the rollback and features of transactions and transactions. Next we will use another simple small example to feel the transaction at the code level. By the way, we will be familiar with the use of generics.

Environment: vs 2010 SQL 2008

Project test: update and add data through transactions

Create Database code

Create database login

Use login

Create table login
(
Username varchar (20 ),
Userpwd varchar (20 ),
Userlevel varchar (20 ),
)

Interface

It's a super simple interface. For the sake of integrity, upload it.

Client code

Private void btnExcuteSQLs_Click (object sender, EventArgs e)
{
List <string> strsql = new List <string> ();
String strone = "insert into login values ('aaa', 'aaa', 'aaa ')";
String strtwo = "update login set userlevel = '12' where username = 'cfl '";
Strsql. Add (strone );
Strsql. Add (strtwo );
Try
{
ExcuteSQLs (strsql );
}
Catch (Exception ee)
{

MessageBox. Show (ee. Message );

Throw new Exception (ee. Message );
// MessageBox. Show (ee. Message );
}
}
Public void ExcuteSQLs (List <string> SQLStringList)
{
// Create a database connection
SqlConnection conn = new SqlConnection ("server =" ip "; database = login; uid = sa; pwd = 123456 ;");
// Open the database
Conn. Open ();
// Create a data operation command object
SqlCommand cmd = new SqlCommand ();

// Assign the database connection object to the command object
Cmd. Connection = conn;

// Execute the transaction
SqlTransaction tx = conn. BeginTransaction ();
// Assign the transaction value to the command object
Cmd. Transaction = tx;

Try
{
// Use the for loop to traverse SQL statements in the generic type
For (int I = 0; I <SQLStringList. Count; I ++)
{
String strsql = SQLStringList [I]. ToString ();
// When the string is not empty, execute
If (strsql. Length> 1)
{
// Input the SQL statement to be executed
Cmd. CommandText = strsql;
// Execute a non-parameterized Query
Cmd. ExecuteNonQuery ();
}
}
// Transaction commit
Tx. Commit ();
}
Catch (System. Data. SqlClient. SqlException e)
{
Throw new Exception (e. Message );
}
}

Summary
The above is some understanding and practice of SQL transactions, in general. The transaction is used to execute a series of database operations based on its own name. It is commonly used to execute multiple SQL statements. The above is a simple example, although the sparrow is small and dirty. There are some basic operations such as encapsulation of SQL by transactions, simple and practical generics, and how to input parameters to transactions. Share with you.

This article permanently updates the link address: