Hi,

We are currently using the .NET OleDB Provider to connect to a SQL Anywhere 11.0.1 database and everything is working fine. We were looking into moving to the SQL Anywhere .NET provider for performance reasons, but we have not been able to get transactions to work like they are for us currently. In the code below we create an SATransaction from the connection and then execute an insert on that transaction. We then call the transaction's Rollback, but the row that we just added is not rolled back. Is there something that we are missing or doing incorrectly?

Imports System.Data
Imports NUnit.Framework
Imports iAnywhere.Data.SQLAnywhere

<TestFixture()>
Public Class TestSQLAnywhereNetProvider
    Dim Connection As SAConnection

    <SetUp()>
    Public Sub Setup()
        Connection = New SAConnection("Data Source=Sybase Has4win; UID=<UID>; PWD=<Password>;POOLING=True;")
        Connection.InitString = "SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='<Our Authentication Key'"
        Connection.Open
    End Sub

    <TearDown()>
    Public Sub TearDown()
        Connection.Close
        Connection.Dispose
    End Sub

    <Test()>
    Public Sub TestTransactionRollsback()
        Dim CountQuery As SACommand = Connection.CreateCommand()
        CountQuery.CommandText = "SELECT COUNT(*) FROM DBA.t_version"

        Assert.AreEqual(1, CountQuery.ExecuteScalar(), "Number of rows in t_version") 
        Dim TransactionOne As SATransaction = Connection.BeginTransaction()
        Dim Query As SACommand = Connection.CreateCommand 
        Query.CommandText = String.Format("INSERT INTO DBA.t_version(major, minor, revision, path, cutdate) VALUES('{0}','{1}','{2}', '', '2014-01-01')", 11, 12, 13)
        Query.Transaction = TransactionOne
        Query.ExecuteNonQuery()

        CountQuery.Transaction = TransactionOne 
        Assert.AreEqual(2, CountQuery.ExecuteScalar(), "Number of rows in t_version") 
        TransactionOne.Rollback

        Assert.AreEqual(1, CountQuery.ExecuteScalar(), "Number of rows in t_version") 
    End Sub

This test fails on the last assert, because there are two rows in the table.

Thanks,