在C#中开启事务的步骤
01.调用SqlConnection对象的BeginTransaction()方法,创建一个SqlTransaction对象,标志事务开始。
02.将创建的SqlTransaction对象分配给要执行的SqlCommand的Transaction属性。
03.调用相应的方法执行SqlCommand命令。
04.调用SqlTransaction的Commit()方法完成事务。或调用Rollback()方法终止事务。
例子
//准备连接字符串
string str = "data source=.;initial catalog=Myschool;uid=sa;pwd=123";
//创建数据库连接对象
SqlConnection con = new SqlConnection(str);
//sql语句:添加一条记录到年级表
string sql = "insert into grade values(@gradename)";
//创建SqlParameter对象,设置参数
SqlParameter sp = new SqlParameter("@gradename", txtgradename.Text);
//创建命令对象
SqlCommand cmd = new SqlCommand(sql, con);
//通过Parameter集合的add()方法天填充参数集合
cmd.Parameters.Add(sp);
//打开连接
con.Open();
//默认让SqlTransaction对象为空
SqlTransaction trans = null;
//开启事务:标志事务的开始
trans = con.BeginTransaction();
try
{
//将创建的SqlTransaction对象分配给要执行的sqlCommand的Transaction属性
cmd.Transaction = trans;
//执行sql如果添加成功放回1
int count=cmd.ExecuteNonQuery();
if (count > 0)
{
MessageBox.Show("成功");
//事务提交
trans.Commit();
}
else
{
MessageBox.Show("失败");
//事务回滚
trans.Rollback();
}
}
catch (Exception)
{
//如果某个环节出现问题,则将整个事务回滚
trans.Rollback();
}
///循环事务代码
public static bool SqlTransSqlStr(List<string> SQLStringList, ref string ErrorMsg)
{
bool i = false;
string connectionString = ConfigurationManager.ConnectionStrings["demo"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
tx.Commit(); i = true;
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
ErrorMsg = E.Message;
i = false;
}
}
return i;
}
//下面的具体代码为个人实际项目测试成功
SqlConnection con = DB.lianjie();
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
SqlTransaction tx = con.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int i = 1; i < dt.Rows.Count; i++)
{
DateTime a1 = Convert.ToDateTime(dt.Rows[i][1]);
DateTime a2 = Convert.ToDateTime(dt.Rows[i][2]);
DateTime a3 = Convert.ToDateTime(dt.Rows[i][3]);
DateTime a4 = Convert.ToDateTime(dt.Rows[i][4]);
DateTime a5 = Convert.ToDateTime(dt.Rows[i][10]);
DateTime a6 = Convert.ToDateTime(dt.Rows[i][11]);
string sqlstr = "insert into xuhao(shebeirukudata,shebeichukudata,SIMrukudata,SIMchukudata,shebeileixing,shebeiSN,SIMkahao,zhanghu,changshang,jiaofeiri,daoqiri,rukujia,chukujia,beizhu,flagkehu,flagguangbu)values('" + a1 + "','" + a2 + "','" + a3 + "','" + a4 + "','" + dt.Rows[i][5] + "','" + dt.Rows[i][6] + "','" + dt.Rows[i][7] + "','" + dt.Rows[i][8] + "','" + dt.Rows[i][9] + "','" + a5 + "','" + a6 + "','" + dt.Rows[i][12] + "','" + dt.Rows[i][13] + "','" + dt.Rows[i][14] + "',0,0)";
//插入的insert语句??c1?c2?c3?是数据在数据库中的列名?table1是表名
cmd.CommandText = sqlstr;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tx.Commit();
int cc = dt.Rows.Count - 1;
MessageBox.Show("您成功添加了'" + cc + "'条数据");
textBox2.Text = Convert.ToString(cc);
}
catch
{
tx.Rollback();
MessageBox.Show("出错");
}
C#开启事务处理(8)
最新推荐文章于 2024-07-29 09:00:00 发布