using UnityEngine;
using System.Collections;
using System;
using System.Data;
using System.IO;
using MySql.Data;
using MySql.Data.MySqlClient;
public class MySQLAccess {
public static MySqlConnection dbConnection;
/// <summary>
/// 如果只是在本地，就写localhost
/// </summary>
private static string _host = "localhost";
/// <summary>
/// 如果是局域网，就写本机的局域网IP
/// </summary>
//private static string host = "192.168.177.120";
private static string _user = "root";
private static string _pwd = "123456";
//被访问的数据库名称
private static string _database = "test2";
public MySQLAccess()
{
OpenSql();
}
/// <summary>
/// 打开指定数据库
/// </summary>
public static void OpenSql()
{
try
{
//打开指定数据库
string connectString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};", _host, _database, _user, _pwd, "3306");
dbConnection = new MySqlConnection(connectString);
dbConnection.Open();
}
catch (Exception e)
{
throw new Exception("服务器连接失败，请检查是否已启动mysql" + e.Message.ToString());
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="name"></param>
/// <param name="col"></param>
/// <param name="colType"></param>
/// <returns></returns>
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 += ");";
return ExecuteQuery(query);
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
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 exc)
{
throw new Exception("SQL语句:" + sqlString + " /n " + exc.Message.ToString());
}
finally
{
}
return ds;
}
return null;
}
/// <summary>
/// 创建表，设置主键递增
/// </summary>
/// <param name="name"></param>
/// <param name="col"></param>
/// <param name="colType"></param>
/// <returns></returns>
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(query);
return ExecuteQuery(query);
}
/// <summary>
/// 插入一条数据，包括所有，不适用自动累加ID
/// </summary>
/// <param name="tableName"></param>
/// <param name="values"></param>
/// <returns></returns>
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(query+"-----------------插入语句");
return ExecuteQuery(query);
}
/// <summary>
/// 插入部分ID
/// </summary>
/// <param name="talbelName"></param>
/// <param name="col"></param>
/// <param name="values"></param>
/// <returns></returns>
public DataSet InsertInto(string talbelName, string[] col, string[] values)
{
if (col.Length != values.Length)
throw new Exception("columns.length!=colType.length");
string query = "INSERT INTO " + talbelName + " (" + 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);
}
/// <summary>
/// 从表中查找数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="items"></param>
/// <param name="col"></param>
/// <param name="operations"></param>
/// <param name="values"></param>
/// <returns></returns>
public DataSet SelectWhere(string tableName, string[] items, string[] col, string[] operations, string[] values)
{
if (col.Length != operations.Length || operations.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] + operations[0] + "'" + values[0] + "'";
for (int i = 1; i < col.Length; ++i)
{
query += " AND " + col[i] + operations[i] + "' " + values[0] + "' ";
}
Debug.Log(query);
return ExecuteQuery(query);
}
/// <summary>
/// 更新表中数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="cols"></param>
/// <param name="colsvalues"></param>
/// <param name="selectKey"></param>
/// <param name="selectValue"></param>
/// <returns></returns>
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(query);
return ExecuteQuery(query);
}
/// <summary>
/// 删除表中某个数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="cols"></param>
/// <param name="colsvalues"></param>
/// <returns></returns>
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(query);
return ExecuteQuery(query);
}
/// <summary>
/// 断开与数据库的操作连接
/// </summary>
public void Close()
{
if (dbConnection != null)
{
Debug.Log("close mysql....");
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
}
}
}
