DAL层利用反射获取数据列表，增加和修改数据

一般DAL层都会涉及到往表里添加和修改数据，根据Model设置SqlParameter[]，以及将datarow数据转化为Model,利用泛型和反射，可以大大简化代码。

首先是DalHelper类

 

public static class DalHelper<T> where T : new()
    {
        #region SqlParameter
        /// <summary>
        /// 通过Model类获取属性的SqlParameter数组
        /// </summary>
        /// <param name="classObject">Model类</param>
        /// <returns>SqlParameter数组</returns>
        public static SqlParameter[] GetClassSqlParameters(object classObject)
        {
            var sqlParms = new List<SqlParameter>();
            var oType = classObject.GetType();
            foreach (var mi in oType.GetMembers())
            {
                if (mi.MemberType != MemberTypes.Property) continue;
                var p = (PropertyInfo)mi;
                var pName = p.Name;
                var v = p.GetValue(classObject, null);
             
                if (p.PropertyType == typeof(DateTime) && v.Equals(DateTime.MinValue))
                {
                    v = null;
                }
               
                var parm = new SqlParameter(string.Concat("@", pName), v ?? DBNull.Value);
                
                if (sqlParms.Contains(parm) == false)
                {
                    sqlParms.Add(parm);
                }
            }
            return sqlParms.ToArray();
        }
        #endregion

        #region 添加和修改数据字符串
        /// <summary>
        /// 通过Model获取添加数据的字符串
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="classObject">Model类</param>
        /// <param name="exceptFields">排除的字段</param>
        /// <returns></returns>
        public static string GetAddString(string tableName, object classObject, string[] exceptFields)
        {
            var list = GetClassString(classObject);
            var feilds = new StringBuilder().Append("(");
            var vars = new StringBuilder().Append("(");
            foreach (var str in list)
            {
                var temp = str;
                if (exceptFields.Any(exceptField => exceptField.ToLower() == temp.Key.ToLower()))
                {
                    continue;
                }

                feilds.Append(str.Key).Append(",");
                vars.Append(str.Value).Append(",");
            }

            return string.Concat("insert into ", tableName, feilds.ToString().TrimEnd(','), ")values", vars.ToString().TrimEnd(','), ");select @@identity;");
        }

        /// <summary>
        /// 通过Model获取修改数据的字符串
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="classObject"></param>
        /// <param name="exceptFields"></param>
        /// <returns></returns>
        public static string GetUpdateString(string tableName, object classObject, string[] exceptFields)
        {
            var list = GetClassString(classObject);
            var result = string.Concat("update ", tableName, " set ");
            var whereStr = "";
            foreach (var str in list)
            {
                int flag = 0;
                var temp = str;
                if (exceptFields.Any(exceptField => exceptField.ToLower() == temp.Key.ToLower()))
                {
                    whereStr = string.Concat(whereStr, str.Key, "=", str.Value, ",");
                    flag = 1;
                }
                if (flag == 1) continue;
                result = string.Concat(result, str.Key, "=", str.Value, ",");
            }

            return string.Concat(result.TrimEnd(','), " where ", whereStr.TrimEnd(','));
        }

        /// <summary>
        /// 通过Model类获取属性
        /// </summary>
        /// <param name="classObject">Model类</param>
        /// <returns>SortedList</returns>
        private static SortedList<string, string> GetClassString(object classObject)
        {
            var list = new SortedList<string, string>();
            var oType = classObject.GetType();
            foreach (var mi in oType.GetMembers())
            {
                if (mi.MemberType != MemberTypes.Property) continue;//如不为属性，跳过
                var p = (PropertyInfo)mi;
                var pName = p.Name;
                try
                {
                    list.Add(pName, string.Concat("@", pName));
                }
                catch
                {

                }
            }
            return list;
        }
        #endregion

        #region 获取数据列表
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="paras">SqlParameter[]</param>
        /// <returns></returns>
        public static IList<T> SelectListBySql(string sql, SqlParameter[] paras)
        {
            SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql, paras);
            var dt = new DataTable();
            dt.Load(sdr);
            sdr.Close();
            return (from DataRow dr in dt.Rows select ConvertModel(dr)).ToList();
        }
        /// <summary>
        /// datarow转为model
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static T ConvertModel(DataRow dr)
        {
            T t = new T();
            Type modelType = t.GetType();
            foreach (PropertyInfo p in modelType.GetProperties())
            {
                p.SetValue(t, GetDefaultValue(dr[p.Name], p.PropertyType), null);
            }
            return t;
        }

        private static object GetDefaultValue(object obj, Type type)
        {
            if (obj == DBNull.Value)
            {
                obj = default(object);
            }
            else
            {
                obj = Convert.ChangeType(obj, type);
            }
            return obj;
        }
        #endregion
    }

 

下面是SqlHelper类

 

public abstract class SqlHelper
    {
        //获取数据库连接字符串，其属于静态变量且只读，项目中所有文档可以直接使用，但不能修改
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["connAdmin"].ConnectionString;


        // 哈希表用来存储缓存的参数信息，哈希表可以存储任意类型的参数。
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。
        ///增加，删除，修改等可用此方法。
        /// 使用参数数组形式提供参数列表
        /// </summary>
        /// <remarks>
        /// 使用示例：
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = Convert.ToInt32(cmd.ExecuteNonQuery());
                //清空SqlCommand中的参数列表
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static int ExecuteNonQueryAndGetId(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = Convert.ToInt32(cmd.ExecuteScalar());
                //清空SqlCommand中的参数列表
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        ///执行一条不返回结果的SqlCommand，通过一个已经存在的数据库连接
        /// 使用参数数组提供参数
        /// </summary>
        /// <remarks>
        /// 使用示例：  
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">一个现有的数据库连接</param>
        /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = Convert.ToInt32(cmd.ExecuteScalar());          
            cmd.Parameters.Clear();
            return val;
        }       

        /// <summary>
        /// 执行一条不返回结果的SqlCommand，通过一个已经存在的数据库事物处理
        /// 使用参数数组提供参数
        /// </summary>
        /// <remarks>
        /// 使用示例：
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">一个存在的 sql 事物处理</param>
        /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val =Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行一条返回结果集的SqlCommand命令，通过专用的连接字符串。
        /// 使用参数数组提供参数
        /// </summary>
        /// <remarks>
        /// 使用示例：  
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个包含结果的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            // 在这里使用try/catch处理是因为如果方法出现异常，则SqlDataReader就不存在，
            //CommandBehavior.CloseConnection的语句就不会执行，触发的异常由catch捕获。
            //关闭数据库连接，并通过throw再次引发捕捉到的异常。
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 执行一条返回第一条记录第一列的SqlCommand命令，通过专用的连接字符串。
        /// 使用参数数组提供参数
        /// </summary>
        /// <remarks>
        /// 使用示例：  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个object类型的数据，可以通过 Convert.To{Type}方法转换类型</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行一条返回第一条记录第一列的SqlCommand命令，通过已经存在的数据库连接。
        /// 使用参数数组提供参数
        /// </summary>
        /// <remarks>
        /// 使用示例：
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">一个已经存在的数据库连接</param>
        /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个object类型的数据，可以通过 Convert.To{Type}方法转换类型</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 缓存参数数组
        /// </summary>
        /// <param name="cacheKey">参数缓存的键值</param>
        /// <param name="cmdParms">被缓存的参数列表</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// 获取被缓存的参数
        /// </summary>
        /// <param name="cacheKey">用于查找参数的KEY值</param>
        /// <returns>返回缓存的参数数组</returns>
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            //新建一个参数的克隆列表
            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            //通过循环为克隆参数列表赋值
            for (int i = 0, j = cachedParms.Length; i < j; i++)
                //使用clone方法复制参数列表中的参数
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// 为执行命令准备参数
        /// </summary>
        /// <param name="cmd">SqlCommand 命令</param>
        /// <param name="conn">已经存在的数据库连接</param>
        /// <param name="trans">数据库事物处理</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param>
        /// <param name="cmdParms">返回带参数的命令</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            //判断数据库连接状态
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            //判断是否需要事物处理
            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        public static DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, int startPage, int pageSize, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            DataSet ds = new DataSet();

            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;
            cmd.Connection = conn;

            try
            {
                cmd.CommandTimeout = 0;

                if (commandParameters != null)
                {
                    foreach (SqlParameter parm in commandParameters)
                        cmd.Parameters.Add(parm);
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, startPage, pageSize, "table");
                return ds.Tables[0];
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }

    }

 

 

示例：

先定义一个Art类，对应的数据库表为Art，字段与Art类属性一致。

 

/// <summary>
    /// 文章
    /// </summary>
    public class Art
    {
        /// <summary>
        /// Id
        /// </summary>
        public int ArtId { get; set; }
        /// <summary>
        /// 栏目Id
        /// </summary>
        public int ArtTreeId { get; set; }
        /// <summary>
        /// 标题
        /// </summary>
        public string ArtTitle { get; set; }
        /// <summary>
        /// 副标题
        /// </summary>
        public string ArtSubTitle { get; set; }
        /// <summary>
        /// 简介
        /// </summary>
        public string ArtSummary { get; set; }
        /// <summary>
        /// 文章内容
        /// </summary>
        public string ArtContent { get; set; }
        /// <summary>
        /// 上传图片
        /// </summary>
        public string ArtPic { get; set; }
        /// <summary>
        /// 上传文件
        /// </summary>
        public string ArtFile { get; set; }
        /// <summary>
        /// KeyWords
        /// </summary>
        public string ArtKeyWords { get; set; }
        /// <summary>
        /// Description
        /// </summary>
        public string ArtDescription { get; set; }
        /// <summary>
        /// 文章来源
        /// </summary>
        public string ArtSource { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        public string ArtWriter { get; set; }
        /// <summary>
        /// 发布者
        /// </summary>
        public int ArtAdmin { get; set; }
        /// <summary>
        /// 排序
        /// </summary>
        public int ArtSort { get; set; }
        /// <summary>
        /// 是否置顶
        /// </summary>
        public int ArtIfTop { get; set; }
        /// <summary>
        /// 是否外部链接
        /// </summary>
        public int ArtIfLink { get; set; }
        /// <summary>
        /// 外部链接地址
        /// </summary>
        public string ArtLinkName { get; set; }
        /// <summary>
        /// 是否首页显示
        /// </summary>
        public int ArtIfIndex { get; set; }
        /// <summary>
        /// 文章状态
        /// </summary>
        public int ArtStatus { get; set; }
        /// <summary>
        /// 文章点击数
        /// </summary>
        public int ArtClick { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime AddTime { get; set; }

    }

 

 

    有了以上准备工作，在DAL中操作就很简单了：

 

/// <summary>
        /// 添加文章
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(MODEL.Art model)
        {
            var sql = DalHelper<MODEL.Art>.GetAddString("Art", model, new[] { "ArtId" }); //ArtId是主键和标识列，所以排除
            var para = DalHelper<MODEL.Art>.GetClassSqlParameters(model);
            return SqlHelper.ExecuteNonQueryAndGetId(SqlHelper.ConnectionString, CommandType.Text, sql, para);
        }

 

修改文章

 

/// <summary>
        /// 更新文章
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(MODEL.Art model)
        {
            var sql = DalHelper<MODEL.Art>.GetUpdateString("Art", model, new[] { "ArtId" });//ArtId是主键和标识列，作为where条件
            var para = DalHelper<MODEL.Art>.GetClassSqlParameters(model);

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql, para) > 0;
        }

 

      

        //根据栏目获取文章列表

       public IList<MODEL.Art> SelectList(int treeId)

       {

             var sql="select * from Art  where ArtTreeId=@ArtTreeId  order  by Addtime desc";   

             var para=new []{new SqlParameter("@ArtTreeId",treeId)};

             return SelectListBySql(sql, para);

      }

 

     //获取指定Id的文章

        public MODEL.Art GetModel(int artId)
        {
            var sql = string.Concat("select * from Art where artId=", artId);
            var list = SelectListBySql(sql, null);  //如果不用SqlParameter传值，此处设为null
            return list.Count > 0 ? list[0] : null;
        }

        /// <summary>
        /// 获取指定sql及paras的文章列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        private static IList<MODEL.Art> SelectListBySql(string sql, SqlParameter[] para)
        {          

            return DalHelper<MODEL.Art>.SelectListBySql(sql, para);
        }