也求一事务回滚程序，请教高手！
public int proc_returnid(string sqlstr)
{
using (SqlConnection conn = new SqlConnection(connection()))
{
try
{
SqlCommand cmd = new SqlCommand( "addsimpstor ", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter para;
para = cmd.Parameters.Add( "@csql ", SqlDbType.VarChar, 7500);
cmd.Parameters[ "@csql "].Value = sqlstr;
para = cmd.Parameters.Add( "@returnid ", SqlDbType.Int);
para.Direction = ParameterDirection.Output;
para = cmd.Parameters.Add( "@retime ", SqlDbType.VarChar, 500);
para.Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
return Convert.ToInt32(cmd.Parameters[ "@returnid "].Value);
}
catch (Exception ex)
{
errmessage.logerr(sqlstr);
return 0;
}
}
}
以上执行的是一个插入程序
注释：
SqlDataReader read;
int i;
程序开始
while read.read()
{
i=proc_returnid( "insert into news(titles)values( ' "+read[ "titles "].tostring()+ " ') ");
}
假如read里有10条记录，当在插入某一条数据的时候出现了错误，那么就回滚到本次插入之前的数据状态，请问回滚程序应该怎么写啊（我使用的回滚方式为SqlTransaction）请教高手！
[解决办法]
用Save()方法。。。
回滚的时候可以RollBack()到一个点上。。。
[解决办法]
示例代码：
/// <summary>
/// 新增 SMD_QCDailyReport 数据
/// </summary>
/// <param name= "ntySMD_QCDailyReport "> SMD_QCDailyReport Entity </param>
/// <param name= "dtBugAmount "> BugAmount DataTable </param>
/// <returns> 成功：true, 失败：false </returns>
public bool New(SMD_QCDailyReport ntySMD_QCDailyReport, DataTable dtBugAmount)
{
bool blnReturn = false;
int ReportID;
BaseData basData = new BaseData();
// Init DataAccess Class
BugAmountData mBugAmountData = new BugAmountData(basData);
SMD_QCDailyReportData mQCDailyReportData = new SMD_QCDailyReportData(basData);
basData.BeginTransaction(); // Begin Transaction
// 增加报表,并返回 ID
blnReturn = mQCDailyReportData.New(ntySMD_QCDailyReport, out ReportID);
if (!blnReturn)
{
basData.RollbackTransaction(); // Rollback Transaction
return blnReturn;
}
// 保存 "缺陷 "数据
if (dtBugAmount.Rows.Count > 0)
{
BugAmount ntyBugAmount = new BugAmount();
ntyBugAmount.ReportType = "SMD01 ";
ntyBugAmount.ReportID = ReportID;
for (int i=0; i <dtBugAmount.Rows.Count; i++)
{
ntyBugAmount.BugID = Int32.Parse(dtBugAmount.Rows[i][ "BugID "].ToString());
ntyBugAmount.Amount = Int32.Parse(dtBugAmount.Rows[i][ "Amount "].ToString());
blnReturn = mBugAmountData.New(ntyBugAmount);
if (!blnReturn)
{
basData.RollbackTransaction(); // Rollback Transaction
return blnReturn;
}
}
}
basData.CommitTransaction(); // Commit Transaction
return blnReturn;
}
[解决办法]
晕死
public int proc_returnid(SqlConnection conn, string sqlstr)
{
return proc_returnid(conn, null, sqlstr);
}
public int proc_returnid(SqlConnection conn, SqlTransaction transaction, string sqlstr)
{
try
{
SqlCommand cmd = new SqlCommand( "addsimpstor ", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter para;
para = cmd.Parameters.Add( "@csql ", SqlDbType.VarChar, 7500);
cmd.Parameters[ "@csql "].Value = sqlstr;
para = cmd.Parameters.Add( "@returnid ", SqlDbType.Int);
para.Direction = ParameterDirection.Output;
para = cmd.Parameters.Add( "@retime ", SqlDbType.VarChar, 500);
para.Direction = ParameterDirection.Output;
conn.Open();
if (transaction != null)
{
cmd.Transaction = transaction;
}
cmd.ExecuteNonQuery();
return Convert.ToInt32(cmd.Parameters[ "@returnid "].Value);
}
catch (Exception ex)
{
errmessage.logerr(sqlstr);
return 0;
}
}
调用：
SqlDataReader read;
int i;
SqlConnection conn = new SqlConnection(connection());
SqlTransaction transaction = conn.BeginTransaction();
while (read.read())
{
i = proc_returnid( "insert into news(titles)values( ' " + read[ "titles "].tostring() + " ') ");
if (有错误)
{
transaction.Rollback();
}
}
transaction.Commit();
[解决办法]
调用同一个连接，调用之间 conn.begintransaction()
调用之后 conn.commit()
中途出现异常就 conn.rollback
