两个不同实体对象实现事物提交（SqlTransaction ）

public int ExecuteSqlTran(Maticsoft.Model.SHWL_Stock model, Maticsoft.Model.SHWL_OutPutComponet model2)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SHWL_Stock(");
strSql.Append("ID,ComponentName,Brand,Type,Unit,Count,Supplier)");
strSql.Append(" values (");
strSql.Append("@ID,@ComponentName,@Brand,@Type,@Unit,@Count,@Supplier)");
SqlParameter[] parameters = {
new SqlParameter("@ID", SqlDbType.Int),
new SqlParameter("@ComponentName", SqlDbType.NVarChar,50),
new SqlParameter("@Brand", SqlDbType.Int),
new SqlParameter("@Type", SqlDbType.Int),
new SqlParameter("@Unit", SqlDbType.Int),
new SqlParameter("@Count", SqlDbType.Int),
new SqlParameter("@Supplier", SqlDbType.Int)};
parameters[0].Value = model.ID;
parameters[1].Value = model.ComponentName;
parameters[2].Value = model.Brand;
parameters[3].Value = model.Type;
parameters[4].Value = model.Unit;
parameters[5].Value = model.Count;
parameters[6].Value = model.Supplier;

StringBuilder strSql2 = new StringBuilder();
strSql2.Append("insert into SHWL_OutPutComponet(");
strSql2.Append("SNum,ComponentName,Brand,Type,Unit,Count,Price,ModelName,Supplier,IsOld,WareHouse,Location,Operator,Memo,Company,Department,Status)");
strSql2.Append(" values (");
strSql2.Append("@SNum,@ComponentName,@Brand,@Type,@Unit,@Count,@Price,@ModelName,@Supplier,@IsOld,@WareHouse,@Location,@Operator,@Memo,@Company,@Department,@Status)");
strSql2.Append(";select @@IDENTITY");
SqlParameter[] parameters2 = {
new SqlParameter("@SNum", SqlDbType.VarChar,35),
new SqlParameter("@ComponentName", SqlDbType.NVarChar,50),
new SqlParameter("@Brand", SqlDbType.Int),
new SqlParameter("@Type", SqlDbType.Int),
new SqlParameter("@Unit", SqlDbType.Int),
new SqlParameter("@Count", SqlDbType.Int),
new SqlParameter("@Price", SqlDbType.Money),
new SqlParameter("@ModelName", SqlDbType.NVarChar,50),
new SqlParameter("@Supplier", SqlDbType.Int),
new SqlParameter("@IsOld", SqlDbType.NChar,1),
new SqlParameter("@WareHouse", SqlDbType.Int),
new SqlParameter("@Location", SqlDbType.Int),
new SqlParameter("@Operator", SqlDbType.NChar,15),
new SqlParameter("@Memo", SqlDbType.NVarChar,100),
new SqlParameter("@Company", SqlDbType.NVarChar,70),
new SqlParameter("@Department", SqlDbType.NVarChar,30),
new SqlParameter("@Status", SqlDbType.NChar,2)};
parameters2[0].Value = model2.SNum;
parameters2[1].Value = model2.ComponentName;
parameters2[2].Value = model2.Brand;
parameters2[3].Value = model2.Type;
parameters2[4].Value = model2.Unit;
parameters2[5].Value = model2.Count;
parameters2[6].Value = model2.Price;
parameters2[7].Value = model2.ModelName;
parameters2[8].Value = model2.Supplier;
parameters2[9].Value = model2.IsOld;
parameters2[10].Value = model2.WareHouse;
parameters2[11].Value = model2.Location;
parameters2[12].Value = model2.Operator;
parameters2[13].Value = model2.Memo;
parameters2[14].Value = model2.Company;
parameters2[15].Value = model2.Department;
parameters2[16].Value = model2.Status;
System.Collections.Generic.List<CommandInfo> cmdList = new System.Collections.Generic.List<CommandInfo>();
CommandInfo cmdl = new CommandInfo();
cmdl.CommandText = strSql.ToString();
cmdl.Parameters = parameters;
cmdList.Add(cmdl);
CommandInfo cmd2 = new CommandInfo();
cmdl.CommandText = strSql2.ToString();
cmdl.Parameters = parameters2;
cmdList.Add(cmd2);
return DbHelperSQL.ExecuteSqlTran(cmdList);
}

 

 

 

/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{ int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return 0;
}

object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;

if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return 0;
}
cmd.Parameters.Clear();
}
trans.Commit();
return count;
}
catch
{
trans.Rollback();
throw;
}
}
}
}

 

 

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{

foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}

 

public class CommandInfo
{
public object ShareObject = null;
public object OriginalData = null;
event EventHandler _solicitationEvent;
public event EventHandler SolicitationEvent
{
add
{
_solicitationEvent += value;
}
remove
{
_solicitationEvent -= value;
}
}
public void OnSolicitationEvent()
{
if (_solicitationEvent != null)
{
_solicitationEvent(this,new EventArgs());
}
}
public string CommandText;
public System.Data.Common.DbParameter[] Parameters;
public EffentNextType EffentNextType = EffentNextType.None;
public CommandInfo()
{

}
public CommandInfo(string sqlText, SqlParameter[] para)
{
this.CommandText = sqlText;
this.Parameters = para;
}
public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type)
{
this.CommandText = sqlText;
this.Parameters = para;
this.EffentNextType = type;
}
}