I have created a simpler example that illustrates my problem:

        public static void TestSqlExecutor()
        {
            var options = new OdbcSqlAnywhereDataContextOptions(connectionString);
            using (var context = new OdbcSqlAnywhereDataContext(options))
            {
                string sql = "select connection_property ('number') as connection_id from dummy";
                string connectionId1 = string.Empty;
                string connectionId2 = string.Empty;

                var list1 = context.SqlExecutor.Select(sql);
                if (list1.Count > 0)
                {
                    connectionId1 = list1.FirstOrDefault().GetValue("connection_id");
                }

                var list2 = context.SqlExecutor.Select(sql);
                if (list2.Count > 0)
                {
                    connectionId2 = list2.FirstOrDefault().GetValue("connection_id");
                }

                Console.WriteLine($"connectionId1={connectionId1}, connectionId2={connectionId2}.");
            }
        }

The two ConnectionId values written to the console are different and sequential numbers.  

I do not have this problem if I use System.Data.Odbc classes.  In this case, subsequent SQL operations using the same OdbcConnection object use the same database connection. 

I'm really hoping I can find a solution as I would rather stick with SnapObjects.Data and SnapObjects.Data.Odbc because (1) I like the functionality and the .NET Datastore, and (2) it supports async operation.

Thanks!

Hi Dan,

SnapObjects uses connection pooling to minimize the cost of repeatedly opening and closing connections (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-pooling). By default, each database call will automatically open a connection, and will automatically close the connection after the call is completed.

To make multiple database calls use the same database connection, you can manually control it. You can try to use the CurrentConnection of the DataContext to manage the database connection through "open" and "close", which will enable multiple consecutive database operations to use the same connection. After manually opening the connection, you also need to manually close it, otherwise the connection will always exist and will not be closed automatically.

Please try the following example and let us know if there are any problems. Thank you!

      public static void TestSqlExecutor()

        {

            var options = new OdbcSqlAnywhereDataContextOptions(connectionString);

            using (var context = new OdbcSqlAnywhereDataContext(options))

            {

                context.CurrentConnection.Open();

                string sql = "select connection_property ('number') as connection_id from dummy";

                string connectionId1 = string.Empty;

                string connectionId2 = string.Empty;

                var list1 = context.SqlExecutor.Select<DynamicModel>(sql);

                if (list1.Count> 0)

                {

                    connectionId1 = list1.FirstOrDefault().GetValue("connection_id").ToString();

                }

                var list2 = context.SqlExecutor.Select<DynamicModel>(sql);

                if (list2.Count> 0)

                {

                    connectionId2 = list2.FirstOrDefault().GetValue("connection_id").ToString();

                }

                context.CurrentConnection.Close();

                Console.WriteLine($"connectionId1={connectionId1}, connectionId2={connectionId2}.");

            }

        }

Regards,

David

Thanks, David. Your suggested code additions solved the problem!

By the way, I determined that Connection Pooling was not enabled for the ODBC driver. I changed that.

A few quick follow-ups, if you don't mind:

Q1) If I manually control the connection as you suggest, does the connection return to the pool after "context.CurrentConnection.Close();".

Q2) If I had not manually closed the connection, would it have been closed when the context object is destroyed upon exiting the "using" block?

Q3) Is there any way to tell whether an opened connection came from the pool or is fresh? I have a reason to ask this: SQL Anywhere database server Authenticated Edition requires the client to specify an authentication key for new connections.

Thanks again!