/// <summary>
/// 新增 多条sql 事务处理
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(List<UserModel> model)
{
//Hashtable hatable = new Hashtable();
SortedList sl = new SortedList();
for (int i = 0; i < model.Count; i++)
{
string sql = " insert into 用户 (姓名,密码,公司编号,部门编号,职位编号,可用状态) values(@xingming,@mima,@gongsibianhao,@bumjenbianhao,@zhiweibianhao,@keyongzhuangtai) ";
SqlParameter[] field = {
new SqlParameter("@xingming",SqlDbType.VarChar),
new SqlParameter("@mima",SqlDbType.VarChar),
new SqlParameter("@gongsibianhao",SqlDbType.VarChar),
new SqlParameter("@bumjenbianhao",SqlDbType.VarChar),
new SqlParameter("@zhiweibianhao",SqlDbType.VarChar),
new SqlParameter("@keyongzhuangtai",SqlDbType.VarChar)
};
field[0].Value = model[i].姓名;
field[1].Value = model[i].密码;
field[2].Value = model[i].公司编号;
field[3].Value = model[i].部门编号;
field[4].Value = model[i].职位编号;
field[5].Value = model[i].可用状态;
for (int j = 0; j < i; j++)
{
sql += ";";
}
sl.Add(sql, field);
//hatable.Add(sql, field);
}
//bool bl = DbHelperSQL.ExecuteSqlTran(hatable);
bool bl = DbHelperSQL.ExecuteSqlTranSL(sl);
return bl;
}
DbHelperSQL中的方法
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
public static bool ExecuteSqlTranSL(SortedList SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return true;
}
catch
{
trans.Rollback();
return false;
throw;
}
}
}
}
注:使用Hashtable是无序的,而使用SortedList是按顺序排列的
如果我们想按照一定的顺序添加数据上面的方法就不好用了。我们可以使用ArrayList进行转换一下。
我们也可以根据需要对list进行排序
/// <summary>
/// 新增 多条sql 事务处理
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(List<UserModel> model)
{
ArrayList list = new ArrayList();
//Hashtable hatable = new Hashtable();
SortedList sl = new SortedList();
for (int i = 0; i < model.Count; i++)
{
string sql = " insert into 用户 (姓名,密码,公司编号,部门编号,职位编号,可用状态) values(@xingming,@mima,@gongsibianhao,@bumjenbianhao,@zhiweibianhao,@keyongzhuangtai) ";
SqlParameter[] field = {
new SqlParameter("@xingming",SqlDbType.VarChar),
new SqlParameter("@mima",SqlDbType.VarChar),
new SqlParameter("@gongsibianhao",SqlDbType.VarChar),
new SqlParameter("@bumjenbianhao",SqlDbType.VarChar),
new SqlParameter("@zhiweibianhao",SqlDbType.VarChar),
new SqlParameter("@keyongzhuangtai",SqlDbType.VarChar)
};
field[0].Value = model[i].姓名;
field[1].Value = model[i].密码;
field[2].Value = model[i].公司编号;
field[3].Value = model[i].部门编号;
field[4].Value = model[i].职位编号;
field[5].Value = model[i].可用状态;
for (int j = 0; j < i; j++)
{
sql += ";";
}
sl.Add(sql, field);
//hatable.Add(sql, field);
list.Add(sql);//向list中添加键
}
//bool bl = DbHelperSQL.ExecuteSqlTran(hatable);
bool bl = DbHelperSQL.ExecuteSqlTranSL(sl);
return bl;
}
DbHelperSQL中的方法
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
public static bool ExecuteSqlTranSL(SortedList SQLStringList,ArrayList list )
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//循环
foreach (string key in list)
{
string cmdText = key;
SqlParameter[] cmdParms = (SqlParameter[])SQLStringList[key];
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return true;
}
catch
{
trans.Rollback();
return false;
throw;
}
}
}
}