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)请教高手!
{
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)请教高手!
回滚的时候可以RollBack()到一个点上
调用之后 conn.commit()中途出现异常就 conn.rollback
/// 新增 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;
}
}while (read.read())
{
i = proc_returnid("insert into news(titles)values('" + read["titles"].tostring() + "')");
if (有错误)
{
transaction.Rollback();
}
}
调用:
transaction.Commit();
}
{
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;
}
}调用:
while (read.read())
{
i = proc_returnid("insert into news(titles)values('" + read["titles"].tostring() + "')");
if (有错误)
{
transaction.Rollback();
}
}transaction.Commit();
{
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.commit()中途出现异常就 conn.rollback