学习了一些面向对象的语言之后,总会想要实现一些高层次的抽象。这篇文章用反射实现了insert SQL的命令,简易实现了ORM。
数据库 作为示例,先新建一个company database,再新建一个包含name、age、telephone、address四列的employees table。
1 2 3 4 5 6 7 8 9 10 11 CREATE DATABASE company;USE company; CREATE TABLE employees ( id INT NOT NULL , Name VARCHAR (20 ) NOT NULL , Age INT NOT NULL , Telephone VARCHAR (20 ) NOT NULL , Address VARCHAR (40 ) NOT NULL , PRIMARY KEY (id) );
创建好以后的结构如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 SHOW DATABASES;+ | Database | + | company | | | | < ...> | + USE company; SHOW TABLES;+ | Tables_in_company | + | employees | + + | id | Name | Age | Telephone | Address | +
model 新建一个model文件夹。按照上面创建的数据库,在model下创建一个employee类。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 namespace <ProjectName>.Model { public class Employee { public string Name { get ; } public int Age { get ; } public string Telephone { get ; } public string Address { get ; } public Employee (string name, int age, string telephone, string address ) { Name = name; Age = age; Telephone = telephone; Address = address; } } }
注意,设定getter
对于GetProperties()
来说是必要的。
数据库连接 新建Dao文件夹,在Dao下新建一个使用单例模式 的CompanyDb类,用于连接到数据库。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 namespace <ProjectName>.Dao { public sealed class CompanyDb { public static MySqlConnection Connection { get { return Nested.Connection; } } private CompanyDb ( ) { } private class Nested { static Nested ( ) { } internal static readonly MySqlConnection Connection = new MySqlConnection($"server = localhost; User ID = " + $"{<username>} ; Password = {<Password>} ; DataBase = " + "company; Charset = utf8; convert zero datetime = true" ); } } }
操作数据表 分解 在Dao下新建EmployeeHandler,并创建一个接收Model.Employee
对象的Add函数
1 2 3 4 5 6 7 8 9 10 11 12 namespace <ProjectName>.Dao { public class EmployeeHandler { static public int Add (Model.Employee model ) { } } }
首先,将model的属性名与属性值映射为字典 。
1 2 3 4 5 6 7 8 Dictionary<string , object > propertyDict = model.GetType().GetProperties() .ToDictionary ( propInfo => propInfo.Name, propInfo => $"'{propInfo.GetValue(model, null )} '" );
结合Dictionary.Keys和Dictionary.Values分别获取它们的键的列表与值的列表 ,再使用string.Join()
构造SQL命令语句。
1 2 3 4 5 string sqlCmd = $"INSERT INTO employees (" + $"{string .Join(", " , propertyDict.Keys)} ) VALUES " + $"({string .Join(", " , propertyDict.Values)} );" ;
剩下就是没什么好讲的执行过程了。
1 2 3 4 5 MySqlCommand cmd = new MySqlCommand(sqlCmd, CompanyDb.connection); cmd.Connection.Open(); int resultCode = cmd.ExecuteNonQuery();cmd.Connection.Close(); return resultCode;
总览 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 namespace <ProjectName>.Dao { public class EmployeeHandler { static public int Add (Model.Employee model ) { Dictionary<string , object > propertyDict = model.GetType().GetProperties() .ToDictionary ( propInfo => propInfo.Name, propInfo => $"'{propInfo.GetValue(model, null )} '" ); string sqlCmd = $"INSERT INTO employees (" + $"{string .Join(", " , propertyDict.Keys)} ) VALUES " + $"({string .Join(", " , propertyDict.Values)} );" ; MySqlCommand cmd = new MySqlCommand(sqlCmd, CompanyDb.connection); cmd.Connection.Open(); int resultCode = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return resultCode; } } }
使用 1 EmployeeHandler.Add(new Model.Employee(name, age, telephone, address));