garron fawcett garron fawcett - 1 month ago 10
ASP.NET (C#) Question

Where should IDBConnection be put to reduce repeated code?

Most Dapper tutorials use a private

IDBConnection
object to call methods i.e.

private IDbConnection db = new SqlConnection(...)


When using ASP.NET and MVC 5, where should I put this so I do not have to repeat it in every controller/repository using Dapper?
For instance, is there a way to put this in a startup class and use dependency injection like in ASP.NET Core, or some other technique to access it throughout the application?

Answer

Best connection creation mechanism as per my experience is the combination of DependencyInjection and ConnectionFactory.

Advantages are Multi fold:

  • Create a connection object at runtime in transaction or thread scope
  • At runtime change the data provider and thus database of the system ( using Connection Factory)

What you shall do (in Code):

Declare the IDBConnection object in the Data access Layer:

[Inject] // Property Injection
public IDBConnection Connection {get; set;}

Declare the binding using a DI framework like Ninject:

Bind<IDBConnection>().ToMethod(ctx => 
ConnectionFactory.CreateDbConnection("DefaultConnection"));

Create the DBConnection Factory as follows:

Connection factory fetches the Connection provider and connection string from the config file as follows:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=<Value>;Initial Catalog=<Value>;User Id=<Value>;Password=<Value>" providerName="System.Data.SqlClient" />
</connectionStrings>

Identifier is DefaultConnection, which is using the SqlClient provider, but at run time can be changed to the different client like Oracle, MySql

 using System;
 using System.Data.Common;

 public static class ConnectionFactory
    {
        /// <summary>
        /// Create DBConnection type based on provider name and connection string
        /// </summary>
        /// <param name="connectionIdentifier"></param>
        /// <returns></returns>
        public static DbConnection CreateDbConnection(string connectionIdentifier)
        {
            // Provider name setting
            var providerNameValue = ConfigurationManager.ConnectionStrings[connectionIdentifier].ProviderName;

            // Connection string setting
            var connectionStringValue = ConfigurationManager.ConnectionStrings[connectionIdentifier].ConnectionString;

            // Assume failure.
            DbConnection connection;

            // Null connection string cannot be accepted
            if (connectionStringValue == null) return null;

            // Create the DbProviderFactory and DbConnection.
            try
            {
                // Fetch provider factory
                var factory = DbProviderFactories.GetFactory(providerNameValue);

                // Create Connection
                connection = factory.CreateConnection();

                // Assign connection string
                if (connection != null)
                    connection.ConnectionString = connectionStringValue;
            }
            catch (Exception ex)
            {
                connection = null;
            }
            // Return the connection.
            return connection;
        }
}

How to use it:

For a single call and dispose

using(Connection)
{
 ...
}

For a Transaction context, use as-is, no using required