ORM小练习代码

namespace RupengORM{    public class Dog    {        public Dog()        {        }        /// <summary>        ///     显示提供无参构造函数        /// </summary>        /// <param name="aa"></param>        public Dog(int aa)        {        }        public int Id { get; set; }        public string Name { get; set; }        public int Weight { set; get; }    }}

using System.Collections.Generic;using System.Configuration;using System.Data;using MySQL.Data.MySqlClient;namespace RupengORM{    public class DbSqlhelper    {        PRivate static readonly string Sqlconnstr = ConfigurationManager.ConnectionStrings["mysqlconn"].ConnectionString;        public static MySqlConnection CreateConnection()        {            MySqlConnection conn = new MySqlConnection(Sqlconnstr);            conn.Open();            return conn;        }        public static int ExecuteNonQuery(MySqlConnection conn, string sql, params MySqlParameter[] parameters)        {            using (MySqlCommand cmd=conn.CreateCommand())            {                cmd.CommandText = sql;                 cmd.Parameters.AddRange(parameters);                                return   cmd.ExecuteNonQuery();            }        }        public static int ExecuteNonQuery(string sql, params MySqlParameter [] parameters)        {            using (MySqlConnection conn = CreateConnection())            {                return ExecuteNonQuery(conn, sql, parameters);            }        }        public static object ExecuteScalar(MySqlConnection conn, string sql, Dictionary<string, object> dictionary)        {            using (MySqlCommand cmd=conn.CreateCommand())            {                cmd.CommandText = sql;                foreach (var kvp in dictionary)                {                    IDbDataParameter parameter = cmd.CreateParameter();                    parameter.ParameterName = kvp.Key;                    parameter.Value = kvp.Value;                    cmd.Parameters.Add(parameter);                }                return cmd.ExecuteScalar();            }                    }        public static object ExecuteScalar(string sql, Dictionary<string, object> dictionary)        {            using (MySqlConnection conn=CreateConnection())            {                return ExecuteScalar(conn, sql, dictionary);            }        }        public static DataTable ExecuteQuery(MySqlConnection conn, string sql, Dictionary<string, object> dictionary)        {            DataTable dataTable=new DataTable();            using (MySqlCommand cmd=conn.CreateCommand())            {                cmd.CommandText = sql;                foreach (var kvp in dictionary)                {                    IDbDataParameter parameter = cmd.CreateParameter();                    parameter.ParameterName = kvp.Key;                    parameter.Value = kvp.Value;                    cmd.Parameters.Add(parameter);                    using (IDataReader reader=cmd.ExecuteReader())                    {                        dataTable.Load(reader);                    }                }            }            return dataTable;        }        public static DataTable ExecuteQuery(  string sql, Dictionary<string, object> dictionary)        {            using (MySqlConnection conn=CreateConnection())            {                return ExecuteQuery(conn, sql, dictionary);            }        }    }    }

using System;using System.Collections.Generic;using System.Text;using MySql.Data.MySqlClient;namespace RupengORM{    internal class RPorm    {        //约定：1、类名要和表名一样        //2、字段名和数据库列名一样        //3、主键的名字必须叫Id，必须是自动递增，int类型        //        //        //        //        //        public static void Insert(object obj)        {            //获得obj对象的类名            var type = obj.GetType(); //typeof(Person)            var className = type.Name; //类名:Person            //propertyInfos获得类里面所有的属性            var propertyInfos = type.GetProperties();            var propNames = new string[propertyInfos.Length - 1]; //排除掉Id            var paramNames = new string[propertyInfos.Length - 1];            var sqlParameters = new MySqlParameter[propertyInfos.Length - 1];            //    Dictionary<string, object>  dic = new Dictionary<string, object>();            var count = 0;            foreach (var propInfo in propertyInfos)            {                var propName = propInfo.Name;                if (propName != "Id") //排除Id                {                    //遍历赋值,包含ID不进入赋值                    propNames[count] = propName;                    paramNames[count] = "@" + propName;                    var mySqlParameter = new MySqlParameter();                    mySqlParameter.ParameterName = "@" + propName;                    mySqlParameter.Value = propInfo.GetValue(obj); //去obj对象属性的值                    sqlParameters[count] = mySqlParameter;                    count++;                }            }            //拼接生成insert语句            var sbSql = new StringBuilder();            sbSql.Append("insert into ")                .Append(className)                .Append("(")                .Append(string.Join(",", propNames))                .Append(")");            sbSql.Append(" values (").Append(string.Join(",", paramNames)).Append(")");            DbSqlhelper.ExecuteNonQuery(sbSql.ToString(), sqlParameters); //params可变长度参数本质上就是一个数组        }        public static object SelectById(Type type, int id)        {            //将表名获取到            var classname = type.Name;            var sql = "select * from " + classname + " where id=@id";            var dictionary = new Dictionary<string, object>();            dictionary["@id"] = id;            var dataTable = DbSqlhelper.ExecuteQuery(sql, dictionary);            if (dataTable.Rows.Count <= 0)            {                return null;            }            if (dataTable.Rows.Count > 1)            {                throw new Exception("查到多条ID=" + id + "的数据");            }            var row = dataTable.Rows[0];            //创建type类的一个对象            var obj = Activator.CreateInstance(type);            //给obj对象的每一个属性(包括Id）赋值,得到id name weight            foreach (var propInfo in type.GetProperties())            {                var propName = propInfo.Name; //属性名就是别名                var value = row[propName]; //获取数据库中列的值                propInfo.SetValue(obj, value); //给obj对象的propinfo属性赋值为value            }            return obj;        }        public static T SelectById<T>(int id) where T : new() //泛型约束，约束T必须有一个无参的构造函数        {            var type = typeof (T); //typeof(Person)            var classname = type.Name;            var sql = "select * from " + classname + " where id=@id";            var dictionary = new Dictionary<string, object>();            dictionary["@id"] = id;            var dataTable = DbSqlhelper.ExecuteQuery(sql, dictionary);            if (dataTable.Rows.Count <= 0)            {                return default(T); //default(T)运算符用来获得类型的默认值                //default(int)→0 default(bool)→false default(Person)→null            }            if (dataTable.Rows.Count > 1)            {                throw new Exception("查到多条ID=" + id + "的数据");            }            var row = dataTable.Rows[0];            //创建type类的一个对象            //   var obj = Activator.CreateInstance(type);            var obj = new T(); //泛型约束            //给obj对象的每一个属性(包括Id）赋值 返回当前 Type 的所有公共属性。            foreach (var propInfo in type.GetProperties())            {                var propName = propInfo.Name; //属性名就是别名                var value = row[propName]; //获取数据库中列的值                propInfo.SetValue(obj, value); //给obj对象的propinfo属性赋值为value            }            return obj;        }        public static bool DeleteById(Type type, int id)        {            var classname = type.Name;            var sql = "delete from " + classname + " where id=@id ";            var i = DbSqlhelper.ExecuteNonQuery(sql, new MySqlParameter {ParameterName = "@id", Value = id});            //delete from dog where name='孔老二4'                return i > 0;        }        public static bool UpdateById(object obj)        {            var type = obj.GetType();            var classname = type.Name; //获得表名            var propertyInfos = type.GetProperties(); //获得表名中的功能属性            var propNames = new string[propertyInfos.Length]; //获取该属性的长度            var paramNames = new string[propertyInfos.Length];            var sqlParameters = new MySqlParameter[propertyInfos.Length];            var count = 0;            foreach (var propInfo in propertyInfos)            {                var propName = propInfo.Name;                var mySqlParameter = new MySqlParameter();                mySqlParameter.ParameterName = "@" + propName;                mySqlParameter.Value = propInfo.GetValue(obj); //去obj对象属性的值                sqlParameters[count] = mySqlParameter;                if (propName != "Id") //排除Id                {                    //遍历赋值,包含ID不进入赋值                    propNames[count] = propName; //name                    paramNames[count] = propName + "=@" + propName; //@name                }                count++;            }            var oop = string.Join("  ,  ", paramNames).Substring(4);            // sqlParameters;            var sb = new StringBuilder();            sb.Append("update ").Append(classname).Append(" set ").Append(oop).Append(" where id=@id");              var sqltxt = sb.ToString();            var i = DbSqlhelper.ExecuteNonQuery(sqltxt, sqlParameters);            //生成update语句            //update dog set name=@name weight=@weight where id=@id            //怎么知道那一列被修改了呢            //把所有列都更新一下。反正不变的还是不变            return i > 0;        }    }}

using System;namespace RupengORM{    internal class Program    {        private static void Main(string[] args)        {            //ORM:EF(entity framework,Dapper,Nhibernate)            // Person p1=new Person();            // p1.Name = "rupeng";            // p1.Age = 7;            // RPorm.Insert(p1);            for (var i = 0; i < 10; i++)            {                var d1 = new Dog();                d1.Name = "孔老二" + i;                d1.Weight = 30;                RPorm.Insert(d1);            }            //Person p1 = (Person)RPorm.SelectById(typeof(Person),1);            //Console.WriteLine(p1.Name+"的年龄是"+p1.Age);            //   Dog p2 = (Dog)RPorm.SelectById(typeof(Dog), 1);            //if (p2 == null)            //{            //    Console.WriteLine("没找到狗");            //}            //else            //{            //    Console.WriteLine(p2.Name);            //}            //Dog dog = RPorm.SelectById<Dog>(1);            //Console.WriteLine(dog.Name);            //  Type type = new Type typeof(Dog);            //bool aa=    RPorm.DeleteById(typeof(Dog),2);            //Console.WriteLine(aa);            var dog = new Dog();            dog.Weight++;            dog.Name = "孔老二";            dog.Id = 9;            var update = RPorm.UpdateById(dog);            Console.WriteLine(update);            Console.ReadKey();        }    }}