The principle and practice of C # database transaction (i)

Data | database

This article will cover various aspects of manipulating database transactions within the. NET Framework using the C # language.

Experience the transaction mechanism of SQL language

 as a large enterprise-level database, SQL Server2000 provides good support for transactions. We can use SQL statements to define, commit, and roll back a transaction.

 the SQL code shown below defines a transaction and is named "Mytransaction" (limited to space, this article does not discuss how to write a SQL language program, please refer to the reader's own books):

DECLARE @TranName VARCHAR (20)

SELECT @TranName = ' mytransaction '
BEGIN TRANSACTION @TranNameGOUSE Pubs
Go

UPDATE roysched
SET royalty = Royalty * 1.10
WHERE title_id like ' pc% '
Go

COMMIT TRANSACTION mytransaction
Go

 Here is a sample database with SQL Server2000, which, when committed, will increase the royalties paid for all best-selling computer books by 10%.

 Open the SQL Server2000 Query Analyzer, select the Pubs database, and then run this program, the results are obvious.

How does  run in a C # program? We remember that in ordinary SQL queries, it is generally necessary to assign the query statement to the Salcommand.commandtext attribute, and here, as with normal SQL query statements, assign these statements to the Sqlcommand.commandtext property. One thing to note is that the "go" statement marks the end of the SQL batch, which is needed to write SQL scripts, but is not necessary here. We can write the following procedure to verify this idea:

Transql.csusing System;
Using System.Data;
Using System.Data.SqlClient;
Namespace ASPCN
{
public class Dbtransql
{
file://transactions into SQL Server execution
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn (); MyConn.Open ();
SqlCommand mycomm=new SqlCommand ();
Try
{
Mycomm.connection=myconn;
mycomm.commandtext= "DECLARE @TranName VARCHAR (20)";
mycomm.commandtext+= "Select @TranName = ' Mytransaction '";
mycomm.commandtext+= "BEGIN TRANSACTION @TranName";
mycomm.commandtext+= "use pubs";
mycomm.commandtext+= "UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id like ' pc% '";
mycomm.commandtext+= "COMMIT TRANSACTION mytransaction";
Mycomm.executenonquery ();
}
catch (Exception err)
{
throw new ApplicationException ("Transaction operation error, System Information:" +err. message);
}
Finally
{
Myconn.close ();
}
}
file://Get Data connections
Private SqlConnection Getconn ()
{
String strsql= "Data source=localhost;integrated security=sspi;user id=sa;password=";
SqlConnection myconn=new SqlConnection (strSQL);
return myconn;
}
}

public class Test
{
public static void Main ()
{
Dbtransql trantest=new dbtransql ();
Trantest.dotran ();
Console.WriteLine (transaction processing completed successfully.) ");
Console.ReadLine ();
}
}
}

Notice that the SqlCommand object Mycomm, its CommandText property is just a concatenation of the preceding SQL code string, of course, where the "Go" statement has been completely removed. This statement is like a normal query, where the program submits the SQL text to the DBMS for processing and then receives the returned results (if any results are returned).

 It's natural that we finally see the output "Transaction completed successfully" and then use Enterprise Manager to view the roysched table of the pubs database, where all title_id fields have a value of 0.1 times times the royalty field of a book that begins with "PC."

 here, instead of using the ado.net transaction mechanism, we simply execute the SQL statement that executes the transaction as a normal query, so in fact the transaction does not use the related attributes of. NET at all.
Understanding transaction mechanisms in. Net

 as you know, there are two namespaces (namespace) in the. NET Framework that are used by applications to interact with the database system: System.Data.SqlClient and System.Data.OleDb. The former is designed to connect to a Microsoft company's own SQL Server database, which can be adapted to a variety of different databases. Both namespaces contain classes dedicated to managing database transactions, respectively, the System.Data.SqlClient.SqlTranscation class and the System.Data.OleDb.OleDbTranscation class.

 like their names, most of these two classes are the same, and the main difference is their connection mechanism, which provides a set of objects that call SQL Server directly, and the latter uses native OLE DB to enable data access. In fact, ado.net transactions are handled entirely within the database and are not supported by the Microsoft Distributed Transaction Coordinator (DTC) or any other transactional mechanism. This article will mainly introduce the System.Data.SqlClient.SqlTranscation class, the following paragraph, in addition to the special note, will use the System.Data.SqlClient.SqlTranscation class. 

Opening and submitting of transactions

 Now that we have a clear idea of the concepts and principles of the transaction, and as a C # developer who already has some basics, we are already familiar with some of the key points in writing database interactions, namely, using the open () method of the object of the SqlConnection class to establish a connection to the database server, The connection is then assigned to the Connection property of the SqlCommand object, and the SQL statement to execute is assigned to its CommandText property, so the database can be manipulated through the SqlCommand object. Of course we need to define an object of the SqlTransaction type for the transaction handler we are going to write. And seeing the transcation attribute of the SqlCommand object, it's easy to think that the new SqlTransaction object should be associated with it.

 based on the above understanding, we will begin to write our first transaction handler. We can skillfully write the following procedure:

Dotran.csusing System;
Using System.Data;
Using System.Data.SqlClient;
Namespace ASPCN
{
public class DbTran
{
file://Perform transaction processing
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn ();
MyConn.Open ();
SqlCommand mycomm=new SqlCommand ();
SqlTransaction mytran=new sqltransaction ();
Try
{
Mycomm.connection=myconn;
Mycomm.transaction=mytran;
　　　
file://Navigate to the pubs database
mycomm.commandtext= "use pubs";
Mycomm.executenonquery ();

file://Update Data
FILE://will all the computer class books
mycomm.commandtext= "UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id like ' pc% '";
Mycomm.executenonquery ()//COMMIT TRANSACTION
Mytran.commit ();
}
catch (Exception err)
{
throw new ApplicationException ("Transaction operation error, System Information:" +err. message);
}
Finally
{
Myconn.close ();
}
}
file://Get Data connections
Private SqlConnection Getconn ()
{
String strsql= "Data source=localhost;integrated security=sspi;user id=sa;password=";
SqlConnection myconn=new SqlConnection (strSQL);
return myconn;
}
}

public class Test{public static void Main ()
{
DbTran trantest=new DbTran ();
Trantest.dotran ();
Console.WriteLine (transaction processing completed successfully.) ");
Console.ReadLine ();
}
}
}

 Obviously, the program is very simple, we compile it confidently, but the unexpected results are immediately dispelled by our sense of achievement:

error CS1501: Overload "SqlTransaction" method does not get "0" parameter

What is the reason for ? Notice the code we initialized:

SqlTransaction mytran=new sqltransaction ();

 Obviously, the problem is here, in fact, the SqlTransaction class doesn't have a public constructor, and we can't create a new sqltrancaction type of variable like this. It is necessary to have a variable of type sqltransaction before transaction processing, and it is essential to associate the variable with the Transcation property of the SqlCommand class, but the initialization method is rather special. When initializing the SqlTransaction class, you need to use the Begintranscation () method of the SqlConnection class:

SqlTransaction Mytran; Mytran=myconn.begintransaction ();

　　
 This method returns a variable of type sqltransaction. After the BeginTransaction () method is invoked, all SQL statement execution actions based on the data connection object are considered to be part of the transaction Mytran. You can also specify transaction isolation levels and transaction names in the parameters of the method, such as:

SqlTransaction Mytran;
Mytran=myconn.begintransaction (isolationlevel.readcommitted, "sampletransaction");

　　
 on the concept of isolation levels we'll explore in the following sections, where we just need to remember how a transaction is started and associated with a particular data link.

 do not rush to understand what our affairs have done, see this line:

Mytran.commit ();

 Yes, that's how the transaction is committed. After the statement is executed, all database operations of the transaction take effect and persist for the persistence mechanism of the database transaction-even if the system has a fatal error after that, the transaction's impact on the database does not disappear.

 The above program has been modified, we can get the following code (in order to save space, the repetition has been omitted, please refer to the previous text):

DoTran.cs ... ｝

file://Perform transaction processing
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn ();
MyConn.Open ();
SqlCommand mycomm=new SqlCommand ();

File://SqlTransaction mytran=new sqltransaction ();
file://Note that the SqlTransaction class has no exposed constructors

SqlTransaction Mytran;

FILE://Create a transaction
Mytran=myconn.begintransaction ();
Try
{
file://Since then, data operations based on the connection are considered part of the transaction
file://the following binding connection and transaction objects
Mycomm.connection=myconn;
Mycomm.transaction=mytran; file://Navigate to the pubs database
mycomm.commandtext= "use pubs";
Mycomm.executenonquery ()//Update data
FILE://will all the computer class books
mycomm.commandtext= "UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id like ' pc% '";
Mycomm.executenonquery ();
　
file://COMMIT Transaction
Mytran.commit ();
}
catch (Exception err)
{
throw new ApplicationException ("Transaction operation error, System Information:" +err. message);
}
Finally
{
Myconn.close ();
}
}
......

 so far, we have just mastered how to start and commit transactions. The next step we have to consider is what we can do and what not to do in a business.