Unity SQL Database !

I am working on database for web based multi-player game. I have kept the System.Data.dll in the game assets folder with following c# script at same location (inside game Assets folder). Do I need any more file/files or some other script to be written or added to perform database connection?

I am using SQL server 2008 R2 for database. I am not getting the connection established.

My database is a "bank" with columns as in the foreach loop in below C# class.

DataAccess.CS
-------------------------
using UnityEngine;
using System.Collections;
using System;
using System.Data;
using System.Data.SqlClient;

class DataAccess : MonoBehaviour
{
public string dollars;
public string points;

//This is database connection:
static string connectionString = "data source=DEVILSZONE.SQLEXPRESS;initial catalog=game;uid=Sa;pwd=;";

//string connectionString = "Server=localhost,1433;" + "Database=game;" +
"User ID=citm;" +
"Password=citm;" +
"Pooling=false;" ;
//static string connectionString = "data source=DEVILSZONE;initial catalog=Safariville;integrated security=True;";

static SqlConnection cn = new SqlConnection(connectionString);

// This is your command to execute:
static string sCommand = "SELECT * FROM dbo.bank";

// This is your data adapter that understands SQL databases:
static SqlDataAdapter da = new SqlDataAdapter(sCommand, cn);

// This is your table to hold the result set:
static DataTable dataTable = new DataTable();

void Start()
{
cn.Open();

// Fill the data table with select statement's query results:
int recordsAffected = da.Fill(dataTable);

if (recordsAffected > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
dollars = dr["SCash"].ToString();
points = dr["APoints"].ToString();
}
}
}
}

I wish to read these values from this c# script into a javascript where its displaying the values.

But the database connection is not getting established, I feel the connectionString is not proper. Can some one pls help me with this? I have tried three options of connectionString (one uncommented and others commented in above code).

If I am using this
string connectionString = "Server=localhost,1433;" +
"Database=;" +
"User ID=citm;" +
"Password=citm;" +
"Pooling=false;" + "data source = DEVILSZONE";

I have tried with DNS: 192.168.xxxx,1433 or IP addr of my machine but no luck

do let me know if any more information is needed.

URGENT

 

Not sure about SQL Server linking to Unity but I think the dll's etc need to be in a directory called 'Plugins' within your Assets directory, not just anywhere or within the root of Assets... have you done this?

Regards,
Matt.

 

yes did it ....

its just System.Data.dll and SQLDatabase.CS . Any more files to be included, as in any other lib for SQL ?

cheers,
Sandeep

 

Oops ! it is... DataAccess.CS

no luck still.....

Cheers,
Sandeep

 

Try this ...

using UnityEngine;
using System;
using System.Collections;
using System.Data;
using System.Data.Sql
using System.Data.SqlClient;

class DataAccess : MonoBehaviour
{
public string dollars;
public string points;

//This is database connection:
static string connectionString ="Server=localhost;Database=game;User ID=sa;Password=;";


static SqlConnection cn = null;

// This is your command to execute:
static string sCommand = "SELECT * FROM dbo.bank";

// This is your data adapter that understands SQL databases:
static SqlDataAdapter da = null;

// This is your table to hold the result set:
static DataTable dataTable = new DataTable();

void Start()
{
try
{
using( cn = new SqlConnection( connectionString ) )
{
cn.Open();
PopulateDataTable();
cn.Close;
}//using

}//try
catch( Exception e )
{
Debug.Log( "Data Connection failed " + e );

}// catch

}// Start()


// Fill the data table with select statement's query results:
void PopulateDataTable()
{
// your data collection here

}// end PopulateDataTable()


If you are not connecting post the error message

 

using System.Data.SqlClient;

shudn't this be Mono.Data.SqliteClient.dll ? just wondering as i dont have other lib in my unity installation folders.

Sandeep

 

Error is :

(Filename: Assets/Plugins/DataAccess.CS Line: 65)

Updating assets/plugins/dataaccess.cs - GUID: f3e578cbdab186643a08463e9c1ced66...
Calculating changed assemblies
End Calculating changed assemblies
- starting compile Library/ScriptAssemblies/Assembly - CSharp - first pass.dll
done: hash - eda817800ee30dcea4623eb032ab0a1f
- Finished compile Library/ScriptAssemblies/Assembly - CSharp - first pass.dll
Assets/Plugins/DataAccess.CS(19,15): warning CS0414: The private field `DataAccess.sCommand' is assigned but its value is never used

(Filename: Assets/Plugins/DataAccess.CS Line: 19)

Calculating changed assemblies
End Calculating changed assemblies
- starting compile Library/ScriptAssemblies/Assembly - CSharp.dll
- starting compile Library/ScriptAssemblies/Assembly - UnityScript.dll
- Finished compile Library/ScriptAssemblies/Assembly - CSharp.dll
Calculating changed assemblies
End Calculating changed assemblies
- Finished compile Library/ScriptAssemblies/Assembly - UnityScript.dll
Calculating changed assemblies
End Calculating changed assemblies
Mono: successfully reloaded assembly
Mono: successfully reloaded assembly
Reload assembly time: 0.445240
------------------------------
NullReferenceException: Object reference not set to an instance of an object
UnityEngine.GUILayoutUtility.EndLayoutGroup ()
UnityEngine.GUILayout.EndHorizontal ()
UnityEditor.ConsoleWindow.OnGUI ()
System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000]

(Filename: Line: -1)
-------------------------------

the above marked between lines, i get only after i click on "open editor log" button on console. Till then rest all comes as error.

 

Try to put those dll files outside the ASSETs folder please. Hope it works, else just come back here and form us about what happens.

 

get below error after doing above change....


Mono: successfully reloaded assembly
Refresh: trashing asset 45fa0370837d4ba479bba47688f08c63
Refresh: trashing asset 59f20551ee1fae345b5542c5da9e15ca
Refresh: trashing asset d2653f78e9741e743a0d2740b0ae11ab
Refresh: trashing asset 7a033ad84b0fe714c97d4af299c4012b
Refresh: trashing asset f3e578cbdab186643a08463e9c1ced66
Refresh: trashing asset 52e7540dac5965d4294df9e5422379ac
Updating assets - GUID: 00000000000000001000000000000000...
done: hash - b3795fd68ab4f27992613b8acfafba21
Updating library/editorbuildsettings.asset - GUID: 0000000000000000b000000000000000...
done: hash - 4a25f167fea1484ab870733407c61fcb
Updating library/networkmanager.asset - GUID: 0000000000000000a000000000000000...
done: hash - 6d8cbd47ad5a9022a015765b686c1053
Updating library/buildplayer.prefs - GUID: 00000000000000005000000000000000...
done: hash - 03f71213fbc619fdf2c5bfbbd1780f60
Updating library/projectsettings.asset - GUID: 00000000000000004000000000000000...
done: hash - 98f9c8530b3b772e83e8832c025849a1
Updating library/qualitysettings.asset - GUID: 00000000000000009000000000000000...
done: hash - ea5ccef6d60594380a78c9e34a02eeb8
Updating library/tagmanager.asset - GUID: 00000000000000003000000000000000...
done: hash - d9c8727117cb36fef37b9b91dd771d56
Updating library/editorsettings.asset - GUID: 0000000000000000c000000000000000...
done: hash - be195a9d98d55caa8f41b0fdfb0bbec5
Updating library/dynamicsmanager.asset - GUID: 00000000000000008000000000000000...
done: hash - 0a202696f9c21bf69324c8212f612d15
Updating library/audiomanager.asset - GUID: 00000000000000006000000000000000...
done: hash - 9fb9c957e6a1da942770963c32de23b1
Updating library/inputmanager.asset - GUID: 00000000000000002000000000000000...
done: hash - f49bcd0a7aabbee02776e8c02f1a9975
Updating library/timemanager.asset - GUID: 00000000000000007000000000000000...
done: hash - 1456d9aea04a8533d696f7a7b233b9c6
Calculating changed assemblies
End Calculating changed assemblies
Calculating changed assemblies
End Calculating changed assemblies
- starting compile Library/ScriptAssemblies/Assembly - CSharp.dll
- starting compile Library/ScriptAssemblies/Assembly - UnityScript.dll
- Finished compile Library/ScriptAssemblies/Assembly - CSharp.dll
Calculating changed assemblies
End Calculating changed assemblies
- Finished compile Library/ScriptAssemblies/Assembly - UnityScript.dll
Calculating changed assemblies
End Calculating changed assemblies
Mono: successfully reloaded assembly
Mono: successfully reloaded assembly
Reload assembly time: 0.447297
NullReferenceException: Object reference not set to an instance of an object
UnityEngine.GUILayoutUtility.EndLayoutGroup ()
UnityEngine.GUILayout.EndHorizontal ()
UnityEditor.ConsoleWindow.OnGUI ()
System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000]

(Filename: Line: -1)

 

Use only "System.Data.dll" from the Unity. You will find it under 'framework' folder. Copy and past it inside your 'Asset' folder of your project. That should be the one and only dll for SQL. Nothing from mono.

 

thanks Sundar, I have been trying with only System.Data.dll till now ....no luck

 

Is this file from mono or Unity? I mean if you have a mono installation in your system other than Unity's and you have used its System.Data.dll then it won't work with Unity. Use only that come with Unity. You can also try reinstalling Unity with its current version. Replace new dll in Asset folder and see what happens.

 

I got this file from this location on my system.

C:\Unity3D\Editor\Data\Frameworks\Mono.framework

Is this not the one to be used ?

 

can someone provide me with correct System.data.dll for UNity version 2.6.2f . I just reinstalled it on my system. No luck....my unity crashes abruptly when I try to click on any bank button on the screen.

Is there any other place too where I can find logs after unity crahses ?

 

Here is the System.Data.dll that you have requested. Good luck

 

sundar, no luck again.

Is there any config to be done at SQL side while unity tries to access SQL server. What I did was ,created new ODBC datasource and using that I am connecting from .cs script in unity.

I open SQL server 2008 R2 management studio and connect to my database using database engine. Then I can see database connected shown on left hand side.

Am I missing on something that needs to be done ?

Sandeep

 

The error you are getting is Unity compilation output. Can you post the sanp shot of your console window with all error message in. As you have not passed Unity to work on your SQL, we will go one step at a time.

 

I do not get any compilation error of my script on console but upon clicking on editor logs, i get following error on console, not sure if this is what you want ?

NullReferenceException: Object reference not set to an instance of an object
UnityEngine.GUILayoutUtility.EndLayoutGroup ()
UnityEngine.GUILayout.EndHorizontal ()
UnityEditor.ConsoleWindow.OnGUI ()
System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000]
[/img]

 

It look like your app connected to your database. Now you need to work on your data retrival method to get data from the database.

 

Sundar, one more doubt in that case...will I be able to see data in editor or should I build run every time I want to see data retrieval?

Also, the code for data retrieval is same as in the script found on forums so did you mean , the data display part has some problem ?

 

Yes you will get all your data in your editor itself, to be safe create an exe and test it also.

write your data access codes inside
try
{
...
...
Debug.Log( your data string );
}
catch( Exception ex )
{
Debug.Log( ex );
}

if you have any error you will get it as an exception or you get all your data displayed in console as well as in your other display area. If you are getting it only in console then you know where the problem is, good luck.

 

How do I refer to values retrieved from DB in c# file to a variable in javascript file using which I am displaying the values.

its like server side and client side scripting and variables transfer....can anyone pls suggest how to accomplish it?

by doing so I can see if values are retrieved from DB or not ?

 

man, check this out! i've made a tutorial!

http://forum.unity3d.com/threads/77447-Starting-my-MMO-with-SQL-SERVER!-in-Javascript

I'm using SQL server with unity Javascript!

hope it work for you!