Connection Per Request

Question

I am trying to maintain a single connection per request in my web application.

On the first attempt to connect to the database each request I create a new instance of the connection and store it in HttpContext.Current.Items, which works great in a traditional web application, however this all falls apart when I use async await.

The issue is that httpContact.Current returns null when called from an async method, (I am guessing this is because current is tied into the original thread?).

Is there an alternative which will allow my to create and dispose my connection per request and still work when using async await?

Edit: Code sample as requested

    public static DatabaseContext Context
    {
        get
        {
            if (HttpContext.Current.Items.Contains(DbContextKey))
            {
                return (DatabaseContext)HttpContext.Current.Items[DbContextKey];
            }

            var context = new DatabaseContext();
            HttpContext.Current.Items.Add(DbContextKey, context);
            return context;
        }
    }

Can you show an example of an async call? Our code is full of async calls and we didn't have to change anything, altough we access the context through `Controller.HttpContext`. — user247702, Aug 05 '14 at 15:20
If you are worried about the weight of creating new connections with each request, the MSSQL connection pool should alleviate this concern. It is best practice to create a new connection with every request. — vesuvious, Aug 05 '14 at 15:26
To use `async`/`await` on ASP.NET, you have to upgrade to .NET 4.5 and [explicitly set the `targetFramework` to `4.5` in your `app.config`](http://blogs.msdn.com/b/webdev/archive/2012/11/19/all-about-httpruntime-targetframework.aspx). — Stephen Cleary, Aug 05 '14 at 16:59

score 3 · Accepted Answer · edited May 23 '17 at 11:56

To flow HttpContext across async-await calls, you can set aspnet:UseTaskFriendlySynchronizationContext to true in your app config.

From MSDN:

If this key value is set to false [default], asynchronous code paths in ASP.NET 4.5 behave as they did in ASP.NET 4.0. If this key value is set to true, ASP.NET 4.5 uses code paths that are optimized for Task-returning APIs. Setting this compatibility switch is mandatory for WebSockets-enabled applications, for using Task-based asynchrony in Web Forms pages, and for certain other asynchronous behaviors. This will cause you ASP.NET application to use the new AspNetSynchronizationContext instead of LegacyAspNetSynchronizationContext:

<appSettings>
   <add  key="aspnet:UseTaskFriendlySynchronizationContext" value="true" />
</appSettings>

You can read more about this in What's the meaning of "UseTaskFriendlySynchronizationContext"? and Understanding the SynchronizationContext in ASP.NET

+1. Be aware, though, that this means you need to use async methods all the way from the top of your app down. If you try to do a `Task.Wait()` on an async operation, you'll end up with a thread lock. — StriplingWarrior, Aug 05 '14 at 15:37
This is true not only for ASP.NET, but also for any type of application where a custom synchronizationcontext is implemented (other than the default ThreadPoolSynchronizationContext) — Yuval Itzchakov, Aug 05 '14 at 15:40

score 0 · Answer 2 · answered Oct 27 '21 at 09:05

Why not put this into dependency injection, so the dependency injection is set to create the connection object with each request?

You could then have a base repository class where you inject the connection object, and open it in the constructor.

For example in simple injector, you would write something like this:

var container = new Container();
container.Options.DefaultScopedLifestyle = new WebRequestLifestyle();

var connectionStringMain = ConfigurationManager.ConnectionStrings["main"].ConnectionString;
container.Register<IDbConnectionMain>(() => new DbConnectionMain(new MySqlConnection(connectionStringMain)), Lifestyle.Scoped);

That way you can just inject it into your class:

public class MyClass
{
    private IDbConnectionMain _myconnection;

    public MyClass (IDbConnectionMain myConnection)
    {
        _myConnection = myConnection;
        _myConnection.Open();
    }
}

I would be interested to hear why this has been marked down – Philip Johnson Feb 21 '22 at 14:08 — Philip Johnson, Feb 21 '22 at 14:08

score -2 · Answer 3 · answered Aug 05 '14 at 15:24

You may implement the singleton pattern using ado.net

using System;
using System.Data;
using System.Data.SqlClient;
public class ConnSingleton{

    public static SqlConnection connInstance = null ;
    public static Object obj = new Object();

        public static SqlConnection GetInstance(string connstring)
            {
               if (connInstance == null)
                {
                     lock(obj){
                               if (connInstance == null)
                                  {
                                    connInstance = new SqlConnection (connstring);
                                   }
                               }
                 }
                return connInstance ;
            }

    }

Usage

ConnSingleton.GetInstance("your connection string").Open();

This will result in slowing down requests to the database. Basically every request is being serialized instead of being able to run on parallel. Although this is the proper way to share a connection (and therefore the right answer to the question), it would not be optimal. Utilizing connection pooling and creating a new connection for every request will be faster even though it doesn't sound like it would be. — vesuvious, Aug 05 '14 at 15:29
This results in concurrent usage of the same connection object. That's not allowed. — usr, Aug 05 '14 at 17:29

Connection Per Request

3 Answers3