反射ORM 三层（for sql server/mysql）

sql server and oracle 不同之处只在于：

1·参数@和：

2·自增和序列

RPROM.cs //(写错愕，应该是RPORM)

RPROM.Insert(p1); //需求：DBS中插入一条数据

约定：
类名要和表名一样
字段名和数据库列名一样
主键的名字必须是Id,必须自动递增,int类型

    class RupengROM
    {
        /// 插入对象
        internal static void Insert(Object obj)
        {
            //获得类名
            Type type = obj.GetType();
            string className = type.Name;
            //获得除id的所有列名
            PropertyInfo[] properties = type.GetProperties();
            string[] propertyNames = new string[properties.Length - 1]; //存除id的属性名(列名)
            string[] propertyAtNames = new string[properties.Length - 1]; //存除id的属性+@名
            //获得除id的所有参数
            MySqlParameter[] mysqlParameters = new MySqlParameter[properties.Length - 1]; //存除id的参数名
            int i = 0;
            foreach(PropertyInfo property in properties)
            {
                if(property.Name!="Id")
                {
                    propertyNames[i] = property.Name;
                    propertyAtNames[i] = "@" + property.Name;
                    MySqlParameter mysqlParam = new MySqlParameter();
                    mysqlParam.ParameterName = "@" + property.Name;
                    mysqlParam.Value = property.GetValue(obj); //获得指定对象的属性值
                    mysqlParameters[i] = mysqlParam;
                    i++;
                }
            }
            string propertyNamesStr = string.Join(",", propertyNames);
            string propertyAtNamesStr = string.Join(",", propertyAtNames);
            //拼接sql语句
            //insert into T_Person(Name,Age) values(@Name,@Age)
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into T_").Append(className).Append("(").Append(propertyNamesStr).Append(") values(").Append(propertyAtNamesStr).Append(")");
            int im = MySqlHelper.ExecuteNonQuery(sb.ToString(), mysqlParameters);
            string msg = im > 0 ? "插入成功" : "插入失败";
            Console.WriteLine(msg);
        }

        /// 根据id查询对象
        internal static Object SelectById(Type type,int id)
        {
            string className = type.Name;
            //拼接sql语句
            //select * from T_Person where Id=@Id
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from T_").Append(className).Append(" where Id=@Id");
            DataTable dt = MySqlHelper.ExecuteQuery(sb.ToString(), new MySqlParameter() { ParameterName = "@Id", Value = id });
            if(dt.Rows.Count<=0)
            {
                return null;
            }
            else if(dt.Rows.Count>1)
            {
                throw new Exception("数据库发生异常，存在id=" + id + "的重复数据");
            }
            else
            {
                DataRow row = dt.Rows[0];
                Object obj = Activator.CreateInstance(type); //动态创建指定类型的实例
                PropertyInfo[] propertyInfoes = type.GetProperties();
                foreach(PropertyInfo propertyInfo in propertyInfoes)
                {
                    string propertyName = propertyInfo.Name; //属性名就是列名
                    object value = row[propertyName];
                    propertyInfo.SetValue(obj, value); //设置指定对象的属性值
                }
                return obj;
            }
        }

        /// 根据Id更新实例
        internal static void UpdateById(Object obj)
        {
            //获得类名
            Type type = obj.GetType();
            string className = type.Name;
            //获得除id的列名变化形式 "Name=@Name"
            PropertyInfo[] propertyInfoes = type.GetProperties();
            string[] propertyChanges = new string[propertyInfoes.Length - 1]; //存除id的属性变化形式
            MySqlParameter[] mysqlParameters = new MySqlParameter[propertyInfoes.Length]; //存sql参数
            int i = 0;
            foreach(PropertyInfo propertyInfo in propertyInfoes)
            {
                string propertyName = propertyInfo.Name;
                if (propertyName != "Id")
                {
                    propertyChanges[i] = propertyName + "=@" + propertyName;
                    i++;
                }
                MySqlParameter mysqlParam = new MySqlParameter();
                mysqlParam.ParameterName = "@" + propertyName;
                mysqlParam.Value = propertyInfo.GetValue(obj);
                mysqlParameters[i] = mysqlParam;
            }
            string propertyChangesStr = string.Join(",", propertyChanges);

            //拼接sql语句
            //update T_Person set Name=@Name,Age=@Age where Id=@Id
            StringBuilder sb = new StringBuilder();
            sb.Append("update T_").Append(className).Append(" set ").Append(propertyChangesStr).Append(" where Id=@Id");
            int im = MySqlHelper.ExecuteNonQuery(sb.ToString(), mysqlParameters);
            string msg = im > 0 ? "更新成功" : "更新失败";
            Console.WriteLine(msg);
        }

        /// 根据id删除
        internal static void DeleteById(Type type, int id)
        {
            //获得类名
            string className = type.Name;

            //拼接sql语句
            //delete from T_Person where Id=@Id
            StringBuilder sb = new StringBuilder();
            sb.Append("delete from T_").Append(className).Append(" where Id=@Id");
            int im = MySqlHelper.ExecuteNonQuery(sb.ToString(), new MySqlParameter() { ParameterName = "@Id", Value = id });
            string msg = im > 0 ? "删除成功" : "删除失败";
            Console.WriteLine(msg);
        }
    }