private static void ExecuteSqlTransaction(string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { //以下代码是我修改的 //增加了for for(int i = 0; i < 10; ++i) { //下面的commandtext可以根据需要换成update,delete command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (i, 'Description')";//RegionID = i command.ExecuteNonQuery(); }//for //以上代码是我修改的 // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } } }
2.一个表如果有几十个字段,其实是比较少见的吧.数据库的可能还是需要提练的.
语句类似于下面：
sqlCommand.Transaction=connection.BeginTransaction();
sqlCommand.Execute(.....)
sqlCommand.Commit()(或Rollback)
“mobydick(敌伯威|我排著队拿著爱的号码牌) ”你说做法有问题。但如果要在一个有30列的表中插入200条记录，且没有相互重复的数据，要求在一个事务中执行，还有别的办法吗？请指点一下吧。如果里边来有修改、删除什么的。我这个保存的方法是通用的。
===================================================================================新增、修改、删除都可以做成存储过程，或者不想做成存储过程也行，可以直接把带参数SQL语句保存在程序中。这样，对于一个操作来说，参数就是最多30个。
使用事务不一定必须要在sql语句中实现事务，使用ado.net中的事务也是一样的。下面的帖子中是实例代码，使用一个for循环，执行10次insert语句。
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); SqlCommand command = connection.CreateCommand();
SqlTransaction transaction; // Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction; try
{
//以下代码是我修改的
//增加了for
for(int i = 0; i < 10; ++i)
{
//下面的commandtext可以根据需要换成update,delete
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (i, 'Description')";//RegionID = i
command.ExecuteNonQuery();
}//for
//以上代码是我修改的 // Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
没你说得那么麻烦,
N多个操作(增,删,改)都可以放到一个事务中,但不是一个sql,
什么时候你想提交了,提交整个事务就行了,
出错了,直接用事务回滚就行了,所有做的操作,都会作为一个整体回滚掉.用 soaringbird(飞翔鸟) ( ) 信誉：100 的方法就行.
而且,你那种一个sql字符串中的多个操作,好像也不是在一个事务中的吧(我记得sql server中是这么说的.).所以,建议正确的思路是 开 ado的显示事务.
我们做了一个专门的数据访问组件。采用所谓面向对象的思路，该组件的使用者不用组织SQL语句。他们只需创建数据包的实例设置数据包的属性，传给数据访问组件即可。这个数据访问组件比较通用，有人传入的数据包个数少，有人传入的多，所以......。我用soaringbird、mobydick的方法试试应该没问题。多谢了弟兄们。