SAP Sybase SQL Anywhere NullReference Exception when openening and closing many connections in a service

Question

Currently I've the problem that SAP Sybase SQL Anywhere randomly throws NullReferenceExceptions in a service which executes a lot of sql queries. The connections are always created in a using block and opened & closed correctly. There are not many parallel connections, but after a while (randomly) the following exception is thrown when opening and closing connections:

Exception: System.NullReferenceException: The object was not set to an instance.
   bei iAnywhere.Data.SQLAnywhere.SAConnection.Dispose(Boolean disposing)
   bei iAnywhere.Data.SQLAnywhere.SAConnection.Close()
   bei iAnywhere.Data.SQLAnywhere.SAConnection.get_State()
   bei product.Framework.DAL.ConnectionManager.GetOpenPoolConnection[T](String ModuleName, String Connection, Boolean resizePoolOnRimteOut, Int64 Time
Out, Boolean isSecondTry)
   bei product.Framework.DAL.ORM.Sybase.SybaseStack.LoadDataFromDB[T](String where_part, String connectionStringName, Object[] sa_params)
   bei product.Framework.DAL.ORM.Sybase.SybaseStack.LoadData[T](String optWherePart, Object[] parameter)
   bei product.PlugIn.DocCenterClient.AS_Modules.DefaultInstanceDataExportModule.DoSingalProcessing()
   bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCt
x)
   bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   bei System.Threading.ThreadHelper.ThreadStart()

Does someone know what is causing this behavior? We could not figure out any regularity behind it.

The exception is being thrown in GetOpenPoolConnection, which creates a new SAConnection and opens it:

internal static T GetOpenPoolConnection<T>(string Connection = "Default") where T : DbConnection
{
    // Read connection string from static dictionary
    string cConnectionString = GetConnectionString(Connection);
    T cToReturn = null;

    if (cConnectionString != null)
    {
        if (typeof(T) == typeof(SqlConnection))
        {
            cToReturn = (new SqlConnection(cConnectionString) as T);
            cToReturn.Open();
        }
        else if (typeof(T) == typeof(SAConnection))
        {
            cToReturn = (new SAConnection(cConnectionString) as T);
            cToReturn.Open();
        }
        else if (typeof(T) == typeof(OdbcConnection))
        {
            cToReturn = (new OdbcConnection(cConnectionString) as T);
            cToReturn.Open();
        }

        return cToReturn;
    }
    else
    {
        return null;
    }
}

This function is called as:

using (SAConnection connection = DAL.ConnectionManager.GetOpenPoolConnection<SAConnection>())
{
    var res = connection.Query<Guid>("SELECT InstanceDataGuid FROM AS_EX_Objects WHERE ExchangeObjectId = ?", new { ExchangeObjectId = ic.ItemId.ToString() });
    if (res.Any())
    {
        instanceDataGuid = res.Single<Guid>();
   }
}

We are using SAP SQL Anywhere 12 as database engine/server and SAP SQL Anywhere 16 as client component. The project and DB driver are 64-bit only. All the bugs that cause this should be fixed in the version of the ADO .Net Driver we're using (in the bugfixes, see engineering cases #797124, #741721 and #738144).

Old

without seeing you code this is something that you will have to actually go in and evaluate / check all the spots where you are creating instances of objects and disposing them. can you show the code that does the opening and closing of connections..? if you have this in a using then do not explicitly call the connection.Close() method – Corotto

Already thought about posting some code, but this happens at different points, but i will add some example. – Old

if the code is the same / pretty consistent in regards to how you are Opening and Closing then show an example of each.. – Corotto

@Corotto ok, added some code where this is happening (using Dapper there) – Old

check out this link it may answer your question about DAL.ConnectionManager.GetOpenPoolConnection codegur.press/35697729/… also on your ADO NET Drivers is this 32 bit or 64 bit what do you have your platform target set to under the project properties..? – Corotto

I've written DAL.ConnectionManager.GetOpenPoolConnection so thats no problem :D. In this case it is 64 Bit only. – Old

can you post that if it's not too much trouble.. perhaps there is an issue in there can't really tell with the little bit of code that you have posted.. sorry – Corotto

@Corotto ok, added the code. But don't be afraid, thats really ugly code, would not write any thing like this any more :) – Old

I would personally store the 3 different connection string in a config file but the only think that I can recommend is that you step through the code to see if it's doing the same thing you've been noticing on the target machine. can you reproduce the error on your local machine..? – Corotto

Yep, but the service some times crashs after a day and some times after an hour. So it's hard to figure out. But i will try it like you explaned. – Old

if you know of what type of DataBase then I would just assign the connection string to a value that should be already declared and assigned from the reading the App.Config I currently do this but using 2 different connection strings. – Corotto

Yes, i would like to change this code, but it is used in many other places, so i was afraid editing it until now. I will schedule some larger refactoring work and clean this parts. Thanks for your time! – Old

What are Query and Single methods? Did you wrote them? Are they implemented using deferred execution? – Isopleth

They are a part of dapper and written by stackoverflow. This is also happening using raw ado.net – Old

Can you try not to use GetOpenPoolConnection? Given the call stack it's the last place in your code before the error. Next are methods in the Sybase libs; I don't think you could do anything about them except update (if it is available)... – Isopleth

I think you will need more information. You don't really know what is null, it could be a number of variables from what is posted. – Hellenic

I would advise wrapping your GetOpenPoolConnection internal statements in a try catch block, and then log in a text file somewhere a everything you can about the call... including what step in the code you are in. – Hellenic

Preferably, you could step through the code, but you're stating that this is happening randomly in production after some time, so my assumption is that you cannot reproduce the error locally. That's why I'm suggesting the logging route. – Hellenic

Your call to GetConnectionString(Connection) could in fact be returning null for some reason, which might be causing this.... impossible to tell from the code posted at the moment. – Hellenic

As a matter of fact, you could also try // Read connection string from static dictionary string cConnectionString = GetConnectionString(Connection); T cToReturn = null; if (cConnectionString == null) {throw new Exception("Connection String was null!")}; else{..... – Hellenic

that would be easier to implement instead of the verbose logging (but might not be sufficient for your issue.) – Hellenic

@Hellenic thank you, will add more logging functions in here, so maybe can get more information about the environment. – Old

it may be helpful check below link. #4660642 – Pilgrimage

As from your code whenever the connection returned is null the "using" is hitting an exception, perhaps using a try catch will allow to consider this eventuality to avoid a crash and simply leave a single SQL undone or tried another time. Consider if any external root cause can prevent it, i.e. network timeout. Once I experienced DB connection issues due to a "next generation" network firewall which had IPS function detecting too many new connections per second as brute force attack against the server. – Wapiti

What error do you get when a connection gets a timeout? – Telium

Suggestion to verify that each of the API calls you are using for connecting to the database server, issuing SQL commands and looping through the results be checked to see if they need a dispose call. The .net framework may take some time to clean up the memory objects associated with a SQL query even after being disposed, in some cases with database wrapper code, they do not get deallocated. You can check via NetStat -A if running on Windows if the TCP/IP connections keep growing as this process runs. That would be indicative of .NET database code not being cleaned up. – Blip

The stack trace clearly shows a connection was closed and disposed, rendering it unusable and subject to garbage collection. I'm going to go out on a limb and suggest that occasionally, the string passed to GetOpenPoolConnection is null. An invalid string would throw some other type of exception, at least when cToReturn.Open() is called. Maybe you are using a property name or resource name that is looked up to get the connection string that is passed. It really does seem you have to be hitting return null. Have you added a breakpoint on it? – Yarbrough