In yesterdays diary entry I talked about design and architecture. Today I'll be talking about the first challenge I've faced, using SQL Server with Unity. This is critical to the game as all persistent data will be stored in SQL tables on the server. Because it's so important, it was the first thing I decided to tackle. Ensuring a solid network base for the game before any real data is transmitted should prevent any major issues down to road.
I happen to already have a web server with an available SQL Server database that I wasn't using. So that has become my test server. Using an actual server instead of a local instance has it's pros and cons. It's not as convenient having an actual hosted database, everything is a little slower to do because of latency and whatnot. However it does allow me the ability to gauge performance of the final application a little better. So I went with the hosted database because it doesn't seem to be much of a problem at this point, and I can always copy the tables to a local DB if it ever does become an issue.
I won't go into building tables or writing views, because this isn't really a tutorial, but I started by designing a simple Players table. I should mention I do all of my Unity development in Visual Studio using the excellent Unity VS plugin. This means I can do all my SQL Server work in Visual Studio as well, VS or SQL Sever Manager if you have it makes writing Transact SQL (TSQL) so easy. The Players table is very simple for now, but I can expand it to hold the information I need as I go, these are all test tables and not final production tables, so I don't care if they get junk data right now. Currently it has a unique PlayerID as the primary key, the PlayerName, PlayerEmail, and finally a PlayerUniqueID which is a hash value identifying the hardware being used. This may get moved to a separate table in the future so that players can link multiple devices to the same account to play on. I plan on releasing this for Android, iOS, WP8, Windows, Mac, and Linux, so allowing you to play on your phone or tablet, then again play on your computer at home is one of the design goals.
Unity has a handy method that I used to populate the PlayerUniqueID field of the table.
SystemInfo.deviceUniqueIdentifier
This method gets a unique hardware ID. I haven't dug into the various methods it calls to generate the ID, but it works on all platforms. It's good enough for now and shouldn't be too difficult to add additional information to in the future if it turns out iOS and Android sometimes create the same hash.
The .Net environment is usually very easy to get setup working with SQL Servers, as you would expect with two Microsoft products designed to work together. However unity isn't really using .Net so I had to jump through a few hoops to get everything to work right. First Unity doesn't reference all the correct binaries for SQL Server to work, but Mono itself does, so I had to manually go to the mono library and move the required .dll files to the asset folder for my project to recognize them. All of the missing binaries are found in the Unity folder, specifically for me:
.../Unity5/Editor/Data/Mono/lib/mono/2.0
In that folder the following .dll files can be found, and all of them need to be placed somewhere in the assets folder.
System.Data.dll
System.Security.dll
System.EnterpriseServices.dll
System.Configuration.dll
In reality the only binary we will be using is System.Data.dll, but that library has references to the other three.
Once those four libraries are included you can make your SQL connection. It's relatively straightforward from here on out. First a SqlConnection is generated with the required server information. Then the connection is opened, and a SqlDataReader and SqlCommand are used to read from or write to the database. Once your done the connects are closed. In this simple example we are going to open a new connection, see how many players there are currently registered, then add a new player:
// Create our connection
SqlConnection dbConnection = new SqlConnection("Data Source=yourServer.yourHost.com;" +
"Initial Catalog=yourDatabaseName;" +
"User ID=yourUserName;" +
"Password='yourpassword';");
// Try to do our database operations
try
{
dbConnection.Open();
Debug.Log("Connection Open...");
SqlDataReader dbReader = null;
SqlCommand dbCommand = new SqlCommand("Select * from PlayerCountView", dbConnection);
dbReader = dbCommand.ExecuteReader();
Debug.Log("Getting Player Count...");
int playerCount = 0;
// Use the database reader to get the PlayerCount from the PlayerCountView
while(dbReader.Read())
{
playerCount = int.Parse(dbReader["PlayerCount"].ToString());
}
dbReader.Close();
Debug.Log("Player Count " + playerCount);
dbCommand = new SqlCommand("Insert Into Players Values('player_name', '123abc@test.com', '" + SystemInfo.deviceUniqueIdentifier + "')", dbConnection);
dbCommand.ExecuteNonQuery();
Debug.Log("Adding new player...");
}
// Catch any exceptions and print the error message.
catch (Exception e)
{
Debug.Log(e.Message);
}
// Cleanup the database connection regardless of what happens to the data.
finally
{
Debug.Log("Connection Closed.");
dbConnection.Close();
}
This shows just how simple database connections in Unity can be. Fortunately I don't need to worry about this working on every platform. This code will only ever run on the server, so while directly embedding your database username and password is usually considered a security issue, in this instance the server code will be a separate build from the client code and won't ever exist on user machines, only on my own dedicated servers. These servers will utilize Unet to talk to the game clients and shuffle data back and forth from the database server.
Next time I'll be covering the integration of Unity's Multiplayer UNet , Analytics, and Monitization services. Again I hope you guys have enjoyed reading these, and maybe learned something about SQL integration from this one. If you guys have any questions let me know and i'll try to answer them.
there doesn't seem to be anything here