Where should I specify IDBConnection to reduce repeated code?

Question

Most Dapper tutorials use a private object IDBConnectionto call ie methods

private IDbConnection db = new SqlConnection(...)

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

Answer 1

The best connection creation mechanism in my experience is a combination of DependencyInjectionand ConnectionFactory.

Advantages: Multi fold:

• , , ( Connection Factory)

( ):

IDBConnection :

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

DI, Ninject:

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

DBConnection Factory :

Factory :

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

DefaultConnection, SqlClient, , 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;
        }
}

:

using(Connection)
{
 ...
}

as-is, no using required