public static async Task <DataSet> ExcuteAsync(RequestCollection requests, string session)
{
var response = new DataSet();
SqlTransaction transaction = null;
string connectString = string.Format(@"Server={0};User Id=sa;
Password=@Dkcnyh20081992;", servername);
using (SqlConnection connection = new SqlConnection(connectString))
{
try
{
await connection.OpenAsync();
transaction = connection.BeginTransaction();
foreach (var request in requests)
{
var category = request["Attributes"]["Category"].Value;
var command = request["Attributes"]["Command"].Value;
var parameters = request["Parameters"];
if (1 == 1 || command.StartsWith("sp_"))
{
if (ProcessNative(session, category, command, parameters, response) == false)
{
await ProcessSqlAsync(connection, transaction, session, category, command, parameters, response);
}
}
}
transaction.Commit();
connection.Close();
}
catch (Exception e)
{
var table = new DataTable("Error");
table.Columns.Add("Message");
table.Columns.Add("Source");
table.Columns.Add("StackTrace");
table.Columns.Add("HelpLink");
var row = table.NewRow();
row["Message"] = e.Message;
row["Source"] = e.Source;
row["StackTrace"] = e.StackTrace;
row["HelpLink"] = e.HelpLink;
table.Rows.Add(row);
response = new DataSet();
response.Tables.Add(table);
}
finally
{
connection.Close();
}
}
return(response);
}
/// <summary>
/// ExecuteTransaction执行一组SQL语句，如果有一条操作失败，所有的操作都回滚
/// </summary>
/// <param name="sqlList">要执行的SQL语句集合 </param>
/// <param name="parameters">参数数组 </param>
/// <param name="earlyTermination">事务中有数据不满足要求是否提前终止事务 </param>
/// <returns></returns>
public 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;
}
}
}
}
}
/// <summary>
/// 开启事务
/// </summary>
public static void BeginTransaction()
{
Conn.Open(); //打开连接
tran = conn.BeginTransaction(); //开启事务
}