你的位置:首页 > ASP.net教程

[ASP.net教程]如鹏网学习的ORM小练习代码


自己刚刚学完反射,看着老师留着的作业写的代码。希望今后能够有用。

 

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