Let's look at the database -- (4) transactions and database transactions.

Let's look at the database -- (4) transactions and database transactions.

What is a transaction? Simply put, it is what you do or what you do. A term refers to a program execution unit that accesses and may update various data items in the database.

In a database, a transaction can be an SQL statement, a group of SQL statements, or the entire program.

For example, transfer money from account A to account B. This process requires two steps: 1) deduct money from account A; 2) Add the corresponding amount of money to account B. These two steps are indispensable. If an error occurs, the consequences are unimaginable. What should we do? This leads to the usefulness of transactions.

The SQL statement is as follows. If no error occurs, the transaction transfer is successfully submitted. If an error occurs, perform rollback. The database design is also very simple, a money table, two fields bankName (account name) and totalMoney (total assets ).

Begin trybegin tran -- start transaction update money set totalMoney = totalMoney-100 where bankName = 'A' -- A account minus 100 update money set totalMoney = totalMoney + 100 where bankName = 'B' -- B account add 100 commit tran -- commit transaction End trybegin catchrollback tran -- transaction rollback end catch

The transfer is also used as an example to transfer money from A to B.

The Code is as follows:

Private void button1_Click (object sender, EventArgs e) {SqlTransaction sqlTrans = null; SqlConnection con = new SqlConnection ("server = .; database = bank; uid = sa; pwd = 123456; "); try {con. open (); // start the transaction sqlTrans = con. beginTransaction (); SqlCommand cmd = new SqlCommand ("", con, sqlTrans); cmd. commandTimeout = 120; cmd. commandType = System. data. commandType. text; string cutA = "update money set totalMoney = TotalMoney-@ count where bankName = 'A' "; string addB =" update money set totalMoney = totalMoney + @ count where bankName = 'B '"; // assign SqlParameter paras = new SqlParameter ("@ count", txtMoney. text); cmd. parameters. add (paras); cmd. commandText = cutA; cmd. executeNonQuery (); cmd. commandText = addB; cmd. executeNonQuery (); // throw new Exception ("test exception. the transaction must rollback "); used to test transaction rollback // commit SqlTrans. commit ();} catch (Exception ex) {// transaction rollback sqlTrans. rollback (); Console. write (ex. message);} finally {if (con. state! = System. Data. ConnectionState. Closed) con. Close () ;}console. ReadLine ();}

Now let's take a look at the transaction rollback situation: Restore the code that has been viewed above to normal and run it again. The two operations are not executed to reduce the money of account A and increase the money of Account B. Even if an error occurs, no more serious consequences will be caused, it only restores the program to the status before execution.

After reading the example above, we can better understand the attributes of transactions, including atomicity, consistency, isolation, and durability. These four attributes are generally called ACID properties.
1) atomicity. This indicates that the transaction is an inseparable unit of work, and all operations included in the transaction are either done or not done. This is also the core.

2) consistency. Transactions must change the database from one consistent state to another. This is very similar to atomicity.

3) isolation. The execution of a transaction cannot be disturbed by other transactions.
4) durability persistence. Once a transaction is committed, its changes to the data in the database should be permanent. If the transfer is successful, the data will be updated to the database.

Feeling: transaction. This term has long been heard. It is difficult to understand and practice it until now. As a result, similar content is always walking away and many learning opportunities are missed. The sooner you get to know it, the more opportunities you will have to practice.

Database Transaction Level

ACID refers to the four basic elements of the correct execution of database transactions, including: Atomicity, Consistency, Isolation, and Durability ). A database system that supports transactions must have these four features. Otherwise, data correctness cannot be ensured in the Transaction process (Transaction processing, the transaction process cannot meet the requirements of the transaction party.
Atomicity
All the operations in the entire transaction are either completed or not completed, and it is impossible to stop at a stage in the middle. When a transaction encounters an error during execution, it will be rolled back to the state before the start of the transaction, just as this transaction has never been executed.
Consistency
The integrity constraints of the database are not damaged before and after the transaction starts.
Isolation
The execution of two transactions does not interfere with each other. One transaction cannot see the data at a certain time in the middle of another transaction.
Durability
After the transaction is completed, the changes made by the firm to the database are permanently stored in the database and will not be rolled back.

How to view the database?

USE pubs

Before the USE statement is used again to change the current database, each row of the subsequent operation statement will be executed for the pubs database. Use the system stored procedure sp_helpdb to view information about all databases on the current server. If the database name is specified, information about the specified database is returned.

Use the system stored procedure sp_databases to view all available databases on the current server.

Use the system stored procedure sp_helpfile to view information about all files (including data files and log files) in the current database. If the file name is specified, the file information is returned.

Use the system stored procedure sp_helpfilegroup to view all the file groups in the current database, including the information of the Primary file group and User_defined file group. If the name of a file group is specified, information about the file group is returned.

You can use the Enterprise Manager to view more detailed information about the database:

(1) Select the database to be viewed.

(2) Select the Properties command from the Operation menu or shortcut menu. the dialog box shown in 3.4 is displayed. You can view or modify attributes such as data files, log files, file groups, and permissions.

Figure 3.4 view database attributes

3.4 manage databases
After creating a database, you may find that the file capacity of the database is insufficient and the database has been used for a while, you must manage the database.

3.4.1 modify database size
SQL Server 2000 data files can automatically expand the length, so the size of the database will automatically increase. However, if the maximum file length is set, database expansion is still necessary. Modifying the database size is essentially modifying the length of data files and log files, or adding/deleting operating system files. This operation can be implemented through the following syntax:

Alter database database

{Add file <filespec> [,... n] [to filegroup filegroup_name]

| Add log file <filespec> [,... n]

| Remove file logical_file_name

| Add filegroup filegroup_name

| Remove filegroup filegroup_name

| Modify file <filespec>

| Modify filegroup filegroup_name filegroup_property

}

<Filespec >::=

(NAME = logical_file_name

[, FILENAME = 'OS _ file_name']

[, SIZE = size]

[, MAXSIZE = {max_size | UNLIMITED}]

[, FILEGROWTH = growth_increment])

The following example adds a data file in the default file group Primary file group of the Company database.

Alter database Company

ADD FILE

(

NAME = Test1dat2,

FILENAME = 'e: \ program files \ Microsoft SQL server \ mssql \ data \ t1dat2. ndf ',

SIZE = 5 MB,

Max size = 100 MB,

FILEGROWTH = 5 MB

) ...... The remaining full text>