访问SQLServer的通用DAL层

首先是DataTable转List<T>的方法,这个方法通用性极强.

#region Table转List
        /// <summary>
        /// Table转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> TableToList<T>(DataTable dt) where T : new()
        {
            // 定义集合
            IList<T> list = new List<T>();

            // 获得此模型的类型
            Type type = typeof(T);

            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();

                // 获得公共属性
                PropertyInfo[] propertys = t.GetType().GetProperties();

                foreach (PropertyInfo pi in propertys)
                {
                    // 判断此属性是否有Setter
                    if (!pi.CanWrite) continue;

                    object value = dr[pi.Name];
                    if (value != DBNull.Value)
                        pi.SetValue(t, value, null);
                }

                list.Add(t);
            }
            return list.ToList();
        }
        #endregion

/// <summary>
    /// 数据访问基类:BaseService
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class BaseService<T> : IBaseService<T> where T : class ,new()
    {
        /// <summary>
        /// 得到某列最大值
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="fieldname"></param>
        /// <returns></returns>
        public virtual int GetMax(string connection,string fieldname)
        {
            #region 参数
            T entity = new T();
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@fieldname",fieldname)
            }; 
            #endregion

            StringBuilder sqlStr=new StringBuilder();
            sqlStr.Append("select max(");
            sqlStr.Append("@fieldname");
            sqlStr.Append(") from ");
            sqlStr.Append(entity.GetType().Name);

            return int.Parse(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters).Tables[0].Rows[0][0].ToString());
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="primarykey">主键值</param>
        /// <returns></returns>
        public virtual bool Exists(string connection, object primarykey)
        {
            #region 参数
            T entity = new T();

            string primaryKey = GetPrimarykey(connection, entity);
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@" + primaryKey, primarykey));
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select count(1) from ");
            sqlStr.Append(entity.GetType().Name);
            sqlStr.Append(" where ");
            sqlStr.Append(primaryKey + "=@" + primaryKey);

            int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
            if (res > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public virtual bool Add(string connection, T entity)
        {
            #region 参数
            List<SqlParameter> parameters = new List<SqlParameter>();
            string fields = "";
            string placeholders = "";
            foreach (var item in entity.GetType().GetProperties())
            {
                fields += item.Name + ",";
                placeholders += "@" + item.Name + ",";

                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@" + item.Name;
                parameter.Value = item.GetValue(entity, null);
                parameters.Add(parameter);
            }
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert " + entity.GetType().Name + " (");
            sqlStr.Append(fields.Substring(0, fields.Length - 1) + ")");
            sqlStr.Append(" values (");
            sqlStr.Append(placeholders.Substring(0, placeholders.Length - 1) + ")");

            int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
            if (res > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="entity">类</param>
        /// <returns></returns>
        public virtual bool Update(string connection, T entity)
        {
            #region 参数
            string primaryKey = GetPrimarykey(connection, entity);
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@"+primaryKey, entity.GetType().GetProperty(primaryKey).GetValue(entity,null)));
            string fields = "";
            foreach (var item in entity.GetType().GetProperties())
            {
                if (item.Name != primaryKey)
                {
                    fields += item.Name + "=@" + item.Name + ",";

                    SqlParameter parameter = new SqlParameter();
                    parameter.ParameterName = "@" + item.Name;
                    parameter.Value = item.GetValue(entity, null);
                    parameters.Add(parameter);
                }
            }
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update " + entity.GetType().Name);
            sqlStr.Append(" set ");
            sqlStr.Append(fields.Substring(0,fields.Length-1));
            sqlStr.Append(" where ");
            sqlStr.Append(primaryKey + "=@" + primaryKey);

            int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
            if (res > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="primaryKey">主键值</param>
        /// <returns></returns>
        public virtual bool Delete(string connection, object primarykey)
        {
            #region 参数
            T entity = new T();

            string primaryKey = GetPrimarykey(connection, entity);
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); 
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete from ");
            sqlStr.Append(entity.GetType().Name);
            sqlStr.Append(" where ");
            sqlStr.Append(primaryKey + "=@" + primaryKey);

            int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
            if (res > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 删除多条数据
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="base_idlist">主键值列表</param>
        /// <returns></returns>
        public virtual bool DeleteList(string connection, List<object> primarykeys)
        {
            #region 参数
            T entity = new T();

            string primaryKey = GetPrimarykey(connection, entity);

            string primaryKeys = "";
            foreach(var item in primarykeys)
            {
                primaryKeys += item.ToString();
            }

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@" + primaryKey, primaryKeys));
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete from ");
            sqlStr.Append(entity.GetType().Name);
            sqlStr.Append(" where ");
            sqlStr.Append(primaryKey + " in(@" + primaryKey + ")");

            int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
            if (res > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="primarykey">主键值</param>
        /// <returns></returns>
        public virtual T GetModel(string connection, object primarykey)
        {
            #region 参数
            T entity = new T();

            string primaryKey = GetPrimarykey(connection, entity);

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@table", entity.GetType().Name));
            parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); 
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from ");
            sqlStr.Append(entity.GetType().Name);
            sqlStr.Append(" where ");
            sqlStr.Append(primaryKey + "=@" + primaryKey);

            return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]).FirstOrDefault();
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public virtual List<T> GetList(string connection, string strWhere)
        {
            #region 参数
            T entity = new T();

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@where", strWhere));
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from ");
            sqlStr.Append(entity.GetType().Name);
            if (strWhere.Trim().Length > 0)
                sqlStr.Append(" where @where");

            return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]);
        }

        /// <summary>
        /// 获得总数
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public virtual int GetRecordCount(string connection, string strWhere)
        {
            #region 参数
            T entity = new T();

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@where", strWhere));
            #endregion

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select count(1) FROM ");
            sqlStr.Append(entity.GetType().Name);
            if (strWhere.Trim().Length > 0)
                sqlStr.Append(" where @where");

            return int.Parse(DatabaseAccess.SqlHelper.ExecuteScalarText(connection, sqlStr.ToString(), parameters.ToArray()).ToString());
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="cmdtype"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual int ExecteNonQuerySQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters)
        {
            return DatabaseAccess.SqlHelper.ExecteNonQuery(connection, cmdtype, sql, parameters);
        }

        /// <summary>
        /// 获取类主键
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="entity">类</param>
        /// <returns></returns>
        public virtual string GetPrimarykey(string connection, T entity)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("EXEC sp_pkeys @table_name='" + entity.GetType().Name + "'");

            SqlParameter[] parameters = new SqlParameter[]
            {
               
            };

            return DatabaseAccess.SqlHelper.ExecuteDataSetText(connection,sqlStr.ToString(),parameters).Tables[0].Rows[0]["COLUMN_NAME"].ToString();
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="cmdtype"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual DataSet ExecuteDataSetSQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters)
        {
            return DatabaseAccess.SqlHelper.ExecuteDataSet(connection, cmdtype, sql, parameters);
        }
    }