Small ORM practice code and orm practice code learned by rupeng net

Small ORM practice code and orm practice code learned by rupeng net

After learning reflection, I watched the code written by the teacher's homework. I hope it will be useful in the future.

 

DOG

Namespace RupengORM {public class Dog {public Dog () {}/// <summary> /// display the parameter-free constructor // </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 implementation process:

Using System; using System. collections. generic; using System. text; using MySql. data. mySqlClient; namespace RupengORM {internal class RPorm {// conventions: 1. The class name must be the same as the table name. // 2. The field name must be the same as the database column name. // 3. The primary key name must be Id and be auto-incrementing, int type // public static void Insert (object obj) {// obtain the Class Name of the obj object var type = obj. getType (); // typeof (Person) var className = type. name; // Class Name: Person // propertyInfos obtain all the attributes in the class var propertyInfos = type. ge TProperties (); var propNames = new string [propertyInfos. length-1]; // exclude 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") // exclude Id {// traverse the assignment, including the ID which does not enter the assignment propNames [count] = propName; paramNames [count] = "@" + propName; var mySqlParameter = new MySqlParameter (); mySqlParameter. parameterName = "@" + propName; mySqlParameter. value = propInfo. getValue (obj); // sqlParameters [count] = mySqlParameter; count ++ ;}// concatenate an insert statement var sbSql = new StringBuilder (); sbSql. append ("insert "). append (className ). app End ("("). append (string. join (",", propNames )). append (")"); sbSql. append ("values ("). append (string. join (",", paramNames )). append (")"); DbSqlhelper. executeNonQuery (sbSql. toString (), sqlParameters); // The variable length parameter params is essentially an array} public static object SelectById (Type type, int id) {// obtain the table name var classname = type. name; var SQL = "select * from" + classname + "where id = @ id"; var dictionary = new Dictiona Ry <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 ("multiple data records with ID =" + id + "are found");} var row = dataTable. rows [0]; // create an object of the type class var obj = Activator. createInstance (type); // assign a value to each attribute (including Id) of the obj object to obtain the id name weight foreach (var propInfo in type. getPropertie S () {var propName = propInfo. name; // The attribute Name is the alias var value = row [propName]; // gets the value of the column in the database propInfo. setValue (obj, value); // assign the value to the propinfo attribute of the obj object to value} return obj;} public static T SelectById <T> (int id) where T: new () // generic constraint. The constraint T must have a constructor without parameters {var type = typeof (T); // typeof (Person) var classname = type. name; var SQL = "select * from" + classname + "where id = @ id"; var dictionary = new Dictionary <str Ing, object> (); dictionary ["@ id"] = id; var dataTable = DbSqlhelper. executeQuery (SQL, dictionary); if (dataTable. rows. count <= 0) {return default (T); // The default (T) operator is used to obtain the default value of the type. // default (int) → 0 default (bool) → false default (Person) → null} if (dataTable. rows. count> 1) {throw new Exception ("multiple data records with ID =" + id + "are found");} var row = dataTable. rows [0]; // create an object of the type class // var obj = Activator. createInstance (ty Pe); var obj = new T (); // generic constraint // assign a value to each attribute (including Id) of the obj object and return all public attributes of the current Type. Foreach (var propInfo in type. getProperties () {var propName = propInfo. name; // The attribute Name is the alias var value = row [propName]; // gets the value of the column in the database propInfo. setValue (obj, value); // assign the value to the propinfo attribute of the obj object to 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 {P ArameterName = "@ id", Value = id}); // delete from dog where name = 'hole 2 4' return I> 0;} public static bool UpdateById (object obj) {var type = obj. getType (); var classname = type. name; // obtain the table Name var propertyInfos = type. getProperties (); // obtain the function attribute var propNames = new string [propertyInfos. length]; // obtain the Length of this attribute var paramNames = new string [propertyInfos. length]; var sqlParameters = new MySqlParameter [pr OpertyInfos. 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); // sqlParameters [count] = mySqlParameter; if (propName! = "Id") // exclude Id {// traverse the assignment, including the ID that does not enter the assignment 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 (SQL Txt, sqlParameters ); // generate the update statement // update dog set name = @ name weight = @ weight where id = @ id // how do I know that the column has been modified? // update all columns. Returns I> 0 ;}}}

 

 

 

Main Program:

Using System; namespace RupengORM {internal class Program {private static void Main (string [] args) {// ORM: EF (entity framework, Dapper, nhib.pdf) // 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 = "hole 2" + I; d1.Weight = 30; RPorm. insert (d1);} // Person p1 = (Person) RPorm. selectById (typeof (Person), 1); // Console. writeLine (p1.Name + "is" + p1.Age); // Dog p2 = (Dog) RPorm. selectById (typeof (Dog), 1); // if (p2 = null) // {// Console. writeLine ("no dog found"); // 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 = "second hole"; dog. id = 9; var update = RPorm. updateById (dog); Console. writeLine (update); Console. readKey ();}}}