OleDbTransaction RollBack does not work with FoxPro data

I am using OleDb in a C # program that can work with SQL or foxpro. I noticed that when I run statements in foxpro data transaction and then try to call RollBack it returns no data. Rollback works great when working with SQL data, so I'm a little confused as to why it doesn't work in FoxPro either.

In my test application, I am reading records from a table (TESTTABLE) into a datatable that is bound to a datasource for a grid. I am adding rows to the grid and then hitting the save button to clear the database table and then looping through all the current rows of the associated DataTable to insert them back into the database table. In theory, if something went wrong during inserts, RollBack should undo the deletion. I notice that if the insert fails and I roll back the transaction, the deleted records remain deleted and the table reverts back to how it was before the deletion.

Edit 01/23/2013

I am using Visual FoxPro 9.0.0.5815. The tables are loose , so I need to run "MAKETRANSACTABLE" on the table after creating it. I do this by running the following command via OleDbCommand

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

      

Here is the save function I'm using ...

    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 transaction worked fine. I used the connection string:

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

It seems to me that the problem here is related to vfpoledb, the provider is not processing transactions correctly. An alternative I have researched using containers for databases instead of free tables, but it seems that the CREATE DATABASE function is not supported by vfpoledb.

+3


source to share


1 answer


I took your edit MakeTransactable

and focused it. He named:

cnn.MakeTransactable("directory/table");

      

Code:



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]);
    }
}

      

Method call GetActiveTransaction()

and WithTransactionSet()

pulls the active transaction out of the connection using internal state.

+3


source







All Articles