通过反射进行数据访问层的抽象

     新项目中与Team的成员讨论了一下关于数据访问层的整理和重构，基于对原有的访问层的一些不满意：主要是每个方法都要自己手写SQL语句这一状况，我提出建议是使用O/R Mapping工具，现在这类工具有如过江之鲫，但比较好用也就是那几个：nHibernate，iBatis.Net，nObject。但这一提议并没有收到效果，Team里的成员有些没有接触过这些框架，且害怕学习曲线过长，无法在项目的预期时间内完成任务，只得作罢。

    后来与他们聊了聊，大家的想法是：如果能一个操作器，只要简单地将我们定义的实体类塞进去，而访问层就能完成，那就是最理想的情况——即，DAL层都不愿一个个写方法了。是人就想要偷偷懒，这也是人类进步的动力嘛：）况且程序员本来就是最懒的一群人，宁愿多用些脑细胞，也不愿多写一行代码，对此深表理解~ 说白了，要做到DAL简单的几行代码搞定，那也就是自己开发O/R Mapping工具，不是多牛，至少是初级的，能更高抽象一些重复代码工作的框架。

    自然，这个光荣而艰巨的任务落在了本人的头上。怎么下手呢？首先跳入脑海的就是“反射”，无非就是，将数据库中的表一字不差的按列生成各个实体，列和实体中的字段名称相同，用反射来生成一些SQL句子，通过反射获取的字段属性PropertyInfo.GetValue()方法来做参数的绑定（SqlParameter，防注入）.，最后将这些方法提取出来，结合Ado.Net方法，给DAL层提供调用。

   有了思路，就可以动手了。

   假设有一张这样的表：TB_NEWS，其中id列既为主键又是自增列（自增列在生成通用代码，需要注意，必须进行处理）

    那么，定义一个实体类dtoNews与其对应：

 

using System;

namespace IndieFacade
{
    public class dtoNews
    {
        public dtoNews() { }

        private int m_id;
        private string m_author;
        private DateTime m_pubtime;
        private string m_contents;
        private int m_reads;
        private string m_tags;
        private string m_title;
        private string m_summary;

        /// <summary>
        /// ID 
        /// </summary>
        public int id
        {
            get { return m_id; }
            set { m_id = value; }
        }
        /// <summary>
        /// 标题 
        /// </summary>
        public string Title
        {
            get { return m_title; }
            set { m_title = value; }
        }
        /// <summary>
        /// Summary 
        /// </summary>
        public string Summary
        {
            get { return m_summary; }
            set { m_summary = value; }
        }
        /// <summary>
        /// Author 
        /// </summary>
        public string Author
        {
            get { return m_author; }
            set { m_author = value; }
        }
        /// <summary>
        /// Tags 
        /// </summary>
        public string Tags
        {
            get { return m_tags; }
            set { m_tags = value; }
        }
        /// <summary>
        /// Reads 
        /// </summary>
        public int Reads
        {
            get { return m_reads; }
            set { m_reads = value; }
        }
        /// <summary>
        /// Contents 
        /// </summary>
        public string Contents
        {
            get { return m_contents; }
            set { m_contents = value; }
        }
        /// <summary>
        /// 发布时间
        /// </summary>
        public DateTime PubTime
        {
            get { return m_pubtime; }
            set { m_pubtime = value; }
        }
    }
}

 

    然后，我写了这样一个DbCommon类，用于处理一些通用的方法，当然，只是试验了几个简单的CRUD方法： 

 

using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace BLLV2.manager
{
    /**//// <summary>
    /// DB访问代理
    /// </summary>
    public class DbCommon
    {
        // ----------------------------------- 属性字段 --------------------------------------- //
        private static object syncRoot = new Object();                    // 同步对象
        private static SqlPaging _sqlPaging;                            // 分页器

        /**//// <summary>
        /// 分页器
        /// </summary>
        static SqlPaging SqlPager#region static SqlPaging SqlPager
        public static SqlPaging SqlPager
        {
            get
            {
                if(_sqlPaging==null)
                {
                    lock(syncRoot)
                    {
                        if(_sqlPaging==null)
                        {
                            _sqlPaging=new SqlPaging();
                        }
                    }
                }
                return _sqlPaging;
            }            
        }
        #endregion

        // ----------------------------------- 对外调用的查询通用方法 ----------------------------------- //

        /**//// <summary>
        /// 获取一个基于dto对象的列表
        /// </summary>
        /// <param name="_connString">数据库连接串</param>
        /// <param name="tableName">表名</param>
        /// <param name="dtoObject">实体类，与表中列名一致的实体</param>
        /// <param name="where">条件</param>
        /// <param name="errMsg">错误信息</param>
        /// <returns>IList列表</returns>
        IList SelectDtoFromDb(string _connString, string tableName, object dtoObject, string where, ref string errMsg)#region IList SelectDtoFromDb(string _connString, string tableName, object dtoObject, string where, ref string errMsg)
        public static IList SelectDtoFromDb(string _connString, string tableName, object dtoObject, string where, ref string errMsg)
        {
            // 通过反射，生成Select操作所需要的串
            string _sqlcmd =  _sqlcmd = DbCommon.GenerateSeleteSQLText(tableName, dtoObject,where);
            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // 数据连接
            SqlConnection _conn = new SqlConnection(_connString);

            try
            {
                SqlDataReader sr = DbCommon.ExcuteReader(_conn, _sqlcmd);
                IList list = new ArrayList();
                while (sr.Read())
                {
                    // 反射创建一个类的实例
                    object obj = dtoObject.GetType().Assembly.CreateInstance(dtoObject.GetType().ToString());

                    // 各字段填充
                    int i = 0;
                    foreach (System.Reflection.PropertyInfo pi in pps)
                    {
                        switch (pi.PropertyType.Name.ToString())
                        {
                            case "Int32":
                                pi.SetValue(obj, sr.GetInt32(i), null);                                
                                break;
                            case "Int16":
                                pi.SetValue(obj, sr.GetInt16(i), null);
                                break;
                            case "String":
                                pi.SetValue(obj, sr.GetString(i), null);
                                break;
                            case "DateTime":
                                pi.SetValue(obj, sr.GetDateTime(i), null);
                                break;
                            case "Decimal":
                                pi.SetValue(obj, sr.GetDecimal(i), null);
                                break;
                            case "Boolean":
                                pi.SetValue(obj, sr.GetBoolean(i), null);
                                break;
                            case "Double":
                                pi.SetValue(obj, sr.GetDouble(i), null);
                                break;
                            default:
                                pi.SetValue(obj, sr.GetString(i), null);
                                break;

                        }
                        i++;
                    }

                    // 添加到列表
                    list.Add(obj);
                }
                sr.Close();
                return list;
            }
            catch (Exception err)
            {
                errMsg = err.Message;
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.SelectDtoFromDb");
                return null;
            }
            finally
            {
                _conn.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 获取SELECT的SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dtoObject"></param>
        /// <returns></returns>
        static string GenerateSeleteSQLText(string tableName, object dtoObject)#region static string GenerateSeleteSQLText(string tableName, object dtoObject)
        private static string GenerateSeleteSQLText(string tableName, object dtoObject)
        {
            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // SQL串
            string sqlstr = string.Empty;
            // 字段串
            string fieldstr = string.Empty;

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                fieldstr += string.Format("{0},", pi.Name);
            }
            fieldstr = fieldstr.TrimEnd(new char[] { ',' });

            sqlstr = string.Format("SELECT {0} FROM {1}", fieldstr, tableName);

            return sqlstr;
        }
        #endregion

        /**//// <summary>
        /// 获取SELECT的SQL语句(带条件版本）
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dtoObject"></param>
        /// <returns></returns>
        static string GenerateSeleteSQLText(string tableName, object dtoObject,string where)#region static string GenerateSeleteSQLText(string tableName, object dtoObject,string where)
        private static string GenerateSeleteSQLText(string tableName, object dtoObject,string where)
        {
            // 条件为空，用另一个版本
            if (where.Trim() == string.Empty)
                return GenerateSeleteSQLText(tableName, dtoObject);

            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // SQL串
            string sqlstr = string.Empty;
            // 字段串
            string fieldstr = string.Empty;

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                fieldstr += string.Format("{0},", pi.Name);
            }
            fieldstr = fieldstr.TrimEnd(new char[] { ',' });

            sqlstr = string.Format("SELECT {0} FROM {1} {2}", fieldstr, tableName,
                // 是否已经是WHERE开头
                where.ToUpper().StartsWith("WHERE") ? where : "WHERE "+where);

            return sqlstr;
        }
        #endregion


        // ----------------------------------- 对外调用的增删改通用方法 --------------------------------- //
        /**//// <summary>
        /// 根据反射，将一个对象的记录从数据表中删除
        /// </summary>
        /// <param name="_connString">数据库连接串</param>
        /// <param name="tableName">表名</param>
        /// <param name="dtoObject">实体类，与表中列名一致的实体</param>
        /// <param name="errMsg">错误信息</param>
        /// <returns>是否成功</returns>
        static bool DeleteDtoToDb(string _connString,string tableName,object dtoObject,int primaryKeyNum,ref string errMsg)#region static bool DeleteDtoToDb(string _connString,string tableName,object dtoObject,int primaryKeyNum,ref string errMsg)
        public static bool DeleteDtoToDb(string _connString, string tableName, object dtoObject, int primaryKeyNum, ref string errMsg)
        {
            // 通过反射，生成INSERT操作所需要的串
            string _sqlcmd = DbCommon.GenerateDeleteSQLText(tableName, dtoObject, primaryKeyNum);
            // 通过反射，生成一个SqlParameter数组
            SqlParameter par = DbCommon.GetParameterForPrimaryKey(dtoObject, primaryKeyNum);   
            // 数据连接
            SqlConnection _conn = new SqlConnection(_connString);

            try
            {
                // 传入SQL语句，及绑定参数列表
                int res = DbCommon.ExcuteNoQuery(_conn, _sqlcmd, par);
                if (res > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception err)
            {
                errMsg = err.Message;
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.DeleteDtoToDb");
                return false;
            }
            finally
            {
                _conn.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 根据反射，将一个对象做为一条记录更新到表中
        /// </summary>
        /// <param name="_connString">数据库连接串</param>
        /// <param name="tableName">表名</param>
        /// <param name="dtoObject">实体类，与表中列名一致的实体</param>
        /// <param name="primaryKeyNum">主键列号</param>
        /// <param name="isIdentity">是否有自增长列</param>
        /// <param name="identityColNum">自增长列号</param> 
        /// <param name="errMsg">错误信息</param>
        /// <returns>是否成功</returns>
        static bool UpdateDtoToDb(string _connString,string tableName,object dtoObject,int primaryKeyNum,bool isIdentity, int identityColNum,,ref string errMsg)#region static bool UpdateDtoToDb(string _connString,string tableName,object dtoObject,int primaryKeyNum,bool isIdentity, int identityColNum,,ref string errMsg)
        public static bool UpdateDtoToDb(string _connString, string tableName, object dtoObject, int primaryKeyNum, bool isIdentity, int identityColNum, ref string errMsg)
        {
            // 通过反射，生成INSERT操作所需要的串
            string _sqlcmd = DbCommon.GenerateUpdateSQLText(tableName, dtoObject, primaryKeyNum);
            // 通过反射，生成一个SqlParameter数组
            SqlParameter[] pars = DbCommon.GetParameters(dtoObject, isIdentity, identityColNum);
            // 数据连接
            SqlConnection _conn = new SqlConnection(_connString);

            try
            {
                // 传入SQL语句，及绑定参数列表
                int res = DbCommon.ExcuteNoQuery(_conn, _sqlcmd, pars);
                if (res > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception err)
            {
                errMsg = err.Message;
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.UpdateDtoToDb");
                return false;
            }
            finally
            {
                _conn.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 根据反射，将一个对象做为一条记录插入到表中
        /// </summary>
        /// <param name="_connString">数据库连接串</param>
        /// <param name="tableName">表名</param>
        /// <param name="dtoObject">实体类，与表中列名一致的实体</param>
        /// <param name="primaryKeyNum">主键列号，-1表示无主键</param>
        /// <param name="isIdentity">是否有自增长列</param>
        /// <param name="identityColNum">自增长列号</param>        
        /// <param name="errMsg">出错信息，参数传递</param>
        /// <returns>是否成功</returns>
        static bool InsertDtoToDb(string _connString,string tableName, object dtoObject,int primaryKeyNum, bool isIdentity, int identityColNum,ref string errMsg)#region static bool InsertDtoToDb(string _connString,string tableName, object dtoObject,int primaryKeyNum, bool isIdentity, int identityColNum,ref string errMsg)
        public static bool InsertDtoToDb(string _connString, string tableName, object dtoObject,int primaryKeyNum, bool isIdentity, int identityColNum, ref string errMsg)
        {
            // 通过反射，生成INSERT操作所需要的串
            string _sqlcmd = DbCommon.GenerateInsertSQLText(tableName, dtoObject, isIdentity, identityColNum);
            // 通过反射，生成一个SqlParameter数组
            SqlParameter[] pars = DbCommon.GetParameters(_connString,tableName,dtoObject, primaryKeyNum, isIdentity, identityColNum,ref errMsg);
            // 数据连接
            SqlConnection _conn = new SqlConnection(_connString);

            try
            {
                // 传入SQL语句，及绑定参数列表
                int res = DbCommon.ExcuteNoQuery(_conn, _sqlcmd, pars);
                if (res > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception err)
            {
                errMsg = err.Message;
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.InsertDtoToDb");
                return false;
            }
            finally
            {
                _conn.Close();
            }
        }
        #endregion

        // ----------------------------------- 生成SQL串方法 --------------------------------- //
        /**//// <summary>
        /// 生成DELETE操作所需要的串
        /// </summary>
        /// <param name="tableName">要删除的表名</param>
        /// <param name="dtoObject">dto对象</param>
        /// <param name="primaryKeyNum">主键列号</param>
        /// <returns>串</returns>
        static string GenerateDeleteSQLText(string tableName,object dtoObject,int primaryKeyNum)#region static string GenerateDeleteSQLText(string tableName,object dtoObject,int primaryKeyNum)
        private static string GenerateDeleteSQLText(string tableName, object dtoObject, int primaryKeyNum)
        {
            string primaryKeystr = string.Empty;        // 主键条件的串
            string sqlstr = string.Empty;                // 最终SQL串

            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // 列号
            int i = 0;

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                if (primaryKeyNum == i)
                {
                    primaryKeystr = string.Format("{0}=@{0}", pi.Name);
                    break;
                }
                i++;
            }

            sqlstr = string.Format("DELETE {0} WHERE {1}", tableName, primaryKeystr);

            return sqlstr;
        }
        #endregion

        /**//// <summary>
        /// 生成UPDATE操作所需要的串
        /// </summary>
        /// <param name="tableName">要更新的表名</param>
        /// <param name="dtoObject">dto对象</param>
        /// <param name="primaryKeyNum">主键列号</param>
        /// <returns>串</returns>
        static string GenerateUpdateSQLText(string tableName,object dtoObject,int primaryKeyNum)#region static string GenerateUpdateSQLText(string tableName,object dtoObject,int primaryKeyNum)
        private static string GenerateUpdateSQLText(string tableName, object dtoObject, int primaryKeyNum)
        {
            string fieldstr = string.Empty;                // 表达式的串
            string primaryKeystr = string.Empty;        // 主键条件的串
            string sqlstr = string.Empty;                // 最终SQL串

            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // 列号
            int i = 0;

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                if (primaryKeyNum != i)
                {
                    fieldstr += string.Format("{0}=@{0},", pi.Name);
                }
                else
                {
                    primaryKeystr = string.Format("{0}=@{0}", pi.Name);
                }
                i++;
            }
            fieldstr = fieldstr.TrimEnd(new char[] { ',' });

            sqlstr = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, fieldstr, primaryKeystr);

            return sqlstr;
        }
        #endregion


        /**//// <summary>
        /// 生成INSERT操作所需要的串
        /// </summary>
        /// <param name="tableName">要插入的表名</param>
        /// <param name="dtoObject">dto对象</param>
        /// <param name="isIdentity">是否有自增长列</param>
        /// <param name="identityColNum">自增长列号</param>
        /// <returns>串</returns>
        static string GenerateInsertSQLText(string tableName,object dtoObject, bool isIdentity, int identityColNum)#region static string GenerateInsertSQLText(string tableName,object dtoObject, bool isIdentity, int identityColNum)
        private static string GenerateInsertSQLText(string tableName, object dtoObject, bool isIdentity, int identityColNum)
        {
            string fieldstr = string.Empty;                // 字段的串
            string bindstr = string.Empty;                // 绑定变量的串
            string sqlstr = string.Empty;                // 最终SQL串

            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // 列号
            int i = 0;

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                if (!isIdentity || identityColNum != i)
                {
                    fieldstr += pi.Name + ",";
                    bindstr += "@" + pi.Name + ",";
                }
                i++;
            }
            fieldstr = fieldstr.TrimEnd(new char[] { ',' });
            bindstr = bindstr.TrimEnd(new char[] { ',' });

            sqlstr = string.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, fieldstr, bindstr);

            return sqlstr;
        }
        #endregion

        /**//// <summary>
        /// 获取dto对象各字段对应到表中的绑定参数数组
        /// </summary>
        /// <param name="dtoObject">dto对象</param>
        /// <param name="isIdentity">是否有自增长列</param>
        /// <param name="identityColNum">自增长列号</param>
        /// <returns>绑定参数数组</returns>
        static SqlParameter[] GetParameters(object dtoObject, bool isIdentity, int identityColNum)#region static SqlParameter[] GetParameters(object dtoObject, bool isIdentity, int identityColNum)
        private static SqlParameter[] GetParameters(object dtoObject, bool isIdentity, int identityColNum)
        {
            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // 确定成员数量，生成动态数组
            SqlParameter[] pars = (SqlParameter[])
                Array.CreateInstance(typeof(SqlParameter), isIdentity ? pps.Length - 1 : pps.Length);
            int i = 0;              // 数组已经绑定的下标
            int j = 0;              // 实际循环的游标

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                // 是否越界
                if (i > pars.Length)
                    break;
                // 当前列为自增列，跳过
                if (isIdentity && identityColNum == j)
                {
                    j++;
                    continue;
                }

                pars[i] = new SqlParameter();
                pars[i].ParameterName = string.Format("@{0}", pi.Name);
                pars[i].Value = pi.GetValue(dtoObject, null);

                i++;
                j++;
            }

            return pars;
        }
        #endregion

        /**//// <summary>
        /// 获取dto对象各字段对应到表中的绑定参数数组(为插入用版本)
        /// </summary>
        /// <param name="dtoObject">dto对象</param>
        /// <param name="primaryKeyNum">主键ID,-1表示无主键</param>
        /// <param name="isIdentity">是否有自增长列</param>
        /// <param name="identityColNum">自增长列号</param>
        /// <returns>绑定参数数组</returns>
        static SqlParameter[] GetParameters(string _connString,string tableName,object dtoObject,int primaryKeyNum, bool isIdentity, int identityColNum,ref string errMsg)#region static SqlParameter[] GetParameters(string _connString,string tableName,object dtoObject,int primaryKeyNum, bool isIdentity, int identityColNum,ref string errMsg)
        private static SqlParameter[] GetParameters(string _connString,string tableName,object dtoObject,int primaryKeyNum, bool isIdentity, int identityColNum,ref string errMsg)
        {
            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // 确定成员数量，生成动态数组
            SqlParameter[] pars = (SqlParameter[])
                Array.CreateInstance(typeof(SqlParameter), isIdentity ? pps.Length - 1 : pps.Length);
            int i = 0;              // 数组已经绑定的下标
            int j = 0;              // 实际循环的游标

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                // 是否越界
                if (i >= pars.Length)
                    break;
                // 当前列为自增列，跳过
                if (isIdentity && identityColNum == j)
                {
                    j++;
                    continue;
                }
                // 绑定参数
                pars[i] = new SqlParameter();
                pars[i].ParameterName = string.Format("@{0}", pi.Name);
                // 是否是主键
                if (j != primaryKeyNum)
                {
                    pars[i].Value = pi.GetValue(dtoObject, null);
                }
                else
                {
                    // 是否为需要计算的Int值
                    if(pi.PropertyType.Name=="Int32")
                        pars[i].Value = GetNextID(_connString, tableName, dtoObject, primaryKeyNum, ref errMsg);
                    else
                        pars[i].Value = pi.GetValue(dtoObject,null);
                }

                i++;
                j++;
            }

            return pars;
        }
        #endregion

        /**//// <summary>
        /// 获取dto对象的主键的绑定参数
        /// </summary>
        /// <param name="dtoObject">dto对象</param>
        /// <param name="primaryKeyNum">主键列号</param>
        /// <returns>绑定参数</returns>
        static SqlParameter GetParameterForPrimaryKey(object dtoObject,int primaryKeyNum)#region static SqlParameter GetParameterForPrimaryKey(object dtoObject,int primaryKeyNum)
        private static SqlParameter GetParameterForPrimaryKey(object dtoObject, int primaryKeyNum)
        {
            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            // 确定成员数量，生成动态数组
            SqlParameter par = new SqlParameter();
            int i = 0;

            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                if (primaryKeyNum == i)
                {
                    par.ParameterName = string.Format("@{0}", pi.Name);
                    par.Value = pi.GetValue(dtoObject, null);
                    break;
                }

                i++;
            }

            return par;
        }
        #endregion

        /**//// <summary>
        /// 获取下一个可用的主键ID号
        /// </summary>
        /// <param name="_connString"></param>
        /// <param name="tableName"></param>
        /// <param name="dtoObject"></param>
        /// <param name="primaryKeyNum"></param>
        /// <returns></returns>
        static int GetNextID(string _connString, string tableName, object dtoObject, int primaryKeyNum,ref string errMsg)#region static int GetNextID(string _connString, string tableName, object dtoObject, int primaryKeyNum,ref string errMsg)
        public static int GetNextID(string _connString, string tableName, object dtoObject, int primaryKeyNum ,ref string errMsg)
        {
            int intNewUserID = -1;
            string strSql = "";
            object objMaxUserID;
            string primaryKeyName = string.Empty;

            // 反射DTO对象的各字段,必须把字段和DB中字段同名
            System.Reflection.PropertyInfo[] pps = dtoObject.GetType().GetProperties();
            int i = 0;
            // 获取主键名称
            foreach (System.Reflection.PropertyInfo pi in pps)
            {
                if (primaryKeyNum == i)
                {
                    primaryKeyName = pi.Name;
                    break;
                }
                i++;
            }

            lock (syncRoot)
            {
                // 数据连接
                SqlConnection _conn = new SqlConnection(_connString);
                try
                {
                    strSql = string.Format("select max({0}) from {1}", primaryKeyName, tableName);
                    objMaxUserID = DbCommon.ExcuteScalar(_conn, strSql);
                    if (Convert.IsDBNull(objMaxUserID))
                    {
                        intNewUserID = 1;
                    }
                    else
                    {
                        intNewUserID = Convert.ToInt32(objMaxUserID) + 1;
                    }
                    return intNewUserID;
                }
                catch (Exception err)
                {
                    errMsg = err.Message;
                    return -1;
                }
                finally
                {
                    _conn.Close();
                }
            }
        }
        #endregion

        // ----------------------------------- 工具方法 --------------------------------------- //
        /**//// <summary>
        /// SQLDATAREADER
        /// </summary>
        static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext)#region static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext)
        public static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext)
        {            
            SqlCommand cmd = new SqlCommand(cmdtext, conn);            
            try
            {
                conn.Open();
                return cmd.ExecuteReader();
            }
            catch(Exception err)
            {
                conn.Close();
                throw err;
            }            
        }
        #endregion

        /**//// <summary>
        /// SQLDATAREADER参数版本
        /// </summary>
        static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext,params SqlParameter[] sqlparams)#region static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext,params SqlParameter[] sqlparams)
        public static SqlDataReader ExcuteReader(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
        {           
            SqlCommand cmd = new SqlCommand(cmdtext, conn);
            if (sqlparams.Length > 0)
            {
                for (int i = 0; i < sqlparams.Length; i++)
                    cmd.Parameters.Add(sqlparams[i]);
            }

            try
            {
                conn.Open();
                return cmd.ExecuteReader();
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }           
        }
        #endregion

        /**//// <summary>
        /// SQLSCALAR
        /// </summary>
        static object ExcuteScalar(SqlConnection conn, string cmdtext)#region static object ExcuteScalar(SqlConnection conn, string cmdtext)
        public static object ExcuteScalar(SqlConnection conn, string cmdtext)
        {            
            SqlCommand cmd = new SqlCommand(cmdtext, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }            
        }
        #endregion

        /**//// <summary>
        /// SQLSCALAR参数版本
        /// </summary>
        static object ExcuteScalar(SqlConnection conn, string cmdtext,params SqlParameter[] sqlparams)#region static object ExcuteScalar(SqlConnection conn, string cmdtext,params SqlParameter[] sqlparams)
        public static object ExcuteScalar(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
        {           
            SqlCommand cmd = new SqlCommand(cmdtext, conn);
            if (sqlparams.Length > 0)
            {
                for (int i = 0; i < sqlparams.Length; i++)
                    cmd.Parameters.Add(sqlparams[i]);
            }
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }           
        }
        #endregion

        /**//// <summary>
        /// ExcuteNoQuery
        /// </summary>
        /// <returns>影响行数</returns>
        static int ExcuteNoQuery(SqlConnection conn, string cmdtext)#region static int ExcuteNoQuery(SqlConnection conn, string cmdtext)
        public static int ExcuteNoQuery(SqlConnection conn, string cmdtext)
        {
            SqlCommand cmd = new SqlCommand(cmdtext, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }           
        }
        #endregion

        /**//// <summary>
        /// ExcuteNoQuery参数版本
        /// </summary>
        /// <returns>影响行数</returns>
        static int ExcuteNoQuery(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)#region static int ExcuteNoQuery(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
        public static int ExcuteNoQuery(SqlConnection conn, string cmdtext, params SqlParameter[] sqlparams)
        {
            SqlCommand cmd = new SqlCommand(cmdtext, conn);
            if (sqlparams.Length > 0)
            {
                for (int i = 0; i < sqlparams.Length; i++)
                    cmd.Parameters.Add(sqlparams[i]);
            }
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;                
            }
        }
        #endregion

        /**//// <summary>
        /// 获取指定表的总记录数
        /// </summary>
        /// <param name="connstr"></param>
        /// <param name="TableName"></param>
        static int GetRecordCount(string connstr, string TableName)#region static int GetRecordCount(string connstr, string TableName)
        public static int GetRecordCount(string connstr, string TableName)
        {
            // 取表的记录数
            string SQLTEXT = @"SELECT COUNT(*) FROM "+TableName;

            SqlConnection conn = new SqlConnection(connstr);
            try
            {                
                int count = (int)DbCommon.ExcuteScalar(conn, SQLTEXT);
                return count;
            }
            catch(Exception err)
            {
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.GetRecordCount");
                return 0;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

        }
        #endregion

        /**//// <summary>
        /// 获取指定表的总记录数（带条件）
        /// </summary>
        /// <param name="connstr"></param>
        /// <param name="TableName"></param>
        static int GetRecordCount(string connstr, string TableName,string Where)#region static int GetRecordCount(string connstr, string TableName,string Where)
        public static int GetRecordCount(string connstr, string TableName,string Where)
        {
            // 取表的记录数
            string SQLTEXT = @"SELECT COUNT(*) FROM " + TableName + " WHERE "+Where;

            SqlConnection conn = new SqlConnection(connstr);
            try
            {
                int count = (int)DbCommon.ExcuteScalar(conn, SQLTEXT);
                return count;
            }
            catch(Exception err)
            {
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.GetRecordCount");
                return 0;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

        }
        #endregion

        /**//// <summary>
        /// 返回指定SQL语句查询的记录页数
        /// </summary>
        /// <param name="pagesize"></param>
        /// <param name="bid"></param>
        /// <param name="SQLTEXT"></param>
        /// <param name="sqlparams"></param>
        static int GetPageCountBySQL(string connnstr,int pagesize,string SQLTEXT)#region static int GetPageCountBySQL(string connnstr,int pagesize,string SQLTEXT)
        public static int GetPageCountBySQL(string connstr, int pagesize, string SQLTEXT)
        {
            SqlConnection conn = new SqlConnection(connstr);

            try
            {
                int pagecount = 0;
                int count = (int)DbCommon.ExcuteScalar(conn, SQLTEXT);
                pagecount = count / pagesize;
                // 余数
                int yushu = count % pagesize;
                if (yushu > 0)
                    pagecount += 1;
                return pagecount;
            }
            catch (Exception err)
            {
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.GetPageCountBySQL");
                return 0;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        #endregion

        /**//// <summary>
        /// 返回指定SQL语句查询的记录页数(带参数)
        /// </summary>
        /// <param name="pagesize"></param>
        /// <param name="bid"></param>
        /// <param name="SQLTEXT"></param>
        /// <param name="sqlparams"></param>
        static int GetPageCountBySQL(string connnstr,int pagesize,string SQLTEXT,params SqlParameter[] sqlparams)#region static int GetPageCountBySQL(string connnstr,int pagesize,string SQLTEXT,params SqlParameter[] sqlparams)
        public static int GetPageCountBySQL(string connstr,int pagesize,string SQLTEXT, params SqlParameter[] sqlparams)
        {
            SqlConnection conn = new SqlConnection(connstr);

            try
            {                
                int pagecount = 0;
                int count = (int)DbCommon.ExcuteScalar(conn, SQLTEXT, sqlparams);
                pagecount = count / pagesize;
                // 余数
                int yushu = count % pagesize;
                if (yushu > 0)
                    pagecount += 1;
                return pagecount;
            }
            catch (Exception err)
            {
                // 记录LOG
                CommonHelper.ErrorLog(err.Source,err.Message,"DbCommon.GetPageCountBySQL");
                return 0;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        #endregion
    }
}

 

 

   再写一个针对dtoNews实体与TB_NEWS表进行操作的DAL类：ManagerNews，调用DBCommon类中的方法进行这样的操作，将数据实体转换为一条数据记录：

 

 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;

namespace IndieFacade
{
    public class ManagerNews :IDisposable
    {
        private string _connString;             // 连接串
        private string _message;                // 错误消息
        private string _tableName;              // 表名

        对外接口:错误消息#region 对外接口:错误消息
        /**//// <summary>
        /// 错误消息
        /// </summary>
        public string Message
        {
            get { return this._message; }
        }
        #endregion

        构造函数#region 构造函数
        /**//// <summary>
        /// 使用默认配置，进行数据库连接
        /// </summary>
        public ManagerNews()
        {
            _connString = ConfigCache.ConnStr;
            _tableName = "TB_NEWS";
            _message = string.Empty;
        }

        /**//// <summary>
        /// 指定数据库连接串
        /// </summary>
        public ManagerNews(string connString)
        {
            _connString = connString;
            _tableName = "TB_NEWS";
            _message = string.Empty;
        }
        #endregion

        //----------------------------查询方法-----------------------------//
        获取所有新闻#region 获取所有新闻
        public IList GetAllNews()
        {
            dtoNews news = new dtoNews();
            return DbCommon.SelectDtoFromDb(this._connString, this._tableName, news, string.Empty, ref this._message);
        }
        #endregion

        //----------------------------修改方法-----------------------------//
        /**//// <summary>
        /// 添加一个新闻
        /// </summary>
        /// <param name="blog">日志分类</param>
        /// <returns>成功否</returns>
        bool AddNews(dtoNews news)#region bool AddNews(dtoNews news)
        public bool AddNews(dtoNews news)
        {
            return DbCommon.InsertDtoToDb(this._connString,this._tableName, news,0,false,0,ref this._message);
        }
        #endregion

        /**//// <summary>
        /// 更新一个新闻
        /// </summary>
        /// <param name="news">日志分类</param>
        /// <returns>成功否</returns>
        bool UpdateNews(dtoNews news)#region bool UpdateNews(dtoNews news)
        public bool UpdateNews(dtoNews news)
        {
            return DbCommon.UpdateDtoToDb(this._connString, this._tableName, news, 0, false, 0, ref this._message);
        }
        #endregion

        /**//// <summary>
        /// 删除一个新闻
        /// </summary>
        /// <param name="news"></param>
        /// <returns></returns>
        bool DeleteNews(dtoNews news)#region bool DeleteNews(dtoNews news)
        public bool DeleteNews(dtoNews news)
        {
            return DbCommon.DeleteDtoToDb(this._connString, this._tableName, news, 0,ref this._message);
        }
        #endregion

        //-----------------------实现Idisposable接口-----------------//

        // Implement IDisposable.
        // Do not make this method virtual.
        // A derived class should not be able to override this method.
        public void Dispose()
        {
            // This object will be cleaned up by the Dispose method.
            // Therefore, you should call GC.SupressFinalize to
            // take this object off the finalization queue 
            // and prevent finalization code for this object
            // from executing a second time.
            GC.SuppressFinalize(this);
        }
    }
}

 

   最后到客户端（例如WEB上的.aspx页面）就变得非常的轻松了：

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using IndieFacade;

public partial class demo_demo : BasePage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        this.txtToken.Value = GetToken();
    }

    // ---------------------- event ------------------------- //

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            if (ValidateToken(this.txtToken.Value))
            {
                insert();
            }
        }
        finally
        {
            this.txtToken.Value = GetToken();
        }
    }

    /**//// <summary>
    /// update
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button2_Click(object sender, EventArgs e)
    {
        try
        {
            if (ValidateToken(this.txtToken.Value))
            {
                update();
            }
        }
        finally
        {
            this.txtToken.Value = GetToken();
        }
    }

    /**//// <summary>
    /// delete
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button3_Click(object sender, EventArgs e)
    {
        try
        {
            if (ValidateToken(this.txtToken.Value))
            {
                delete();
            }
        }
        finally
        {
            this.txtToken.Value = GetToken();
        }
    }

    /**//// <summary>
    /// get list
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button4_Click(object sender, EventArgs e)
    {
        using(ManagerNews mn = new ManagerNews())
        {
            IList list = mn.GetAllNews();
        }
    }

    // ---------------------- method ------------------------- //

    private void insert()
    {
        dtoNews news = new dtoNews();
        news.Title = txtTile.Text;
        news.Author = txtAuthor.Text;
        news.Summary = txtSummary.Text;
        news.Tags = txtTags.Text;
        news.Contents = txtContent.Text;        
        news.Reads = 0;
        news.PubTime = DateTime.Now;

        using (ManagerNews mn = new ManagerNews())
        {
            bool ss = mn.AddNews(news);
        }
    }

    private void update()
    {
        dtoNews news = new dtoNews();
        news.Title = txtTile.Text;
        news.Author = txtAuthor.Text;
        news.Summary = txtSummary.Text;
        news.Tags = txtTags.Text;
        news.Contents = txtContent.Text;
        news.Reads = 0;
        news.PubTime = DateTime.Now;
        news.id = Convert.ToInt32(this.txtID.Text);

        using (ManagerNews mn = new ManagerNews())
        {
            bool ss = mn.UpdateNews(news);
        }
    }

    public void delete()
    {
        dtoNews news = new dtoNews();
        news.id = Convert.ToInt32(this.txtId2.Text);

        using (ManagerNews mn = new ManagerNews())
        {
            bool ss = mn.DeleteNews(news);
        }
    }

}

 

　　这个框架还很稚嫩，还只是解决了一两个如何偷懒的问题，至少在本人可怜的512内存机器上，性能堪忧，以后再慢慢改进吧。