首先以一個問題做開端 : 「如何管理Connection資源」,最簡單的方式是使用using語法糖,管控connection做完需要的邏輯後盡快釋放。

using(var cn = CreateConnection())
{
	//..略
}

但,隨著asp.net core越來越成熟,依賴注入的概念也普及於.net core開發人員。看到這樣是否會想,假如我有N處用到,代表要寫N次重複代碼,如以下代碼

public class HomeController : Controller
{
    public string A()
    {
		using(var cn = CreateConnection())
		{
			//..略
		}
    }

    public string B()
    {
		using(var cn = CreateConnection())
		{
			//..略
		}
    }	

	//..以此類推
}

那麼是否可以使用熟悉的DI方式解決呢? 或許就會寫出以下代碼,想要達到避免重複代碼,交由系統管理生命週期目的。

public class Startup
{
	public IConfiguration Configuration { get; }
	public Startup(IConfiguration configuration) => Configuration = configuration;
	public void ConfigureServices(IServiceCollection services)
	{
		services.AddMvc(options => options.EnableEndpointRouting = false);
		services.AddTransient<IDbConnection>(db => new SqlConnection(Configuration.GetConnectionString("DefaultConnection")));
	}
	public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
	{
		app.UseMvcWithDefaultRoute();
	}
}

public class HomeController : Controller
{
    private readonly IDbConnection _connection;

    public HomeController(IDbConnection connection) => _connection = connection;

    public string Index()
    {
		var result = _connection.Query("sql");
		//..略
    }
}

接著探討對Connection的概念 :

1. 同個邏輯內,盡量共用Connection不要浪費
2. 使用完盡快釋放
3. 沒用到就不要浪費資源

前面第一個條件上面例子做到了,但是第二點需要特別注意 : DI注入回收機制依賴於GC,生命週期最快也要等到方法運行完後才能結束。
假如遇到需要提早結束生命周期的情況,像是查找完資料接著放到AP端計算、整理資料,connection正確做法是查找完資料就釋放。
這時候建議還是使用using告訴系統何時要回收資源,如以下代碼 :

public class HomeController : Controller
{
    private readonly IDbConnection _connection;

    public HomeController(IDbConnection connection) => _connection = connection;

    public string Index()
    {
		var data = default(IEnumerable<dynamic>);
		using(_connection)
		{
			//資料查詢動作
			data = _connection.Query("SQL");
		}

		//資料計算、整理動作
    }
}

接著討論第三點 : 「沒用到就不要浪費資源」概念,舉例,當使用者輸入錯誤格式input資料,這時候就不應該建立connection物件。
但上面DI代碼邏輯是Controller一建立,Connection就會跟著建立,以SqlConnection為例子,當new的時候除了建立物件還會去做以下動作,這樣是一種資源浪費

public SqlConnection(string connectionString)
	: this(connectionString, null)
public SqlConnection(string connectionString, SqlCredential credential)
	: this()
{
	ConnectionString = connectionString;	
	//..略
	CacheConnectionStringProperties();
}	
private void CacheConnectionStringProperties()
{
	SqlConnectionString sqlConnectionString = ConnectionOptions as SqlConnectionString;
	if (sqlConnectionString != null)
	{
		_connectRetryCount = sqlConnectionString.ConnectRetryCount;
		if (_connectRetryCount == 1 && ADP.IsAzureSqlServerEndpoint(sqlConnectionString.DataSource))
		{
			_connectRetryCount = 2;
		}
	}
}
internal static bool IsAzureSqlServerEndpoint(string dataSource)
{
	int num = dataSource.LastIndexOf(',');
	if (num >= 0)
	{
		dataSource = dataSource.Substring(0, num);
	}
	num = dataSource.LastIndexOf('\\');
	if (num >= 0)
	{
		dataSource = dataSource.Substring(0, num);
	}
	dataSource = dataSource.Trim();
	for (num = 0; num < AzureSqlServerEndpoints.Length; num++)
	{
		if (dataSource.EndsWith(AzureSqlServerEndpoints[num], StringComparison.OrdinalIgnoreCase))
		{
			return true;
		}
	}
	return false;
}

甚至看過選擇在DI建立Connection時順便Open,正確應該等到有用到查詢的時候再open

services.AddTransient<IDbConnection>(db =>
{
	var connection = new SqlConnection(Configuration.GetConnectionString("DefaultConnection"));
	connection.Open();
	return connection;
});

這時候其實可以用簡單工廠 + Singleton來避免資源浪費的情況,這也是Java Spring SessionFactory使用的技巧

public interface IConnectionFactory
{
    IDbConnection CreateConnection();
}

public class SqlConnectionFactory : IConnectionFactory
{
    private readonly Func<string> _getConnectionString;
    public SqlConnectionFactory(Func<string> getConnectionString) => this._getConnectionString = getConnectionString;
    public IDbConnection CreateConnection() => new SqlConnection(_getConnectionString());
}

public class Startup
{
	public IConfiguration Configuration { get; }
	public Startup(IConfiguration configuration) => Configuration = configuration;

	public void ConfigureServices(IServiceCollection services)
	{
		services.AddMvc(options => options.EnableEndpointRouting = false);
		services.AddSingleton<IConnectionFactory>(_ => new SqlConnectionFactory(()=> Configuration.GetConnectionString("DefaultConnection")));
	}

	public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
	{
		app.UseMvcWithDefaultRoute();
	}
}

public class HomeController : Controller
{
    private readonly IConnectionFactory _factory;
    public HomeController(IConnectionFactory factory) => this._factory = factory;

    public string Index()
    {
		//..驗證資料,假如資料格式錯誤,不需要建立Connection
        using (var cnn = this._factory.CreateConnection())
        {
            var result = cnn.QueryFirst<string>("select 'Hello World' message");
            return result;
        }
    }
}