利用反射将MySQL数据表数据转化为对应的类

首先建立两个类

MySqlOpr负责封装数据库增删改查等基本操作

MySqlMgr负责数据库管理

1.数据库连接和查找操作

这两个操作写在MySqlOpr中

    /// <summary>
    /// 获取MySql数据库连接对象。
    /// </summary>
    /// <param name="dbFile">数据库文件路径和文件名</param>
    /// <param name="openImmediately">是否立即调用Open方法打开数据库，默认不调用</param>
    /// <returns></returns>
    public static MySqlConnection GetMySqlConnection(string datasource, string port, string database, string user, string pwd, bool openImmediately = false)
    {
        string uri = string.Format("datasource={0};port={1};database={2};user={3};pwd={4};Allow User Variables = True;", datasource, port, database, user, pwd);
        MySqlConnection dbConn;
        try
        {
            dbConn = new MySqlConnection(uri);
        }
        catch (Exception e)
        {
            Debug.LogError(e.Message);
            return null;
        }

        if (openImmediately)
        {
            try
            {
                dbConn.Open();
            }
            catch (Exception e)
            {
                LogError("数据库连接失败" + e.Message);
            }
           
        }

        return dbConn;
    }

    /// <summary>
    /// 从数据表中查找数据。
    /// </summary>
    /// <param name="dbConn">MySql数据库连接对象</param>
    /// <param name="tableName">数据表名</param>
    /// <param name="columnNames">字段名，多个字段名之间使用半角逗号分隔</param>
    /// <param name="wheres">查找条件，多个条件之间使用 AND 分隔</param>
    /// <param name="limit">查找数量</param>
    /// <param name="orderBy">排序规则</param>
    /// <returns></returns>
    public static MySqlDataReader Select(MySqlConnection dbConn, string tableName, string columnNames, string where = null, string orderBy = null, int limit = -1)
    {
        //if (!CheckTableExists(dbConn, tableName))
        //{
        //    Debug.LogError(string.Format("数据表 {0} 不存在，无法查找数据。", tableName));
        //    return null;
        //}
        StringBuilder sql = new StringBuilder();
        sql.Append(string.Format("SELECT {0} FROM {1}", columnNames, tableName));
        if (!string.IsNullOrEmpty(where)) sql.Append(" WHERE " + where);
        if (!string.IsNullOrEmpty(orderBy)) sql.Append(" ORDER BY " + orderBy);
        if (limit != -1) sql.Append(" LIMIT " + limit);
        sql.Append(';');

        MySqlDataReader reader;

        MySqlCommand cmd = dbConn.CreateCommand();
        cmd.CommandText = sql.ToString();

        try
        {
             reader = cmd.ExecuteReader();
             cmd.Dispose();
        }
        catch (Exception e)
        {
            Debug.LogError("读取数据库报错");
            Debug.LogError(e.Message);
            return null;
        }
        return reader;
    }

 2.从数据表到类

这个方法放入MySqlMgr中，我这里返回的是一个字典，可以根据自身需要做改动，数据表所对应的类中的字段名要和数据表中的名字保持一致，这样才可以通过反射获取到字段名来查找数据

    /// <summary>
    /// 将一个数据表转化成对应的类并存储在字典里
    /// </summary>
    /// <typeparam name="K">字典Key的类型</typeparam>
    /// <typeparam name="T">数据表对应的类</typeparam>
    /// <param name="tableName">数据表名</param>
    /// <param name="dicId">要定义为字典K的字段名</param>
    /// <returns></returns>
    public Dictionary<K, T> TableToEntity<K, T>(string tableName, string dicId, string columnNames, string condition = null) where T : class, new()
    {
        Dictionary<K, T> returnDatas = new Dictionary<K, T>();

        // 数据库连接的参数我保存在类中了，这里根据实际情况做一些改动就行了
        MySqlConnection conn = MySqlOpr.GetMySqlConnection(datasource, port, database, user, pwd, true);  // 连接数据库

        if (conn == null)
        {
            Debug.LogError("据库空链接");
            return returnDatas;
        }

        MySqlDataReader reader = MySqlOpr.Select(conn, tableName, columnNames, condition);                                   // 查找表

        if (reader == null)
        {
            Debug.LogError("数据库读取失败");
            return returnDatas;
        }

        Type type = typeof(T);                // 获取T的类型信息
        K dicKey = default(K);                // 字典存储Id
        int id = 0;
        string numName = string.Empty;
        try
        {
            while (reader.Read())
            {
                id = 0;
                FieldInfo[] pArray = type.GetFields();// 得到T的成员变量信息
                T entity = new T();
                foreach (FieldInfo p in pArray)
                {
                    id++;
                    string fieldInfo = p.FieldType.ToString();
                    numName = p.Name;
                    // 通过成员变量的信息选择读写的类型
                    switch (fieldInfo)
                    {
                        case "System.Int32":
                            p.SetValue(entity, reader.GetInt32(p.Name));
                            break;
                        case "System.Single":
                            p.SetValue(entity, reader.GetFloat(p.Name));
                            break;
                        case "System.Double":
                            p.SetValue(entity, reader.GetDouble(p.Name));
                            break;
                        case "System.Boolean":
                            p.SetValue(entity, reader.GetBoolean(p.Name));
                            break;
                        case "System.String":
                            p.SetValue(entity, reader.GetString(p.Name));
                            break;
                        case "UnityEngine.Vector2[]":
                            string typeName = reader.GetString(p.Name);
                            if (!String.IsNullOrEmpty(typeName))// 安全校验
                            {
                                p.SetValue(entity, GetVector2s(typeName));
                            }
                            break;
                        case "UnityEngine.Vector3[]":
                            break;
                        case "UnityEngine.Color":
                            string colorName = reader.GetString(p.Name);
                            if (!string.IsNullOrEmpty(colorName))
                            {
                                p.SetValue(entity, GetColor(colorName));
                            }
                            break;
                        default:
                            break;
                    }
                    if (p.Name == dicId)
                    {
                        dicKey = (K)p.GetValue(entity);
                    }
                }
                returnDatas.Add(dicKey, entity);// 加入字典
            }
        }
        catch (Exception e)
        {
            Debug.Log("[表转类读写出错] " + e.Message);
            Debug.Log("第" + id + "个数据有错");
            Debug.Log(numName);
        }
        reader.Close();
        conn.Close();// 关闭数据库连接
        conn.Dispose();

        return returnDatas;
    }

Unity中有很多 MySqlDataReader无法直接转化的类型，常用的有Vector2，Vector3，Color，他们在数据库中以字符串的形式保存，格式可以自定义，这里提供一种格式的转化方法

    /// <summary>
    /// 通过分号和逗号将字符串转化成二维向量数组
    /// </summary>
    /// <param name="BOUNDARY_POINT"></param>
    /// <returns></returns>
    public Vector2[] GetVector2s(string BOUNDARY_POINT)
    {
        BOUNDARY_POINT = BOUNDARY_POINT.Replace("\n", string.Empty).Replace("\r", string.Empty).Replace(" ", string.Empty);
        string[] points = BOUNDARY_POINT.Split(';');
        List<Vector2> returnList = new List<Vector2>();
        for (int i = 0; i < points.Length; i++)
        {
            if (!String.IsNullOrEmpty(points[i]))//安全校验
            {
                string[] point = points[i].Split(',');
                returnList.Add(new Vector2(float.Parse(point[0]), float.Parse(point[1])));
            }
        }
        return returnList.ToArray();
    }

    /// <summary>
    /// 将字符信息转为颜色类
    /// </summary>
    /// <param name="ColorInfo"></param>
    /// <returns></returns>
    public Color GetColor(string ColorInfo)
    {
        ColorInfo = ColorInfo.Replace("\n", string.Empty).Replace("\r", string.Empty).Replace(" ", string.Empty);
        string[] paras = ColorInfo.Split(',');
        return new Color32(Byte.Parse(paras[0]), Byte.Parse(paras[1]), Byte.Parse(paras[2]), 120);
    }