C#制作ORM映射学习笔记三 ORM映射实现

现在开始实现ORM的主体模块，首先需要在项目中新建一个类，命名为DbAccess，然后在项目的引用中添加两个dll，分别是MySql.Data.dll和System.Data.SQLite.dll，这两个dll都可以在对应的数据库官网上下载到，为了方便我这里也提供一个下载地址。添加好dll后需要在DbAccess中添加几个名空间，具体代码如下：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Reflection;
using MySql.Data;
using MySql.Data.MySqlClient;

下面开始实现ORM，首先需要实现对数据库的访问，具体代码如下：

private DbConnection dbConnection;

private DbCommand dbCommand;

private DbDataReader reader;

//打开数据库连接
public void OpenDB()
{
    try
    {
        switch (DbConfig.Type)
        {
            case DbType.Sqlite: dbConnection = new SQLiteConnection("data source = " + DbConfig.Host); break;
            case DbType.Mysql: dbConnection = new MySqlConnection(DbConfig.Host); break;
            default: break;
        }
        dbConnection.Open();
    }
    catch (Exception e)
    {
        throw e;
    }
}

//关闭数据库连接
public void CloseSqlConnection()
{
    if (dbCommand != null)
    {
        dbCommand.Dispose();
    }
    dbCommand = null;
    if (reader != null)
    {
        reader.Dispose();
    }
    reader = null;
    if (dbConnection != null && dbConnection.State == ConnectionState.Open)
    {
        dbConnection.Close();
        dbConnection.Dispose();
    }
    dbConnection = null;
}

//执行Sql命令
public int ExecuteQuery(string sql)
{
    OpenDB();
    dbCommand = dbConnection.CreateCommand();
    dbCommand.CommandText = sql;
    reader = dbCommand.ExecuteReader();
    return reader.RecordsAffected;
}

实现了对数据库的连接访问后就可以开始具体实现ORM了，首先实现两个查询方法：FirstOrDefault和Fetch，分别实现查询第一个满足条件的记录和查询所有满足条件的记录，代码如下：

//查询符合条件的第一个记录
public T FirstOrDefault<T>(Sql sql)
{
    try
    {
        ExecuteQuery(sql.GetSql());
        T result = default(T);
        if (reader.Read())
        {
            Type type = typeof(T);
            if (type.IsPrimitive || type == typeof(string) || type == typeof(DateTime) || type.IsEnum)
            {
                if (type.IsEnum)
                {
                    result = (T)Enum.ToObject(type, reader.GetValue(0));
                }
                else
                {
                    result = (T)Convert.ChangeType(reader.GetValue(0), type);
                }
            }
            else
            {
                result = Activator.CreateInstance<T>();
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo property in properties)
                {
                    string columName = AttributeProcess.GetColumnName(property);
                    if (property.PropertyType.IsEnum)
                    {
                        property.SetValue(result, Enum.ToObject(property.PropertyType, reader.GetValue(reader.GetOrdinal(columName))), null);
                    }
                    else
                    {
                        property.SetValue(result, Convert.ChangeType(reader.GetValue(reader.GetOrdinal(columName)), property.PropertyType), null);
                    }
                }
            }
        }
        return result;
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        CloseSqlConnection();
    }
}

//查询所有符合条件的记录
public List<T> Fetch<T>(Sql sql)
{
    try
    {
        ExecuteQuery(sql.GetSql());
        List<T> list = new List<T>();
        Type type = typeof(T);
        if (type.IsPrimitive || type == typeof(string) || type == typeof(DateTime) || type.IsEnum)
        {
            while (reader.Read())
            {
                if (type.IsEnum)
                {
                    list.Add((T)Enum.ToObject(type, reader.GetValue(0)));
                }
                else
                {
                    list.Add((T)Convert.ChangeType(reader.GetValue(0), type));
                }
            }
        }
        else
        {
            while (reader.Read())
            {
                T result = Activator.CreateInstance<T>();
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo property in properties)
                {
                    string columName = AttributeProcess.GetColumnName(property);
                    if (property.PropertyType.IsEnum)
                    {
                        property.SetValue(result, Enum.ToObject(property.PropertyType, reader.GetValue(reader.GetOrdinal(columName))), null);
                    }
                    else
                    {
                        property.SetValue(result, Convert.ChangeType(reader.GetValue(reader.GetOrdinal(columName)), property.PropertyType), null);
                    }
                }
                list.Add(result);
            }
        }
        return list;
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        CloseSqlConnection();
    }
}

这里有两点需要注意，第一、一定要再finally中执行CloseSqlConnection，确保每次查询结束后都会关闭连接，哪怕是查询时出现异常。第二、对于只查询一列的情况要特殊处理。
下面来实现增删改三个方法，代码如下：

/// <summary>
/// 更新指定的列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="columns"></param>
/// <returns></returns>
public bool Update<T>(T data, IEnumerable<string> columns)
{
    try
    {
        if (columns == null || columns.Count() == 0)
        {
            Update<T>(data);
        }
        Type type = data.GetType();