转：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();  
        string table = AttributeProcess.GetTableName(type);  
        string sql = "Update " + table + " Set ";  
        string where = " Where ";  
        List<string> sets = new List<string>();  
        PropertyInfo[] properties = type.GetProperties();  
        foreach (PropertyInfo property in properties)  
        {  
            string column = AttributeProcess.GetColumnName(property);  
            if (!AttributeProcess.IsPrimary(type, property))  
            {  
                if (columns.Any(a => a == column))  
                {  
                    if (property.PropertyType == typeof(bool))  
                    {  
                        bool value = bool.Parse(property.GetValue(data, null).ToString());  
                        sets.Add(column + "=" + (value ? "1" : "0"));  
                    }  
                    else if (property.PropertyType.IsPrimitive)  
                    {  
                        sets.Add(column + "=" + property.GetValue(data, null));  
                    }  
                    else if (property.PropertyType.IsEnum)  
                    {  
                        int intValue = (int)property.GetValue(data, null);  
                        sets.Add(column + "=" + intValue);  
                    }  
                    else  
                    {  
                        if (Sql.InjectionDefend(property.GetValue(data, null).ToString()))  
                        {  
                            sets.Add(column + "=\'" + property.GetValue(data, null) + "\'");  
                        }  
                    }  
                }  
            }  
            else  
            {  
                if (property.PropertyType.IsPrimitive)  
                {  
                    where += column + "=" + property.GetValue(data, null);  
                }  
                else  
                {  
                    where += column + "=\'" + property.GetValue(data, null) + "\'";  
                }  
            }  
        }  
        sql += (string.Join(",", sets) + where);  
        ExecuteQuery(sql);  
        return true;  
    }  
    catch (Exception e)  
    {  
        throw e;  
    }  
    finally  
    {  
        CloseSqlConnection();  
    }  
}  
  
//更新指定的记录  
public bool Update<T>(T data)  
{  
    try  
    {  
        Type type = data.GetType();  
        string table = AttributeProcess.GetTableName(type);  
        string sql = "Update " + table + " Set ";  
        List<string> sets = new List<string>();  
        string where = " Where ";  
        PropertyInfo[] properties = type.GetProperties();  
        foreach (PropertyInfo property in properties)  
        {  
            string column = AttributeProcess.GetColumnName(property);  
            if (!AttributeProcess.IsPrimary(type, property))  
            {  
                if (property.PropertyType == typeof(bool))  
                {  
                    bool value = bool.Parse(property.GetValue(data, null).ToString());  
                    sets.Add(column + "=" + (value ? "1" : "0"));  
                }  
                else if (property.PropertyType.IsPrimitive)  
                {  
                    sets.Add(column + "=" + property.GetValue(data, null));  
                }  
                else if (property.PropertyType.IsEnum)  
                {  
                    int intValue = (int)property.GetValue(data, null);  
                    sets.Add(column + "=" + intValue);  
                }  
                else  
                {  
                    if (Sql.InjectionDefend(property.GetValue(data, null).ToString()))  
                    {  
                        sets.Add(column + "=\'" + property.GetValue(data, null) + "\'");  
                    }  
                }  
            }  
            else  
            {  
                if (property.PropertyType.IsPrimitive)  
                {  
                    where += column + "=" + property.GetValue(data, null);  
                }  
                else  
                {  
                    where += column + "=\'" + property.GetValue(data, null) + "\'";  
                }  
            }  
        }  
        sql += (string.Join(",", sets) + where);  
        ExecuteQuery(sql);  
        return true;  
    }  
    catch (Exception e)  
    {  
        throw e;  
    }  
    finally  
    {  
        CloseSqlConnection();  
    }  
}  
  
//插入新数据  
public bool Insert<T>(T data)  
{  
    try  
    {  
        Type type = data.GetType();  
        string table = AttributeProcess.GetTableName(type);  
        List<string> columns = new List<string>();  
        List<string> values = new List<string>();  
        PropertyInfo[] properties = type.GetProperties();  
        foreach (PropertyInfo property in properties)  
        {  
            if (!(AttributeProcess.IsPrimary(type, property) && AttributeProcess.IsIncrement(type)))  
            {  
                if (property.GetValue(data, null) != null)  
                {  
                    columns.Add(AttributeProcess.GetColumnName(property));  
                    if (property.PropertyType == typeof(bool))  
                    {  
                        bool value = bool.Parse(property.GetValue(data, null).ToString());  
                        values.Add((value ? "1" : "0"));  
                    }  
                    else if (property.PropertyType.IsPrimitive)  
                    {  
                        values.Add(property.GetValue(data, null).ToString());  
                    }  
                    else if (property.PropertyType.IsEnum)  
                    {  
                        int intValue = (int)property.GetValue(data, null);  
                        values.Add(intValue.ToString());  
                    }  
                    else  
                    {  
                        if (Sql.InjectionDefend(property.GetValue(data, null).ToString()))  
                        {  
                            values.Add("\'" + property.GetValue(data, null) + "\'");  
                        }  
                    }  
                }  
            }  
        }  
        string sql = "INSERT INTO " + table + "(" + string.Join(",", columns) + ")" + "VALUES" + "(" + string.Join(",", values) + ")";  
        ExecuteQuery(sql);  
        return true;  
    }  
    catch (Exception e)  
    {  
        throw e;  
    }  
    finally  
    {  
        CloseSqlConnection();  
    }  
}  
  
//删除数据  
public bool Delete<T>(object id)  
{  
    try  
    {  
        Type type = typeof(T);  
        string table = AttributeProcess.GetTableName(type);  
        string sql = "DELETE FROM " + table + " WHERE ";  
        PropertyInfo[] properties = type.GetProperties();  
        foreach (PropertyInfo property in properties)  
        {  
            if (AttributeProcess.IsPrimary(type, property))  
            {  
                sql += (AttributeProcess.GetColumnName(property) + "=");  
                if (property.PropertyType.IsPrimitive)  
                {  
                    sql += (id.ToString() + ";");  
                }  
                else  
                {  
                    sql += ("\'" + id.ToString() + "\';");  
                }  
            }  
        }  
        ExecuteQuery(sql);  
        return true;  
    }  
    catch (Exception e)  
    {  
        throw e;  
    }  
    finally  
    {  
        CloseSqlConnection();  
    }  
}  

上面我实现了两个update，因为如果只实现一个对一行数据的所有列的update的话，那么在实现一些如更是状态等只更新某几列数据的功能时数据更新会变慢。此外还有两点需要注意，第一、每个方法最后都要关闭数据库访问连接；第二update和insert中设置数据值的地方如果数据为string类型需要进行防sql注入的操作。

下面是完整代码：

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;  
  
namespace ORM  
{  
    public class DbAccess  
    {  
        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;  
        }  
  
        //查询符合条件的第一个记录  
        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();  
            }  
        }  
  
        /// <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();  
                string table = AttributeProcess.GetTableName(type);  
                string sql = "Update " + table + " Set ";  
                string where = " Where ";  
                List<string> sets = new List<string>();  
                PropertyInfo[] properties = type.GetProperties();  
                foreach (PropertyInfo property in properties)  
                {  
                    string column = AttributeProcess.GetColumnName(property);  
                    if (!AttributeProcess.IsPrimary(type, property))  
                    {  
                        if (columns.Any(a => a == column))  
                        {  
                            if (property.PropertyType == typeof(bool))  
                            {  
                                bool value = bool.Parse(property.GetValue(data, null).ToString());  
                                sets.Add(column + "=" + (value ? "1" : "0"));  
                            }  
                            else if (property.PropertyType.IsPrimitive)  
                            {  
                                sets.Add(column + "=" + property.GetValue(data, null));  
                            }  
                            else if (property.PropertyType.IsEnum)  
                            {  
                                int intValue = (int)property.GetValue(data, null);  
                                sets.Add(column + "=" + intValue);  
                            }  
                            else  
                            {  
                                if (Sql.InjectionDefend(property.GetValue(data, null).ToString()))  
                                {  
                                    sets.Add(column + "=\'" + property.GetValue(data, null) + "\'");  
                                }  
                            }  
                        }  
                    }  
                    else  
                    {  
                        if (property.PropertyType.IsPrimitive)  
                        {  
                            where += column + "=" + property.GetValue(data, null);  
                        }  
                        else  
                        {  
                            where += column + "=\'" + property.GetValue(data, null) + "\'";  
                        }  
                    }  
                }  
                sql += (string.Join(",", sets) + where);  
                ExecuteQuery(sql);  
                return true;  
            }  
            catch (Exception e)  
            {  
                throw e;  
            }  
            finally  
            {  
                CloseSqlConnection();  
            }  
        }  
  
        //更新指定的记录  
        public bool Update<T>(T data)  
        {  
            try  
            {  
                Type type = data.GetType();  
                string table = AttributeProcess.GetTableName(type);  
                string sql = "Update " + table + " Set ";  
                List<string> sets = new List<string>();  
                string where = " Where ";  
                PropertyInfo[] properties = type.GetProperties();  
                foreach (PropertyInfo property in properties)  
                {  
                    string column = AttributeProcess.GetColumnName(property);  
                    if (!AttributeProcess.IsPrimary(type, property))  
                    {  
                        if (property.PropertyType == typeof(bool))  
                        {  
                            bool value = bool.Parse(property.GetValue(data, null).ToString());  
                            sets.Add(column + "=" + (value ? "1" : "0"));  
                        }  
                        else if (property.PropertyType.IsPrimitive)  
                        {  
                            sets.Add(column + "=" + property.GetValue(data, null));  
                        }  
                        else if (property.PropertyType.IsEnum)  
                        {  
                            int intValue = (int)property.GetValue(data, null);  
                            sets.Add(column + "=" + intValue);  
                        }  
                        else  
                        {  
                            if (Sql.InjectionDefend(property.GetValue(data, null).ToString()))  
                            {  
                                sets.Add(column + "=\'" + property.GetValue(data, null) + "\'");  
                            }  
                        }  
                    }  
                    else  
                    {  
                        if (property.PropertyType.IsPrimitive)  
                        {  
                            where += column + "=" + property.GetValue(data, null);  
                        }  
                        else  
                        {  
                            where += column + "=\'" + property.GetValue(data, null) + "\'";  
                        }  
                    }  
                }  
                sql += (string.Join(",", sets) + where);  
                ExecuteQuery(sql);  
                return true;  
            }  
            catch (Exception e)  
            {  
                throw e;  
            }  
            finally  
            {  
                CloseSqlConnection();  
            }  
        }  
  
        //插入新数据  
        public bool Insert<T>(T data)  
        {  
            try  
            {  
                Type type = data.GetType();  
                string table = AttributeProcess.GetTableName(type);  
                List<string> columns = new List<string>();  
                List<string> values = new List<string>();  
                PropertyInfo[] properties = type.GetProperties();  
                foreach (PropertyInfo property in properties)  
                {  
                    if (!(AttributeProcess.IsPrimary(type, property) && AttributeProcess.IsIncrement(type)))  
                    {  
                        if (property.GetValue(data, null) != null)  
                        {  
                            columns.Add(AttributeProcess.GetColumnName(property));  
                            if (property.PropertyType == typeof(bool))  
                            {  
                                bool value = bool.Parse(property.GetValue(data, null).ToString());  
                                values.Add((value ? "1" : "0"));  
                            }  
                            else if (property.PropertyType.IsPrimitive)  
                            {  
                                values.Add(property.GetValue(data, null).ToString());  
                            }  
                            else if (property.PropertyType.IsEnum)  
                            {  
                                int intValue = (int)property.GetValue(data, null);  
                                values.Add(intValue.ToString());  
                            }  
                            else  
                            {  
                                if (Sql.InjectionDefend(property.GetValue(data, null).ToString()))  
                                {  
                                    values.Add("\'" + property.GetValue(data, null) + "\'");  
                                }  
                            }  
                        }  
                    }  
                }  
                string sql = "INSERT INTO " + table + "(" + string.Join(",", columns) + ")" + "VALUES" + "(" + string.Join(",", values) + ")";  
                ExecuteQuery(sql);  
                return true;  
            }  
            catch (Exception e)  
            {  
                throw e;  
            }  
            finally  
            {  
                CloseSqlConnection();  
            }  
        }  
  
        //删除数据  
        public bool Delete<T>(object id)  
        {  
            try  
            {  
                Type type = typeof(T);  
                string table = AttributeProcess.GetTableName(type);  
                string sql = "DELETE FROM " + table + " WHERE ";  
                PropertyInfo[] properties = type.GetProperties();  
                foreach (PropertyInfo property in properties)  
                {  
                    if (AttributeProcess.IsPrimary(type, property))  
                    {  
                        sql += (AttributeProcess.GetColumnName(property) + "=");  
                        if (property.PropertyType.IsPrimitive)  
                        {  
                            sql += (id.ToString() + ";");  
                        }  
                        else  
                        {  
                            sql += ("\'" + id.ToString() + "\';");  
                        }  
                    }  
                }  
                ExecuteQuery(sql);  
                return true;  
            }  
            catch (Exception e)  
            {  
                throw e;  
            }  
            finally  
            {  
                CloseSqlConnection();  
            }  
        }  
    }  
}  

在之前建立的userinfo表中插入一条数据用于测试，如下图：

 

然后在main函数中添加如下代码进行测试：

static void Main(string[] args)  
{  
    DbAccess dao = new DbAccess();  
    Sql sql = new Sql();  
    sql.Select("*").From("userinfo");  
    sql.Where("Id=@0", 1);  
    User user = dao.FirstOrDefault<User>(sql);  
    Console.WriteLine(user.UserName);  
    user.UserName = "tczhoulan";  
    Console.WriteLine(dao.Update<User>(user, new string[] { "UserName" }));  
}  

执行结果如下图：

 

再看数据库中的数据，UserName中的值已经被修改了，如下图所示：

到这里一个简单的ORM映射框架就基本完成了，当然这只是一个最简单的ORM框架，其中还有许多不完善的地方，如有需要可以自己在上面进行扩充，我也会慢慢的进行完善。