unity中 c#操作数据库的方法

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());}}/// 
/// 获取全部的表的名字/// 
/// public List GetAllTableName(){List vs = new List();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;}/// 
/// 删除表/// 
/// /// 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);}//插入部分IDpublic 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;}/// 
/// 正序排列/// 
/// 数据表名字/// 表头/// public DataSet ASCsort(string tableName,string condition){string query = "SELECT * FROM " + tableName + " ORDER BY " + condition;return ExecuteQuery(query);}/// 
/// 倒序排列/// 
/// 数据表名字/// 表头/// 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.");}}#endregionpublic MessageItem allmessage = new MessageItem();//初始化的操作，一开始调用打开数据库public void Init(){InitTpl();InitDatabase();}public void InitDatabase(){_sql = new SqlAccess();}public void CloseDatabase(){_sql.Close();}#region 保存和读取按钮所用到的方法/// 
/// 创建表/// 
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)",});}}/// 
/// 判断数据库中是否存在该表/// 
/// public bool IsExistTable(){List tableName = new List();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 GetAllClassNames(){List cns = new List();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;}/// 
/// 获取所有的信息/// 
/// public List GetAllData(){List listMessges = new List();//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;}/// 
/// 删除某一班级的信息/// 
/// public void DeleteClass(string ID){if (_sql != null){_sql.Delete("classinfotable", new string[] { "ID" }, new string[] { ID });}}/// 
/// 正序/// 
/// public List ascSort(string condition){List listMessges = new List();//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;}/// 
/// 倒序/// 
/// public List descSort(string condition){List listMessges = new List();//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;
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！