Problems with connection to DB stops working after a while

ServiceStack.OrmLite
1

Hi,
i have a API running on a IIS website.
After a few days/weeks i sometimes get this errormessage below.

The only thing that helps is to restart the IIS website.
Do you have any clue how this could happen?

I use ORMLite for database calls.

{

“ResponseStatus”: {
“ErrorCode”: “InvalidOperationException”,
“Message”: “ExecuteReader requires an open and available Connection. The connection’s current state is closed.”,
“StackTrace”: " at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ExprConvertToList[T](IDbCommand dbCmd, String sql, IEnumerable1 sqlParams, HashSet1 onlyFields)\r\n at ServiceStack.OrmLite.ReadExpressionCommandExtensions.Select[T](IDbCommand dbCmd, SqlExpression1 q)\r\n at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)\r\n at BokaMera.API.ServiceInterface.Requestfilters.ApiKeyRequestFilter.IsValid(String remoteIP, String apiKey)\r\n at BokaMera.API.ServiceInterface.Requestfilters.ApiKeyRequestFilter.Verify(IRequest req, IResponse res, Object dto)\r\n at ServiceStack.ServiceStackHost.ApplyRequestFiltersSingle(IRequest req, IResponse res, Object requestDto)\r\n at ServiceStack.ServiceStackHost.ApplyRequestFilters(IRequest req, IResponse res, Object requestDto)\r\n at ServiceStack.Host.RestHandler.ProcessRequestAsync(IRequest httpReq, IResponse httpRes, String operationName)"
}
}

2

I dont know if this helps but in AppHost i have this

   private static void ConfigureDataSources(Container container)
    {
        //Register to use an In Memory Cache Provider (default)
        container.Register<ICacheClient>(new MemoryCacheClient());

        // Register our OrmLite sql database factory
        var connectionString = WebConfigurationManager.ConnectionStrings["db"].ConnectionString;
        SqlServerDialect.Provider.RegisterConverter<TimeSpan>(
            new SqlServerTimeConverter
            {
                Precision = 7
            });
        container.Register<IDbConnectionFactory>(new OrmLiteConnectionFactory(connectionString,
            SqlServerOrmLiteDialectProvider.Instance));

        // Register our database  sql connection
        container.Register(db => container.Resolve<IDbConnectionFactory>().Open()).ReusedWithin(ReuseScope.Request);
    }
3

You should only register the DB Connection Factory as a singleton:

container.Register<IDbConnectionFactory>(c =>
    new OrmLiteConnectionFactory(connectionString, ...));

Don’t register a Memory Cache Client (it’s already the default):

container.Register<ICacheClient>(new MemoryCacheClient());

and don’t register an open DB Connection in an IOC:

container.Register(db => 
    container.Resolve<IDbConnectionFactory>().Open())
 .ReusedWithin(ReuseScope.Request);

When you use base.Db in your Service it automatically resolves an Open DB connection and automatically takes care of disposing it after your Service has finished executing.

If you want to access a DB connection outside of a Service, open it in a using scope so you can dispose of it straight away after you’ve finished using it, i.e:

using (var db = HostContext.Resolve<IDbConnectionFactory>().Open())
{
     //...
}
2 Likes
4

Thx @mythz, will test this and change it.

Regards Kristian

5

Hi Mythz,
sorry for my dumb questions but how can i access the db connection in AppHost.

Today i use the container.resolve to retrieve it but now it crashes as i dont add it into the container anymore.

 private void ConfigureRequestFilters(Container container)
    {
        var apiKeyFilter = new ApiKeyRequestFilter(container.Resolve<IDbConnection>());
        var basicAuthFilter = new BasicAuthPreAuthFilter();

        container.Register(apiKeyFilter);
        container.Register(basicAuthFilter);

        GlobalRequestFilters.Add(apiKeyFilter.Verify);
        GlobalRequestFilters.Add(basicAuthFilter.Verify);
        
        
        //Add customer authentication repsonse metadata properties for user
        this.GlobalResponseFilters.Add((req, res, responseDto) =>
        {
            if (res.Dto.GetType() == typeof(AuthenticateResponse))
            {
                var user = ((BokaMera.API.ServiceInterface.Security.AspNetMembership.AspnetMembershipAuthSession)res.Request.GetSession());
                var cs = (BokaMera.API.ServiceInterface.Security.AspNetMembership.AspnetMembershipAuthSession)req.GetSession();

                Dictionary<string, string> customUserData = new Dictionary<string, string>();
                customUserData.Add("CompanyUserId", user.CompanyUserId.ToString());
                customUserData.Add("RegisteredUserId", user.RegisteredUserId.ToString());
                customUserData.Add("FirstName", user.FirstName);
                customUserData.Add("LastName", user.LastName);
                customUserData.Add("Language", user.Language);
                customUserData.Add("ReferrerUrl", user.ReferrerUrl);
                customUserData.Add("SessionExpires", DateTime.Now.ToString());
                
                ((AuthenticateResponse)res.Dto).Meta = customUserData;
            }
        });
    }
6

You should be passing the thread-safe IDbConnectionFactory in your dependencies not an open DB Connection.

So you should change it to:

var apiKeyFilter = new ApiKeyRequestFilter(container.Resolve<IDbConnectionFactory>());

Then inside your ApiKeyRequestFilter you can access the db connection as normal, e.g:

using (var db = dbFactory.Open())
{
    //...
}

Anywhere else you DB access (e.g. outside ServiceStack) you can access it with:

using (var db = HostContext.Resolve<IDbConnectionFactory>().Open())
{
     //...
}
7

Thx!
I tried that before your answer but was not sure if it was correct, thx for confirming it!