用事务进行批量操作的问题？急!

[caoit]

public bool AddOrganizeKPI( DataTable listTable )
{
Open();
SqlTransaction myTrans;
myTrans =this.conn.BeginTransaction();
try
{

foreach(DataRow dr in listTable.Rows )
{
KPIID = HRM.CPublicFn.IntObjectToInt( dr["KPIID"] );
KPIIndex = short.Parse( dr["KPIIndex"].ToString() );
KPIPower = short.Parse( dr["KPIPower"].ToString() );
DestValue = decimal.Parse( dr["DestValue"].ToString() );
RealValue = decimal.Parse( dr["RealValue"].ToString() );
BigType = dr["BigType"].ToString();
SmallType = dr["SmallType"].ToString();
CheckScore = decimal.Parse( dr["CheckScore"].ToString() );
sqlCmd = this.AddOrganizeKPIListCmd();
sqlCmd.Connection = this.conn;
sqlCmd.Transaction = myTrans;
int i = sqlCmd.ExecuteNonQuery();
iRet = (int)(sqlCmd.Parameters["@ReturnValue"].Value);
if ( iRet != 0 )
{
ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
myTrans.Rollback();
return false;
}
}
myTrans.Commit();
return true;
}
catch(Exception e)
{
ErrorMsg = e.Message;
try
{
myTrans.Rollback();
}
catch (SqlException sqlexcept)
{
ErrorMsg = sqlexcept.Errors[0].Message;
}
}
finally
{
Close();
}
return false;
}

AddOrganizeKPIListCmd（）这是增加操作方法，为什么增加有两次记录了，在没有记录的情况下，可以用修改方法来操作,想了两天了，还没有解决，请指教

[caoit]

有人解决吗？

[jiezhi]

你的事物写得不对。
string sql = "update ...";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DSN"].ToString());
conn.Open();
SqlTransaction sqlTran = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = sqlTran;
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

sql = "update ...";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

sqlTran.Commit();
}
catch(Exception e1)
{
try
{
sqlTran.Rollback();
}
catch(SqlException ex)
{
throw ex;
}
throw e1;
}
finally
{
conn.Close();
}

[brightheroes]

楼主想根据ExecuteNonQuery()来控制是否回滚
请参考

http://community.csdn.net/Expert/topic/2995/2995537.xml?temp=.2118952

[caoit]

我的事务没错吧，

[brightheroes]

Sorry没有看清楚
楼主的问题

AddOrganizeKPIListCmd（）这是增加操作方法，为什么增加有两次记录了，在没有记录的情况下，可以用修改方法来操作,想了两天了，还没有解决，请指教
－－－什么意思？

[goody9807]

SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();

// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();

// Enlist the command in the current transaction.
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

Console.WriteLine("An exception of type " + e.GetType() +
"was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}

[caoit]

就是加了两个table到数据表里面了。

在没有任何记录的情况下，可以用修改记录，来增加一条，也就是说在执行事务之前，table已增加一次。

[caoit]

up

[Seeko0]

AddOrganizeKPI 是不是被执行了两次？ 根据数据量大小，可以考虑string sqlstr +=" insert ; ";生成完sqlstr语句后，再执行事务控制，插入数据库

[andrawsky]

SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();

// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();

// Enlist the command in the current transaction.
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

Console.WriteLine("An exception of type " + e.GetType() +
"was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}

[因为想念才会寂寞]

本人也在寻找方法中，不过建议你看看sqlhelper，楼主共同交流吧。

[husiwei]

up

[因为想念才会寂寞]

to: caoit (lost) 行了吗？
俺解决了，你将两个Sql语句写在一起，一并提交，这样就可以了。如下：
public bool DelCourse(int pID)
{
wisdeconnection conn=new wisdeconnection();
try
{
conn.BeginTransaction();
conn.RunSql("delete from courseware where id= "+pID
+"delete from coursepop where courseid="+pID);
conn.CommitTransaction();
return true;
}
catch(Exception er)
{
conn.Rollback();
throw new Exception(er.Message);
}
finally
{
conn.Dispose();
}

}

[liuyong_lll]

看来没有我的西了哦^_^

[wanghuixue]

mark