在发布System.Transaction名称之前,可以使用ADO.NET创建事务,也可以通过组件,特性和COM+(位于System.EnterpriseServices)运行库进行事务处理。
1.ADO.NET事务
首先看看传统的ADO.NET事务,如果没有手动创建事务,每条SQL语句就都有一个事务。如果多条语句应参与到一个事务处理中,就必须手动创建一个事务。
下面的代码是说明如何使用ADO.NET事务。SqlConnection类定义了BeginTransaction方法,它返回一个SqlTransation类型的对象。这个事务对象必须参与事务处理的每条命令关联起来。要把命令关联到事务处理上,可将SqlCommand类的Transaction属性设置SqlTransaction实例,为了使事务成功完成,必须调用Commit方法。如果有错误,就必须调用RollBack方法,并撤销每个修改。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess
{
public abstract class DBHelper
{
#region 获取数据库连接的地址
private static string _ConnStr;
public static string ConnString
{
set { _ConnStr =
value; }
}
#endregion 获取数据库连接的地址
#region 对数据库进行 增、删、改 操作
public static int ExecuteNonQuery(
string sql)
{
return ExecuteNonQuery(sql, CommandType.Text,
null);
}
public static int ExecuteNonQuery(
string sql, SqlParameter[] sqlParams)
{
return ExecuteNonQuery(sql, CommandType.Text, sqlParams);
}
public static int ExecuteNonQuery(
string sql, CommandType commandType, SqlParameter[] sqlParams)
{
int count =
0;
using (SqlConnection sqlConn =
new SqlConnection(_ConnStr))
{
using (SqlCommand sqlComm =
new SqlCommand(sql, sqlConn))
{
sqlComm.CommandType = commandType;
if (sqlParams !=
null)
{
foreach (SqlParameter sqlParam
in sqlParams)
{
sqlComm.Parameters.Add(sqlParam);
}
}
try
{
sqlConn.Open();
count = sqlComm.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
}
}
return count;
}
#endregion 对数据库进行 增、删、改 操作
#region 查询并返回一个DataSet类型结果
public static DataSet
ExecuteDataSet(
string sql)
{
return ExecuteDataSet(sql, CommandType.Text,
null);
}
public static DataSet
ExecuteDataSet(
string sql, SqlParameter[] parameters)
{
return ExecuteDataSet(sql, CommandType.Text, parameters);
}
public static DataSet
ExecuteDataSet(
string sql, CommandType commandType, SqlParameter[] parameters)
{
DataSet ds =
new DataSet();
using (SqlConnection connection =
new SqlConnection(_ConnStr))
{
using (SqlCommand command =
new SqlCommand(sql, connection))
{
command.CommandType = commandType;
command.CommandTimeout =
120;
if (parameters !=
null)
{
foreach (SqlParameter parameter
in parameters)
{
command.Parameters.Add(parameter);
}
}
try
{
SqlDataAdapter adapter =
new SqlDataAdapter(command);
adapter.Fill(ds);
}
catch (Exception e)
{
throw e;
}
}
}
return ds;
}
#endregion 查询并返回一个DataSet类型结果
#region 查询并返回一个DataTabel类型结果
public static DataTable
ExecuteDataTable(
string sql)
{
return ExecuteDataTable(sql, CommandType.Text,
null);
}
public static DataTable
ExecuteDataTable(
string sql, SqlParameter[] parameters)
{
return ExecuteDataTable(sql, CommandType.Text, parameters);
}
public static DataTable
ExecuteDataTable(
string sql, CommandType commandType)
{
return ExecuteDataTable(sql, commandType,
null);
}
public static DataTable
ExecuteDataTable(
string sql, CommandType commandType, SqlParameter[] parameters)
{
DataTable dt =
new DataTable();
using (SqlConnection connection =
new SqlConnection(_ConnStr))
{
using (SqlCommand command =
new SqlCommand(sql, connection))
{
command.CommandType = commandType;
if (parameters !=
null)
{
foreach (SqlParameter parameter
in parameters)
{
command.Parameters.Add(parameter);
}
}
try
{
SqlDataAdapter adapter =
new SqlDataAdapter(command);
adapter.Fill(dt);
}
catch (Exception e)
{
throw e;
}
}
}
return dt;
}
public static DataTable
ExecuteDataTable(
string sql,
string sqlCount, SqlParameter[] parameters,
ref int totalCount)
{
DataSet ds =
new DataSet();
using (SqlConnection connection =
new SqlConnection(_ConnStr))
{
using (SqlCommand command =
new SqlCommand(sql +
";" + sqlCount, connection))
{
command.CommandType = CommandType.Text;
if (parameters !=
null)
{
foreach (SqlParameter parameter
in parameters)
{
command.Parameters.Add(parameter);
}
}
try
{
SqlDataAdapter adapter =
new SqlDataAdapter(command);
adapter.Fill(ds);
if (ds.Tables.Count ==
2 && ds.Tables[
1] !=
null && ds.Tables[
1].Rows.Count >
0)
{
totalCount = (
int)ds.Tables[
1].Rows[
0][
0];
}
else
{
totalCount =
0;
}
}
catch (Exception e)
{
throw e;
}
}
}
if (ds !=
null && ds.Tables.Count >
0)
{
return ds.Tables[
0];
}
else
{
throw new Exception(
"Failed to query to meet the conditions of the collection!");
}
}
#endregion 查询并返回一个DataTabel类型结果
#region 查询并返回一个SqlDataReader对象实例
public static SqlDataReader
ExecuteReader(
string sql, CommandType commandType, SqlParameter[] parameters)
{
SqlDataReader dataReader;
using (SqlConnection connection =
new SqlConnection(_ConnStr))
{
using (SqlCommand command =
new SqlCommand(sql, connection))
{
command.CommandType = commandType;
if (parameters !=
null)
{
foreach (SqlParameter parameter
in parameters)
{
command.Parameters.Add(parameter);
}
}
try
{
dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
throw e;
}
}
}
return dataReader;
}
#endregion 查询并返回一个SqlDataReader对象实例
#region 查询并返回查询结果的第一行第一列
public static Object
ExecuteScalar(
string sql)
{
return ExecuteScalar(sql, CommandType.Text,
null);
}
public static Object
ExecuteScalar(
string sql, SqlParameter[] parameters)
{
return ExecuteScalar(sql, CommandType.Text, parameters);
}
public static Object
ExecuteScalar(
string sql, CommandType commandType, SqlParameter[] parameters)
{
object result =
null;
using (SqlConnection connection =
new SqlConnection(_ConnStr))
{
using (SqlCommand command =
new SqlCommand(sql, connection))
{
command.CommandType = commandType;
if (parameters !=
null)
{
foreach (SqlParameter parameter
in parameters)
{
command.Parameters.Add(parameter);
}
}
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
result = command.ExecuteScalar();
}
catch (Exception e)
{
throw e;
}
}
}
return result;
}
#endregion 查询并返回查询结果的第一行第一列
#region 执行事务
public static bool ExecuteTransaction(
string[] sqlList,
bool earlyTermination)
{
return ExecuteTransaction(sqlList,
null, earlyTermination);
}
public static bool ExecuteTransaction(
string[] sqlList, SqlParameter[] parameters,
bool earlyTermination)
{
using (SqlConnection connection =
new SqlConnection(_ConnStr))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
using (SqlCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
if (parameters !=
null)
{
foreach (SqlParameter parameter
in parameters)
{
command.Parameters.Add(parameter);
}
}
try
{
bool mark =
true;
foreach (
string str
in sqlList)
{
command.CommandText = str;
if (earlyTermination)
{
if (command.ExecuteNonQuery() <=
0)
{
mark =
false;
break;
}
}
else
{
command.ExecuteNonQuery();
}
}
if (!mark)
{
transaction.Rollback();
return false;
}
else
{
transaction.Commit();
return true;
}
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
}
}
}
public static bool ExecuteTransaction(Dictionary<
string, SqlParameter[]> sqlAndPara,
bool earlyTermination)
{
using (SqlConnection connection =
new SqlConnection(_ConnStr))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
using (SqlCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
try
{
if (sqlAndPara !=
null)
{
bool mark =
true;
foreach (KeyValuePair<
string, SqlParameter[]> kvp
in sqlAndPara)
{
command.CommandText = kvp.Key;
command.Parameters.Clear();
if (kvp.Value !=
null)
{
foreach (SqlParameter parameter
in kvp.Value)
{
command.Parameters.Add(parameter);
}
}
if (earlyTermination)
{
if (command.ExecuteNonQuery() <=
0)
{
mark =
false;
break;
}
}
else
{
command.ExecuteNonQuery();
}
}
if (!mark)
{
transaction.Rollback();
return false;
}
else
{
transaction.Commit();
return true;
}
}
else
{
return false;
}
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
}
}
}
#endregion 执行事务
}
}
调用事务
//XXX为在webconfig配置的数据库链接地址
DBHelper
.ConnString = Common
.Config.DBConnString[
"XXX"]
.ToString()
return DBHelper
.ExecuteDataTable(sbSQL
.ToString(), sbSQLTotal
.ToString(), sqlParamList
.ToArray(), ref totalCount)
注:如果有多条命令要运行在一个事务中,每条命令都必须与该事务关联起来。因为事务还与一个链接关联起来,所有这些命令也必须关联到同一链接实例上。ADO.NET事务不支持跨多个链接的事务,它总是关联到一个链接上的本地事务。
ADO.NET事务不是分布式事务,在ADO.NET事务中,很难使多个对象参与同一个事务中。