一个图书表,一个借书表,当借书的时候 将图书表中的‘借书情况’字段 变为 是,并且在借书表中插入一条借书的记录在winform 中 下面是实现代码 如果不加 事务就可以实现借书(!说明语句没问题),但加了 就总是提示失败! sqlConnection2.Open();
SqlTransaction trans = sqlConnection2.BeginTransaction();//定义一个事务
SqlCommand command = new SqlCommand();
command.Connection = sqlConnection2;
try
{
//修改书号为**的图书的状态为借出
command.CommandText = "UPDATE tbl_SM SET ZD = '是'WHERE (SH ='" + txt_sh.Text.ToString().Trim() + "')";
command.ExecuteNonQuery();
//向借书表中添加记录表示借出的图书
command.CommandText = "INSERT INTO tbl_JYQK VALUES('" + txt_bh.Text.Trim() + "','" + txt_sh.Text.Trim() + "','" + DateTime.Now + "')";
command.ExecuteNonQuery();
MessageBox.Show("借阅成功!");
trans.Commit(); //正常情况下提交事务 }
catch
{
MessageBox.Show("借阅失败!"); //发生异常则回滚事务
trans.Rollback();
}
finally
{
sqlConnection2.Close();
}
SqlTransaction trans = sqlConnection2.BeginTransaction();//定义一个事务
SqlCommand command = new SqlCommand();
command.Connection = sqlConnection2;
try
{
//修改书号为**的图书的状态为借出
command.CommandText = "UPDATE tbl_SM SET ZD = '是'WHERE (SH ='" + txt_sh.Text.ToString().Trim() + "')";
command.ExecuteNonQuery();
//向借书表中添加记录表示借出的图书
command.CommandText = "INSERT INTO tbl_JYQK VALUES('" + txt_bh.Text.Trim() + "','" + txt_sh.Text.Trim() + "','" + DateTime.Now + "')";
command.ExecuteNonQuery();
MessageBox.Show("借阅成功!");
trans.Commit(); //正常情况下提交事务 }
catch
{
MessageBox.Show("借阅失败!"); //发生异常则回滚事务
trans.Rollback();
}
finally
{
sqlConnection2.Close();
}
command.Connection = sqlConnection2;
下面加一句
command.Transaction =trans;
SqlTransaction sqlTrans = null;
try
{
sqlConn.Open();
sqlTrans = sqlConn.BeginTransaction();//事务开始
SqlCommand sqlComm = new SqlCommand("", sqlConn, sqlTrans);
sqlComm.CommandTimeout = 120;
sqlComm.CommandType = System.Data.CommandType.Text;
string Sql = "";
sqlComm.CommandText = Sql;
sqlComm.ExecuteNonQuery();
sqlTrans.Commit();
}
catch (Exception ex)
{
sqlTrans.Rollback();//事务回滚
}
finally
{
sqlConn.Close();
}
或
SqlConnection cn = new SqlConnection("");
cn.Open();
using (SqlTransaction txn = cn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand(strSQL, cn, txn);
}
{
int rowCount = 0;
int newID = 0;
Type type = obj.GetType();
OleDbCommand cmd = new OleDbCommand();
try
{
cmd.Connection = _Connection;
OpenConnection();
cmd.Transaction = _Connection.BeginTransaction();
cmd.CommandText = sql;
rowCount += cmd.ExecuteNonQuery(); //累加插入记录次数
//生成ID
cmd.CommandText = string.Format("SELECT MAX([{0}]) FROM [{1}]", _DataKey, type.Name);
newID = (int)cmd.ExecuteScalar();
cmd.Transaction.Commit();
CloseConnection();
type.GetProperty(_DataKey).SetValue(obj, newID, null); //不是很懂
return rowCount;
}
catch
{
try
{
cmd.Transaction.Rollback();
}
catch
{ }
CloseConnection();
throw new Exception(sql);
}
}
command.Transaction=sqlConnection2.BeginTransaction();trans.Commit(); //正常情况下提交事务 -->改为:command.Transaction.Commit();
1.用到事务就不要喷饭的关连接,至少在事务提交之前不要把连接关掉。
2.不要在所有的地方用事务,仅仅在需要数据完整性的地方用事务。事务会慢的呀,如果事务加了X锁,那就是串行处理的。
3.不要sqlTrans.Rollback();
好像有一个sqlTrans.SaveTransactionPoint("point");
做一个存储点,然后
sqlTrans.Rollback("point");
这是俺过瘾的看法,
StartTransaction
CommitTransaction
RollbackTransaction