主要是利用上篇文章(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
{
}
}
这样就可以用了。