前言:这几天用Unity3d与Mysql制作了一个汽车租赁系统,enmmm(无奈之举),里面有用到Mysql的一些操作,因为自己不常用,所以就记不住,所以索性这次记录一下。
- 准备
需要在UnityProject里新建一个文件夹,命名:Plugins,下一步就是有两个文件需要放进去,这里放一下链接
链接:https://pan.baidu.com/s/1v3CuVxlee8ckpJKrxKSFcw
提取码:502c
<这里的dll文件Unity2017可以用,其他版本暂时没试过。。>
- 代码部分<1>
- <连接>
public static MySqlConnection mysqlConnection; private static string database = "Test1";//数据库名 private static string host = "127.0.0.1";//本地 private static string id = "root";//用户名 private static string pwd = "123456";//密码 public void Init() { string sqlString = string.Format("Database={0};Data Source={1};User Id= {2};Password={3};", database, host, id, pwd, "3306"); mysqlConnection = new MySqlConnection(sqlString); }
- 增
public void Insert(string tablename,string str1Name,string str1) { mysqlConnection.Open(); string sql =string.Format("insert into {0} set {1}=@a", tablename,str1Name); MySqlCommand command = new MySqlCommand(sql, mysqlConnection); command.Parameters.AddWithValue("a", str1);//将sql语句中的a置换成str1,可能你会问,为啥不直接format,因为在sql语句中,有时候要加'',所以用Parameters更保险. return command.ExecuteReader(); mysqlConnection.Close(); }
- 删
public void Delete(string tablename,string str1Name,string str1) { mysqlConnection.Open(); string sql=string.Format("delete from {0} where {1}=@a",tablename,str1Name); MySqlCommand cmd = new MySqlCommand(sql,mysqlConnection); cmd.Parameters.AddWithValue("a", str1); cmd.ExecuteNonQuery(); mysqlConnection.Close(); }
- 改
public void Delete(string tablename,string str1Name,string str1,string str2Name,string str2) { mysqlConnection.Open(); string sql=string.Format("update {0} set {1}=@a where {2}=@b",tablename,strName,str2Name); MySqlCommand cmd = new MySqlCommand(sql,mysqlConnection); cmd.Parameters.AddWithValue("a", str1); cmd.Parameters.AddWithValue("b", str2); cmd.ExecuteNonQuery(); mysqlConnection.Close(); }
- 查
public MySqlDataReader Select(string tablename,string str1Name,string str1) { string sql =string.Format("select * from {0} where {1}=@u", tablename,str1Name); MySqlCommand command = new MySqlCommand(sql, mysqlConnection); command.Parameters.AddWithValue("u", str1); return command.ExecuteReader(); }
- 代码部分<2>
- PS
- 代码部分1主要是四种操作,查询的返回值类型是MySqlDataReader类,那么他怎么获取数据呢?
下面这一段是获取数据库表carinfo里的数据用来生成car实体。
public List<Car> GetCarList() { mysqlConnection.Open(); MySqlDataReader reader = Select("carinfo", "state_car", "Idle"); List<Car> carList = new List<Car>(); while (reader.Read()) { carList.Add(new Car(reader.GetInt32("id_car"), reader.GetString("vendor_car"), reader.GetString("model_car"), reader.GetInt32("money_car"))); } mysqlConnection.Close(); return carList; }
- 好像忘了一点哦,引入命名空间不要忘了哦
using MySql.Data.MySqlClient; using System.Data;