ORM小练习代码

2018-06-18 04:54:27来源：未知阅读 ()

DOG类

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; }
    }
}

Sqlhelper:

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);
            }
        }




    }

    
}

RProm 实现过程：

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();
        }
    }
}