首页 > > 程序设计 > C/C++ >

ORM小练习代码

2018-06-18 02:49:51来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

 

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

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：SQL Server 存储过程（转）

下一篇：Test failed.尝试加载Oracle客户端库时引发BadImageFormatExcept

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签