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