unity 实现Mysql、Oracle数据库(二)

主要是利用上篇文章(https://blog.csdn.net/qq_40120946/article/details/100558714)进行扩展。

其他不用变,只需要修改管理类。

Mqsql的管理类

/*******************************************************************
* 作者:     # maki  #
* 创建日期: # 2019年9月5日17:18:52 #
* 描述:  MySql 数据库管理类
* 
* 处理Mysql工具:
* 
* MySQL下载、安装以及环境配置 链接:https://blog.csdn.net/watestill/article/details/81532780
*                                                   https://blog.csdn.net/Imliao/article/details/88850043
* 
* 彻底删除mysql服务(清理注册表)链接:https://www.cnblogs.com/fanbi/p/6432838.html
* 
* Unity连接MySql  链接:https://blog.csdn.net/qq_42462109/article/details/83385658
* 
* MySQL使用注意事项:
* 当SqlDataReader没有关闭之前,数据库连接会一直保持open状态,所以在使用SqlDataReader时,使用完毕应该马上调用SqlDataReader.Close()关闭它
* 链接:http://blog.knowsky.com/258608.htm
* 
******************************************************************/
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using UnityEngine;

namespace DataBaseTool
{
    public class MySqlManager : IDataBase
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private MySqlConnection dbConnection = null;

        /// <summary>
        /// SQL命令
        /// </summary>
        private MySqlCommand dbCommand = null;

        /// <summary>
        /// 数据读取
        /// </summary>
        private MySqlDataReader dataReader = null;

        private MySqlParameter myParameter;

        /// <summary>
        /// 事务
        /// </summary>
        private MySqlTransaction transaction;
        /// <summary>
        /// 是否开始事务
        /// </summary>
        private bool isStartUseTransaction;

        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open(string sqlUrl)
        {
            try
            {
                //构造数据库连接
                dbConnection = new MySqlConnection(sqlUrl);
                //打开数据库
                dbConnection.Open(); 
            }
            catch (Exception e)
            {
                Debug.Log(e.Message);
            }
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            CloseDealData();
            //销毁Connection
            if (dbConnection != null) dbConnection.Close();

            dbConnection = null;
            Debug.Log("关闭数据库");
        }
        /// <summary>
        /// 销毁处理数据缓存
        /// </summary>
        private void CloseDealData()
        {
            //销毁Command
            if (dbCommand != null) dbCommand.Cancel();
            //销毁Reader
            if (dataReader != null) dataReader.Close();
            dbCommand = null;
            dataReader = null;
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="queryString">SQL命令字符串</param>
        /// <returns></returns>
        public MySqlDataReader ExecuteQuery(string queryString)
        {
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryString;
            dataReader = dbCommand.ExecuteReader();
            return dataReader;
        }
        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="queryString">SQL命令字符串</param>
        /// <param name="content">SQL命令字符串</param>
        /// <returns></returns>
        public void ExecuteQuery(string queryString, MySqlParameter[] content)
        {
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryString;
            dbCommand.Parameters.AddRange(content);

            /**ExecuteNonQuery方法主要用来更新数据,
             * 当然也可以用来执行目标操作
             * (例如查询数据库的结构或者创建诸如表等的数据库对象)。
             * 通常用它来执行insert、update、delete语句,
             * 在不使用Dataset的情况下更改数据库中的数据。
             * select语句不适合ExecuteNonQuery()方法。
             * 文档地址:https://www.cnblogs.com/erhanhan/p/8270052.html
             * **/
            dbCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 读取整张数据表
        /// </summary>
        /// <returns>The full table.</returns>
        /// <param name="tableName">数据表名称</param>
        public MySqlDataReader ReadFullTable(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            return ExecuteQuery(queryString);
        }
        /****************************   事务操作   ************************************/

        /// <summary>
        /// 开始使用事务,在停止使用事务前,通过ReplaceValues保存数据的SQL命令会集中起来,等待停止事务后一起执行命令
        /// </summary>
        public void StartTransaction()
        {
            isStartUseTransaction = true;
            dbCommand = dbConnection.CreateCommand();
            transaction = dbConnection.BeginTransaction();
        }
        /// <summary>
        /// 停止使用事务
        /// </summary>
        public void StopTransaction()
        {
            isStartUseTransaction = false;
            transaction.Commit();
        }

        /****************************   对表操作   ************************************/

        /// <summary>
        /// 检查数据表是否存在
        /// </summary>
        public bool CheckTableIsExist(string tableName)
        {
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = "SELECT table_name FROM information_schema.TABLES WHERE table_name = '"+ tableName + "'";// "SELECT COUNT(*) FROM information_schema.tables where type='table' and name='" + tableName + "';";
            if (dbCommand.ExecuteScalar()==null)//Convert.ToInt32(dbCommand.ExecuteScalar())   TODO:ExecuteScalar 这个方法在 mysql和 sqllite 中是有区别的,sqllite返回是数字,mysql范围是类型
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 创建数据表
        /// </summary>
        public void CreatTable(string tableName, string[] colNames, string[] colTypes)
        {
            string queryString = "CREATE TABLE " + tableName + "( " + colNames[0] + " " + colTypes[0];
            for (int i = 1; i < colNames.Length; i++)
            {
                queryString += ", " + colNames[i] + " " + colTypes[i];
            }
            queryString += "  ) ";
            ExecuteQuery(queryString);
            CloseDealData();
        }

        /****************************   更新或者保存数据    ************************************/

        /// <summary>
        /// 更新或者保存数据
        /// </summary>
        public bool UpdateOrSave(string tableName, string[] colNames, object[] colValues)
        {
            try
            {
                string queryString = "REPLACE INTO " + tableName + "(" + colNames[0];
                for (int i = 1; i < colNames.Length; i++)
                {
                    queryString += "," + colNames[i];
                }

                queryString += ") VALUES (" + "@" + colNames[0];
                for (int j = 1; j < colNames.Length; j++)
                {
                    queryString += "," + "@" + colNames[j];
                }
                queryString += ")";

                MySqlParameter[] parameters = new MySqlParameter[colNames.Length];
                for (int k = 0; k < parameters.Length; k++)
                {
                    parameters[k] = new MySqlParameter("@" + colNames[k], colValues[k]);
                }

                if (isStartUseTransaction)
                {
                    // 累积SQL执行语句
                    dbCommand.CommandText = queryString;
                    dbCommand.Parameters.AddRange(parameters);
                    dbCommand.ExecuteNonQuery();
                }
                else
                {
                    ExecuteQuery(queryString, parameters);
                }
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }
        /// <summary>
        /// 更新或者保存数据
        /// </summary>
        public bool UpdateOrSave(string tableName, List<string[]> listColNames, List<object[]> listColValues)
        {
            try
            {
                System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
                watch.Start();
                string[] colNames = listColNames[0];

                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("REPLACE INTO {0}({1}", tableName, colNames[0]);
                for (int i = 1; i < colNames.Length; i++)
                {
                    sb.Append(",").Append(colNames[i]);
                }

                sb.Append(") VALUES (@").Append(colNames[0]);
                for (int j = 1; j < colNames.Length; j++)
                {
                    sb.Append(",@").Append(colNames[j]); ;
                }
                sb.Append(")");

                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sb.ToString();

                var transaction = dbConnection.BeginTransaction();
                MySqlParameter[] parameters;
                for (int i1 = 0; i1 < listColNames.Count; i1++)
                {
                    parameters = new MySqlParameter[colNames.Length];
                    for (int k = 0; k < parameters.Length; k++)
                    {
                        parameters[k] = new MySqlParameter("@" + colNames[k], listColValues[i1][k]);
                    }

                    dbCommand.Parameters.AddRange(parameters);
                    dbCommand.ExecuteNonQuery();
                }
                transaction.Commit();

                watch.Stop();
                Debug.Log(watch.Elapsed);
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }

        /****************************  删除符合条件的数据    ************************************/

        /// <summary>
        /// 删除符合任一条件的数据
        /// </summary>
        public bool DeleteAnyone(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                //当字段名称和字段数值不对应时引发异常
                if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
                {
                    throw new ExternalException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
                }

                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("DELETE FROM {0} WHERE {1}{2}'{3}'", tableName, colNames[0], operations[0], colValues[0]);
                for (int i = 1; i < colValues.Length; i++)
                {
                    sb.AppendFormat("OR {0}{1}'{2}'", colNames[i], operations[i], colValues[i]);
                }

                ExecuteQuery(sb.ToString());
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }
        /// <summary>
        /// 删除符合所有条件的数据
        /// </summary>
        public bool DeleteEveryone(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                //当字段名称和字段数值不对应时引发异常
                if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
                {
                    throw new ExternalException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
                }

                string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 1; i < colValues.Length; i++)
                {
                    queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }
                ExecuteQuery(queryString);
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }

        /****************************  获取该表数据    ************************************/

        /// <summary>
        /// 获取该表所有数据
        /// </summary>
        public List<T> GetAll<T>(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            return GetValues<T>(queryString);
        }
        /// <summary>
        /// 获取符合任一条件的数据
        /// </summary>
        public List<T> GetAnyone<T>(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT * FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 1; i < colNames.Length; i++)
                {
                    queryString += " OR " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }
                return GetValues<T>(queryString);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<T>();
            }
        }
        /// <summary>
        /// 获取符合全部条件的数据
        /// </summary
        public List<T> GetEveryone<T>(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT * FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 1; i < colNames.Length; i++)
                {
                    queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }
                return GetValues<T>(queryString);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<T>();
            }
        }
        /// <summary>
        /// 获取符合任一条件的某些字段的数据
        /// </summary>
        public List<List<object>> GetForAnyone(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT " + items[0];
                for (int i = 1; i < items.Length; i++)
                {
                    queryString += ", " + items[i];
                }
                queryString += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 0; i < colNames.Length; i++)
                {
                    queryString += " OR " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }

                return GetValuesForItems(queryString, items);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<List<object>>();
            }
        }
        /// <summary>
        /// 获取符合全部条件的某些字段的数据
        /// </summary>
        public List<List<object>> GetForEveryone(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT " + items[0];
                for (int i = 1; i < items.Length; i++)
                {
                    queryString += ", " + items[i];
                }
                queryString += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 0; i < colNames.Length; i++)
                {
                    queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + ",";
                }

                return GetValuesForItems(queryString, items);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<List<object>>();
            }
        }

        /****************************  获取表,名 相关数据    ************************************/

        /// <summary>
        /// 获取某数据表的所有字段名
        /// </summary>
        public List<string> GetTableAllFields(string tableName)
        {
            string queryString =" select COLUMN_NAME from information_schema.COLUMNS " +
                "where" +
                " table_name = '"+ tableName +
                "' and" +
                " table_schema = '"+ dbConnection.Database + "'";  // TODO: mysql、需要修改 ;sqllite "Pragma Table_Info(" + tableName + ")";  链接:https://www.cnblogs.com/fuqia/p/8994080.html

            List<string> listFields = new List<string>();
            try
            {
                ExecuteQuery(queryString);

                while (dataReader.Read())
                {
                    string field_name = dataReader.GetString(0);
                    listFields.Add(field_name);
                }
                CloseDealData();
                return listFields;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return listFields;
            }
        }
        /// <summary>
        /// 获取所有数据表名
        /// </summary>
        public List<string> GetAllTableName()
        {
            string  db_name=dbConnection.Database;
            string queryString ="select table_name from information_schema.tables where table_schema = '"+db_name+"'";

            List<string> list = new List<string>();
            try
            {
                ExecuteQuery(queryString);
                while (dataReader.Read())
                {
                    string table_name = dataReader.GetString(0);  // 解决方式链接:https://blog.csdn.net/u012835905/article/details/41212615
                    list.Add(table_name);
                }
                CloseDealData();
                return list;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return list;
            }
        }
        /// <summary>
        /// 获取表的相关信息
        /// </summary>
        public DataTable GetTableInfo(string tableName)
        {
            DataTable dt = new DataTable();
            try
            {
                ReadFullTable(tableName);
                dt = dataReader.GetSchemaTable();
                CloseDealData();
                return dt;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return dt;
            }
        }
        /****************************  其他处理数据    ************************************/

        /// <summary>
        /// 通过SQL语句获取数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryString">获取数据方式</param>
        /// <returns></returns>
        private List<T> GetValues<T>(string queryString)
        {
            try
            {
                List<string> fields = GetTableAllFields(typeof(T).Name);
                ExecuteQuery(queryString);

                PropertyInfo[] pros = typeof(T).GetProperties();
                List<T> listT = new List<T>();
                while (dataReader.Read())
                {
                    T t = Activator.CreateInstance<T>();
                    foreach (PropertyInfo item in pros)
                    {
                        //如果数据表不包含该字段,不读取
                        if (!fields.Contains(item.Name))
                            continue;
                        var result = dataReader.GetValue(dataReader.GetOrdinal(item.Name)); 
                        if (!string.IsNullOrEmpty(result.ToString()))
                        {
                            item.SetValue(t, Convert.ChangeType(result, item.PropertyType), null);
                        }
                    }
                    listT.Add(t);
                }
                CloseDealData();
                return listT;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<T>();
            }
        }
        /// <summary>
        /// 获取指定的某些字段数据
        /// </summary>
        /// <param name="queryString">SQL语句</param>
        /// <param name="items">要获取的字段</param>
        /// <returns></returns>
        private List<List<object>> GetValuesForItems(string queryString, string[] items)
        {
            try
            {
                ExecuteQuery(queryString);

                List<List<object>> list = new List<List<object>>();
                for (int i = 0; i < items.Length; i++)
                {
                    List<object> listTemp = new List<object>();
                    list.Add(listTemp);
                }

                while (dataReader.Read())
                {
                    for (int i = 0; i < items.Length; i++)
                    {
                        var result = dataReader.GetValue(dataReader.GetOrdinal(items[i]));
                        list[i].Add(result);
                    }
                }
                CloseDealData();
                return list;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<List<object>>();
            }
        }
    }
}

Oracle管理类,

/*******************************************************************
* 作者:     # maki  #
* 创建日期: # 2019年9月5日17:18:52 #
* 描述:  Oracle 数据库管理类
******************************************************************/
using System.Collections.Generic;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using UnityEngine;
using System;
using System.Text;
using System.Reflection;
using System.Runtime.InteropServices;

namespace DataBaseTool
{
    public class OracleManager : IDataBase
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private OracleConnection dbConnection = null;

        /// <summary>
        /// SQL命令
        /// </summary>
        private OracleCommand dbCommand = null;

        /// <summary>
        /// 数据读取
        /// </summary>
        private OracleDataReader dataReader = null;

        private OracleParameter myParameter;

        /// <summary>
        /// 事务
        /// </summary>
        private OracleTransaction transaction;
        /// <summary>
        /// 是否开始事务
        /// </summary>
        private bool isStartUseTransaction;

        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open(string sqlUrl)
        {
            try
            {
                //构造数据库连接
                dbConnection = new OracleConnection(sqlUrl);
                //打开数据库
                dbConnection.Open();
            }
            catch (Exception e)
            {
                Debug.Log(e.Message);
            }
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            CloseDealData();
            //销毁Connection
            if (dbConnection != null) dbConnection.Close();

            dbConnection = null;
            Debug.Log("关闭数据库");
        }
        /// <summary>
        /// 销毁处理数据缓存
        /// </summary>
        private void CloseDealData()
        {
            //销毁Command
            if (dbCommand != null) dbCommand.Cancel();
            //销毁Reader
            if (dataReader != null) dataReader.Close();
            dbCommand = null;
            dataReader = null;
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="queryString">SQL命令字符串</param>
        /// <returns></returns>
        public OracleDataReader ExecuteQuery(string queryString)
        {
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryString;
            dataReader = dbCommand.ExecuteReader();
            return dataReader;
        }
        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="queryString">SQL命令字符串</param>
        /// <param name="content">SQL命令字符串</param>
        /// <returns></returns>
        public void ExecuteQuery(string queryString, OracleParameter[] content)
        {
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryString;
            dbCommand.Parameters.AddRange(content);

            /**ExecuteNonQuery方法主要用来更新数据,
             * 当然也可以用来执行目标操作
             * (例如查询数据库的结构或者创建诸如表等的数据库对象)。
             * 通常用它来执行insert、update、delete语句,
             * 在不使用Dataset的情况下更改数据库中的数据。
             * select语句不适合ExecuteNonQuery()方法。
             * 文档地址:https://www.cnblogs.com/erhanhan/p/8270052.html
             * **/
            dbCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 读取整张数据表
        /// </summary>
        /// <returns>The full table.</returns>
        /// <param name="tableName">数据表名称</param>
        public OracleDataReader ReadFullTable(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            return ExecuteQuery(queryString);
        }
        /****************************   事务操作   ************************************/

        /// <summary>
        /// 开始使用事务,在停止使用事务前,通过ReplaceValues保存数据的SQL命令会集中起来,等待停止事务后一起执行命令
        /// </summary>
        public void StartTransaction()
        {
            isStartUseTransaction = true;
            dbCommand = dbConnection.CreateCommand();
            transaction = dbConnection.BeginTransaction();
        }
        /// <summary>
        /// 停止使用事务
        /// </summary>
        public void StopTransaction()
        {
            isStartUseTransaction = false;
            transaction.Commit();
        }

        /****************************   对表操作   ************************************/

        /// <summary>
        /// 检查数据表是否存在
        /// </summary>
        public bool CheckTableIsExist(string tableName)
        {
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = "SELECT table_name FROM information_schema.TABLES WHERE table_name = '" + tableName + "'";// "SELECT COUNT(*) FROM information_schema.tables where type='table' and name='" + tableName + "';";
            if (dbCommand.ExecuteScalar() == null)//Convert.ToInt32(dbCommand.ExecuteScalar())   TODO:ExecuteScalar 这个方法在 mysql和 sqllite 中是有区别的,sqllite返回是数字,mysql范围是类型
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 创建数据表
        /// </summary>
        public void CreatTable(string tableName, string[] colNames, string[] colTypes)
        {
            string queryString = "CREATE TABLE " + tableName + "( " + colNames[0] + " " + colTypes[0];
            for (int i = 1; i < colNames.Length; i++)
            {
                queryString += ", " + colNames[i] + " " + colTypes[i];
            }
            queryString += "  ) ";
            ExecuteQuery(queryString);
            CloseDealData();
        }

        /****************************   更新或者保存数据    ************************************/

        /// <summary>
        /// 更新或者保存数据
        /// </summary>
        public bool UpdateOrSave(string tableName, string[] colNames, object[] colValues)
        {
            try
            {
                string queryString = "REPLACE INTO " + tableName + "(" + colNames[0];
                for (int i = 1; i < colNames.Length; i++)
                {
                    queryString += "," + colNames[i];
                }

                queryString += ") VALUES (" + "@" + colNames[0];
                for (int j = 1; j < colNames.Length; j++)
                {
                    queryString += "," + "@" + colNames[j];
                }
                queryString += ")";

                OracleParameter[] parameters = new OracleParameter[colNames.Length];
                for (int k = 0; k < parameters.Length; k++)
                {
                    parameters[k] = new OracleParameter("@" + colNames[k], colValues[k]);
                }

                if (isStartUseTransaction)
                {
                    // 累积SQL执行语句
                    dbCommand.CommandText = queryString;
                    dbCommand.Parameters.AddRange(parameters);
                    dbCommand.ExecuteNonQuery();
                }
                else
                {
                    ExecuteQuery(queryString, parameters);
                }
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }
        /// <summary>
        /// 更新或者保存数据
        /// </summary>
        public bool UpdateOrSave(string tableName, List<string[]> listColNames, List<object[]> listColValues)
        {
            try
            {
                System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
                watch.Start();
                string[] colNames = listColNames[0];

                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("REPLACE INTO {0}({1}", tableName, colNames[0]);
                for (int i = 1; i < colNames.Length; i++)
                {
                    sb.Append(",").Append(colNames[i]);
                }

                sb.Append(") VALUES (@").Append(colNames[0]);
                for (int j = 1; j < colNames.Length; j++)
                {
                    sb.Append(",@").Append(colNames[j]); ;
                }
                sb.Append(")");

                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sb.ToString();

                var transaction = dbConnection.BeginTransaction();
                OracleParameter[] parameters;
                for (int i1 = 0; i1 < listColNames.Count; i1++)
                {
                    parameters = new OracleParameter[colNames.Length];
                    for (int k = 0; k < parameters.Length; k++)
                    {
                        parameters[k] = new OracleParameter("@" + colNames[k], listColValues[i1][k]);
                    }

                    dbCommand.Parameters.AddRange(parameters);
                    dbCommand.ExecuteNonQuery();
                }
                transaction.Commit();

                watch.Stop();
                Debug.Log(watch.Elapsed);
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }

        /****************************  删除符合条件的数据    ************************************/

        /// <summary>
        /// 删除符合任一条件的数据
        /// </summary>
        public bool DeleteAnyone(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                //当字段名称和字段数值不对应时引发异常
                if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
                {
                    throw new ExternalException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
                }

                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("DELETE FROM {0} WHERE {1}{2}'{3}'", tableName, colNames[0], operations[0], colValues[0]);
                for (int i = 1; i < colValues.Length; i++)
                {
                    sb.AppendFormat("OR {0}{1}'{2}'", colNames[i], operations[i], colValues[i]);
                }

                ExecuteQuery(sb.ToString());
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }
        /// <summary>
        /// 删除符合所有条件的数据
        /// </summary>
        public bool DeleteEveryone(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                //当字段名称和字段数值不对应时引发异常
                if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
                {
                    throw new ExternalException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
                }

                string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 1; i < colValues.Length; i++)
                {
                    queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }
                ExecuteQuery(queryString);
                CloseDealData();
                return true;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return false;
            }
        }

        /****************************  获取该表数据    ************************************/

        /// <summary>
        /// 获取该表所有数据
        /// </summary>
        public List<T> GetAll<T>(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            return GetValues<T>(queryString);
        }
        /// <summary>
        /// 获取符合任一条件的数据
        /// </summary>
        public List<T> GetAnyone<T>(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT * FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 1; i < colNames.Length; i++)
                {
                    queryString += " OR " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }
                return GetValues<T>(queryString);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<T>();
            }
        }
        /// <summary>
        /// 获取符合全部条件的数据
        /// </summary
        public List<T> GetEveryone<T>(string tableName, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT * FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 1; i < colNames.Length; i++)
                {
                    queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }
                return GetValues<T>(queryString);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<T>();
            }
        }
        /// <summary>
        /// 获取符合任一条件的某些字段的数据
        /// </summary>
        public List<List<object>> GetForAnyone(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT " + items[0];
                for (int i = 1; i < items.Length; i++)
                {
                    queryString += ", " + items[i];
                }
                queryString += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 0; i < colNames.Length; i++)
                {
                    queryString += " OR " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
                }

                return GetValuesForItems(queryString, items);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<List<object>>();
            }
        }
        /// <summary>
        /// 获取符合全部条件的某些字段的数据
        /// </summary>
        public List<List<object>> GetForEveryone(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
        {
            try
            {
                string queryString = "SELECT " + items[0];
                for (int i = 1; i < items.Length; i++)
                {
                    queryString += ", " + items[i];
                }
                queryString += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
                for (int i = 0; i < colNames.Length; i++)
                {
                    queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + ",";
                }

                return GetValuesForItems(queryString, items);
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<List<object>>();
            }
        }

        /****************************  获取表,名 相关数据    ************************************/

        /// <summary>
        /// 获取某数据表的所有字段名
        /// </summary>
        public List<string> GetTableAllFields(string tableName)
        {
            string queryString = " select COLUMN_NAME from information_schema.COLUMNS " +
              "where" +
              " table_name = '" + tableName +
              "' and" +
              " table_schema = '" + dbConnection.Database + "'";  // TODO: mysql、需要修改 ;sqllite "Pragma Table_Info(" + tableName + ")";  链接:https://www.cnblogs.com/fuqia/p/8994080.html

            List<string> listFields = new List<string>();
            try
            {
                ExecuteQuery(queryString);

                while (dataReader.Read())
                {
                    string field_name = dataReader.GetString(0);
                    listFields.Add(field_name);
                }
                CloseDealData();
                return listFields;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return listFields;
            }
        }
        /// <summary>
        /// 获取所有数据表名
        /// </summary>
        public List<string> GetAllTableName()
        {
            string db_name = dbConnection.Database;
            string queryString = "select table_name from information_schema.tables where table_schema = '" + db_name + "'";

            List<string> list = new List<string>();
            try
            {
                ExecuteQuery(queryString);
                while (dataReader.Read())
                {
                    string table_name = dataReader.GetString(0);  // 解决方式链接:https://blog.csdn.net/u012835905/article/details/41212615
                    list.Add(table_name);
                }
                CloseDealData();
                return list;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return list;
            }
        }
        /// <summary>
        /// 获取表的相关信息
        /// </summary>
        public DataTable GetTableInfo(string tableName)
        {
            DataTable dt = new DataTable();
            try
            {
                ReadFullTable(tableName);
                dt = dataReader.GetSchemaTable();
                CloseDealData();
                return dt;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return dt;
            }
        }
        /****************************  其他处理数据    ************************************/

        /// <summary>
        /// 通过SQL语句获取数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryString">获取数据方式</param>
        /// <returns></returns>
        private List<T> GetValues<T>(string queryString)
        {
            try
            {
                List<string> fields = GetTableAllFields(typeof(T).Name);
                ExecuteQuery(queryString);

                PropertyInfo[] pros = typeof(T).GetProperties();
                List<T> listT = new List<T>();
                while (dataReader.Read())
                {
                    T t = Activator.CreateInstance<T>();
                    foreach (PropertyInfo item in pros)
                    {
                        //如果数据表不包含该字段,不读取
                        if (!fields.Contains(item.Name))
                            continue;
                        var result = dataReader.GetValue(dataReader.GetOrdinal(item.Name));
                        if (!string.IsNullOrEmpty(result.ToString()))
                        {
                            item.SetValue(t, Convert.ChangeType(result, item.PropertyType), null);
                        }
                    }
                    listT.Add(t);
                }
                CloseDealData();
                return listT;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<T>();
            }
        }
        /// <summary>
        /// 获取指定的某些字段数据
        /// </summary>
        /// <param name="queryString">SQL语句</param>
        /// <param name="items">要获取的字段</param>
        /// <returns></returns>
        private List<List<object>> GetValuesForItems(string queryString, string[] items)
        {
            try
            {
                ExecuteQuery(queryString);

                List<List<object>> list = new List<List<object>>();
                for (int i = 0; i < items.Length; i++)
                {
                    List<object> listTemp = new List<object>();
                    list.Add(listTemp);
                }

                while (dataReader.Read())
                {
                    for (int i = 0; i < items.Length; i++)
                    {
                        var result = dataReader.GetValue(dataReader.GetOrdinal(items[i]));
                        list[i].Add(result);
                    }
                }
                CloseDealData();
                return list;
            }
            catch (Exception e)
            {
                Debug.Log(e);
                return new List<List<object>>();
            }
        }
    }
}

直接调用帮助类就可以用了,

mysql 测试类:

/*******************************************************************
* 作者:     # maki #
* 创建日期: # 2019年9月6日14:29:56 #
* 描述:  测试连接mysql数据库
* 
******************************************************************/
using DataBaseTool;
using System;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;

public class MySqlTest : MonoBehaviour {
    private DataBaseHelper dbHelper;

    string mysqlUrl = "";
    // Use this for initialization
    void Start () {
         dbHelper = DataBaseHelper.Instance;
        mysqlUrl = Login("localhost", 3306, "root", "Aa123456", "Mysqltest"); 
        dbHelper.Init(new MySqlManager(),mysqlUrl);

        //Student stu = new Student()
        //{
        //    id = Guid.NewGuid().ToString(),
        //    name = "mmm",
        //    age = 22,
        //    grade = "七十一班",
        //    score = 32.5f,
        //};
           /**添加数据**/
        //dbHelper.SaveOrUpdate(stu);

        //   /**获取数据**/
        var s = dbHelper.GetAll<Student>();s. ForEach(u => Debug.Log(u.name));



        //   /**删除数据**/
        dbHelper.DeleteById<Student>(s[0].id);

        //   /**获取数据**/
        var s2 = dbHelper.GetAll<Student>(); s2.ForEach(u => Debug.Log(u.id));
        ///**获取所有表名**/
        //var names = dbHelper.GetAllTableName(); Debug.Log(names.Count);
        //names.ForEach(u => Debug.Log(u));
    }

    // Update is called once per frame
    void Update () {
		
	}

    /// <summary>
    /// 构造方法
    /// </summary>
    /// <param name="_host">ip地址</param>
    /// <param name="_userName">用户名</param>
    /// <param name="_password">密码</param>
    /// <param name="_databaseName">数据库名称</param>
    public string Login(string host, int port, string userName, string password, string databaseName)
    {
         return  string.Format("Database={0};Data Source={1};User Id={2};Password={3};port={4}"
               , databaseName, host, userName, password, port);
    }
}

Oracle测试类:

/*******************************************************************
* 作者:     # maki #
* 创建日期: # 2019年9月6日14:29:56 #
* 描述:  测试连接Oracle数据库
* 
******************************************************************/
using DataBaseTool;
using System;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;

public class OracleSqlTest : MonoBehaviour {
    private DataBaseHelper dbHelper;

    string mysqlUrl = "";
    // Use this for initialization
    void Start () {
         dbHelper = DataBaseHelper.Instance;
       // mysqlUrl = Login("localhost", 3306, "root", "Aa123456", "Mysqltest"); 
        dbHelper.Init(new OracleManager(),mysqlUrl);

        //Student stu = new Student()
        //{
        //    id = Guid.NewGuid().ToString(),
        //    name = "mmm",
        //    age = 22,
        //    grade = "七十一班",
        //    score = 32.5f,
        //};
           /**添加数据**/
        //dbHelper.SaveOrUpdate(stu);

        //   /**获取数据**/
        var s = dbHelper.GetAll<Student>();s. ForEach(u => Debug.Log(u.name));



        //   /**删除数据**/
        dbHelper.DeleteById<Student>(s[0].id);

        //   /**获取数据**/
        var s2 = dbHelper.GetAll<Student>(); s2.ForEach(u => Debug.Log(u.id));
        ///**获取所有表名**/
        //var names = dbHelper.GetAllTableName(); Debug.Log(names.Count);
        //names.ForEach(u => Debug.Log(u));
    }

    // Update is called once per frame
    void Update () {
		
	}

    /// <summary>
    /// 构造方法
    /// </summary>
    /// <param name="_host">ip地址</param>
    /// <param name="_userName">用户名</param>
    /// <param name="_password">密码</param>
    /// <param name="_databaseName">数据库名称</param>
    public string Login(string ip, string port, string sever_name, string user, string password)
    {
        string url = "Data Source=(" +
            "DESCRIPTION=(" +
            "ADDRESS=(" +
            "PROTOCOL=TCP)" +
            "(HOST=" + ip + ")" +
            "(PORT=" + port + ")" +
            ")" +
            "(CONNECT_DATA=" +
            "(SERVICE_NAME=" + sever_name + ")" +
            "));" +
            "Persist Security Info=True;" +
            "User ID=" + user + ";" +
            "Password=" + password + ";"+
            "Connection Timeout=3;";
        return url;
    }
}

少了一个属性值得特殊类:

/*******************************************************************
* 作者:     # maki  #
* 创建日期: # 2019年9月5日18:34:04  #
* 描述:  
******************************************************************/
using System;

namespace DataBaseTool
{
    /// <summary>
    /// 用来标记属性类型
    /// </summary>
    public class PropertySign : Attribute
    {
        public PropertyType fieldType { get; set; }
    }
    /// <summary>
    /// 字段类型
    /// </summary>
    public enum PropertyType
    {
        None,
        /// <summary>
        /// 在数据库相对应表中没有该属性
        /// </summary>
        NotInDataBase,
        /// <summary>
        /// 在数据库里该属性为自增主键
        /// </summary>
        PrimaryKeyInDataBase,
    }

    public class DataBaseAttributes
    {
        


    }
}

这样就可以用了。

  • 1
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值