/******************************
* 对数据库操作的封装
* @Author wuzhang
* 2014/12/4
*****************************/
usingUnityEngine;usingSystem;usingSystem.Data;usingSystem.Collections;usingMySql.Data.MySqlClient;usingMySql.Data;usingSystem.IO;public classSqlAccess
{public staticMySqlConnection dbConnection;//如果只是在本地的话,写localhost就可以。//static string host = "localhost";//如果是局域网,那么写上本机的局域网IP
static string host = "127.0.0.1";static string id = "root";static string pwd = "";static string database = "login";publicSqlAccess()
{
OpenSql();
}public static voidOpenSql()
{try{string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};",host,database,id,pwd,"3306");
dbConnection= newMySqlConnection(connectionString);
dbConnection.Open();
}catch(Exception e)
{throw new Exception("服务器连接失败,请重新检查是否打开MySql服务。" +e.Message.ToString());
}
}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+= ")";returnExecuteQuery(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(query);returnExecuteQuery(query);
}//插入一条数据,包括所有,不适用自动累加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(query);returnExecuteQuery (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(query);returnExecuteQuery (query);
}///
///返回表的查询结果///
/// The all.
/// Name.
public DataSet SelectAll(stringName)
{string query ="select * from"+" "+Name;returnExecuteQuery (query);
}///
///条件查找///
/// The where.
/// Table name.
/// Items.
/// Col.
/// Operation.
/// Values.
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[0] + "'";
}returnExecuteQuery (query);
}///
///更新表信息///
/// The into.
/// Table name.
/// Cols.
/// Colsvalues.
/// Selectkey.
/// Selectvalue.
public DataSet UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,stringselectvalue)
{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+" ";returnExecuteQuery (query);
}///
///条件删除///
/// Table name.
/// Cols.
/// Colsvalues.
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);returnExecuteQuery (query);
}///
///关闭数据库///
public voidClose()
{if(dbConnection != null)
{
dbConnection.Close();
dbConnection.Dispose();
dbConnection= null;
}
}public DataSet ExistOrNot(stringsql)
{if(dbConnection.State==ConnectionState.Open)
{
Debug.Log (sql);
DataSet ds= new DataSet(); //表的集合
try{
MySqlDataAdapter da= newMySqlDataAdapter(sql, dbConnection);
da.Fill(ds);
}catch(Exception ee)
{throw new Exception("SQL:" + sql + "/n" +ee.Message.ToString());
}finally{
}returnds;
}return null;
}///
///返回检索结果///
/// The query.
/// Sql string.
public static DataSet ExecuteQuery(stringsqlString)
{if(dbConnection.State==ConnectionState.Open)
{
DataSet ds= new DataSet(); //表的集合
try{
MySqlDataAdapter da= newMySqlDataAdapter(sqlString, dbConnection);
da.Fill(ds);
}catch(Exception ee)
{throw new Exception("SQL:" + sqlString + "/n" +ee.Message.ToString());
}finally{
}returnds;
}return null;
}///
///读取数据集///
/// Ds.
public voidReadDs(DataSet ds)
{if(ds!=null)
{
DataTable user= ds.Tables[0];foreach(DataRow row inuser.Rows)
{foreach(DataColumn colum inuser.Columns)
{
Debug.Log (row[colum]);
}
}
}
}
}