using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using MySql.Data;
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Linq;
public class DataBaceTool : MonoBehaviour {
public MySqlConnection conn;
public void OpenDataBace()
{
string host = "172.20.3.204";
string id = "root";
string pwd = "hololens";
string database = "test";
string result = "";
string connectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd);
try
{
conn = new MySqlConnection(connectionString);
conn.Open();
result = conn.ServerVersion;
}
Debug.Log("打开成功 MySql版本" + result);
catch (Exception exc)
{
Debug.Log( "打开报错" + exc );
}
}
public void CloseDataBace()
{
try
{
conn.Close();
conn = null;
Debug.Log("关闭成功");
}
catch (Exception ex)
{
Debug.Log(ex.ToString() + "关闭报错");
}
}
//删除表,传入表的名称
public void ClearDataFromTable(string tableName)//删除表中数据
{
try
{
string sql = " delete from " + tableName + ";";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Close();
}
catch (MySqlException exc)
{
Debug.Log("删除失败" + exc);
}
}
//更改某个表中 某一列的状态tableName表名 flightNum这里是要更改flingtNum等于传进来的这个值(我的flightNum是唯一的,所以用flightNum查找),
//key 是要更改的哪个字段 value 更改为的值
public void UpdateDataFormTable(string tableName, string flightNum ,string key, string value)
{
try
{
string sql = "update " + tableName + " set " + key + "='" + value + "' " + "where " + "flightNum" + " ='" + flightNum + "';";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
print("修改成功");
}
catch (MySqlException)
{
print("修改失败");
}
}
//机场表插入输入
private void InsertAirportData(string TableName, List<Dictionary<string, object>> list)
{
//先清除一次 机场表
ClearDataFromTable("AirportManager");
for (int i = 0; i < list.Count; i++)
{
try
{
string sql = "insert into " + TableName + " (airportName,airportPos,id)values('" + list[i]["airportName"] + "','" + list[i]["airportPos"] + "','" + (i + 1) + "');";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Close();
print("机场信息储存成功");
}
catch (MySqlException)
{
print("机场信息已存在");
}
}
}
//查询表中所有数据
public List<Dictionary<string, object>> QueryCountFormTable(string tableName)
{
string queryCount = "select count(*) id from " + tableName + "; ";
MySqlCommand cmd1 = new MySqlCommand(queryCount, conn);
MySqlDataReader rdr1 = cmd1.ExecuteReader();
int count = 0;
while (rdr1.Read())
{
Debug.Log("=======count" + rdr1[0]);
int.TryParse(rdr1[0].ToString(), out count);
}
}
//查询表中所有数据
public List<Dictionary<string, object>> QueryAllDataFromTable(string tableName)
{
string sql = "select * from " + tableName ;
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
while (rdr.Read())
{
Dictionary<string, object> dic = new Dictionary<string, object>();
if (tableName == "AirportManager")
{
AddAirportTableData(dic, rdr);
}
list.Add(dic);
}
rdr.Close();
return list;
}
//获取表中指定列数据
public List<Dictionary<string, object>> QuerySpacificDataFromTable(string tableName, string key, string value)
{
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
string sql = "select * from " + tableName + " where " + key + "='" + value + "'; ";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
//Debug.Log( rdr[i]);
}
if (tableName == "RouteManager")
{
AddFlightData(dic, rdr);
}
list.Add(dic);
}
Debug.Log("list数量" + list.Count);
rdr.Close();
return list;
}
//获取单张表中列的数据
public Dictionary<string, object> QuerySingleDataFromTable(string tableName, string id)
{
string sql = "select * from " + tableName + " where id=" + id + "; ";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
Dictionary<string, object> dic = new Dictionary<string, object>();
while (rdr.Read())
{
if(tableName == "AirportManager")
{
AddAirportTableData(dic, rdr);
}
}
rdr.Close();
return dic;
}
//将Airport中所有列中查询的数据 添加到字典
private void AddAirportTableData(Dictionary<string,object> dic, MySqlDataReader rdr)
{
dic.Add("airportName", rdr.GetString(rdr.GetOrdinal("airportName")));
dic.Add("airportPos", rdr.GetString(rdr.GetOrdinal("airportPos")));
}
}
c# MySQL 一些语句
最新推荐文章于 2024-07-24 08:25:59 发布