using System;
using System.Collections.Generic;
using System.Threading;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
namespace Chn.gzGISer.DataBase.Access
{
/// <summary>
/// Access数据库访问帮助类
/// </summary>
public class AccessDataBase
{
private OleDbConnection _connection;
/// <summary>
/// 构造函数
/// </summary>
public AccessDataBase()
{
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">连接字符串</param>
public AccessDataBase(string connectionString)
{
_connection = new OleDbConnection(connectionString);
}
/// <summary>
/// 从配置文件中初始化连接字段
/// </summary>
/// <param name="connectionStringName">数据库连接配置文件(.config)中的数据库连接字段的标签名</param>
public void InitOleDbConnectionFromConfig(string connectionStringName)
{
string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
_connection = new OleDbConnection(connectionString);
}
/// <summary>
/// 打开Connection连接
/// </summary>
public void EnsureConnectionOpen()
{
var retries = 3;
if (_connection.State == ConnectionState.Open)
{
return;
}
else
{
while (retries >= 0 && _connection.State != ConnectionState.Open)
{
_connection.Open();
retries--;
Thread.Sleep(30);
}
}
}
/// <summary>
/// 关闭Connection连接
/// </summary>
public void EnsureConnectionClose()
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
}
/// <summary>
/// 释放Connection连接
/// </summary>
public void Dispose()
{
if (_connection != null)
{
_connection.Dispose();
_connection = null;
}
}
/// <summary>
/// 执行 SQL 查询语句并返回一个表的数据副本
/// </summary>
/// <param name="commadText">SQL 语句</param>
/// <param name="parameters">SQL 语句参数列表</param>
/// <returns>表的数据副本</returns>
public DataTable ExecuteQuery(string commadText, Dictionary<string, object> parameters)
{
if (string.IsNullOrEmpty(commadText))
{
throw new Exception("查询条件为空");
}
DataTable result = new DataTable();
try
{
EnsureConnectionOpen();
OleDbCommand command = CreatCommand(commadText, parameters);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter())
{
dataAdapter.Fill(result);
}
}
catch (Exception)
{
throw;
}
finally
{
EnsureConnectionClose();
}
return result;
}
/// <summary>
/// 执行非查询 SQL 语句
/// </summary>
/// <param name="commadText">SQL 语句</param>
/// <param name="parameters">SQL 语句参数列表</param>
/// <returns>受 SQL 语句影响的行数</returns>
public int ExcuteNonQuery(string commadText, Dictionary<string, object> parameters)
{
if (string.IsNullOrEmpty(commadText))
{
throw new Exception("查询条件为空");
}
int result = 0;
try
{
EnsureConnectionOpen();
OleDbCommand command = CreatCommand(commadText, parameters);
result = command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
EnsureConnectionClose();
}
return result;
}
/// <summary>
/// 执行一个 SQL 语句并返回一个标量
/// </summary>
/// <param name="commadText">SQL 语句</param>
/// <param name="parameters">SQL 语句参数列表</param>
/// <returns></returns>
public object QueryValue(string commadText, Dictionary<string, object> parameters)
{
if (string.IsNullOrEmpty(commadText))
{
throw new Exception("查询条件为空");
}
object result = null;
try
{
EnsureConnectionOpen();
OleDbCommand command = CreatCommand(commadText, parameters);
result = command.ExecuteScalar();
}
catch (Exception)
{
throw;
}
finally
{
EnsureConnectionClose();
}
return result;
}
private OleDbCommand CreatCommand(string commadText, Dictionary<string, object> parameters)
{
OleDbCommand command = _connection.CreateCommand();
command.CommandText = commadText;
AddParameters(command, parameters);
return command;
}
private void AddParameters(OleDbCommand command, Dictionary<string, object> parameters)
{
if (parameters == null)
{
return;
}
foreach (KeyValuePair<string, object> param in parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = param.Key;
parameter.Value = param.Value ?? DBNull.Value;
command.Parameters.Add(parameter);
}
}
}
}