刚学C#,想在一个事务中按条件更新一些表记录,更新完后读出数据做些其他动作,不知道怎么处理,下面代码老是报错
public bool delMail(byte vtype, string vidlist)
{
string sql = "";
bool rt = false;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open(); SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = tran;
cmd.CommandTimeout = 2000;
try
{
sql = "update t_mail set fsenddel=1 where fitemid in (" + vidlist + ")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery(); sql = "select * from t_mail where fsenddel=1 and factdel=1 and fhasannex=1 and fitemid in (" + vidlist + ")";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
string ss =row["fannex"].ToString();
//删除该路径文件
} tran.Commit();
rt = true;
}
catch(Exception ex)
{
tran.Rollback();
SystemError.SystemLog(ex.Message);
throw new SystemException("1003", ex.Message, ex);
}
finally
{
conn.Close();
}
return rt;
}
public bool delMail(byte vtype, string vidlist)
{
string sql = "";
bool rt = false;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open(); SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = tran;
cmd.CommandTimeout = 2000;
try
{
sql = "update t_mail set fsenddel=1 where fitemid in (" + vidlist + ")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery(); sql = "select * from t_mail where fsenddel=1 and factdel=1 and fhasannex=1 and fitemid in (" + vidlist + ")";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
string ss =row["fannex"].ToString();
//删除该路径文件
} tran.Commit();
rt = true;
}
catch(Exception ex)
{
tran.Rollback();
SystemError.SystemLog(ex.Message);
throw new SystemException("1003", ex.Message, ex);
}
finally
{
conn.Close();
}
return rt;
}
应该能正常
如果分配给命令的连接位于本地挂起事务中,ExecuteReader 要求命令拥有事务。命令的 Transaction 属性尚未初始化。
行 220: SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
行 221: DataSet ds = new DataSet();
行 222: adapter.Fill(ds); //这里错了
行 223: foreach (DataRow row in ds.Tables[0].Rows)
行 224: {
源文件: f:\网站\xfwebOA\App_Code\myMail.cs 行: 222
放到 cmd.ExecuteNonQuery(); 下面呢?
adapter.SelectCommand = 还有,别忘了那个隔离级别
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;或者:cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd); 然后再adapter.Fill这样才是正确的做法
获取一个DataReader对象,然后用这个DataReader对象填充DataSet对象,
最后返回DataSet
所以搂主在执行时,必须指定Command