Hi,
I need to connect to a database and retrieve information and use them.
Project:
I need to do two Unity application for a client:
-
Touchscreen viewer of an hotel:
-
View the hotel in 3d, rotate around etc
-
A menu to see per floor with clickable rooms (All in GUIs). There are 2 types of floors, the third one (45 rooms), and 4th to 9th(33rooms / floors). Every information is generated dynamicaly (room number, name of the type of room, size, and page of the second menu).
-
A page per room(All in GUIs).
which display few information about the room and a cad. -
Just a Simple interface in Unity
this app is for my client to send information of the room online.
There are tree text fields and a tickbox. For: number of the room(int), name of the buyer(string), sold or not (tickbox)(bool).
I know I could do this with a mysql program or something else, but I’m really late on this project and I don’t have the time to learn that now (it’s for monday).
Problem:
In my first menu, when you click on a room I need to display if it is available and the name of the buyer. (same for menu 2)
To do that, I’ve got a bool sold or not and a switch between two textures (sold in red - available in green).
Everything else in the project is done…
So i’ve got a script send that I use 4 months ago to learn the interaction with de database. I had to stop learning beacuse of the amount of work and now I need to get back to it.
Can someone guide me to do this ? I’m already connected to my data base.
Here is the logic I had in mind:
Do a table with all the ap number from 301 to 345, 401-433, 501-533, …
and then when you open the menu it loads all the info and store them into variables.
The available/sold will be displayed, the buyer’s name will be displayed in a text field.
OR
Each time you click on an apartment to see his info, it load the specific row in the array. So imported gradualy
How can I do to read an Int ? to know which room is it.
How can I do to read a bool and export a bool? (itm.SoldOrNot = rdr[“SoldOrNot”].ToString(); with SoldOrNot a bool.
How can I do to write in a specific line (if in the Application 2 the client enter the room 407 informations, it needs to update this line and not another).
How can I change my script to read each line and store them ?
Do you have any ideas for that ?
Here is the script: (in two parts my message is too long)
It take the objects with a “Savable” tag and save their position etc…
using UnityEngine;
using MySql.Data;
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
public class MySQL : MonoBehaviour
{
// In truth, the only things you want to save to the database are dynamic objects
// static objects in the scene will always exist, so make sure to set your Tag
// based on the documentation for this demo
// values to match the database columns
string ID, Name, levelname, objectType;
float posx, posy, posz, tranx, trany, tranz;
bool saving = false;
bool loading = false;
// MySQL instance specific items
string constr = "Server=mysql5.dns-systems.net; Port=3307;Database=icreateonline_2;User ID=icreateonline_2;Password=boris7;Pooling=true";
// connection object
MySqlConnection con = null;
// command object
MySqlCommand cmd = null;
// reader object
MySqlDataReader rdr = null;
// error object
MySqlError er = null;
// object collection array
GameObject[] bodies;
// object definitions
public struct data
{
public int UID;
public string ID, Name, levelname, objectType;
public float posx, posy, posz, tranx, trany, tranz;
}
// collection container
List<data> _GameItems;
void Awake()
{
try
{
// setup the connection element
con = new MySqlConnection(constr);
// lets see if we can open the connection
con.Open();
Debug.Log("Connection State: " + con.State);
}
catch (Exception ex)
{
Debug.Log(ex.ToString());
}
}
void OnApplicationQuit()
{
Debug.Log("killing con");
if (con != null)
{
if (con.State != ConnectionState.Closed)
con.Close();
con.Dispose();
}
}
// Use this for initialization
void Start()
{
}
// Update is called once per frame
void Update()
{
}
// gui event like a button, etc
void OnGUI()
{
if (GUI.Button(new Rect(10, 70, 50, 30), "Save") !saving)
{
saving = true;
// first lets clean out the databae
DeleteEntries();
// now lets save the scene information
InsertEntries();
// you could also use the update if you know the ID of the item already saved
saving = false;
}
if (GUI.Button(new Rect(10, 110, 50, 30), "Load") !loading)
{
loading = true;
// lets read the items from the database
ReadEntries();
// now display what is known about them to our log
LogGameItems();
loading = false;
}
}
// Insert new entries into the table
void InsertEntries()
{
prepData();
string query = string.Empty;
// Error trapping in the simplest form
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 != ConnectionState.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 (Exception ex)
{
Debug.Log(ex.ToString());
}
finally
{
}
}