工具类如下:
using System;
using MySql.Data.MySqlClient;
using System.Data;
using UnityEngine;
class ConnectionSQLProgram
{
//远程连接
// string connectionString= "User ID = ; Password =.; Host =; Port =3306 ; Database = ;Charset = ";
//本地连接
//public static string connectionString = "Database = ;Charset = ;User ID = ; Password = ; Host = ; Port = 3306";
//链接
//string connectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd);
//开启
//ConnectionSQLProgram.OpenSqlConnection(connectionString);
//开启数据库一次后,后续再次开启数据库进行操作直接调用:ConnectionSQLProgram.OpenSqlAtAdministratorModel();
//关闭
//ConnectionSQLProgram.CloseConnection();
//每次对数据库进行操作都要有开启及关闭
static MySqlConnection dbConnection;
static string ConnectionString = "";
//打开数据库链接
public static void OpenSqlConnection(string connectionString)
{
try
{
ConnectionString = connectionString;
dbConnection = new MySqlConnection(connectionString);
dbConnection.Open();
if(GS_Loading.Instance!=null)
GS_Loading.Instance.isSqlTrue = true;
}
catch (Exception e)
{
if (GS_Loading.Instance != null)
GS_Loading.Instance.isSqlTrue = false;
//throw new Exception("服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString());
}
if (GS_Loading.Instance != null)
GS_Loading.Instance.WarningShow();
}
public static void OpenSqlAtAdministratorModel()
{
try
{
dbConnection = new MySqlConnection(ConnectionString);
dbConnection.Open();
}
catch (Exception e)
{
throw new Exception("服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString());
}
}
//关闭数据库连接
public static void CloseConnection()
{
if (dbConnection != null)
{
if(dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
}
}
}
//保存数据
public static DataSet GetDataSet(string sqlString)
{
DataSet ds = new DataSet();
try
{
//用于检索和保存数据
//Fill(填充)能改变DataSet中的数据以便于数据源中数据匹配
//Update(更新)能改变数据源中的数据以便于DataSet中的数据匹配
MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
da.Fill(ds);
}
catch (Exception ee)
{
throw new Exception("SQL:" + sqlString + "\n" + ee.Message.ToString());
}
return ds;
}
//增 insert
//string timeS = System.DateTime.Now.Year + "-" + System.DateTime.Now.Month + "-" + System.DateTime.Now.Day + " " + System.DateTime.Now.Hour + ":" + System.DateTime.Now.Minute + ":" + System.DateTime.Now.Second;
//DataSet ds = connectionSQL.InsertInto("XXX", new string[] { "2", "l123", "121326", "0", "0", timeS, "" });
public static DataSet InsertInto(string tableName, string[] values)
{
string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
for (int i = 1; i < values.Length; ++i)
{
query += ", " + "'" + values[i] + "'";
}
query += ")";
Debug.Log(query);
return ExecuteQuery(query);
}
//插入部分ID
//InsertInto("表名", new string[] { "ip", "TaskID", "SelectRoleID", "UserID" }, new string[] { Ip, taskId , "", userID });
public static DataSet InsertInto(string tableName, string[] col, string[] values)
{
if (col.Length != values.Length)
{
throw new Exception("columns.Length != colType.Length");
}
string query = "INSERT INTO " + tableName + " (" + col[0];
for (int i = 1; i < col.Length; ++i)
{
query += ", " + col[i];
}
query += ") VALUES (" + "'" + values[0] + "'";
for (int i = 1; i < values.Length; ++i)
{
query += ", " + "'" + values[i] + "'";
}
query += ")";
Debug.Log(query);
return ExecuteQuery(query);
}
//删 delete
//Delete("GS_USER",new string[] {"ID"} ,new string[] {"2"});
public static DataSet Delete(string tableName, string[] cols, string[] colsvalues)
{
string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + "'" + colsvalues[0] + "'";
for (int i = 1; i < colsvalues.Length; ++i)
{
query += " or " + cols[i] + " = " +"'" + colsvalues[i]+"'";
}
Debug.Log(query);
return ExecuteQuery(query);
}
//删 两个值满足一起删除
public static DataSet DeleteAdd(string tableName, string[] cols, string[] colsvalues)
{
string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + "'" + colsvalues[0] + "'";
for (int i = 1; i < colsvalues.Length; ++i)
{
query += " and " + cols[i] + " = " + "'" + colsvalues[i] + "'";
}
Debug.Log(query);
return ExecuteQuery(query);
}
//改 update
//UpdateInto("表名", new string[] {"UserName","PassWord","Sex","IsAdmin"},new string[] { "s123","wzry","1","1" }, "ID","1");
public static DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
{
string query = "Update " + tableName + " Set " + cols[0] + " = " +"'"+ colsvalues[0]+"'";
for (int i = 1; i < colsvalues.Length; ++i)
{
query += ", " + cols[i] + " =" + "'" + colsvalues[i] + "'";
}
query += " Where " + selectkey + " = " +"'"+ selectvalue+"'" + " ";
Debug.Log(query);
return ExecuteQuery(query);
}
//没用过 如果有错 试着部分地方将""更改为 "'"参考DeleteAdd方法
public static DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string[] selectkey, string[] operation, string[] selectvalue)
{
string query = "Update " + tableName + " Set " + cols[0] + " = " + "'" + colsvalues[0] + "'";
for (int i = 1; i < colsvalues.Length; ++i)
{
query += ", " + cols[i] + " =" + "'" + colsvalues[i] + "'";
}
query += " Where " + selectkey[0] + " = " + selectvalue[0] + " ";
for (int i = 1; i < selectkey.Length; ++i)
{
query += " AND " + selectkey[i] + "'" + operation[i] + "'" + selectvalue[i];
}
Debug.Log(query);
return ExecuteQuery(query);
}
//查询表所有内容 SeldetAll("表名");
public static DataSet SeldetAll(string tableName)
{
string query = "select * from " + tableName;
return ExecuteQuery(query);
}
//SelectWhere("表名", new string[] {"UserName","PassWord","Sex","IsAdmin"},new string[] { "ID" }, new string[] {"="}, new string[] { "1" });
//查 select
public static DataSet SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
{
if (col.Length != operation.Length || operation.Length != values.Length)
{
throw new Exception("col.Length != operation.Length != values.Length");
}
string query = "SELECT " + items[0];
for (int i = 1; i < items.Length; ++i)
{
query += ", " + items[i];
}
query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + values[0];
for (int i = 1; i < col.Length; ++i)
{
query += " AND " + col[i] + operation[i] + values[i];
}
Debug.Log(query);
return ExecuteQuery(query);
}
public static DataSet ExecuteQuery(string sqlString)
{
if (dbConnection.State == ConnectionState.Open)
{
DataSet ds = new DataSet();
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
da.Fill(ds);
}
catch (Exception ee)
{
throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());
}
finally
{
}
return ds;
}
return null;
}
}
将读取到的数据库信息保存类代码:注意数据库中属性类型,保存时虽然转换成字符串进行保存但是数据类型还是要注意的。
读出时也要注意数据类型。如果类型不一致会报错。
using System.Collections;
using System.Collections.Generic;
using System.Data;
using UnityEngine;
public class GS_USER
{
struct USER
{
public int ID;
public string UserName;
public string PassWord;
public int Sex;
public int IsAdmin;
public string LoginTime;
public string CreatTime;
}
USER user;
List<USER> GS_USERList = new List<USER>();
private static GS_USER uniqueInstance;
private GS_USER()
{
}
public static GS_USER GetInstance()
{
// 如果类的实例不存在则创建,否则直接返回
if (uniqueInstance == null)
{
uniqueInstance = new GS_USER();
}
return uniqueInstance;
}
#region 读取MySQL
public void SetPropertyInformation()
{
GS_USERList.Clear();
DataSet ds = ConnectionSQLProgram.SeldetAll("GS_USER");
if (ds != null)
{
DataTable table = ds.Tables[0];
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i][0].ToString() != "")
{
user.ID = (int)(table.Rows[i][0]);
}
else
{
user.ID = -1;
}
if (table.Rows[i][1].ToString() != "")
{
user.UserName = (string)table.Rows[i][1];
}
else
{
user.UserName = "";
}
if (table.Rows[i][2] != null)
{
user.PassWord = (string)(table.Rows[i][2]);
}
else
{
user.PassWord = "";
}
if (table.Rows[i][3].ToString() != "")
{
user.Sex = (int)(table.Rows[i][3]);
}
else
{
user.Sex = -1;
}
if (table.Rows[i][4].ToString() != "")
{
user.IsAdmin = (int)(table.Rows[i][4]);
}
else
{
user.IsAdmin = 0;
}
if (table.Rows[i][5].ToString() != "")
{
user.LoginTime = table.Rows[i][5].ToString();
}
else
{
user.LoginTime = "";
}
if (table.Rows[i][6].ToString() != "")
{
user.CreatTime = table.Rows[i][6].ToString();
}
else
{
user.CreatTime = "";
}
GS_USERList.Add(user);
}
}
}
#endregion
}
调用:
private GS_USER GS_USER;
string host = "0.0.0.1";
string id = "xxx"; //***不要变*** 访问数据库表的用户名
string pwd = "xxx"; //密码 访问数据库表的密码
string database = "表名"; //要访问的数据库表名
connectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd);
ConnectionSQLProgram.OpenSqlConnection(connectionString);
GS_USER = GS_USER.GetInstance();
GS_USER.SetPropertyInformation();
ConnectionSQLProgram.CloseConnection();