1、c#操作的基础类,用于编辑sql语句进行操作
using UnityEngine;
using System;
using MySql.Data;
using System.IO;
using System.Data;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
public class SqlAccess
{
public static MySqlConnection dbConnection;
private static string _host = "localhost";
private static string _id = "root";
private static string _pwd = "";
private static string _database = "test"; //直接使用尝试,暂不使用此参数
public SqlAccess()
{
OpenSql();
}
public static void OpenSql()
{
try
{
string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};", _host, _database, _id, _pwd, "3306");
dbConnection = new MySqlConnection(connectionString);
dbConnection.Open();
}
catch (Exception e)
{
throw new Exception("服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString());
}
}
/// <summary>
/// 获取全部的表的名字
/// </summary>
/// <returns></returns>
public List<string> GetAllTableName()
{
List<string> vs = new List<string>();
string sql = "show tables;";
MySqlCommand cmd = new MySqlCommand(sql, dbConnection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string a = reader.GetString(0);
//Debug.Log(a);
vs.Add(a);
}
reader.Close();
return vs;
}
/// <summary>
/// 删除表
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataSet DeleteTable(string tableName)
{
string sql = "DROP TABLE " + tableName;
return ExecuteQuery(sql);
}
//创建表;
public DataSet CreateTable(string name, string[] col, string[] colType)
{
if (col.Length != colType.Length)
{
throw new Exception("columns.Length != colType.Length");
}
string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
for (int i = 1; i < col.Length; ++i)
{
query += ", " + col[i] + " " + colType[i];
}
query += ")";
Debug.Log("CreateTable : " + query);
return ExecuteQuery(query);
}
public DataSet CreateTableAutoID(string name, string[] col, string[] colType)
{
if (col.Length != colType.Length)
{
throw new Exception("columns.Length != colType.Length");
}
string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT";
for (int i = 1; i < col.Length; ++i)
{
query += ", " + col[i] + " " + colType[i];
}
query += ", PRIMARY KEY (" + col[0] + ")" + ")";
Debug.Log("CreateTableAutoID : " + query);
return ExecuteQuery(query);
}
#region 插入
//插入一条数据,包括所有,不适用自动累加ID。
public 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("InsertInto : " + query);
return ExecuteQuery(query);
}
//插入部分ID
public 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("The Insert query is : " + query);
return ExecuteQuery(query);
}
#endregion
#region 查询
//精确选择数据;
public 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("SelectWhere query : " + query);
return ExecuteQuery(query);
}
//选中所有的数据;
public DataSet SelectAll(string tableName, string condition =null)
{
string query;
if (condition != null)
{
query = "SELECT * FROM " + tableName + " ORDER BY " + condition;
}
else
{
query = "SELECT * FROM " + tableName;
}
Debug.Log("SelectAll query : " + query);
return ExecuteQuery(query);
}
//选中一列数据;
public DataSet SelectOneColumn(string col, string tableName, string condition)
{
string query = "SELECT " + col + " FROM " + tableName + " ORDER BY " + condition;
Debug.Log("SelectOneColumn query : " + query);
return ExecuteQuery(query);
}
//选中一排数据;
public DataSet SelectOneRowData(string tableName, string col, string value)
{
string query = "SELECT * FROM " + tableName + " WHERE " + col + " = " + value;
Debug.Log("SelectOneRowData query : " + query);
return ExecuteQuery(query);
}
#endregion
#region 更新
public 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("UpdateInto query : " + query);
return ExecuteQuery(query);
}
#endregion
#region 删除
//删除数据;
public 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("Delete query : " + query);
return ExecuteQuery(query);
}
#endregion
//执行查询;
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 e)
{
throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
}
finally
{
}
return ds;
}
return null;
}
/// <summary>
/// 正序排列
/// </summary>
/// <param name="tableName">数据表名字</param>
/// <param name="condition">表头</param>
/// <returns></returns>
public DataSet ASCsort(string tableName,string condition)
{
string query = "SELECT * FROM " + tableName + " ORDER BY " + condition;
return ExecuteQuery(query);
}
/// <summary>
/// 倒序排列
/// </summary>
/// <param name="tableName">数据表名字</param>
/// <param name="condition">表头</param>
/// <returns></returns>
public DataSet DESCsort(string tableName, string condition)
{
string query = "SELECT * FROM " + tableName + " ORDER BY " + condition+ " DESC";
return ExecuteQuery(query);
}
public void Close()
{
if (dbConnection != null)
{
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
}
}
}
2、具体的操作类
using Config;
using UnityEngine;
using Basic.Managers;
using System;
using System.IO;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using MySql.Data;
public class UIManager
{
#region 单例
private static UIManager _instance = new UIManager();
public static UIManager instance
{
get
{
return _instance;
}
}
public UIManager()
{
if (_instance != null)
{
throw new UnityException("Error: Please use instance to get UIManager.");
}
}
#endregion
public MessageItem allmessage = new MessageItem();
//初始化的操作,一开始调用打开数据库
public void Init()
{
InitTpl();
InitDatabase();
}
public void InitDatabase()
{
_sql = new SqlAccess();
}
public void CloseDatabase()
{
_sql.Close();
}
#region 保存和读取按钮所用到的方法
/// <summary>
/// 创建表
/// </summary>
public void Creat(string tableName)
{
if (_sql != null)
{
_sql.CreateTable(tableName,
new string[]
{
"ID",
"className",
"Time",
"peopleNum",
"zuociNum",
"macNum",
"num",
"name",
},
new string[]
{
"char(20)",
"char(20)",
"char(20)",
"char(20)",
"char(50)",
"char(50)",
"char(100)",
"char(100)",
}
);
}
}
/// <summary>
/// 判断数据库中是否存在该表
/// </summary>
/// <param name="tableName"></param>
public bool IsExistTable()
{
List<string> tableName = new List<string>();
tableName = _sql.GetAllTableName();
//是否已存在该表
bool isExistTable = false;
foreach (var item in tableName)
{
if (item == "classinfotable")
{
Debug.Log("已存在数据表");
isExistTable = true;
}
}
return isExistTable;
}
//插入数据到基础数据表;
public void AddClassInfoTable(string tableName)
{
if (_sql != null)
{
//for
_sql.InsertInto(tableName,
new string[]
{
"ID",
"className",
"Time",
"peopleNum",
"zuociNum",
"macNum",
"num",
"name",
},
new string[]
{
allmessage.ID.ToString(),
allmessage.className,
allmessage.time,
allmessage.peopleNum,
allmessage.zuoCiNum,
allmessage.macNum,
allmessage.num,
allmessage.name,
}
);
}
}
//取得数据所有班级的名字
public List<string> GetAllClassNames()
{
List<string> cns = new List<string>();
DataSet ds = _sql.SelectOneColumn("className", "ClassInfoTable", "className");
if (ds != null)
{
DataTable table = ds.Tables[0];
foreach (DataRow rows in table.Rows)
{
cns.Add(rows["className"].ToString());
}
}
return cns;
}
/// <summary>
/// 获取所有的信息
/// </summary>
/// <returns></returns>
public List<MessageItem> GetAllData()
{
List<MessageItem> listMessges = new List<MessageItem>();
//DataSet ds = _sql.SelectWhere("sheet1", new string[] { "*" }, new string[] { "zuociNum" }, new string[] { "=" }, new string[] { "1" });
DataSet ds = _sql.SelectAll("classinfotable");
//表头不能用中文
if (ds != null)
{
DataTable table = ds.Tables[0];
foreach (DataRow rows in table.Rows)
{
MessageItem messageItem = new MessageItem();
messageItem.ID = rows["ID"].ToString();
messageItem.className = rows["className"].ToString();
messageItem.time = rows["Time"].ToString();
messageItem.peopleNum = rows["peopleNum"].ToString();
messageItem.zuoCiNum = rows["zuociNum"].ToString();
messageItem.macNum = rows["macNum"].ToString();
messageItem.num = rows["num"].ToString();
messageItem.name = rows["name"].ToString();
listMessges.Add(messageItem);
messageItem = null;
}
}
return listMessges;
}
/// <summary>
/// 删除某一班级的信息
/// </summary>
/// <param name="index"></param>
public void DeleteClass(string ID)
{
if (_sql != null)
{
_sql.Delete("classinfotable", new string[] { "ID" }, new string[] { ID });
}
}
/// <summary>
/// 正序
/// </summary>
/// <returns></returns>
public List<MessageItem> ascSort(string condition)
{
List<MessageItem> listMessges = new List<MessageItem>();
//DataSet ds = _sql.SelectWhere("sheet1", new string[] { "*" }, new string[] { "zuociNum" }, new string[] { "=" }, new string[] { "1" });
DataSet ds = _sql.ASCsort("classinfotable", condition);
//表头不能用中文
if (ds != null)
{
DataTable table = ds.Tables[0];
foreach (DataRow rows in table.Rows)
{
MessageItem messageItem = new MessageItem();
messageItem.ID = rows["ID"].ToString();
messageItem.className = rows["className"].ToString();
messageItem.time = rows["Time"].ToString();
messageItem.peopleNum = rows["peopleNum"].ToString();
messageItem.zuoCiNum = rows["zuociNum"].ToString();
messageItem.macNum = rows["macNum"].ToString();
messageItem.num = rows["num"].ToString();
messageItem.name = rows["name"].ToString();
listMessges.Add(messageItem);
messageItem = null;
}
}
return listMessges;
}
/// <summary>
/// 倒序
/// </summary>
/// <returns></returns>
public List<MessageItem> descSort(string condition)
{
List<MessageItem> listMessges = new List<MessageItem>();
//DataSet ds = _sql.SelectWhere("sheet1", new string[] { "*" }, new string[] { "zuociNum" }, new string[] { "=" }, new string[] { "1" });
DataSet ds = _sql.DESCsort("classinfotable", condition);
//表头不能用中文
if (ds != null)
{
DataTable table = ds.Tables[0];
foreach (DataRow rows in table.Rows)
{
MessageItem messageItem = new MessageItem();
messageItem.ID = rows["ID"].ToString();
messageItem.className = rows["className"].ToString();
messageItem.time = rows["Time"].ToString();
messageItem.peopleNum = rows["peopleNum"].ToString();
messageItem.zuoCiNum = rows["zuociNum"].ToString();
messageItem.macNum = rows["macNum"].ToString();
messageItem.num = rows["num"].ToString();
messageItem.name = rows["name"].ToString();
listMessges.Add(messageItem);
messageItem = null;
}
}
return listMessges;
}
#endregion
}
3、信息类
public class MessageItem
{
public string ID;
public string className;
public string zuoCiNum;
public string macNum;
public string num;
public string name;public string time;
public string peopleNum;
}