.net - OleDbTransaction RollBack 不适用于 FoxPro 数据

Question

我在 C# 程序中使用 OleDb，它可以使用 SQL 或 foxpro。我注意到，当我在使用 foxpro 数据的事务中运行语句然后尝试调用 RollBack 时，它不会还原数据。回滚在使用 SQL 数据运行时工作正常，所以我有点困惑为什么它也不能在 FoxPro 中工作。

在我的测试应用程序中，我正在将表 (TESTTABLE) 中的记录读取到绑定(bind)到网格数据源的数据表中。我将行添加到网格，然后按保存按钮以清除数据库表，然后遍历绑定(bind) DataTable 中的所有当前行，将它们重新插入到数据库表中。理论上，如果在插入过程中出现问题，RollBack 应该撤消删除。我注意到的是，如果插入失败并且我回滚事务，删除的记录将保持删除状态，而不是表恢复到删除前的状态。

Edit 01/23/2013

I am using Visual FoxPro 9.0.0.5815. The tables are loose so I have to run "MAKETRANSACTABLE" on the table after it is created. I do this by executing the following command through OleDbCommand

string commandText = "ExecScript('Use TESTTABLE In 0'+chr(13)+chr(10)+'MakeTransactable([TESTTABLE])')"

这是我正在使用的保存功能...

    private void Save()
    {

        //Table: TESTTABLE
        //Columns: REFERENCE NUMERIC(10), DESCRIPT VARCHAR(20)

        bool CauseException = cbBreak.Checked;
        //string SQLDbConnString = @"Provider=SQLNCLI;Server=TESTSERVER\\SQLExpress;Database=TESTDATA;Uid=test;Pwd=test;Connect Timeout=600";
        string FoxProDbConnString = @"Provider=vfpoledb;Data Source=C:\Test\Data;Exclusive=false;Nulls=false;ConnectTimeOut=600;QueryTimeOut=600;";

        using (OleDbConnection dbConn = new OleDbConnection(FoxProDbConnString))
        {
            dbConn.Open();
            using (OleDbTransaction dbTran = dbConn.BeginTransaction())
            {
                try
                {
                    //Run a delete command to remove all records from the table
                    string deletequery = "DELETE FROM TESTTABLE";
                    using (OleDbCommand cmd = new OleDbCommand(deletequery, dbConn, dbTran))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    //Loop through the DataTable rows and insert them into the database table
                    foreach (DataRow row in dt.Rows)
                    {
                        string insertquery = "INSERT INTO TESTTABLE (REFERENCE, DESCRIPT) VALUES (?,?)";
                        using (OleDbCommand cmd = new OleDbCommand(insertquery, dbConn, dbTran))
                        {
                            if (CauseException)
                            {
                                cmd.Parameters.Add("ref", OleDbType.Numeric).Value = "THIS IS NOT A NUMERIC VALUE";
                            }
                            else
                            {
                                cmd.Parameters.Add("ref", OleDbType.Numeric).Value = Convert.ToDouble(row["reference"]);
                            }
                            cmd.Parameters.Add("descript", OleDbType.VarChar).Value = Convert.ToString(row["descript"]).Trim();
                            cmd.ExecuteNonQuery();
                        }
                    }

                    //If no exceptions were thrown during the inserts, commit the transaction    
                    dbTran.Commit();
                    MessageBox.Show("Saved");
                }
                catch (Exception ex)
                {
                    //If an exception occurs, RollBack the transaction.  This SHOULD undo the delete.
                    dbTran.Rollback();
                    MessageBox.Show("Failed to save. Rolling back changes.");
                }
            }
        }
    }

Edit 01/24/2013

I tested the same code above using System.Data.Odbc instead of System.Data.OleDb and the transacation worked perfectly. I used the following connection string:

string odbcConnStr = @"Driver={INTERSOLV dBASEFile (*.DBF)};SourceDB=C:\Test\Data;";

It seems to me that the issue here is specifically with the vfpoledb provider not handling transactions correctly. An alternative I explored was to use Database Containers instead of loose tables but it seems the "CREATE DATABASE" function is not supported by vfpoledb.

Answer 1

我接受了您的 MakeTransactable 编辑并充实了它。它被称为:

cnn.MakeTransactable("directory/table");

代码:

public static class OleDbConnectionMakeTransactableExtensions
{
    private static readonly Dictionary<int, List<string>> AlreadyTransactable = new Dictionary<int, List<string>>();

    public static void MakeTransactable(this OleDbConnection connection, string freeTableName)
    {
        OleDbTransaction tran = connection.GetActiveTransaction();
        if (tran == null)
        {
            throw new InvalidOperationException("No transaction set");
        }

        int key = tran.GetHashCode();
        List<string> transactable;
        if (!AlreadyTransactable.TryGetValue(key, out transactable))
        {
            transactable = new List<string>();
            AlreadyTransactable.Add(key, transactable);
        }

        freeTableName = OtherExtensionMethods.GetCleanTablePath(freeTableName);

        if (transactable.Contains(freeTableName, StringComparer.InvariantCultureIgnoreCase))
        {
            return;
        }

        string alias = connection.GetCurrentAlias(freeTableName);
        if (alias == null)
        {
            connection.ExecScript(string.Format("use [{0}] shared again in 0", freeTableName));
            alias = connection.GetCurrentAlias(freeTableName);

            if (alias == null)
            {
                throw new InvalidOperationException(string.Format("Could not get an alias for table '{0}'", freeTableName));
            }
        }

        connection.ExecScript(string.Format(@"
IF NOT ISTRANSACTABLE('{0}') THEN
    MAKETRANSACTABLE('{0}')
ENDIF
", alias));

        transactable.Add(freeTableName);
    }

    public static string GetCurrentAlias(this OleDbConnection connection, string freeTableName)
    {
        freeTableName = OtherExtensionMethods.GetCleanTablePath(freeTableName);
        string alias = connection.ExecScript<string>(string.Format(@"
LOCAL cTable, cAlias, nIndex, nAliasCount, cDbf, cCheck
LOCAL ARRAY aUsedAliases[1]
LOCAL cList

cTable = LOWER(ADDBS(JUSTPATH(""{0}"")) + JUSTSTEM(""{0}""))
nAliasCount = AUSED(""aUsedAliases"")
IF nAliasCount = 0 THEN
    RETURN """"
ENDIF

cList = cTable + ': '

FOR nIndex = 1 TO nAliasCount
    cAlias = aUsedAliases[1, 1]
    cDbf = LOWER(DBF(cAlias))

    cCheck = RIGHT(ADDBS(JUSTPATH(cDbf)) + JUSTSTEM(cDbf), LEN(cTable))
    cList = cList + ', ' + cAlias + ' -> ' + cDbf + ' -> ' + cCheck

    IF cCheck = cTable THEN
        RETURN cAlias
    ENDIF
ENDFOR

RETURN """"
", freeTableName));
        return alias.EmptyToNull();
    }
}

public static class OleDbConnectionTransactionExtensions
{
    public static OleDbCommand WithTransactionSet(this OleDbCommand command)
    {
        if (command.Connection == null)
        {
            throw new InvalidOperationException("Command does not have a Connection set");
        }
        var transaction = command.Connection.GetActiveTransaction();
        if (!Equals(command.Transaction, transaction))
        {
            command.Transaction = transaction;
        }
        return command;
    }

    public static OleDbTransaction GetActiveTransaction(this OleDbConnection connection)
    {
        if (connection.State != ConnectionState.Open)
        {
            throw new ArgumentOutOfRangeException("connection", "Connection is not open");
        }
        return GetTransaction(connection);
    }

    private static readonly Func<OleDbConnection, OleDbTransaction> GetTransaction;

    static OleDbConnectionTransactionExtensions()
    {
        var getOpenConnectionMethod = typeof(OleDbConnection).GetMethod("GetOpenConnection", BindingFlags.Instance | BindingFlags.NonPublic);
        if (getOpenConnectionMethod == null)
        {
            throw new InvalidOperationException("Could not find the GetOpenConnection() internal method on OleDbConnection");
        }

        var internalType = getOpenConnectionMethod.ReturnType;
        var localTransactionProperty = internalType.GetProperty("LocalTransaction", BindingFlags.Instance | BindingFlags.NonPublic);
        if (localTransactionProperty == null)
        {
            throw new InvalidOperationException("Could not find the LocalTransaction property on OleDbConnectionInternal");
        }

        var param = Expression.Parameter(typeof(OleDbConnection));
        var connection = Expression.Call(param, getOpenConnectionMethod);
        var transaction = Expression.Property(connection, localTransactionProperty);

        var lambda = Expression.Lambda<Func<OleDbConnection, OleDbTransaction>>(transaction, param);
        var compiled = lambda.Compile();

        GetTransaction = compiled;
    }
}

public static class OtherExtensionMethods
{
    public static void ExecScript(this OleDbConnection connection, string script)
    {
        using (var command = connection.CreateCommand().WithTransactionSet())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "EXECSCRIPT";
            command.Parameters.Add(new OleDbParameter("script", script));
            command.ExecuteNonQuery();
        }
    }

    public static T ExecScript<T>(this OleDbConnection connection, string script)
    {
        using (var command = connection.CreateCommand().WithTransactionSet())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "EXECSCRIPT";
            command.Parameters.Add(new OleDbParameter("script", script));

            object raw = command.Logged().ExecuteScalar();
            return ConvertDbResult<T>(raw);
        }
    }

    public static string GetCleanTablePath(string tablePath)
    {
        return tablePath.ToLower().Replace("/", "\\").Trim("\\"[0]);
    }
}

GetActiveTransaction() 和 WithTransactionSet() 方法调用使用内部状态从连接中提取事件事务。