Access数据库没有数据库连接池，每次打开和关闭数据库时都非常耗时，自己编写了一个简易的数据库连接池，添加数据库类型还可支持其他没有数据库连接池的数据库类型。
该数据库连接池要求必须更改web.config中的数据库连接字符串，添加Max Pool Size及Connect Timeout两个属性，如：<add name="AccessConnectString" connectionString="”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:"db.accdb;Max Pool Size=200;Connect Timeout=300"/>;，以获取需要最大的连接数量及连接对象生存期。
该数据库连接池是在关闭数据库连接，检验其他连接对象的生存期，这样在没有发出关闭数据库连接的请求时，一些已经超过生存期连接对象仍然在内存中，还无法做到与其他数据库连接池同样的效果。
数据库连接池代码如下：
/// <summary>
/// 连接对象。
/// </summary>
public class Pool
{
/// <summary>
/// 初始化。
/// </summary>
/// <param name="id">代码。</param>
/// <param name="connection">数据库连接对象。</param>
/// <param name="isUse">使用标志。</param>
/// <param name="openTime">打开时间。</param>
public Pool(Guid id, IDbConnection connection, bool isUse, DateTime openTime)
{
this.id = id;
this.connection = connection;
IsUse = isUse;
OpenTime = openTime;
}
private Guid id;
/// <summary>
/// 获取连接对象的代码。
/// </summary>
public Guid Id { get { return id; } }
private IDbConnection connection;
/// <summary>
/// 获取数据库连接对象。
/// </summary>
public IDbConnection Connection { get { return connection; } }
/// <summary>
/// 获取或设置一个值，该值指示连接对象是否已经使用，true-已经使用，否则没有使用。
/// </summary>
public bool IsUse { get; set; }
/// <summary>
/// 获取或设置连接对象打开数据库的时间。
/// </summary>
public DateTime OpenTime { get; set; }
}
/// <summary>
/// 连接池管理类。
/// </summary>
public static class ConnectionPool
{
private static List<Pool> pools = new List<Pool>();
private static int poolTimeout = 300;
private static int maxPoolSize = 200;
private static string connectString = "";
private static bool getParam = false;
static ConnectionPool()
{
}
/// <summary>
/// 获取参数。
/// </summary>
/// <param name="connectionString">配置文件中的数据库连接字符串。</param>
private static void GetParams(string connectionString)
{
string[] connectStrings = connectionString.Split(';');
StringBuilder newConnectString = new StringBuilder();
foreach (string subConnectString in connectStrings)
{
if (subConnectString.IndexOf("Provider", StringComparison.InvariantCultureIgnoreCase) != -1 ||
subConnectString.IndexOf("Data Source", StringComparison.InvariantCultureIgnoreCase) != -1)
{
newConnectString.Append(subConnectString);
newConnectString.Append(";");
}
if (subConnectString.IndexOf("Max Pool Size", StringComparison.InvariantCultureIgnoreCase) != -1)
{
string[] poolSizeses = subConnectString.Split('=');
maxPoolSize = int.Parse(poolSizeses[1]);
}
if (subConnectString.IndexOf("Connect Timeout", StringComparison.InvariantCultureIgnoreCase) != -1)
{
string[] timeouts = subConnectString.Split('=');
poolTimeout = int.Parse(timeouts[1]);
}
}
connectString = newConnectString.ToString();
getParam = true;
}
/// <summary>
/// 根据数据库类型创建数据库。
/// </summary>
/// <param name="connectType">数据库连接类型。</param>
/// <returns>指定数据库连接类型的数据库连接对象。</returns>
private static IDbConnection CreateConnection(ConnectionType connectType)
{
switch (connectType)
{
case ConnectionType.OleConnectionType:
return new OleDbConnection(connectString);
default:
throw new Exception("此数据库类型不能使用此连接池。");
}
}
/// <summary>
/// 根据数据库连接字符串及数据库类型创建连接对象。
/// </summary>
/// <param name="connectionString">数据库连接字符串。</param>
/// <param name="connectType">数据库类型。</param>
/// <returns>连接对象。</returns>
public static Pool Open(string connectionString, ConnectionType connectType)
{
lock (typeof(ConnectionPool))
{
if (!getParam)
{
GetParams(connectionString);
}
foreach (Pool pool in pools)
{
if (!pool.IsUse)
{
pool.IsUse = true;
pool.OpenTime = DateTime.Now;
return pool;
}
}
if (pools.Count >= maxPoolSize)
{
throw new Exception("连接池的连接数已经超过最大值，不能再提供数据库连接。");
}
Pool newPool = new Pool(Guid.NewGuid(), CreateConnection(connectType), true, DateTime.Now);
pools.Add(newPool);
newPool.Connection.Open();
return newPool;
}
}
/// <summary>
/// 关闭数据库连接。
/// </summary>
/// <param name="closePool">需要关闭的连接池对象。</param>
public static void Close(Pool closePool)
{
for (int num = pools.Count - 1; num >= 0; num--)
{
if (pools[num].Id == closePool.Id)
{
pools[num].IsUse = false;
}
else
{
if (pools[num].IsUse)
{
continue;
}
TimeSpan time = DateTime.Now - pools[num].OpenTime;
if (time.TotalSeconds > poolTimeout)
{
pools[num].Connection.Close();
pools.Remove(pools[num]);
}
}
}
}
}
调用代码：
private connectionString = ConfigurationManager.ConnectionStrings[“AccessConnectString”].ToString();
//获取连接对象
private Pool pool=ConnectionPool.Open(connectionString, ConnectionType.OleConnectionType);
//获取数据库连接对象
IDbConnection dbConnection= pool.Connection;
数据库操作
//关闭数据库连接
ConnectionPool.Close(pool);
