Search Unity

  1. Unity 6 Preview is now available. To find out what's new, have a look at our Unity 6 Preview blog post.
    Dismiss Notice
  2. Unity is excited to announce that we will be collaborating with TheXPlace for a summer game jam from June 13 - June 19. Learn more.
    Dismiss Notice

Unity SQL Database !

Discussion in 'Multiplayer' started by sandeepsebol, May 24, 2010.

  1. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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 :(
     
  2. fallingbrickwork

    fallingbrickwork

    Joined:
    Mar 16, 2009
    Posts:
    1,072
    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.
     
  3. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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
     
  4. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    Oops ! it is... DataAccess.CS

    no luck still.....

    Cheers,
    Sandeep
     
  5. Sundar

    Sundar

    Joined:
    Mar 1, 2009
    Posts:
    21
    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
     
  6. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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
     
  7. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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.
     
  8. iLoveChocolate

    iLoveChocolate

    Joined:
    Apr 6, 2010
    Posts:
    63
    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.
     
  9. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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)
     
  10. Sundar

    Sundar

    Joined:
    Mar 1, 2009
    Posts:
    21
    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.
     
  11. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    thanks Sundar, I have been trying with only System.Data.dll till now ....no luck :(
     
  12. Sundar

    Sundar

    Joined:
    Mar 1, 2009
    Posts:
    21
    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.
     
  13. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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 ?
     
  14. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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 ?
     
  15. Sundar

    Sundar

    Joined:
    Mar 1, 2009
    Posts:
    21
    Here is the System.Data.dll that you have requested. Good luck
     

    Attached Files:

  16. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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
     
  17. Sundar

    Sundar

    Joined:
    Mar 1, 2009
    Posts:
    21
    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.
     
  18. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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]
     
  19. Sundar

    Sundar

    Joined:
    Mar 1, 2009
    Posts:
    21
    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.
     
  20. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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 ?
     
  21. Sundar

    Sundar

    Joined:
    Mar 1, 2009
    Posts:
    21
    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.
     
  22. sandeepsebol

    sandeepsebol

    Joined:
    Apr 20, 2010
    Posts:
    13
    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 ?
     
  23. brust

    brust

    Joined:
    Jul 13, 2010
    Posts:
    10