/************************************版权所有：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 }}
