Connecting to MS SQL Database

Unity Engine
1

Greetings all :slight_smile:

This is my 1st post as I am a very new Unity user.

I am trying to get some records from an SQL database and display them in a GUI box. I have been using code found on the forums here but so far no good. :frowning:

I have imported system.data.dll (.net version 2) into my assets folder and used the following script:

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

class DataAccess : MonoBehaviour

{
public string result;

//This is your database connection:

static string connectionString = ā€œdata source=c101841-svr;initial catalog=AssetDatabase;uid=jallan;pwd=blah;ā€;
static SqlConnection cn = new SqlConnection(connectionString);

// This is your command to execute:

static string sCommand = ā€œSELECT * FROM dbo.Assetsā€;

// 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)
{
result = dr[ā€œSerialNumberā€].ToString();
}
}
}

// void OnGUI()
// {
// GUI.Label (new Rect (10, 10, 200, 20), "Username from database: "+result);
// }
}

I then dragged that script on to the 3rd person controller to activate it and I get the following error in my console:

InvalidProgramException: Invalid IL code in :.cctor (): IL_0032: call 0x0a00000a

If anyone can help I would be very grateful. If anyone has a step by step guide to getting this kind of thing working that would be even better :slight_smile:

Thanks in advance.

James

2

You should make sure that youā€™re using a Mono-compatible DLL. It sounds like you might not be.

3

From what I can gather directly accessing your database is bad and you should access php scripts which then in turn access the database.

4

Iā€™m not sure if it possible to connect to dabase from Unity, Iā€™ve never tried to do it. Probably Unity will block such requests.
But in any case, you should not declare interdepended variables as static fields and initialize they. That aside, everything is fine.

Try to run this code:

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

    class DataAccess : MonoBehaviour
    {
        public string result;

        string connectionString = "data source=c101841-svr;initial catalog=AssetDatabase;uid=jallan;pwd=blah;";
        string sCommand = "SELECT * FROM dbo.Assets";

        void Start()
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlDataAdapter da = new SqlDataAdapter(sCommand, cn);
            DataTable dataTable = new DataTable();

            // Fill the data table with select statement's query results:
            int recordsAffected = da.Fill(dataTable);
            if (recordsAffected > 0)
            {
                foreach (DataRow dr in dataTable.Rows)
                {
                    result = dr["SerialNumber"].ToString();
                }
            }
        }

        //void OnGUI()
        //{
        //    GUI.Label (new Rect (10, 10, 200, 20), "Username from database: "+result);
        //}
    }

P.S.: Also, if you fill datatable via DataAdapter you donā€™t need to Open an Close connection, DataAdapter does it well.