当我使用SqlDataAdapter修改多个表时，可以使用SqlTransaction提交吗? [英] Can I use SqlTransaction to commit ,when i am using SqlDataAdapter to modify multiple tables?

问题描述

以下是我的代码的一部分.
要么连接到本地数据库，要么连接到与本地计算机具有相同日期的远程数据库，它都可以正常运行.但是，如果连接到远程数据库，如果我更改任一计算机上的日期.it在调用mytran.Commit()时返回"TRANSACTION COMMIT没有相应的BEGIN TRANSACTION"错误.
这是否意味着我不能使用SqlTransaction提交这样的事务?为什么?

These below are part of my codes.
Either connected to a local database ,or connected to a remote database which has the same date with my local computer,it runs without any problem.But,in the case of being connected to a remote database,if I change the date on either computer.it returns "TRANSACTION COMMIT has no corresponding BEGIN TRANSACTION" error ,on calling mytran.Commit().
Does this mean I can''t using SqlTransaction to commit a transaction like this?Why?

mytran = myconn.BeginTransaction();
myda.InsertCommand = myconn.CreateCommand();
myda.UpdateCommand = myconn.CreateCommand();
myda.DeleteCommand = myconn.CreateCommand();
myda.InsertCommand.Transaction = mytran;
myda.UpdateCommand.Transaction = mytran;
myda.DeleteCommand.Transaction = mytran;
DataAdapterUpdate();
DataAdapterInsert();
DataAdapterDelete();
mytran.Commit();

推荐答案

有关如何使用事务的小例子在c#:

a small example on how to use the transaction in c#:

using (SqlCommand cmd = new SqlCommand(SqlQuery, new SqlConnection(settings.ConnectionString)))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 300; // 5 minutes
                if (null != sqlParameters)
                    cmd.Parameters.AddRange(sqlParameters.ToArray());
                cmd.UpdatedRowSource = UpdateRowSource.None;

                if (cmd.Connection.State.Equals(ConnectionState.Open))
                    cmd.Connection.Close();

                cmd.Connection.Open();

                // Start a local transaction.
                using (SqlTransaction transaction = cmd.Connection.BeginTransaction("SampleTransaction"))
                {
                    cmd.Transaction = transaction;

                    try
                    {
                        cmd.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception(ex.Message, ex.InnerException);
                    }
                    finally
                    {
                        if (cmd.Connection.State.Equals(ConnectionState.Open))
                            cmd.Connection.Close();
                    }
                }
            }

这篇关于当我使用SqlDataAdapter修改多个表时，可以使用SqlTransaction提交吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！