.net企业级架构实战之4——Spring.net下的nHibernate数据访问模板

　　在spring.net中集成nHibernate可以获得许多值得称道的特性。比如：基于元标记(meta Attributes)的事务支持、对物理数据库的抽象、对数据层进行切面式拦截。

　　好处是不少，但首先要学会配置。为了这个集成的环境，建立一个配置文件 applicationContext.xml :

<?xml version="1.0" encoding="utf-8" ?>
<objects xmlns="http://www.springframework.net"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.net http://www.springframework.net/xsd/spring-objects.xsd"
>
  <!--spring集合nHibernate-->
  <object id="DbProvider" type="woodigg.DAO.SQLProvider,woodigg.DAO">
    <property name="ConnectionString"
          value="Server=(local);database=Music;User Id=sa;Password=******;Trusted_Connection=False" />
  </object>

  <!--session工厂-->
  <object id="SessionFactory"
          type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate12">
    <property name="DbProvider" ref="DbProvider" />
    <property name="MappingAssemblies">
      <list>
        <value>woodigg.DAO</value>
        <value>woodigg.model</value>
      </list>
    </property>
    <property name="HibernateProperties">
      <dictionary>
        <entry key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
        <entry key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect" />
        <entry key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
        <entry key="show_sql" value="false" />
        <entry key="hibernate.current_session_context_class" value="Spring.Data.NHibernate.SpringSessionContext, Spring.Data.NHibernate12"/>
        <entry key="hibernate.query.factory_class" value="NHibernate.Hql.Classic.ClassicQueryTranslatorFactory" />
        <entry key="hibernate.cache.provider_class" value="NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache" />
        <entry key="relativeExpiration" value="5" />
      </dictionary>
    </property>
  </object>
  <!--事务管理器-->
  <object id="HibernateTransactionManager"
          type="Spring.Data.NHibernate.HibernateTransactionManager, Spring.Data.NHibernate12">
    <property name="DbProvider" ref="DbProvider" />
    <property name="SessionFactory" ref="SessionFactory" />
  </object>
  <!--事务拦截器-->
  <object id="TransactionInterceptor"
          type="Spring.Transaction.Interceptor.TransactionInterceptor, Spring.Data">
    <property name="TransactionManager" ref="HibernateTransactionManager" />
    <property name="TransactionAttributeSource">
      <object type="Spring.Transaction.Interceptor.AttributesTransactionAttributeSource, Spring.Data" />
    </property>
  </object>
  <!--HibernateTemplate-->
  <object id="HibernateTemplate"
          type="Spring.Data.NHibernate.HibernateTemplate,Spring.Data.NHibernate12">
    <property name="SessionFactory" ref="SessionFactory" />
  </object>
  <!--Dao代理模板-->
  <object id="DaoTemplate" type="woodigg.DAO.DaoTemplate, woodigg.DAO">
    <property name="SessionFactory" ref="SessionFactory" />
  </object>
  
</objects>

　　ＯＫ，这里有几处需要说明：
 　　一、woodigg.DAO.SQLProvider　是一个数据结构类，用以描述物理数据库的相关信息，诸如连接串、元数据信息等。这里其实就用到了连接串，在配置中植入位置、帐号信息等就能连接到数据源。这个SQLProvider类结构如下：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Spring.Data.Common;

namespace woodigg.DAO
{
    public class SQLProvider : IDbProvider
    {
        #region IDbProvider 成员
        private string _connectionString = "";
        public string ConnectionString
        {
            get
            {
                return this._connectionString;
            }
            set
            {
                this._connectionString = value;
            }
        }

        public IDbCommand CreateCommand()
        {
            return null;
        }

        public object CreateCommandBuilder()
        {
            return null;
        }

        public IDbConnection CreateConnection()
        {
            return null;
        }

        public IDbDataAdapter CreateDataAdapter()
        {
            return null;
        }

        public IDbDataParameter CreateParameter()
        {
            return null;
        }

        public string CreateParameterName(string name)
        {
            return null;
        }

        public string CreateParameterNameForCollection(string name)
        {
            return null;
        }

        public IDbMetadata DbMetadata
        {
            get
            {
                return null;
            }
        }

        public string ExtractError(Exception e)
        {
            return null;
        }

        public bool IsDataAccessException(Exception e)
        {
            return false;
        }

        #endregion
    }

}

　二、在SessionFactory配置中，指明需要环境映射的程序集名称，通俗说法是：哪些层会在集成环境中，被直接引用？这里以示例项目来说是：woodigg.DAO和woodigg.Model，分别为实体（上一节中生成的一堆.cs实体）层，和数据映射文件（被嵌入在项目中的hbm.xml文件）所在的数据访问层。
三、HibernateProperties节中，可以指定调试时是否显示生成的sql语句。同时，能配置缓存事宜：此处用到的是NHibernate.Caches.SysCache。
四、配置HibernateTemplate。nHibernate的模板，既nHibernate项目已经为开发者写好了一套通用的方法，能便捷的操作数据库，此处将SessionFactory植入引用即能让它工作起来。（并不是所有的复杂SQL，它都能做到，不能完成的功能，我们得自己写，这个马上会交待）。
五、DaoTemplate，就是我自己写的一个基于以上配置的复杂模板，能完成诸如Distinct，top，调用分页存储过程等一干复杂SQL功能，抛出来做点贡献吧：

 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Engine;
using NHibernate.Criterion;
using Spring.Dao;
using Spring.Data.NHibernate.Support;
using woodigg.model;
using log4net;

namespace woodigg.DAO
{
    ParamInfo结构#region ParamInfo结构
    public struct ParamInfo
    {
        public string Name;
        public object Value;
    }
    #endregion

    /**//// <summary>
    /// 继续自HibernateDaoSupport抽象类
    /// HibernateDaoSupport基类拥有HibernateTemplate
    /// </summary>
    public class DaoTemplate : HibernateDaoSupport
    {
        private string _message;
        public string Message
        {
            get { return _message; }
            set { _message = value; }
        }

        /**//// <summary>
        /// 泛型读取
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="id"></param>
        T LoadFromId(object id)#region T LoadFromId<T>(object id)
        public T LoadFromId<T>(object id)
        {
            try
            {                
                T obj =
                    (T)HibernateTemplate.Load(typeof(T), id);
                return obj;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "LoadFromId<T>");
                return default(T);
            }
        }
        #endregion

        /**//// <summary>
        /// 泛型存储
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        bool Save(T obj)#region bool Save<T>(T obj)
        public bool Save<T>(T obj)
        {
            try
            {
                HibernateTemplate.Save(obj);
                return true;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "Save<T>");
                return false;
            }
        }
        #endregion

        /**//// <summary>
        /// 泛型更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        bool Update(T obj)#region bool Update<T>(T obj)
        public bool Update<T>(T obj)
        {
            try
            {
                HibernateTemplate.Update(obj);
                return true;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "Update<T>");
                return false;
            }
        }
        #endregion

        /**//// <summary>
        /// 泛型删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        bool Delete(T obj)#region bool Delete<T>(T obj)
        public bool Delete<T>(T obj)
        {
            try
            {
                HibernateTemplate.Delete(obj);
                return true;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "Delete<T>");
                return false;
            }
        }
        #endregion

        /**//// <summary>
        /// 条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        bool Delete(string where)#region bool Delete<T>(string where)
        public bool Delete<T>(string where)
        {
            try
            {
                string sql = string.Format("from {0} {1}",
                    typeof(T).ToString(),
                    where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);
                HibernateTemplate.Delete(sql);
                return true;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "Delete<T>(where)");
                return false;
            }
        }
        #endregion

        /**//// <summary>
        /// 泛型搜索
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        IList Search(string where)#region IList<T> Search<T>(string where)
        public IList<T> Search<T>(string where)
        {
            try
            {
                //有意思的模板反射哟~
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());

                string hql = string.Format("from {0} {1}",
                    obj.GetType().ToString(),
                    where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);

                IList alist = HibernateTemplate.Find(hql);                

                IList<T> list = new List<T>();
                if (alist != null && alist.Count > 0)
                {
                    foreach (T t in alist)
                    { list.Add(t); }
                    return list;
                }
                else
                    return null;

            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "Search<T>(where)");
                return null;
            }
        }
        #endregion

        /**//// <summary>
        /// 泛型搜索 - DISTINCT
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="field">列名，用","分开，不带别名</param>
        /// <param name="where"></param>
        /// <param name="alias">别名</param>
        IList SearchDistinct(string where,string field,string alias)#region IList<T> SearchDistinct<T>(string where,string field,string alias)
        public IList<T> SearchDistinct<T>(string where, string field, string alias)
        {
            try
            {
                //有意思的模板反射哟~
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
                // 反射DTO对象的各字段,必须把字段和DB中字段同名
                System.Reflection.PropertyInfo[] pps = obj.GetType().GetProperties();

                //拆分成别名+列名
                string[] cols = field.Split(',');
                string columns = string.Empty;
                foreach (string col in cols)
                    columns += string.Format("{0}.{1},", alias, col);
                columns = columns.TrimEnd(',');

                //hql
                string hql = string.Format("select distinct {2} from {0} {3} {1}",
                    obj.GetType().ToString(),
                    where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
                    , columns
                    , alias);

                IList alist = HibernateTemplate.Find(hql);

                IList<T> list = new List<T>();
                if (alist != null && alist.Count > 0)
                {
                    //是否为数组
                    bool isArray = (cols.Length == 1 ? false : true);

                    foreach (object arr in alist)
                    {
                        //产生一个类实例
                        T t = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());


                        for (int i = 0; i < cols.Length; i++)
                        {
                            //逐字段检查名称
                            foreach (System.Reflection.PropertyInfo pi in pps)
                            {
                                if (pi.Name.Equals(cols[i]))
                                {
                                    //数组与object对象
                                    pi.SetValue(t, (isArray ? (arr as object[])[i] : arr), null);
                                }
                            }
                        }

                        list.Add(t);
                    }

                    return list;
                }
                else
                    return null;

            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "SearchDistinct<T>");
                return null;
            }
        }
        #endregion

        /**//// <summary>
        /// 基于表达式的排序查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="propertyName"></param>
        /// <param name="ascending"></param>
        IList SearchWithOrder(string where, string propertyName, bool ascending)#region IList<T> SearchWithOrder<T>(string where, string propertyName, bool ascending)
        public IList<T> SearchWithOrder<T>(string where, string propertyName, bool ascending)
        {
            try
            {
                //排序
                Order order = new Order(propertyName, ascending);
                //排序
                ICriteria ic = Session.CreateCriteria(typeof(T));
                ic.AddOrder(order);
                //表达式
                ICriterion exp = Expression.Sql(where);
                ic.Add(exp);

                return ic.List<T>();
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "SearchWithOrder<T>");
                return null;
            }
        }
        #endregion

        /**//// <summary>
        /// 执行存储过程(返回bool)
        /// </summary>
        /// <param name="spName">名称</param>
        /// <param name="paramInfos">参数表</param>
        bool ExecuteStoredProc2(string spName, IList paramInfos)#region bool ExecuteStoredProc2(string spName, IList<ParamInfo> paramInfos)
        public bool ExecuteStoredProc2(string spName, IList<ParamInfo> paramInfos)
        {
            bool result = true;

            IDbCommand cmd = Session.Connection.CreateCommand();

            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;

            // 加入参数 
            if (paramInfos != null)
            {
                foreach (ParamInfo info in paramInfos)
                {
                    IDbDataParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = info.Name; // driver.FormatNameForSql( info.Name ); 
                    parameter.Value = info.Value;
                    cmd.Parameters.Add(parameter);
                }
            }

            IDbConnection conn = Session.Connection;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                cmd.Connection = conn;
                IDataReader rs = cmd.ExecuteReader();
                return true;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "ExecuteStoredProc2");
                result = false;
            }
            finally
            {
                Session.Connection.Close();
            }

            return result;
        }
        #endregion

        /**//// <summary>
        /// 执行存储过程(返回ILIST)
        /// </summary>
        /// <param name="spName">名称</param>
        /// <param name="paramInfos">参数表</param>
        IList ExecuteStoredProc(string spName, IList paramInfos)#region IList ExecuteStoredProc(string spName, IList<ParamInfo> paramInfos)
        public IList ExecuteStoredProc(string spName, IList<ParamInfo> paramInfos)
        {
            IList result = new ArrayList();

            IDbCommand cmd = Session.Connection.CreateCommand();

            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;

            // 加入参数 
            if (paramInfos != null)
            {
                foreach (ParamInfo info in paramInfos)
                {
                    IDbDataParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = info.Name; // driver.FormatNameForSql( info.Name ); 
                    parameter.Value = info.Value;
                    cmd.Parameters.Add(parameter);
                }
            }

            IDbConnection conn = Session.Connection;
            conn.Open();
            try
            {
                cmd.Connection = conn;
                IDataReader rs = cmd.ExecuteReader();

                while (rs.Read())
                {
                    int fieldCount = rs.FieldCount;
                    object[] values = new Object[fieldCount];
                    for (int i = 0; i < fieldCount; i++)
                        values[i] = rs.GetValue(i);
                    result.Add(values);
                }
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "ExecuteStoredProc");
            }
            finally
            {
                Session.Connection.Close();
            }

            return result;
        }
        #endregion

        /**//// <summary>
        /// 获取记录数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        int GetRecordCount(string where)#region  int GetRecordCount<T>(string where)
        public int GetRecordCount<T>(string where)
        {
            return GetRecordCount<T>(where, "*");
        }
        #endregion

        /**//// <summary>
        /// 获取记录数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        int GetRecordCount(string where,string cols)#region  int GetRecordCount<T>(string where,string cols)
        public int GetRecordCount<T>(string where, string cols)
        {
            try
            {
                //DISTINCT统计
                bool distinct = false;
                if (cols.ToLower().StartsWith("distinct"))
                {
                    distinct = true;
                    string[] columns = cols.Replace("distinct", "").Split(',');
                    StringBuilder sb = new StringBuilder();
                    sb.Append("distinct ");
                    for (int i = 0; i < columns.Length; i++)
                        sb.Append("alia." + columns[i].Trim());
                    cols = sb.ToString().TrimEnd(',');
                }

                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
                string hql = "";
                if (where.Trim() == String.Empty)
                {
                    hql = string.Format("select count({1}) from {0} {2}",
                        obj.GetType().ToString(), cols
                        , (distinct ? "alia" : "")
                        );
                }
                else
                {
                    hql = string.Format("select count({2}) from {0} {3} {1}",
                        obj.GetType().ToString(),
                        where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
                        , cols, (distinct ? "alia" : ""));
                }

                IQuery query = Session.CreateQuery(hql);
                object o = query.UniqueResult();
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "GetRecordCount<T>");
                return 0;
            }
            finally
            {
                Session.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 求某一字段最大值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        int GetMaxNum(string where, string col)#region int GetMaxNum<T>(string where, string col)
        public int GetMaxNum<T>(string where, string col)
        {
            try
            {
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
                string hql = "";
                if (where.Trim() == String.Empty)
                {
                    hql = string.Format("select max(alia.{1}) from {0} alia",
                        obj.GetType().ToString(), col
                        );
                }
                else
                {
                    hql = string.Format("select max(alia.{2}) from {0} alia {1}",
                        obj.GetType().ToString(),
                        where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
                        , col);
                }

                IQuery query = Session.CreateQuery(hql);
                object o = query.UniqueResult();
                if (o == null)
                    return 0;
                else
                    return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "GetMaxNum<T>");
                return 0;
            }
            finally
            {
                Session.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 求某一字段和
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        int GetSumNum(string where, string col)#region int GetSumNum<T>(string where, string col)
        public int GetSumNum<T>(string where, string col)
        {
            try
            {
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
                string hql = "";
                if (where.Trim() == String.Empty)
                {
                    hql = string.Format("select Sum(alia.{1}) from {0} alia",
                        obj.GetType().ToString(), col
                        );
                }
                else
                {
                    hql = string.Format("select Sum(alia.{2}) from {0} alia {1}",
                        obj.GetType().ToString(),
                        where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
                        , col);
                }

                IQuery query = Session.CreateQuery(hql);
                object o = query.UniqueResult();
                if (o == null)
                    return 0;
                else
                    return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "GetSumNum<T>");
                return 0;
            }
            finally
            {
                Session.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 求某一字段平均值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        int GetAvgNum(string where, string col)#region int GetAvgNum<T>(string where, string col)
        public int GetAvgNum<T>(string where, string col)
        {
            try
            {
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
                string hql = "";
                if (where.Trim() == String.Empty)
                {
                    hql = string.Format("select AVG(alia.{1}) from {0} alia",
                        obj.GetType().ToString(), col
                        );
                }
                else
                {
                    hql = string.Format("select AVG(alia.{2}) from {0} alia {1}",
                        obj.GetType().ToString(),
                        where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
                        , col);
                }

                IQuery query = Session.CreateQuery(hql);
                object o = query.UniqueResult();
                if (o == null)
                    return 0;
                else
                    return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "GetAvgNum<T>");
                return 0;
            }
            finally
            {
                Session.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 获取记录数（全文检索）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        int GetRecordCount4Fulltext(string where,string tbName)#region int GetRecordCount4Fulltext<T>(string where,string tbName)
        public int GetRecordCount4Fulltext<T>(string where, string tbName)
        {
            try
            {
                string hql = string.Format("select count(*) as CountNum from {0} {1}",
                        tbName,
                        where == string.Empty ? string.Empty : (
                            where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where)
                        );

                IQuery query = Session.CreateSQLQuery(hql)
                    .AddScalar("CountNum", NHibernateUtil.Int32);
                object o = query.UniqueResult();
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "GetRecordCount4Fulltext<T>");
                return 0;
            }
            finally
            {
                Session.Close();
            }
        }
        #endregion

        /**//// <summary>
        ///  通过where条件查询获取分页数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="varQuerysort">排序</param>
        /// <param name="Start"></param>
        /// <param name="Max"></param>
        /// <returns></returns>
        IList GetPageEntites(string where,string varQuerysort, int Start, int Max)#region  IList<T> GetPageEntites<T>(string where,string varQuerysort, int Start, int Max)
        public IList<T> GetPageEntites<T>(string where, string varQuerysort, int Start, int Max)
        {
            try
            {
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
                string hql = "";
                if (where.Trim() == String.Empty)
                {
                    hql = string.Format("from {0}",
                        obj.GetType().ToString());
                }
                else
                {
                    hql = string.Format("from {0} {1}",
                        obj.GetType().ToString(),
                        where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);
                }

                if (varQuerysort != String.Empty) hql += " " + varQuerysort;

                IQuery query = Session.CreateQuery(hql);

                IList<T> list = query.SetFirstResult(Start).SetMaxResults(Max).List<T>();

                return list;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "GetPageEntites<T>");
                return null;
            }
            finally
            {
                Session.Close();
            }

        }
        #endregion

        /**//// <summary>
        /// 通过存储过程查询分页信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="Primarykeyname"></param>
        /// <param name="colName">列名集合</param>
        /// <param name="orderCol">排序列名</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="pageIdx">当前页</param>
        /// <param name="orderType">升降序，true-0为升序，false-非0为降序</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public DataTable GetPageEntitesByStoredProc(string tableName, string Primarykeyname, string colName, string orderCol,int pageSize, int pageIdx, bool orderType, string condition)#region  public DataTable GetPageEntitesByStoredProc(string tableName, string Primarykeyname, string colName, string orderCol,int pageSize, int pageIdx, bool orderType, string condition)
        public DataTable GetPageEntitesByStoredProc(string tableName, string Primarykeyname, string colName, string orderCol,
            int pageSize, int pageIdx, bool orderType, string condition)
        {
            IList result = new ArrayList();

            ISessionFactoryImplementor imp = (ISessionFactoryImplementor)SessionFactory;
            IDbConnection conn = imp.ConnectionProvider.GetConnection();
            IDbCommand cmd = imp.ConnectionProvider.GetConnection().CreateCommand();

            cmd.CommandText = "pagination";
            cmd.CommandType = CommandType.StoredProcedure;

            IDbDataParameter parameter = cmd.CreateParameter();
            parameter.ParameterName = "@tblName";
            parameter.Value = tableName;
            cmd.Parameters.Add(parameter);

            parameter = cmd.CreateParameter();
            parameter.ParameterName = "@PrimaryKey";
            parameter.Value = Primarykeyname;
            cmd.Parameters.Add(parameter);

            parameter = cmd.CreateParameter();
            parameter.ParameterName = "@strGetFields";
            parameter.Value = colName;
            cmd.Parameters.Add(parameter);

            parameter = cmd.CreateParameter();
            parameter.ParameterName = "@fldName";
            parameter.Value = orderCol;
            cmd.Parameters.Add(parameter);

            parameter = cmd.CreateParameter();
            parameter.ParameterName = "@PageSize";
            parameter.Value = pageSize;
            cmd.Parameters.Add(parameter);

            parameter = cmd.CreateParameter();
            parameter.ParameterName = "@PageIndex";
            parameter.Value = pageIdx;
            cmd.Parameters.Add(parameter);

            parameter = cmd.CreateParameter();
            parameter.ParameterName = "@OrderType";
            parameter.Value = orderType;
            cmd.Parameters.Add(parameter);

            parameter = cmd.CreateParameter();
            parameter.ParameterName = "@strWhere";
            parameter.Value = condition;
            cmd.Parameters.Add(parameter);

            try
            {
                cmd.Connection = conn;
                IDataReader rs = cmd.ExecuteReader();
                // 分割列
                string[] cols = SplitsColumnNames(colName, ',');
                // 数据表
                DataTable dt = new DataTable(tableName);
                foreach (string col in cols)
                    dt.Columns.Add(col);

                // 取数据

                while (rs.Read())
                {
                    // 创建行
                    DataRow row = dt.NewRow();
                    for (int i = 0; i < cols.Length; i++)
                        row[cols[i]] = rs.GetValue(i);
                    // 插入行
                    dt.Rows.Add(row);
                }
                // 返回结果集
                return dt;
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger(typeof(T));
                //log.Error(ex.Message, ex);
                Message=ex.Message; Tool.Logger.ErrorLog(ex.Source, ex.Message, "GetPageEntitesByStoredProc");
                return null;
            }
            finally
            {
                imp.CloseConnection(conn);
            }
        }
        #endregion

        /**//// <summary>
        /// 将字符里的列表分解出来
        /// </summary>
        /// <returns></returns>
        internal static string[] SplitsColumnNames(string columns, char separator)#region  internal static string[] SplitsColumnNames(string columns, char separator)
        internal static string[] SplitsColumnNames(string columns, char separator)
        {
            return columns.Split(new char[] { separator });
        }
        #endregion


        // -------- SELECT TOP 方法s --------- //
        /**//// <summary>
        /// 获取一个基于分页程序的泛型列表
        /// 集合中仅仅只有Id字段被赋值，它用于缓存
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="where">条件</param>
        /// <param name="topN">头多少条</param>
        /// <param name="orderCol">排序字段</param>
        /// <param name="ascending">顺序</param>    
        /// <returns>Id列表</returns>
        IList GetToplistOnlyhasId(string tableName,string where, int topN,string orderCol,bool ascending)#region IList<int> GetToplistOnlyhasId<T>(string tableName,string where, int topN,string orderCol,bool ascending)
        public IList<int> GetToplistOnlyhasId<T>(string tableName,string where, int topN,string orderCol,bool ascending)
        {            
            string typeName = typeof(T).FullName.ToString();
            object dtoObject = typeof(T).Assembly.CreateInstance(typeName);
            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            bool hasId = false;
            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                if (pi.Name.ToLower() == "id")
                {
                    hasId = true;
                    break;
                }
            }
            if (!hasId)
                return null;

            DataTable dt = GetPageEntitesByStoredProc(tableName, "Id", "Id", orderCol, topN, 1, ascending, where);
            if (dt == null)
                return null;
            if (dt.Rows.Count == 0)
                return null;

            IList<int> list = new List<int>();
            foreach (DataRow row in dt.Rows)
            {
                int id =  Convert.ToInt32( row[0]);
                list.Add(id);
            }
            return list;
        }
        #endregion      
    }
}

这是内个配合使用的sql server分页存储过程，原来从网上摘的，动手改过两次以适配distinct取数据：

 

-- 获取指定页的数据 
CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名 
@PrimaryKey varchar(100), --主键
@strGetFields varchar(1000) = '*', -- 需要返回的列 
@fldName varchar(255)='', -- 排序的字段名 
@PageSize int = 10, -- 页尺寸 
@PageIndex int = 1, -- 页码 
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回 
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) 
AS 
declare @strSQL varchar(5000) -- 主语句 
declare @strTmp varchar(110) -- 临时变量 
declare @strOrder varchar(400) -- 排序类型 
if @doCount != 0 
begin 
if @strWhere !='' 
set @strSQL = "select count(*) as Total from " + @tblName + " where "+@strWhere 
else 
set @strSQL = "select count(*) as Total from " + @tblName + "" 
end 
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况 
else 
begin 
if @OrderType != 0 
begin 
--set @strTmp = "<(select min" 
set @strTmp = " not in " 
set @strOrder = " order by [" + @fldName +"] desc" 
--如果@OrderType不是0，就执行降序，这句很重要！ 
end 
else 
begin 
--set @strTmp = ">(select max" 
set @strTmp = " not in " 
set @strOrder = " order by [" + @fldName +"] asc" 
end 
if @PageIndex = 1 
begin 
if @strWhere != '' 
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from " + @tblName + " where " + @strWhere + @strOrder 
else 
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from "+ @tblName + " "+ @strOrder 
--如果是第一页就执行以上代码，这样会加快执行速度 
end 
else 
begin 
--以下代码赋予了@strSQL以真正执行的SQL代码 
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from " 
+ @tblName + " where [" + @PrimaryKey + "]" + @strTmp + "  (select top " + str((@PageIndex-1)*@PageSize,3) + " ["+ @PrimaryKey + "] from " + @tblName + "" + @strOrder + ") "+ @strOrder 
if @strWhere != '' 
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from " 
+ @tblName + " where [" + @PrimaryKey + "]" + @strTmp 
+ "  (select top " + str((@PageIndex-1)*@PageSize,3) + " [" 
+ @PrimaryKey + "] from " + @tblName + " where " + @strWhere + " " 
+ @strOrder + ")  and " + @strWhere + " " + @strOrder 
end 
end 
print @strSQL
exec (@strSQL)
GO

 

　　最后，要让这个环境在程序中生效，得在web.config中加载它：

 

    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.WebContextHandler, Spring.Web"/>
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core"/>
        </sectionGroup>
        <section name="SpringOverrideProperty" type="System.Configuration.NameValueSectionHandler"/>
        <section name="nhibernate" type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
        <!--log4net-->
        <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>
    </configSections>    
    <spring>
        <context>
            <resource uri="config://spring/objects"/>
            <resource uri="~/config/applicationContext.xml"/>
            <resource uri="~/config/business.xml"/>
            <resource uri="~/config/controllers.xml"/>
            <resource uri="~/config/pageConfig.xml"/>
            <resource uri="~/config/serviceConfig.xml"/>
        </context>
        <objects xmlns="http://www.springframework.net"/>
    </spring>

 

　　先写到这里吧，博客园的编辑器对机器配置要求不低，粘贴几段代码，界面几乎不能动弹，做罢了。下一回，将介绍怎么应用这个集成的环境。

---

 

　　这里放出一个项目的mini demo:精简版spring.net集成Demo,点击下载