简单的持久层框架尝试

namespace DAL
{
    /// <summary>
    ///Persistence 的摘要说明
    /// </summary>
    public class Persistence<T> where T : new()
    {
        public delegate string DelegeteGetId(string tbName, string str);
        public Persistence()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        #region 添加
        public int insert(T obj, string tbName)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;//测试标志  取消注释可以查看生产的sql语句及模型类的属性


            Database database = DatabaseFactory.CreateDatabase();
            string sql = "insert into " + tbName + "(";
            string _sql = "values(";
            Type type = obj.GetType();
            bool flag = true;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            foreach (System.Reflection.PropertyInfo info in type.GetProperties())
            {
                if (info.GetValue(obj, null) != null)
                {
                    string iName = info.Name;
                    if (iName != "id" && iName != tbName + "_id")
                    {
                        if (flag)
                        {
                            flag = false;
                            sql = sql + " " + iName;
                            _sql = _sql + " @" + iName;
                        }
                        else
                        {
                            sql = sql + "," + iName;
                            _sql = _sql + ",@" + iName;
                        }
                    }
                    SqlParameter parm = new SqlParameter("@" + info.Name, info.GetValue(obj, null));
                    cmd.Parameters.Add(parm);
                }
            }
            sql = sql + ")";
            _sql = _sql + ")";
            sql = sql + _sql;
            cmd.CommandText = sql;
            if (testFlag)
            {
                testResponse.write(sql);
                return 0;
            }
            else
            {
                return database.ExecuteNonQuery(cmd);    
            }
        }
        public int insertAll(T obj, string tbName, string append, DelegeteGetId getId)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;

            string id = getId(tbName, append);

            Database database = DatabaseFactory.CreateDatabase();
            string sql = "insert into " + tbName + "(";
            string _sql = "values(";
            Type type = obj.GetType();
            bool flag = true;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            foreach (System.Reflection.PropertyInfo info in type.GetProperties())
            {
                if (info.GetValue(obj, null) != null)
                {
                    string iName = info.Name;
                    if (id != iName)
                    {
                        if (flag)
                        {
                            flag = false;
                            sql = sql + " " + iName;
                            _sql = _sql + " @" + iName;
                        }
                        else
                        {
                            sql = sql + "," + iName;
                            _sql = _sql + ",@" + iName;
                        }
                        SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
                        cmd.Parameters.Add(parm);
                    }
                }
            }
            sql = sql + ")";
            _sql = _sql + ")";
            sql = sql + _sql;
            cmd.CommandText = sql;
            if (testFlag)
            {
                testResponse.write(sql);
                return 0;
            }
            else
            {
                return database.ExecuteNonQuery(cmd);
            }
        }

        public int insert(T obj, string tbName, string append, DelegeteGetId getId)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;

            string id = getId(tbName, append);

            Database database = DatabaseFactory.CreateDatabase();
            string sql = "insert into " + tbName + "(";
            string _sql = "values(";
            Type type = obj.GetType();
            bool flag = true;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            T t = new T();
            foreach (System.Reflection.PropertyInfo info in type.GetProperties())
            {
                if (!Equals(info.GetValue(obj, null), info.GetValue(t, null)))
                {
                    string iName = info.Name;
                    if (id != iName)
                    {
                        if (flag)
                        {
                            flag = false;
                            sql = sql + " " + iName;
                            _sql = _sql + " @" + iName;
                        }
                        else
                        {
                            sql = sql + "," + iName;
                            _sql = _sql + ",@" + iName;
                        }
                        SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
                        cmd.Parameters.Add(parm);
                    }
                }
            }
            sql = sql + ")";
            _sql = _sql + ")";
            sql = sql + _sql;
            cmd.CommandText = sql;
            if (testFlag)
            {
                foreachModel(obj);
                testResponse.write(sql);
                return 0;
            }
            else
            {
                return database.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region 更新数据
        public int update(T obj, string tbName)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;//测试标志  取消注释可以查看生产的sql语句及模型类的属性

            Database database = DatabaseFactory.CreateDatabase();
            string sql = "update " + tbName + " set ";
            string _sql = "where ";
            Type type = obj.GetType();
            bool flag = true;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            foreach (System.Reflection.PropertyInfo info in type.GetProperties())
            {
                if (info.GetValue(obj, null) != null)
                {
                    string iName = info.Name;
                    if (iName == tbName + "_id")
                    {
                        _sql = _sql + "where " + iName + "=@" + info.Name;
                    }
                    else if (iName == "id")
                    {
                        _sql = _sql + "where id=@" + info.Name;
                    }
                    else
                    {
                        if (flag)
                        {
                            sql = sql + " " + info.Name + "@" + info.Name;
                        }
                        else
                        {
                            sql = sql + "," + info.Name + "@" + info.Name;
                        }
                    }
                    SqlParameter parm = new SqlParameter("@" + info.Name, info.GetValue(obj, null));
                    cmd.Parameters.Add(parm);
                }
            }
            cmd.CommandText = sql;
            if (testFlag)
            {
                testResponse.write(sql);
                return 0;
            }
            else
            {
                return database.ExecuteNonQuery(cmd);
            }
        }
        public int update(T obj, string tbName, string append, DelegeteGetId getId)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;//测试标志  取消注释可以查看生产的sql语句及模型类的属性

            string id = getId(tbName, append);
            Database database = DatabaseFactory.CreateDatabase();
            string sql = "update " + tbName + " set ";
            string _sql = " where ";
            Type type = obj.GetType();
            bool flag = true;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            T t = new T();
            foreach (System.Reflection.PropertyInfo info in type.GetProperties())
            {
                //Object ddd = info.GetValue(obj, null);
                //string str = info.GetValue(obj, null).ToString();
                //testResponse.write("info:"+info.GetValue(obj, null).ToString());
                //testResponse.write("t"+info.GetValue(t, null).ToString());
                //testResponse.write(info.GetValue(obj,null).ToString());
                if (!Equals(info.GetValue(obj, null), info.GetValue(t, null)))
                //if (info.GetValue(obj, null)!=null)
                {
                    /**
                     * 这里有些问题需要研究
                     *1：下面的两个输出语句在用Equals对比后就出错
                     *2：并且在用Equals(info.GetValue(obj, null).Equals(info.GetValue(t, null)) 时也出错
                     */
                    //testResponse.write("info:"+info.GetValue(obj, null).ToString());
                    //testResponse.write("t"+info.GetValue(t, null).ToString());
                    string iName = info.Name;
                    if (iName == id)
                    {
                        _sql = _sql + iName + "=@" + iName;
                    }
                    else
                    {
                        if (flag == true)
                        {
                            sql = sql + iName + "=@" + iName;
                            flag = false;
                        }
                        else
                        {
                            sql = sql + "," + iName + "=@" + iName;
                        }
                    }
                    SqlParameter parm = new SqlParameter("@" + iName, info.GetValue(obj, null));
                    cmd.Parameters.Add(parm);
                }
            }
            cmd.CommandText = sql + _sql;
            
            if (testFlag)
            {
                testResponse.write(sql);
                return 0;
            }
            else
            {
                return database.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region 删除
        public int delete(int id, string tbName)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;//测试标志  取消注释可以查看生产的sql语句及模型类的属性

            Database database = DatabaseFactory.CreateDatabase();
            string sql = "delete from " + tbName + " where id= @id";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            SqlParameter parm = new SqlParameter("@id", id);
            cmd.Parameters.Add(parm);
            if (testFlag)
            {
                testResponse.write(sql);
                return 0;
            }
            else
            {
                return database.ExecuteNonQuery(cmd);
            }
        }
        public int delete(int id, string tbName, string append, DelegeteGetId getId)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;//测试标志  取消注释可以查看生产的sql语句及模型类的属性

            string idName = getId(tbName, append);
            Database database = DatabaseFactory.CreateDatabase();
            string sql = "delete from " + tbName + " where " + idName + "= @id";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            SqlParameter parm = new SqlParameter("@id", id);
            cmd.Parameters.Add(parm);
            if (testFlag)
            {
                testResponse.write(sql);
                return 0;
            }
            else
            {
                return database.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region 显示页面
        /// <summary>
        /// 返回详细页面
        /// </summary>
        /// <param name="id"></param>
        /// <param name="tbName"></param>
        /// <param name="append"></param>
        /// <param name="getId"></param>
        /// <returns></returns>
        public T getView(int id, string tbName, string append, DelegeteGetId getId)
        {
            //测试标志
            bool testFlag = false;
            //testFlag = true;
            string ids = getId(tbName, append);
            string sql = "select * from " + tbName + " where " + ids + " =@id";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@id",id)
                };
            T obj = new T();

            if (testFlag)
            {
                testResponse.write(sql);
                
                foreach (SqlParameter item in parms)
                {
                    testResponse.write(item.ParameterName + ":  " + item.Value);       
                }
                return default(T);
            }
            else
            {
                using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms))
                {
                    if (reader.Read())
                    {
                        foreach (PropertyInfo info in obj.GetType().GetProperties())
                        {
                            if (reader[info.Name] != Convert.DBNull)
                            {
                                info.SetValue(obj, reader[info.Name], null);
                            }
                        }
                    }
                }
                return obj;
            }
        }
        /// <summary>
        /// 根据sql 返回详细页面
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public T getViewWhere(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            T obj = new T();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(cmd, null))
            {
                if (reader.Read())
                {
                    foreach (PropertyInfo info in obj.GetType().GetProperties())
                    {
                        if (reader[info.Name] != Convert.DBNull)
                        {
                            info.SetValue(obj, reader[info.Name], null);
                        }
                    }
                }
            }
            return obj;
        }
        /// <summary>
        /// 根据sql 返回页面内容
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public T getViewWhere(string sql, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            T obj = new T();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, cmdParms))
            {
                if (reader.Read())
                {
                    foreach (PropertyInfo info in obj.GetType().GetProperties())
                    {
                        if (reader[info.Name] != Convert.DBNull)
                        {
                            info.SetValue(obj, reader[info.Name], null);
                        }
                    }
                }
            }
            return obj;
        }
        #endregion

        #region 列表页
        /// <summary>
        /// 返回列表页的所有内容
        /// </summary>
        /// <param name="tbName"></param>
        /// <returns></returns>
        public List<T> getAll(string tbName)
        {
            Database database = DatabaseFactory.CreateDatabase();
            string sql = "select * from " + tbName;
            DbCommand cmd = database.GetSqlStringCommand(sql);
            List<T> items = new List<T>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
            {
                while (reader.Read())
                {
                    T obj = new T();
                    foreach (PropertyInfo info in obj.GetType().GetProperties())
                    {
                        if (reader[info.Name] != Convert.DBNull)
                        {
                            info.SetValue(obj, reader[info.Name], null);
                        }
                    }
                    items.Add(obj);
                }
            }
            return items;
        }
        /// <summary>
        /// 根据sql语句和参数数组返回内容
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public List<T> getListWhere(string sql, SqlParameter[] cmdParms)
        {
            Database database = DatabaseFactory.CreateDatabase();
            //string sql = "";
            SqlCommand cmd = new SqlCommand();
            List<T> items = new List<T>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, cmdParms))
            {
                while (reader.Read())
                {
                    T obj = new T();
                    foreach (PropertyInfo info in obj.GetType().GetProperties())
                    {
                        if (reader[info.Name] != Convert.DBNull)
                        {
                            info.SetValue(obj, reader[info.Name], null);
                        }
                    }
                    items.Add(obj);
                }
            }
            return items;   
        }
        public List<T> getListWhere(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            List<T> items = new List<T>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    T obj = new T();
                    foreach (PropertyInfo info in obj.GetType().GetProperties())
                    {
                        info.SetValue(obj, reader[info.Name], null);
                    }
                    items.Add(obj);
                }
            }
            return items;   
        }
        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageDal"></param>
        /// <returns></returns>
        public DataRecordList<T> getList(PageT pageDal)
        {
            DataRecordList<T> dataRecordList = new DataRecordList<T>();
            List<T> items = new List<T>();
            SqlCommand cmd = new SqlCommand();
            string sql = "proc_getpagedata";
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@TableName", DbType.String),
                new SqlParameter("@FieldList", DbType.String),
                new SqlParameter("@PageSize", DbType.Int32),
                new SqlParameter("@PageIndex", DbType.Int32),
                new SqlParameter("@OrderField", DbType.String),
                new SqlParameter("@OrderType", DbType.Boolean),
                new SqlParameter("@Where", DbType.String),
                new SqlParameter("@RecordCount", DbType.Int32),
                new SqlParameter("@PageCount", DbType.Int32)
            };
            parms[0].Value = pageDal._table;
            parms[1].Value = pageDal.fieldList;
            parms[2].Value = pageDal.pageSize;
            parms[3].Value = pageDal.pageIndex;
            parms[4].Value = pageDal.orderField;
            parms[5].Value = pageDal.orderBy;
            parms[6].Value = pageDal.where;
            parms[7].Direction = ParameterDirection.Output;
            parms[8].Direction = ParameterDirection.Output;
            
            using (IDataReader reader = SqlHelper.ExecuteReader(cmd,parms))
            {
                while (reader.Read())
                {
                    T obj = new T();
                    foreach (PropertyInfo info in obj.GetType().GetProperties())
                    {
                        if (reader[info.Name] != Convert.DBNull)
                        {
                            info.SetValue(obj, reader[info.Name], null);
                        }
                    }
                    items.Add(obj);
                }
            }
            int recordCount = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
            int pageCount = int.Parse(cmd.Parameters["@PageCount"].Value.ToString());
            dataRecordList.listData = items;
            dataRecordList.PageSize = pageDal.pageSize;
            dataRecordList.PageIndex = pageDal.pageIndex;
            dataRecordList.PageCount = pageCount;
            dataRecordList.RecordCount = recordCount;
            return dataRecordList;
        }
        #endregion

        #region 公共函数->测试
        /// <summary>
        /// 遍历模型
        /// </summary>
        /// <param name="obj"></param>
        public static void foreachModel(object obj)
        {
            Type type = obj.GetType();
            foreach (System.Reflection.PropertyInfo info in type.GetProperties())
            {
                System.Web.HttpContext.Current.Response.Write("<br>name:" + info.Name + "--value:--" + info.GetValue(obj, null));
            }
        }
        #endregion
    }
}