学习了一些面向对象的语言之后,总会想要实现一些高层次的抽象。这篇文章用反射实现了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
{

// set getter for GetProperties()
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)
{
// Code will goes here
}

}
}

首先,将model的属性名与属性值映射为字典

1
2
3
4
5
6
7
8
// Put property names and property values of model as pairs into dictionary
Dictionary<string, object> propertyDict = model.GetType().GetProperties()
.ToDictionary
(
propInfo => propInfo.Name,
// Add quote to avoid user input breaks SQL command
propInfo => $"'{propInfo.GetValue(model, null)}'"
);

结合Dictionary.Keys和Dictionary.Values分别获取它们的键的列表与值的列表,再使用string.Join()构造SQL命令语句。

1
2
3
4
5
// Build a SQL command by traversing properties of model
// For example: "INSERT INTO table (column1, column2) VALUES ('value1', 'value2');"
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)
{
// Put property names and property values of model as pairs into dictionary
Dictionary<string, object> propertyDict = model.GetType().GetProperties()
.ToDictionary
(
propInfo => propInfo.Name,
// Add quote to avoid user input breaks SQL command
propInfo => $"'{propInfo.GetValue(model, null)}'"
);

// Build a SQL command by traversing properties of model
// For example: "INSERT INTO table (column1, column2) VALUES ('value1', 'value2');"
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));