关于数据回滚问题请教

[ewdragon]

大家好。我现在是用dataset直接去更新数据库。发现如果更新到第三条时出错了。那时第一，二条在存储过程中的数据并不会回滚。我想请问大家怎么样才能实现更新时整张表都会回滚。也就是如果第三条出错时。第一，二条已经修改的数据也会回滚回去。

[time_is_life]

public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open();

SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;

// Start a local transaction
myTrans = myConnection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
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();
}
}

[自由人2008]

楼上的很对,在程序中实现的。当然也可以在存储过程来实现事务,速度更快一些

[nanhaoff]

使用事务

[kkeemmgg]

个人认为在数据访问层中,最好不要使用事务.其理由主要有三:其一,数据存储与处理,是数据库的本份,访问层作为数据交换的通道保持相对独立性;其二,OLEDB数据源不支持并行事务,如果在访问层加上事务处理,不便于以后的移植;其三,当前主流数据库基本都有高效,安全事务处理;其四,把数据及商业逻辑统一放在数据源本身处理,执行效率高,而且从一种数据库移植到另一种数据库的时候,基本不需要修改其它层.仅需改变一种存储过程的语法到另一种的翻译就行了.
个人体会.请批评.