ASP. NET 3.5 core programming learning notes (12): sqlcommand, sqldatareader, transaction

Command Execution

The ADO. Net object model provides two types of command objects: one is a one-time command and the other is a data adapter. A one-time command is used to execute SQL commands or stored procedures. A cursor is returned. If a cursor is used, the connection must be open. A data adapter is a more powerful object that uses commands and cursors internally. It is used to obtain data and load it to the container class-dataset or datatable. Client Applications can process data without being connected to the data source.

Sqlcommand class

Sqlcommand represents SQL Server statements or stored procedures. It is derived from the dbcommand class and implements the idbcommand and icloneable interfaces. Execute commands in the connection context or use transactions. The constructor is as follows:

       public SqlCommand();
public SqlCommand(string);
public SqlCommand(string, SqlConnection);
public SqlCommand(string, SqlConnection, SqlTransaction);

The attributes of the sqlcommand class are shown in the following table:

 

The methods of the sqlcommand class are shown in the following table:

 

Sqlcommand can be executed synchronously or asynchronously. Synchronization Methods: executenonquery, executereader, executescalar, and executexmlreader.

ADO. NET data reader

The data reader-based method performs well in terms of system overhead and performance. Each time there is only one record cache, you do not have to wait until the entire result set is loaded into the memory.

The attributes of the sqldatareader class are listed in the following table:

 

The methods of the sqldatareader class are shown in the following table:

 

Close method to close the reader object. Note that closing the reader will not automatically close the underlying connection.

The data reader also has various get methods for DBMS, such as getsqldouble and getsqlmoney. The difference between getxxx and getsqlxxx is that getxxx returns the. NET Framework type, while getsqlxxx returns the. NET Framework encapsulation of the SQL server type (such as sqldouble and sqlmoney ).

The data reader is the most effective way to read data from the data source, but you should read the data and release the connection as soon as possible. You can read only one row at a time. You can use the read method to traverse the entire result set.

When executing the executereader method of the sqlcommand class, we can select "command behavior ":

Cmd. executereader (commandbehavior. closeconnection );

Commandbehavior is an enumeration type. Its members are shown in the following table:

 

Data reader and database connection are independent objects and must be managed and disabled separately. Both objects have the close method, which should be called separately. First, close the data reader and then close the database connection. After closeconnection is specified, the underlying connection is automatically closed when the data reader is closed.

The data reader cannot be directly created. Its constructor is marked as internal use (internal) and can be called only for classes defined in the same assembly. The data reader is created implicitly when the executereader method is executed.

Multi-result set access

The query may return multiple result sets. By default, the data reader first locates in the first result set. We can use the read method to traverse each record in the current result set. After the current result set is traversed, we can use the nextresult method to move it to the next result set. If no result set is available for reading, this method returns false.

Asynchronous commands

Ado. NET 2.0 provides asynchronous Command Execution Support for SQL Server. NET data providers. We can construct asynchronous execution commands in three ways: non-blocking, round robin, and callback.

To enable asynchronous commands, set the async attribute of the connection string to true. Enabling asynchronous commands improves the overall performance overhead to some extent. Therefore, it is best to use async keywords only for connection objects that require asynchronous operations.

Although we can call the synchronous method on the connections supported by the account opening asynchronous operation, it will only consume more resources, leading to performance degradation. If you want to use both synchronous commands and asynchronous commands, try to select different connections.

Asynchronous commands are not implemented by creating a new thread and blocking its execution. If asynchronous commands are enabled, ADO. NET will open the TCP socket to the database in overlapped mode and bind it to the I/O completion port. In this way, the synchronous operation can mimic the Asynchronous Operation execution mode.

Non-blocking commands are the easiest way to Implement Asynchronous commands. After starting this operation, you can continue to execute other unrelated methods and obtain the results when they are returned later. Regardless of the model selected, the first step for executing asynchronous commands is to call the beginexecutexxx method. Take the READ command as an example:

// Start asynchronous operations
Iasyncresult IAR = cmd. beginexecutereader ();
// Perform other operations
......
// Wait until the asynchronous operation is complete, and add this code to become the round robin Method
Do
{
......
} While (! IAR. iscompleted)

Sqldatareader reader = cmd. endexecutereader (IAR );

// Processing result
Processdata (Reader );

Example of using callback function:

// Start asynchronous commands

Iasyncresult AR = cmd. beginexecutereader (New asynccallback (processdata), CMD );

// Callback function processing

Public void processdata (iasyncresult AR)

{

// Retrieve the asynchronous Context

Sqlcommand cmd = (sqlcommand) IAR. asyncstate;

// Wait until the operation is completed

Sqldatareader reader = cmd. encexecutereader (IAR );

......

}

The call context passed into beginexecutereader as the second parameter will be stored in the asyncstate attribute of the iasyncresult object.

The callback function will be called in the thread pool thread.

Use of transactions

Ado. Net has two transaction types: local transaction and distributed transaction. Local transactions only use one resource, usually the database to be connected. Distributed transactions involve multiple types of resources. If the entire transaction is committed or rolled back, make sure that the changes made in each step are also committed or rolled back. Distributed transactions require the transaction process monitor.

In ADO. Net 1.x, you need to manage local transactions through many database-specific transaction objects (such as sqltransaction for SQL Server transactions.

In ADO. NET 2.0 and later versions, local transactions and distributed transactions can be managed through classes in the system. Transactions namespace.

Local Transaction Management in ADO. Net 1.x

To start a local transaction, you can use the intransaction method of the connection class. We can specify the name and isolation level for the transaction. This method maps to the begin transaction statement in SQL Server.

Sample Code:

       SqlTransaction tran = conn.BeginTransaction();
       SqlCommand cmd1 = new SqlCommand(cmdText1);

       cmd1.Connection = conn;
       cmd1.Transaction = tran;
       ……
       SqlCommand cmd2 = new SqlCommand(cmdText2);
       cmd2.Connection = conn;
       cmd2.Transaction = tran;
       ……

try
       {
              cmd1.ExecuteNonQuery();
              cmd2.ExecuteNonQuery();
              tran.Commit();
       }
catch
       {
           tran.Rollback();
       }
finally
       {
           conn.close();
       }

The isolation level of the transaction is used to specify the locking behavior of the connection. The general values include readcommitted (default), readuncommitted, repeatableread, and serializable. The functions of various isolation levels are shown in the following table:

 

We can use the commit and roolback methods to explicitly terminate transactions. Sqltransaction supports "Naming and saving points" in a transaction and can be used to roll back some transactions. The naming and saving points use the special SQL Server statement save transaction.

Transactionscope object

What should I do if I need to manipulate transactions in different databases? In ADO. Net 1.x, we need Enterprise Services to create distributed transactions. In ADO. NET 2.0 and later versions, local transactions and distributed transactions can be executed through the new transactionscope object. As follows:

      using(TransactionScope ts = new TransactionScope())
       {
using(SqlConnection conn = new SqlConnection(connString))
              {
                     SqlCommand cmd = new SqlCommand(cmdText, conn);
                     cmd.Connection.Open();
try
                     {
                            cmd.ExecuteNonQuery();
                     }
catch(SqlException ex)
                     {
                            lblMessage.Text = ex.Message;
                     }
               }
               ts.Complete();
       }

The connection object is defined within the scope of the transaction object and will be automatically involved in the transaction. The only thing we need to do is commit transactions. If this method is not called, the transaction will fail, and the rollback will be performed regardless of the commands in this process. Obviously, any exception will interrupt the transaction.

Distributed Transaction example:

       using(TransactionScope ts = new TransactionScope())
       {
using(SqlConnection conn = new SqlConnection(connString1))
              {
                     SqlCommand cmd = new SqlCommand(cmdText1, conn);
                     cmd.Connection.Open();
                     cmd.ExecuteNonQuery();
              }
using(SqlConnection conn = new SqlConnection(connString2))
              {
                     SqlCommand cmd = new SqlCommand(cmdText2, conn);
                     cmd.Connection.Open();
                     cmd.ExecuteNonQuery();
              }
              ts.Complete();
       }

Transactionscope automatically determines whether local transactions or distributed transactions are required. If necessary, transactionscope registers distributed resources; otherwise, it continues local processing. If the Code cannot run locally somewhere, transactionscope will submit the transaction to DTC in an appropriate way.