What if you want to connect your unity project and connect it to your database.
For instance, you have a set of users and a set of skills associated to each user, everything stored in the database.
First, you need to create an empty GameObject in your Unity Scene and then attach a script to this object. This will allow you to create all the visuals from the script without having to drag and drop. It may also be very beneficial if you have a dynamic list of visuals whose exact number is defined by the number of entries in your database.
Then, inside this script create a user class.
// init class for User (that is shown on the cube)
public class User
{
public string Name { get; set; }
public string About { get; set; }
public List<Skill> Skills { get; set; }
public User(string Name, string About, List<Skill> Skills)
{
this.Name = Name;
this.About = About;
this.Skills = Skills;
}
public override string ToString()
{
return "Person: " + this.Name + " About me: " + this.About;
}
}And a skilll class:
// init class for skill to create an array list of skills for a user
public class Skill
{
public string Name { get; set; }
public Skill(string Name)
{
this.Name = Name;
}
}And here is the main code. Create a method that connects to the database and generates a list of users.
// function to connect to the db and the users list
List<User> ConnectToDB()
{
List<User> users = new List<User>();
// Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "<sql server address>";
builder.UserID = "<login>";
builder.Password = "<password>";
builder.InitialCatalog = "<databases>";
try
{
// connect to the databases
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
// if open then the connection is established
connection.Open();
Debug.Log("connection established");
// sql command
string sql = "SELECT MAX(u.[Name]), " +
"MAX(u.[AboutMe]), " +
"MAX(u.[UserPrincipalName]), " +
"string_agg(s.[Name], ', '), " +
"u.Id FROM [dbo].[Users] u " +
"inner join [dbo].[UserSkills] us " +
"on us.UserId = u.Id " +
"inner join [dbo].[Skills] s " +
"on us.SkillId = s.Id " +
"group by u.Id";
// execute sql command
using (SqlCommand command = new SqlCommand(sql, connection))
{
// read
using (SqlDataReader reader = command.ExecuteReader())
{
// each line in the output
while (reader.Read())
{
// to avoid SqlNullValueException
if (!reader.IsDBNull(0)
& !reader.IsDBNull(1)
& !reader.IsDBNull(3))
{
// Skills list to be attached to each user object
List<Skill> skills = new List<Skill>();
// get output parameters
string username = reader.GetString(0);
string aboutString = reader.GetString(1);
string skillsString = reader.GetString(3);
// as we are getting a list of skills as
// a single string delimited by comma
// we split the string
string[] skillsList = skillsString.Split(',');
// we now iterate through each skill to initialize our
// skill object and put it into skills list
foreach (string skillName in skillsList)
{
// initialize a skill object with a trimmed string
Skill skill = new Skill(skillName.Trim());
// append to the skills array
skills.Add(skill);
}
// initialize User oobject
User user = new User(username.Trim(), aboutString.Trim(), skills);
users.Add(user);
}
}
}
}
}
}
catch (SqlException e)
{
Debug.Log(e.ToString());
}
return users;
}The rest is quite straightforward : inside the Start() routine initialize your users lists
// Use this for initialization
void Start ()
{
// initialize global users array
users = ConnectToDB();
}And inside say, the OnGUI () routine create a label for each user.
void OnGUI()
{
int i = 0;
// create a cube for each user
foreach (User user in users)
{
Rect position = new Rect(i * 20, i * 20, 100, 20);
GUI.Label(position, user.Name);
i++;
}
}Here is the complete code
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.Data.SqlClient;
using UnityEngine.UI;
public class GenerateLabels : MonoBehaviour {
// after the sql query is executed we will have a filled users array
List users = new List();
// Use this for initialization
void Start ()
{
// initialize global users array
users = ConnectToDB();
}
// Update is called once per frame
void Update ()
{
}
void OnGUI()
{
int i = 0;
// create a cube for each user
foreach (User user in users)
{
Rect position = new Rect(i * 20, i * 20, 100, 20);
GUI.Label(position, user.Name);
i++;
}
}
// function to connect to the db and the users list
List ConnectToDB()
{
List users = new List();
// Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "powermatch.database.windows.net";
builder.UserID = "vm-spadm";
builder.Password = "expert@78";
builder.InitialCatalog = "PowerMatchDB";
try
{
// connect to the databases
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
// if open then the connection is established
connection.Open();
Debug.Log("connection established");
// sql command
string sql = "SELECT MAX(u.[Name]), " +
"MAX(u.[AboutMe]), " +
"MAX(u.[UserPrincipalName]), " +
"string_agg(s.[Name], ', '), " +
"u.Id FROM [dbo].[Users] u " +
"inner join [dbo].[UserSkills] us " +
"on us.UserId = u.Id " +
"inner join [dbo].[Skills] s " +
"on us.SkillId = s.Id " +
"group by u.Id";
// execute sql command
using (SqlCommand command = new SqlCommand(sql, connection))
{
// read
using (SqlDataReader reader = command.ExecuteReader())
{
// each line in the output
while (reader.Read())
{
// to avoid SqlNullValueException
if (!reader.IsDBNull(0)
& !reader.IsDBNull(1)
& !reader.IsDBNull(3))
{
// Skills list to be attached to each user object
List skills = new List();
// get output parameters
string username = reader.GetString(0);
string aboutString = reader.GetString(1);
string skillsString = reader.GetString(3);
// as we are getting a list of skills as
// a single string delimited by comma
// we split the string
string[] skillsList = skillsString.Split(',');
// we now iterate through each skill to initialize our
// skill object and put it into skills list
foreach (string skillName in skillsList)
{
// initialize a skill object with a trimmed string
Skill skill = new Skill(skillName.Trim());
// append to the skills array
skills.Add(skill);
}
// initialize User oobject
User user = new User(username.Trim(), aboutString.Trim(), skills);
users.Add(user);
}
}
}
}
}
}
catch (SqlException e)
{
Debug.Log(e.ToString());
}
return users;
}
}
// init class for skill to create an array list of skills for a user
public class Skill
{
public string Name { get; set; }
public Skill(string Name)
{
this.Name = Name;
}
}
// init class for User (that is shown on the cube)
public class User
{
public string Name { get; set; }
public string About { get; set; }
public List Skills { get; set; }
public User(string Name, string About, List Skills)
{
this.Name = Name;
this.About = About;
this.Skills = Skills;
}
public override string ToString()
{
return "Person: " + this.Name + " About me: " + this.About;
}
}
Link to github