http://write.blog.csdn.net/postedit
/************************************
版权所有：SkylineSoft版权所有(C)
创建日期：2010-8-13
作 者：天涯居士
电子邮件：Jiangwzh@163.com
系统名称：数据库访问层
模块名称：通用数据访问
内容摘要：提供数据库访问的通用方法
***********************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Reflection;
using SkylineSoft.Utils;
using SkylineSoft.DAL.Utils;
namespace SkylineSoft.DAL
{
public delegate void DbDataReaderHandler(DbDataReader reader);
public class GenericDao:IDisposable
{
#region 常数定义
/// <summary>
/// 默认数据库连接设置关键字
/// </summary>
public const string DEFAULT_CONNECTIONSETTING_KEY = "Default_Connection";
#endregion
#region 公共属性
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString { get; set; }
/// <summary>
/// 数据提供者名称
/// </summary>
public string ProviderName { get; set; }
#endregion
#region 私有属性
/// <summary>
/// 数据提供者工厂
/// </summary>
private DbProviderFactory ProviderFactory
{
get { return DbProviderFactories.GetFactory(this.ProviderName); }
}
/// <summary>
/// 数据库事务
/// </summary>
private DbTransaction Transaction { get; set; }
/// <summary>
/// 数据库连接
/// </summary>
private DbConnection Connection { get; set; }
#endregion
#region 构造函数
/// <summary>
/// 构造函数
/// 使用应用程序默认配置文件，及默认连接设置
/// </summary>
public GenericDao()
: this(DEFAULT_CONNECTIONSETTING_KEY)
{
}
/// <summary>
/// 构造函数
/// 用应用程序默认配置文件
/// </summary>
/// <param name="settingKey">连接设置关键字</param>
public GenericDao(string settingKey)
{
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[settingKey];
this.ConnectionString = settings.ConnectionString;
this.ProviderName = settings.ProviderName;
}
/// <summary>
/// 构造函数
/// 指定配置文件，使用其中的默认连接设置
/// </summary>
/// <param name="config">配置对象</param>
public GenericDao(Configuration config)
: this(config, DEFAULT_CONNECTIONSETTING_KEY)
{
}
/// <summary>
/// 构造函数
/// 指定配置文件及连接设置关键字
/// </summary>
/// <param name="config">配置对象</param>
/// <param name="settingKey">连接设置关键字</param>
public GenericDao(Configuration config, string settingKey)
{
ConnectionStringSettings settings = config.ConnectionStrings.ConnectionStrings[settingKey];
this.ConnectionString = settings.ConnectionString;
this.ProviderName = settings.ProviderName;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="providerName">数据提供者名称</param>
public GenericDao(string connectionString, string providerName)
{
this.ConnectionString = connectionString;
this.ProviderName = providerName;
}
#endregion
#region 打开关闭数据库连接
/// <summary>
/// 打开数据连接
/// </summary>
public void Open()
{
if (this.Connection == null)
{
this.Connection = this.ProviderFactory.CreateConnection();
this.Connection.ConnectionString = this.ConnectionString;
}
if (this.Connection.State != ConnectionState.Open)
this.Connection.Open();
}
/// <summary>
/// 关闭数据连接
/// </summary>
public void Close()
{
//如果有事务，则提交
if (this.Transaction != null)
{
try
{
CommitTransaction();
}
catch { }
}
//关闭数据连接
if (this.Connection!=null && this.Connection.State == ConnectionState.Open)
{
try
{
this.Connection.Close();
}
catch { }
finally
{
this.Connection = null;
}
}
}
#endregion
#region 事务
/// <summary>
/// 开始事务
/// </summary>
public void BeginTransaction()
{
this.Open();
this.Transaction = this.Connection.BeginTransaction();
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTransaction()
{
if (this.Transaction != null)
{
try
{
this.Transaction.Commit();
}
catch (Exception ex)
{
try
{
this.Transaction.Rollback();
}
catch { };
throw new DaoException("提交事务产生异常，事务已经回滚！",ex);
}
finally
{
this.Transaction = null;
}
}
else
throw new DaoException("没有要提交的事务！");
}
#endregion
#region 执行非查询SQL
/// <summary>
/// 执行非查询SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>影响的行数</returns>
public int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(CommandType.Text, sql, null);
}
/// <summary>
/// 执行非查询SQL
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <returns>影响的行数</returns>
public int ExecuteNonQuery(CommandType commandType, string sql)
{
return ExecuteNonQuery(commandType, sql, null);
}
/// <summary>
/// 执行非查询SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>影响的行数</returns>
public int ExecuteNonQuery(string sql, Dictionary<string, object> parameters)
{
return ExecuteNonQuery(CommandType.Text, sql, parameters);
}
/// <summary>
/// 执行非查询SQL
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>影响的行数</returns>
public int ExecuteNonQuery(CommandType commandType, string sql, Dictionary<string, object> parameters)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, parameters);
return command.ExecuteNonQuery();
}
/// <summary>
/// 执行非查询SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>影响的行数</returns>
public int ExecuteNonQuery(string sql, object paramObject)
{
return ExecuteNonQuery(CommandType.Text, sql, paramObject);
}
/// <summary>
/// 执行非查询SQL
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>影响的行数</returns>
public int ExecuteNonQuery(CommandType commandType, string sql, object paramObject)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, paramObject);
return command.ExecuteNonQuery();
}
#endregion
#region 执行查询SQL
/// <summary>
/// 执行查询SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>DbDataReader</returns>
public DbDataReader ExecuteReader(string sql)
{
return ExecuteReader(CommandType.Text, sql, null);
}
/// <summary>
/// 执行查询SQL
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <returns>DbDataReader</returns>
public DbDataReader ExecuteReader(CommandType commandType, string sql)
{
return ExecuteReader(commandType, sql, null);
}
/// <summary>
/// 执行查询SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>DbDataReader</returns>
public DbDataReader ExecuteReader(string sql, Dictionary<string, object> parameters)
{
return ExecuteReader(CommandType.Text, sql, parameters);
}
/// <summary>
/// 执行查询SQL
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>DbDataReader</returns>
public DbDataReader ExecuteReader(CommandType commandType, string sql, Dictionary<string, object> parameters)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, parameters);
return command.ExecuteReader();
}
/// <summary>
/// 执行查询SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>DbDataReader</returns>
public DbDataReader ExecuteReader(string sql, object paramObject)
{
return ExecuteReader(CommandType.Text, sql, paramObject);
}
/// <summary>
/// 执行查询SQL
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>DbDataReader</returns>
public DbDataReader ExecuteReader(CommandType commandType, string sql, object paramObject)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, paramObject);
return command.ExecuteReader();
}
/// <summary>
/// 执行查询SQL(并由处理器处理)
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>DbDataReader</returns>
public void ExecuteReader(DbDataReaderHandler handler,string sql)
{
ExecuteReader(CommandType.Text, sql, null, handler);
}
/// <summary>
/// 执行查询SQL(并由处理器处理)
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <returns>DbDataReader</returns>
public void ExecuteReader(DbDataReaderHandler handler,CommandType commandType, string sql)
{
ExecuteReader(commandType, sql, null, handler);
}
/// <summary>
/// 执行查询SQL(并由处理器处理)
/// </summary>
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>DbDataReader</returns>
public void ExecuteReader(string sql, Dictionary<string, object> parameters, DbDataReaderHandler handler)
{
ExecuteReader(CommandType.Text, sql, parameters, handler);
}
/// <summary>
/// 执行查询SQL(并由处理器处理)
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>DbDataReader</returns>
public void ExecuteReader(CommandType commandType, string sql, Dictionary<string, object> parameters, DbDataReaderHandler handler)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, parameters);
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
handler(reader);
}
}
/// <summary>
/// 执行查询SQL(并由处理器处理)
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>DbDataReader</returns>
public void ExecuteReader(string sql, object paramObject, DbDataReaderHandler handler)
{
ExecuteReader(CommandType.Text, sql, paramObject, handler);
}
/// <summary>
/// 执行查询SQL(并由处理器处理)
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>DbDataReader</returns>
public void ExecuteReader(CommandType commandType, string sql, object paramObject, DbDataReaderHandler handler)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, paramObject);
DbDataReader reader= command.ExecuteReader();
try
{
while (reader.Read())
{
handler(reader);
}
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
}
}
#endregion
#region 执行纯量(Scalar)SQL
/// <summary>
/// 执行纯量查询SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>纯量值</returns>
public T ExecuteScalar<T>(string sql)
{
return ExecuteScalar<T>(CommandType.Text, sql, null);
}
/// <summary>
/// 执行纯量SQL
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <returns>纯量值</returns>
public T ExecuteScalar<T>(CommandType commandType, string sql)
{
return ExecuteScalar<T>(commandType, sql, null);
}
/// <summary>
/// 执行纯量SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>纯量值</returns>
public T ExecuteScalar<T>(string sql, Dictionary<string, object> parameters)
{
return ExecuteScalar<T>(CommandType.Text, sql, parameters);
}
/// <summary>
/// 执行纯量SQL
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <returns>纯量值</returns>
public T ExecuteScalar<T>(CommandType commandType, string sql, Dictionary<string, object> parameters)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, parameters);
object obj = command.ExecuteScalar();
return obj == DBNull.Value ? default(T) : (T)Convert.ChangeType(obj, typeof(T));
}
/// <summary>
/// 执行纯量SQL
///
/// 默认为CommandText类型
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>纯量值</returns>
public T ExecuteScalar<T>(string sql, object paramObject)
{
return ExecuteScalar<T>(CommandType.Text, sql, paramObject);
}
/// <summary>
/// 执行纯量SQL
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramObject">参数对象</param>
/// <returns>纯量值</returns>
public T ExecuteScalar<T>(CommandType commandType, string sql, object paramObject)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, paramObject);
object obj = command.ExecuteScalar();
return obj == DBNull.Value ? default(T) : (T)Convert.ChangeType(obj, typeof(T));
}
#endregion
#region Fill
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="dataSet">数据集</param>
public void Fill(string sql,DataSet dataSet)
{
Fill(CommandType.Text, sql, null, dataSet);
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="dataSet"></param>
public void Fill(CommandType commandType,string sql, DataSet dataSet)
{
Fill(commandType, sql, null, dataSet);
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="sql">SQL语句<</param>
/// <param name="parameters">参数字典</param>
/// <param name="dataSet">数据集</param>
public void Fill(string sql, Dictionary<string, object> parameters, DataSet dataSet)
{
Fill(CommandType.Text,sql,parameters,dataSet);
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数字典</param>
/// <param name="dataSet">数据集</param>
public void Fill(CommandType commandType, string sql, Dictionary<string, object> parameters, DataSet dataSet)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, parameters);
DbDataAdapter dataAdapter = this.ProviderFactory.CreateDataAdapter();
dataAdapter.SelectCommand=command;
dataAdapter.Fill(dataSet);
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">对数对象</param>
/// <param name="dataSet">数据集</param>
public void Fill(string sql, object parameters, DataSet dataSet)
{
Fill(CommandType.Text, sql, parameters, dataSet);
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="commandType">Command类型</param>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">对数对象</param>
/// <param name="dataSet">数据集</param>
public void Fill(CommandType commandType, string sql, object parameters, DataSet dataSet)
{
this.Open();
DbCommand command = DBUtil.CreateCommand(this.ProviderFactory, this.Connection, commandType, sql, parameters);
DbDataAdapter dataAdapter = this.ProviderFactory.CreateDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataSet);
}
#endregion
#region Entity
/// <summary>
/// 插入实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <typeparam name="K">主键类模析</typeparam>
/// <param name="entity">实体</param>
/// <returns>主键</returns>
public K Insert<T, K>(T entity)
{
//检测实体有效性
EntityUtil.CheckEntityValid(typeof(T));
EntityAttribute attribute = EntityUtil.GetEntityClassAttribute(typeof(T));
PropertyInfo priInfo = EntityUtil.GetEntityPrimaryFieldProperty(typeof(T));
PropertyInfo[] nopriInfos = EntityUtil.GetEntityNoPrimaryFieldProperties(typeof(T));
string sql = SqlBuilder.GetInsertSql(typeof(T));
Dictionary<string, object> parameters = new Dictionary<string, object>();
foreach (PropertyInfo pInfo in nopriInfos)
{
string columnName = EntityUtil.GetEntityFieldName(pInfo);
parameters.Add(columnName, pInfo.GetValue(entity, null) ?? DBNull.Value);
}
PrimaryKeyStrategy strategy = PrimaryKeyStrategy.Identity;
if (attribute != null)
strategy = attribute.PrimaryKeyStrategy;
if (strategy != PrimaryKeyStrategy.Identity)
{
string columnName = EntityUtil.GetEntityFieldName(priInfo);
if (strategy == PrimaryKeyStrategy.Guid)
parameters.Add(columnName, System.Guid.NewGuid().ToString());
else
parameters.Add(columnName, priInfo.GetValue(entity, null) ?? DBNull.Value);
}
if (this.ExecuteNonQuery(sql, parameters) > 0)
{
if (strategy == PrimaryKeyStrategy.Identity)
return this.ExecuteScalar<K>("SELECT @@identity");
else
{
object obj= priInfo.GetValue(entity, null);
return obj == null ? default(K) : (K)obj;
}
}
return default(K);
}
/// <summary>
/// 更新实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <param name="entity">实体</param>
/// <returns>是否成功更新</returns>
public bool Update<T>(T entity)
{
//检测实体有效性
EntityUtil.CheckEntityValid(typeof(T));
EntityAttribute attribute = EntityUtil.GetEntityClassAttribute(typeof(T));
PropertyInfo[] pInfos = EntityUtil.GetEntityFieldProperties(typeof(T));
string sql = SqlBuilder.GetUpdateSql(typeof(T));
Dictionary<string, object> parameters = new Dictionary<string, object>();
foreach (PropertyInfo pInfo in pInfos)
{
string columnName = EntityUtil.GetEntityFieldName(pInfo);
parameters.Add(columnName, pInfo.GetValue(entity, null) ?? DBNull.Value);
}
if (this.ExecuteNonQuery(sql, parameters) > 0)
return true;
else
return false;
}
/// <summary>
/// 删除实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <typeparam name="K">主键类模板</typeparam>
/// <param name="key">主键值</param>
/// <returns>是否成功删除</returns>
public bool Delete<T>(object keyValue)
{
//检测实体有效性
EntityUtil.CheckEntityValid(typeof(T));
string sql = SqlBuilder.GetSingleDeleteSql(typeof(T));
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add(EntityUtil.GetEntityPrimaryFieldName(typeof(T)), keyValue);
if(this.ExecuteNonQuery(sql, parameters)>0)
return true;
else
return false;
}
/// <summary>
/// 删除满足条件的实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <param name="where">Where表达式</param>
/// <param name="parameters">参数类</param>
/// <returns>影响行数</returns>
public int Delete<T>(string where, object parameters)
{
string sql = SqlBuilder.GetDeleteSql(typeof(T),where);
return this.ExecuteNonQuery(sql, parameters);
}
/// <summary>
/// 获取实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <typeparam name="K">主键类模板</typeparam>
/// <param name="key">主键值</param>
/// <returns>实体</returns>
public T Get<T>(object keyValue) where T:new()
{
//检测实体有效性
EntityUtil.CheckEntityValid(typeof(T));
string sql = SqlBuilder.GetSingleSelectSql(typeof(T));
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add(EntityUtil.GetEntityPrimaryFieldName(typeof(T)), keyValue);
DbDataReader reader= this.ExecuteReader(sql, parameters);
try
{
if (reader.Read())
{
return EntityUtil.Read<T>(reader);
}
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
}
return default(T);
}
/// <summary>
/// 获取所有实体
/// </summary>
/// <typeparam name="T">实体类模板<</typeparam>
/// <returns>所有实体</returns>
public List<T> GetAll<T>() where T:new()
{
return Select<T>(null, null, null);
}
/// <summary>
/// 获取满足条件的实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <param name="where">条件表达式</param>
/// <param name="parameters">参数对象</param>
/// <returns>满足条件的实体集合</returns>
public List<T> Select<T>(string where, object parameters) where T : new()
{
return Select<T>(where, parameters, null);
}
/// <summary>
/// 获取满足条件的实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <param name="where">条件表达式</param>
/// <param name="parameters">参数对象</param>
/// <returns>满足条件的实体集合</returns>
public List<T> Select<T>(string where, Dictionary<string,object> parameters) where T : new()
{
return Select<T>(where, ReflectionUtil.ToDictionary(parameters), null);
}
/// <summary>
/// 获取满足条件的实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <param name="where">条件表达式</param>
/// <param name="parameters">参数对象</param>
/// <param name="order">排序表达式</param>
/// <returns>满足条件的实体集合</returns>
public List<T> Select<T>(string where, object parameters, string order) where T:new()
{
return Select<T>(where, ReflectionUtil.ToDictionary(parameters), order);
}
/// <summary>
/// 获取满足条件的实体
/// </summary>
/// <typeparam name="T">实体类模板</typeparam>
/// <param name="where">条件表达式</param>
/// <param name="parameters">参数字典</param>
/// <param name="order">排序表达式</param>
/// <returns>满足条件的实体集合</returns>
public List<T> Select<T>(string where, Dictionary<string,object> parameters, string order) where T : new()
{
string sql = SqlBuilder.GetSelectSql(typeof(T), where, order);
List<T> lst = new List<T>();
this.ExecuteReader(sql, parameters, reader =>
{
lst.Add(EntityUtil.Read<T>(reader));
});
return lst;
}
#endregion
#region 实现IDisposable
public void Dispose()
{
this.Close();
}
#endregion
}
}
2115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
