I am using OleDb in a C# program that can work with either SQL or foxpro. I noticed that when I run statements in a transaction with foxpro data and then try calling RollBack it does not revert the data. The rollback works fine when running with SQL data so I am a little confused why it wouldn't work in FoxPro also.
In my test application, I am reading records from a table (TESTTABLE) into a datatable which is being bound to the datasource for a grid. I add rows to the grid and then press the save button to clear the database table, then loop through all the current rows in the bound DataTable to insert them back into the database table. In theory, if something were to go wrong during the inserts, the RollBack should undo the delete. What I'm noticing is that if the insert fails and I rollback the transaction, the records that were deleted stay deleted rather than the table reverting back to how it was before the delete.
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])')"
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 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.
I took your MakeTransactable edit and fleshed it out. It's called like:
cnn.MakeTransactable("directory/table");
The 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]);
}
}
The GetActiveTransaction() and WithTransactionSet() method calls pull the active transaction from the connection by using internal state.