Error reading data from the connection Firebird

Question

We are working on migrating data from different source systems(Firebird, Oracle, SQL Server) to one target system (SQL Server).

We are getting Error reading data from the connection exception from Firebird connections.

Code we are using

Static DbFactory Class to Create SourceSystem Object

public static class DbFactory
{
    public static DbManager CreateDb(SourceDbType type)
    {
        switch (type)
        {
            case SourceDbType.Sql:
                return new SqlDbManager();
            case SourceDbType.FireBird:
                return new FireBirdDbManager();
        }
        return null;
    }
}

public enum SourceDbType
{
    Sql, FireBird
}

Base DbManager Class

public abstract class DbManager
{
    private DbConnection m_DbConnection;
    public virtual DbConnection DbConnection
    {
        get
        {
            if (m_DbConnection == null)
            {
                m_DbConnection = new SqlConnection();
            }
            return m_DbConnection;
        }
        set
        {
            this.m_DbConnection = value;
        }
    }



    public virtual void SetConnectionString(Migration migration, DataTable dtConnectionDetails = null)
    {
        try
        {
            DbConnection.ConnectionString = string.Format("Server={0};Database={1};User ID={2};Password={3};",
           migration.NetworkPartnerData.Server,
           migration.NetworkPartnerData.Database,
           migration.NetworkPartnerData.User,
          AESEncryptionDecryptionUtility.DecodeBase64(migration.NetworkPartnerData.Password));
        }
        catch
        {

            throw;
        }


    }

    public virtual void SetConnectionString(string connectionString)
    {
        try
        {
            DbConnection.ConnectionString = connectionString;
        }
        catch
        {

            throw;
        }


    }

    public virtual DataTable ExecuteDataTable(string ConnectionString, string queryText)
    {
        throw new NotImplementedException();
    }
}

FireBirdDbManager class inherited from DbManager

 public class FireBirdDbManager : DbManager
{
    private FbConnection fbconnection;
    public override DbConnection DbConnection
    {
        get
        {
            if (fbconnection == null)
            {
                fbconnection = new FbConnection();
            }
            return fbconnection;
        }
        set
        {
            this.fbconnection = value as FbConnection;
        }
    }

    public override void SetConnectionString(Migration migration, DataTable connectionDetails)
    {

        this.DbConnection.ConnectionString = string.Format("Server={0};Database={1};Port=3050;User ID={2};Password={3};Pooling=true;MinPoolSize=0;MaxPoolSize=50;",
            migration.NetworkPartnerData.Server,
            migration.NetworkPartnerData.Database + BuildDatabaseName(Convert.ToString(connectionDetails.Rows[0]["CL_NBR"])) + ApplicationConstants.FIREBIRD_DBFILE_EXTENSION,
            migration.NetworkPartnerData.User,
           iMigrationTool.Common.AESEncryptionDecryptionUtility.DecodeBase64(migration.NetworkPartnerData.Password));
    }
    public override DataTable ExecuteDataTable(string ConnectionString, string queryText)
    {
        using (var connection = new FbConnection(ConnectionString))
        {
            try
            {


                DataTable dt = new DataTable();
                connection.Open();
                using (FbTransaction readTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    FbCommand readCommand = new FbCommand();
                    try
                    {
                        readCommand.CommandText = queryText;
                        readCommand.Connection = connection;
                        readCommand.Transaction = readTransaction;
                        FbDataAdapter da = new FbDataAdapter(readCommand);
                        da.SelectCommand.CommandType = CommandType.Text;
                        da.Fill(dt);

                        readTransaction.Commit();
                    }
                    catch
                    {
                        readTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        readTransaction.Dispose();
                        if (connection.State == ConnectionState.Open)
                        {
                            connection.Close();
                        }
                        connection.Dispose();
                    }
                    return dt;
                }
            }
            catch (Exception ex)
            {
                int errorCode = ex.HResult;
                Logger.LogApplicationException(ex, null, "ERRORCODE:" + errorCode + "ConnectionString:" + ConnectionString, "MigrationWorker");
                throw ex;
            }
        }
    }
}

Exception Images

We are getting exception after running 3 to 4 migrations. If I restart the service again we are able to run 3 to 4 migrations.

Code we use to call the respective source systems:

We are very new to Firebird database systems and unable to solve the problem. On a side note we are using Visual Studio 2012, Azure Cloud service

Answer 1

We were able to reproduce the issue for Firebird 2.1.3.18185 by killing the Firebird task via the Task-Manager during executing a query via ADO.NET data provider (version 4.7.0). After that, we were always receiving the following exception:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred 
while executing the command definition. See the inner exception for details. 
    ---> FirebirdSql.Data.FirebirdClient.FbException: Error reading data from 
    the connection.
    ---> FirebirdSql.Data.Common.IscException: Error reading 
    data from the connection. at 
    FirebirdSql.Data.Client.Managed.Version10.GdsTransaction.BeginTransaction(Transa
    ctionParameterBuffer tpb) in c: Users Jiri Documents devel NETProvider 
    working NETProvider src FirebirdSql.Data.FirebirdClient Client Managed 
    Version10 GdsTransaction.cs:line 162 at 
    FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.BeginTransaction(Transacti
    onParameterBuffer tpb) in c: Users Jiri Documents devel NETProvider working 
    NETProvider src FirebirdSql.Data.FirebirdClient Client Managed Version10 
    GdsDatabase.cs:line 571 at 
    FirebirdSql.Data.FirebirdClient.FbTransaction.BeginTransaction() in c: Users 
    Jiri Documents devel NETProvider working NETProvider src 
    FirebirdSql.Data.FirebirdClient FirebirdClient FbTransaction.cs:line 363 
— End of inner exception stack trace —

In our case, the problem was solved and verified by disabling connection pooling (connection-string: '...;Pooling=false'). Interesting for us was the fact that according to [1] this is expected behavior when using connection pooling where the connection has been disconnected between several requests.

I hope this answer will help somebody else. This solution is also working with current ADO.NET 5.0.5 in our case.

Best regards, Thomas

[1] http://tracker.firebirdsql.org/browse/DNET-585?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel