Geekly Articles each Day
📜 ⬆️ ⬇️

Unity3D and MySQL


Today we will figure out how to connect the project on Unity3D to the MySQL database.


More specifically, we will analyze how to save the state of game objects in the database, and then load them from the database. The basis is taken by Jonathan Wood's guide for Unity 3.0.0f5 and MySQL 5.2.28. It is supplemented and adapted for the 4th version of Unity.

We will need:
1. Unity3D 4.x.
2. Access to MySQL database.
3. The library MySql.Data.dll.
')
In the project folder, you need to create the Libraries directory in which you need to place the library MySQL.Data.dll. You can download it from this archive , but if you already have MySQL installed, you can copy it from c: \ Program Files (x86) \ MySQL \ MySQL Connector Net 6.0.3 \ Assemblies \.

To avoid compatibility issues for build versions, as well as save yourself a few hours of time and nerves (thanks to Joel ), set the compatibility level API Edit -> Project Settings -> Player -> Optimization -> Api Compatibility Level = .NET 2.0.


Preliminary work:

1. State objects that need to be saved are tagged with the “Savable” tag.

2. Create a table in the database in which information about objects will be stored.

To use the new library, add to the top of the script:
using MySql.Data; using MySql.Data.MySqlClient;


And also do not forget to register the connection parameters in the variable:
  // MySQL  string constr = "Server=localhost;Database=demo;User ID=demo;Password=demo;Pooling=true;CharSet=utf8;";


Create a structure in which information about objects will be stored:
  //       string ID, Name, levelname, objectType; float posx, posy, posz, tranx, trany, tranz; //     public struct data { public int UID; public string ID, Name, levelname, objectType; public float posx, posy, posz, tranx, trany, tranz; } //          List<data> _GameItems;


At startup, the script connects to the database, and when it is turned off, it terminates the connection:
  void Awake() { try { //    con = new MySqlConnection(constr); // ,      con.Open(); Debug.Log("Connection State: " + con.State); } catch (IOException ex) {Debug.Log(ex.ToString());} } void OnApplicationQuit() { Debug.Log("killing con"); if (con != null) { // ,  : // if (con.State != ConnectionState.Closed) //  -        if (con.State.ToString()!="Closed") con.Close(); con.Dispose(); } }


There are two procedures responsible for working with objects on the scene:
prepDate - prepares data for writing to the database
 ///         void prepData() { bodies = GameObject.FindGameObjectsWithTag("Savable"); _GameItems = new List<data>(); data itm; foreach (GameObject body in bodies) { itm = new data(); itm.ID = body.name + "_" + body.GetInstanceID(); itm.Name = body.name; itm.levelname = Application.loadedLevelName; itm.objectType = body.name.Replace("(Clone)", ""); itm.posx = body.transform.position.x; itm.posy = body.transform.position.y; itm.posz = body.transform.position.z; itm.tranx = body.transform.rotation.x; itm.trany = body.transform.rotation.y; itm.tranz = body.transform.rotation.z; _GameItems.Add(itm); } Debug.Log("Items in collection: " + _GameItems.Count); } }

loadDate - loads objects into the scene
 ///        void loadData() { //       bodies = GameObject.FindGameObjectsWithTag("Savable"); DestroyObject(bodies); //      if (_GameItems != null) { if (_GameItems.Count > 0) { foreach (data itm in _GameItems) { if (itm.objectType="CUBE") {Instantiate(mCUBE, new Vector3(itm.posx, itm.posy , itm.posz),Quaternion.Euler(itm.tranx, itm.trany, itm.tranz));} else if (itm.objectType="SPHERE") {Instantiate(mSPHERE, new Vector3(itm.posx, itm.posy , itm.posz),Quaternion.Euler(itm.tranx, itm.trany, itm.tranz));} else if (itm.objectType="TRIANGLE") {Instantiate(mTRIANGLE, new Vector3(itm.posx, itm.posy , itm.posz),Quaternion.Euler(itm.tranx, itm.trany, itm.tranz));} } } } } }


As well as the four main procedures responsible for working with the database:
InsertEntries - insert a new entry into the table.
 //      void InsertEntries() { prepData(); string query = string.Empty; //   try { query = "INSERT INTO demo_table (ID, Name, levelname, objectType, posx, posy, posz, tranx, trany, tranz) VALUES (?ID, ?Name, ?levelname, ?objectType, ?posx, ?posy, ?posz, ?tranx, ?trany, ?tranz)"; if (con.State.ToString()!="Open") con.Open(); using (con) { foreach (data itm in _GameItems) { using (cmd = new MySqlCommand(query, con)) { MySqlParameter oParam = cmd.Parameters.Add("?ID", MySqlDbType.VarChar); oParam.Value = itm.ID; MySqlParameter oParam1 = cmd.Parameters.Add("?Name", MySqlDbType.VarChar); oParam1.Value = itm.Name; MySqlParameter oParam2 = cmd.Parameters.Add("?levelname", MySqlDbType.VarChar); oParam2.Value = itm.levelname; MySqlParameter oParam3 = cmd.Parameters.Add("?objectType", MySqlDbType.VarChar); oParam3.Value = itm.objectType; MySqlParameter oParam4 = cmd.Parameters.Add("?posx", MySqlDbType.Float); oParam4.Value = itm.posx; MySqlParameter oParam5 = cmd.Parameters.Add("?posy", MySqlDbType.Float); oParam5.Value = itm.posy; MySqlParameter oParam6 = cmd.Parameters.Add("?posz", MySqlDbType.Float); oParam6.Value = itm.posz; MySqlParameter oParam7 = cmd.Parameters.Add("?tranx", MySqlDbType.Float); oParam7.Value = itm.tranx; MySqlParameter oParam8 = cmd.Parameters.Add("?trany", MySqlDbType.Float); oParam8.Value = itm.trany; MySqlParameter oParam9 = cmd.Parameters.Add("?tranz", MySqlDbType.Float); oParam9.Value = itm.tranz; cmd.ExecuteNonQuery(); } } } } catch (IOException ex) { Debug.Log(ex.ToString()); } finally {} }

UpdateEntries- update existing entries in the table.
  //      void UpdateEntries() { prepData(); string query = string.Empty; //   try { query = "UPDATE demo_table SET ID=?ID, Name=?Name, levelname=?levelname, objectType=?objectType, posx=?posx, posy=?posy, posz=?posz, tranx=?tranx, trany=?trany, tranz=?tranz WHERE iddemo_table=?UID"; if (con.State.ToString()!="Open") con.Open(); using (con) { foreach (data itm in _GameItems) { using (cmd = new MySqlCommand(query, con)) { MySqlParameter oParam = cmd.Parameters.Add("?ID", MySqlDbType.VarChar); oParam.Value = itm.ID; MySqlParameter oParam1 = cmd.Parameters.Add("?Name", MySqlDbType.VarChar); oParam1.Value = itm.Name; MySqlParameter oParam2 = cmd.Parameters.Add("?levelname", MySqlDbType.VarChar); oParam2.Value = itm.levelname; MySqlParameter oParam3 = cmd.Parameters.Add("?objectType", MySqlDbType.VarChar); oParam3.Value = itm.objectType; MySqlParameter oParam4 = cmd.Parameters.Add("?posx", MySqlDbType.Float); oParam4.Value = itm.posx; MySqlParameter oParam5 = cmd.Parameters.Add("?posy", MySqlDbType.Float); oParam5.Value = itm.posy; MySqlParameter oParam6 = cmd.Parameters.Add("?posz", MySqlDbType.Float); oParam6.Value = itm.posz; MySqlParameter oParam7 = cmd.Parameters.Add("?tranx", MySqlDbType.Float); oParam7.Value = itm.tranx; MySqlParameter oParam8 = cmd.Parameters.Add("?trany", MySqlDbType.Float); oParam8.Value = itm.trany; MySqlParameter oParam9 = cmd.Parameters.Add("?tranz", MySqlDbType.Float); oParam9.Value = itm.tranz; cmd.ExecuteNonQuery(); } } } } catch (IOException ex) {Debug.Log(ex.ToString());} finally {} }

DeleteEntries - delete records from the table.
 //     void DeleteEntries() { string query = string.Empty; //   try { //      ID ,    //----------------------------------------------------------------------- // query = "DELETE FROM demo_table WHERE iddemo_table=?UID"; // MySqlParameter oParam = cmd.Parameters.Add("?UID", MySqlDbType.Int32); // oParam.Value = 0; //----------------------------------------------------------------------- query = "DELETE FROM demo_table WHERE iddemo_table"; if (con.State.ToString()!="Open") con.Open(); using (con) { using (cmd = new MySqlCommand(query, con)) { cmd.ExecuteNonQuery(); } } } catch (IOException ex) {Debug.Log(ex.ToString());} finally {} }

ReadEntries- read all records from the table.
 //      void ReadEntries() { string query = string.Empty; if (_GameItems == null) _GameItems = new List<data>(); if (_GameItems.Count > 0) _GameItems.Clear(); //   try { query = "SELECT * FROM view_demo"; if (con.State.ToString()!="Open") con.Open(); using (con) { using (cmd = new MySqlCommand(query, con)) { rdr = cmd.ExecuteReader(); if(rdr.HasRows) while (rdr.Read()) { data itm = new data(); itm.UID = int.Parse(rdr["iddemo_table"].ToString()); itm.ID = rdr["ID"].ToString(); itm.levelname = rdr["levelname"].ToString(); itm.Name = rdr["Name"].ToString(); itm.objectType = rdr["objectType"].ToString(); itm.posx = float.Parse(rdr["posx"].ToString()); itm.posy = float.Parse(rdr["posy"].ToString()); itm.posz = float.Parse(rdr["posz"].ToString()); itm.tranx = float.Parse(rdr["tranx"].ToString()); itm.trany = float.Parse(rdr["trany"].ToString()); itm.tranz = float.Parse(rdr["tranz"].ToString()); _GameItems.Add(itm); } rdr.Dispose(); } } } catch (IOException ex) {Debug.Log(ex.ToString());} finally {} }



It remains only to make a couple of buttons to load and save the scene:
  //     void OnGUI() { if (GUI.Button(new Rect(10, 70, 50, 30), "Save") && !saving) { saving = true; //     DeleteEntries(); //      InsertEntries(); //       ID    saving = false; } if (GUI.Button(new Rect(10, 110, 50, 30), "Load") && !loading) { loading = true; //     ReadEntries(); //    loadData(); //      LogGameItems(); loading = false; } }


But in general, that's all. Comments and additions are welcome in the comments.

Whole script
 using UnityEngine; using MySql.Data; using MySql.Data.MySqlClient; using System.IO; using System.Collections; using System.Collections.Generic; public class MySQLCS : MonoBehaviour { public GameObject mCUBE; public GameObject mSPHERE; public GameObject mTRIANGLE; bool saving = false; bool loading = false; // MySQL  string constr = "Server=localhost;Database=demo;User ID=demo;Password=demo;Pooling=true;CharSet=utf8;"; //  MySqlConnection con = null; //    MySqlCommand cmd = null; //  MySqlDataReader rdr = null; //  MySqlError er = null; //     GameObject[] bodies; //       string ID, Name, levelname, objectType; float posx, posy, posz, tranx, trany, tranz; //     public struct data { public int UID; public string ID, Name, levelname, objectType; public float posx, posy, posz, tranx, trany, tranz; } //          List<data> _GameItems; void Awake() { try { //    con = new MySqlConnection(constr); // ,      con.Open(); Debug.Log("Connection State: " + con.State); } catch (IOException ex) {Debug.Log(ex.ToString());} } void OnApplicationQuit() { Debug.Log("killing con"); if (con != null) { // ,  : // if (con.State != ConnectionState.Closed) //  -        if (con.State.ToString()!="Closed") con.Close(); con.Dispose(); } } // Use this for initialization void Start() { } // Update is called once per frame void Update() { } //     void OnGUI() { if (GUI.Button(new Rect(10, 70, 50, 30), "Save") && !saving) { saving = true; //     DeleteEntries(); //      InsertEntries(); //       ID    saving = false; } if (GUI.Button(new Rect(10, 110, 50, 30), "Load") && !loading) { loading = true; //     ReadEntries(); //    loadData(); //      LogGameItems(); loading = false; } } //      void InsertEntries() { prepData(); string query = string.Empty; //   try { query = "INSERT INTO demo_table (ID, Name, levelname, objectType, posx, posy, posz, tranx, trany, tranz) VALUES (?ID, ?Name, ?levelname, ?objectType, ?posx, ?posy, ?posz, ?tranx, ?trany, ?tranz)"; if (con.State.ToString()!="Open") con.Open(); using (con) { foreach (data itm in _GameItems) { using (cmd = new MySqlCommand(query, con)) { MySqlParameter oParam = cmd.Parameters.Add("?ID", MySqlDbType.VarChar); oParam.Value = itm.ID; MySqlParameter oParam1 = cmd.Parameters.Add("?Name", MySqlDbType.VarChar); oParam1.Value = itm.Name; MySqlParameter oParam2 = cmd.Parameters.Add("?levelname", MySqlDbType.VarChar); oParam2.Value = itm.levelname; MySqlParameter oParam3 = cmd.Parameters.Add("?objectType", MySqlDbType.VarChar); oParam3.Value = itm.objectType; MySqlParameter oParam4 = cmd.Parameters.Add("?posx", MySqlDbType.Float); oParam4.Value = itm.posx; MySqlParameter oParam5 = cmd.Parameters.Add("?posy", MySqlDbType.Float); oParam5.Value = itm.posy; MySqlParameter oParam6 = cmd.Parameters.Add("?posz", MySqlDbType.Float); oParam6.Value = itm.posz; MySqlParameter oParam7 = cmd.Parameters.Add("?tranx", MySqlDbType.Float); oParam7.Value = itm.tranx; MySqlParameter oParam8 = cmd.Parameters.Add("?trany", MySqlDbType.Float); oParam8.Value = itm.trany; MySqlParameter oParam9 = cmd.Parameters.Add("?tranz", MySqlDbType.Float); oParam9.Value = itm.tranz; cmd.ExecuteNonQuery(); } } } } catch (IOException ex) { Debug.Log(ex.ToString()); } finally {} } //      void UpdateEntries() { prepData(); string query = string.Empty; //   try { query = "UPDATE demo_table SET ID=?ID, Name=?Name, levelname=?levelname, objectType=?objectType, posx=?posx, posy=?posy, posz=?posz, tranx=?tranx, trany=?trany, tranz=?tranz WHERE iddemo_table=?UID"; if (con.State.ToString()!="Open") con.Open(); using (con) { foreach (data itm in _GameItems) { using (cmd = new MySqlCommand(query, con)) { MySqlParameter oParam = cmd.Parameters.Add("?ID", MySqlDbType.VarChar); oParam.Value = itm.ID; MySqlParameter oParam1 = cmd.Parameters.Add("?Name", MySqlDbType.VarChar); oParam1.Value = itm.Name; MySqlParameter oParam2 = cmd.Parameters.Add("?levelname", MySqlDbType.VarChar); oParam2.Value = itm.levelname; MySqlParameter oParam3 = cmd.Parameters.Add("?objectType", MySqlDbType.VarChar); oParam3.Value = itm.objectType; MySqlParameter oParam4 = cmd.Parameters.Add("?posx", MySqlDbType.Float); oParam4.Value = itm.posx; MySqlParameter oParam5 = cmd.Parameters.Add("?posy", MySqlDbType.Float); oParam5.Value = itm.posy; MySqlParameter oParam6 = cmd.Parameters.Add("?posz", MySqlDbType.Float); oParam6.Value = itm.posz; MySqlParameter oParam7 = cmd.Parameters.Add("?tranx", MySqlDbType.Float); oParam7.Value = itm.tranx; MySqlParameter oParam8 = cmd.Parameters.Add("?trany", MySqlDbType.Float); oParam8.Value = itm.trany; MySqlParameter oParam9 = cmd.Parameters.Add("?tranz", MySqlDbType.Float); oParam9.Value = itm.tranz; cmd.ExecuteNonQuery(); } } } } catch (IOException ex) {Debug.Log(ex.ToString());} finally {} } //     void DeleteEntries() { string query = string.Empty; //   try { //      ID ,    //----------------------------------------------------------------------- // query = "DELETE FROM demo_table WHERE iddemo_table=?UID"; // MySqlParameter oParam = cmd.Parameters.Add("?UID", MySqlDbType.Int32); // oParam.Value = 0; //----------------------------------------------------------------------- query = "DELETE FROM demo_table WHERE iddemo_table"; if (con.State.ToString()!="Open") con.Open(); using (con) { using (cmd = new MySqlCommand(query, con)) { cmd.ExecuteNonQuery(); } } } catch (IOException ex) {Debug.Log(ex.ToString());} finally {} } //      void ReadEntries() { string query = string.Empty; if (_GameItems == null) _GameItems = new List<data>(); if (_GameItems.Count > 0) _GameItems.Clear(); //   try { query = "SELECT * FROM view_demo"; if (con.State.ToString()!="Open") con.Open(); using (con) { using (cmd = new MySqlCommand(query, con)) { rdr = cmd.ExecuteReader(); if(rdr.HasRows) while (rdr.Read()) { data itm = new data(); itm.UID = int.Parse(rdr["iddemo_table"].ToString()); itm.ID = rdr["ID"].ToString(); itm.levelname = rdr["levelname"].ToString(); itm.Name = rdr["Name"].ToString(); itm.objectType = rdr["objectType"].ToString(); itm.posx = float.Parse(rdr["posx"].ToString()); itm.posy = float.Parse(rdr["posy"].ToString()); itm.posz = float.Parse(rdr["posz"].ToString()); itm.tranx = float.Parse(rdr["tranx"].ToString()); itm.trany = float.Parse(rdr["trany"].ToString()); itm.tranz = float.Parse(rdr["tranz"].ToString()); _GameItems.Add(itm); } rdr.Dispose(); } } } catch (IOException ex) {Debug.Log(ex.ToString());} finally {} } ///          void LogGameItems() { if (_GameItems != null) { if (_GameItems.Count > 0) { foreach (data itm in _GameItems) { Debug.Log("UID: " + itm.UID); Debug.Log("ID: " + itm.ID); Debug.Log("levelname: " + itm.levelname); Debug.Log("Name: " + itm.Name); Debug.Log("objectType: " + itm.objectType); Debug.Log("posx: " + itm.posx); Debug.Log("posy: " + itm.posy); Debug.Log("posz: " + itm.posz); Debug.Log("tranx: " + itm.tranx); Debug.Log("trany: " + itm.trany); Debug.Log("tranz: " + itm.tranz); } } } } ///         void prepData() { bodies = GameObject.FindGameObjectsWithTag("Savable"); _GameItems = new List<data>(); data itm; foreach (GameObject body in bodies) { itm = new data(); itm.ID = body.name + "_" + body.GetInstanceID(); itm.Name = body.name; itm.levelname = Application.loadedLevelName; itm.objectType = body.name.Replace("(Clone)", ""); itm.posx = body.transform.position.x; itm.posy = body.transform.position.y; itm.posz = body.transform.position.z; itm.tranx = body.transform.rotation.x; itm.trany = body.transform.rotation.y; itm.tranz = body.transform.rotation.z; _GameItems.Add(itm); } Debug.Log("Items in collection: " + _GameItems.Count); } } ///        void loadData() { //       bodies = GameObject.FindGameObjectsWithTag("Savable"); DestroyObject(bodies); //      if (_GameItems != null) { if (_GameItems.Count > 0) { foreach (data itm in _GameItems) { if (itm.objectType="CUBE") {Instantiate(mCUBE, new Vector3(itm.posx, itm.posy , itm.posz),Quaternion.Euler(itm.tranx, itm.trany, itm.tranz));} else if (itm.objectType="SPHERE") {Instantiate(mSPHERE, new Vector3(itm.posx, itm.posy , itm.posz),Quaternion.Euler(itm.tranx, itm.trany, itm.tranz));} else if (itm.objectType="TRIANGLE") {Instantiate(mTRIANGLE, new Vector3(itm.posx, itm.posy , itm.posz),Quaternion.Euler(itm.tranx, itm.trany, itm.tranz));} } } } } }

Source: https://habr.com/ru/post/185594/

More articles:


All Articles