Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

MS SQL connection?

Discussion in 'Editor & General Support' started by wilkinnh, Mar 7, 2008.

  1. wilkinnh

    wilkinnh

    Joined:
    Mar 7, 2008
    Posts:
    14
    I've tried a couple different options to try and connect to my MS SQL server. I've already got the sql servers and I'd like to use them if possible. However, every time I try and make a connection, I get a socket error. I've tried using Mono's TDSConnection, SqlConnection, NpgConnection...all of them return the same socket error. Has anyone else been able to connect to an MS SQL 2005 server?
     
  2. tomrino

    tomrino

    Joined:
    Aug 26, 2008
    Posts:
    1
    Any luck connecting to a SqlServer?

    I'm using SqlServer 2005 and can't make it connect through Unity. I've made my own DatabaseAccess dll in mono and it works fine from mono. I've added the dll into my Unity project and all functions are working fine except conn.ConnectToBase();

    Any push in the right direction would be highly appreciated!

    Thanks
     
  3. wilkinnh

    wilkinnh

    Joined:
    Mar 7, 2008
    Posts:
    14
    sorry, we haven't found a good solution yet. we've tried a lot of things, but nothing seems to be working. we basically moved on to web services for now until we find a solution.

    if you figure anything out, let me know!
     
  4. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,307
    What are you connecting from? In the editor and standalones, it should be working fine ... Not sure if that works with Web players - but even if it should work, I'd definitely not recommend letting Web players connect to your database (unless you got everything in a local network). I also wouldn't let any clients connect directly to a database.

    You need System.Data.dll from the Unity.app folder (I think under Frameworks). Do *not* use System.Data.dll from any Mono installation (I had very ugly crashes with that, and it took me almost forever to find what the problem was because the crashes occured when closing my game server / closing Unity after I had started the game server inside Unity - so there was no hint that it was the database connection which was causing the crashes).

    You need to make sure that the SQL Server listens to TCP connections (not a default setting).

    I got this up and running easily with code from the Mono project (modified somewhat, but not toooo significantly):

    Code (csharp):
    1.  
    2. using System.Data;
    3. using System.Data.Sql;
    4. using System.Data.SqlClient;
    5.  
    6. ...
    7.  
    8. ... and then, in some method:
    9.  
    10. using (IDbConnection dbcon = new SqlConnection(connectionString)) {
    11.     using (IDbCommand dbcmd = dbcon.CreateCommand()) {
    12.         dbcmd.CommandType = CommandType.StoredProcedure;
    13.         dbcmd.CommandText = procedureName;
    14.         foreach (SqlParameter parameter in parameters) {
    15.             dbcmd.Parameters.Add(parameter);
    16.         }
    17.         dbcon.Open();
    18.         result = dbcmd.ExecuteScalar();
    19.         dbcon.Close();
    20.     }
    21. }
    22.  
    procedureName also could be an SQL statement.

    Ah, and I think you can't use the IP-address in the connection string (you need to use a name that can be resolved via DNS). Only parameters I could use in the connection string (I think that's a Mono limitation) are: Database, Server, uid and pwd.

    Hope that helps,
    Jashan
     
  5. wilkinnh

    wilkinnh

    Joined:
    Mar 7, 2008
    Posts:
    14
    yeah, actually, the DNS stuff was the biggest problem we had. Every time we tried to connect to an IP address (it was actually local on our 192.168.x.x network), it failed to find that server. weird. that was about the point where we started working with web services.

    i'm starting to agree with you about connecting directly to a sql server. I think for a couple reasons it's good to set it up via web services. like i think you were getting at, it's probably best not to have the sql server public. if the sql server is only being referenced locally with a web service, it's definitely safer and easier to control the traffic to your database. you can also control connections and other things more efficiently with web services (as far as i know).

    i just ended up writing an asmx web service, but you could probably also mess with the svc web services as well.

    good luck!
     
  6. pavlito

    pavlito

    Joined:
    Nov 27, 2007
    Posts:
    136
    I managed to connect to a MS SQL Server 2005/8 (not sure which one it is) =)

    It only works in unity for me, tho. When I build, either a standalone or web, it doesn't.

    More info on:
    http://forum.unity3d.com/viewtopic.php?t=14429
     
  7. matrix211v1

    matrix211v1

    Joined:
    Jan 20, 2009
    Posts:
    193
    I am using the Windows version of Unity 2.5 and when I put my script in more or less like about, I am getting "Assets/Standard Assets/Scripts/NewBehaviourScript.cs(4,14): error CS0234: The type or namespace name `Data' does not exist in the namespace `System'. Are you missing an assembly reference?"

    Obviously I am not including something properly. I have installed the latest version of Mono on my machine. Any thoughts?

    Here is the code:

    Code (csharp):
    1.  
    2. using UnityEngine;
    3. using System.Collections;
    4. using System;
    5. using System.Data;
    6. using System.Data.SqlClient;
    7.  
    8.  
    9. public class NewBehaviourScript : MonoBehaviour {
    10.  
    11.     // Use this for initialization
    12.     void Start () {
    13.  
    14.         string connectionString =
    15.             "Server=MyServer;" +
    16.             "Database=pubs;" +
    17.             "User ID=MySqlServerUserId;" +
    18.             "Password=MySqlServerPassword;";
    19.  
    20.         IDbConnection dbcon;
    21.         dbcon = new SqlConnection(connectionString);
    22.         dbcon.Open();
    23.         IDbCommand dbcmd = dbcon.CreateCommand();
    24.         string sql =
    25.             "SELECT fname, lname " +
    26.             "FROM employee";
    27.         dbcmd.CommandText = sql;
    28.         IDataReader reader = dbcmd.ExecuteReader();
    29.         while(reader.Read()) {
    30.             string FirstName = (string) reader["fname"];
    31.             string LastName = (string) reader["lname"];
    32.             Console.WriteLine("Name: " +
    33.                 FirstName + " " + LastName);
    34.         }
    35.         // clean up
    36.         reader.Close();
    37.         reader = null;
    38.         dbcmd.Dispose();
    39.         dbcmd = null;
    40.         dbcon.Close();
    41.         dbcon = null;
    42.        
    43.     }
    44.    
    45.     // Update is called once per frame
    46.     void Update () {
    47.    
    48.     }
    49. }
     
  8. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,307
    First of all: Don't make the same mistake that I did and try to use the DLLs from the most current version of Mono. In my case, I got really nasty crashes (e.g. when closing Unity or when closing my standalones) with a more recent System.Data.dll

    You do need to include the DLL from the Unity folder into your project (on the Mac, this is Unity/Contents/Frameworks/Mono.framework/System.Data.dll - should be something more or less similar on Windows only that you probably don't have "Contents" which is kind of Mac specific).

    Simply copy that DLL into the "Assets"-folder of your project, and it should work. The MS SQL implementation of the Mono version that Unity uses is not "ideal" but it works "okay most of the time" (there's a few differences/limitations if you compare it to the current .NET MS SQL APIs, in particular when it comes to creating the connection strings; but I think those are documented in the Mono documentation).
     
  9. matrix211v1

    matrix211v1

    Joined:
    Jan 20, 2009
    Posts:
    193
    That worked, but it's telling me that it is refused. I'm pretty sure it's the connection string part.

    I will work on it and post what the code once I get it working.

    Thanks!
     
  10. matrix211v1

    matrix211v1

    Joined:
    Jan 20, 2009
    Posts:
    193
    I've tried a few different connection strings with no joy. I'm using port 1433, do I need to specify that in my connection string?

    Here is the code:

    Code (csharp):
    1.  
    2. using UnityEngine;
    3. using System.Collections;
    4. using System;
    5. using System.Data;
    6. using System.Data.SqlClient;
    7.  
    8. class NetConnection : MonoBehaviour
    9. {
    10.    
    11.    public string name;
    12.    //This is your database connection:
    13.    static string connectionString = "data source=192.168.1.1;initial catalog=databaseName;uid=user;pwd=password;";
    14.    static SqlConnection cn = new SqlConnection(connectionString);
    15.  
    16.    // This is your command to execute:
    17.    static string sCommand = "SELECT * FROM tblUser";
    18.  
    19.    // This is your data adapter that understands SQL databases:
    20.    static SqlDataAdapter da = new SqlDataAdapter(sCommand, cn);
    21.  
    22.    // This is your table to hold the result set:
    23.    static DataTable dataTable = new DataTable();
    24.  
    25.    void Start()
    26.    {
    27.       cn.Open();
    28.  
    29.       // Fill the data table with select statement's query results:
    30.       int recordsAffected = da.Fill(dataTable);
    31.  
    32.       if (recordsAffected > 0)
    33.       {
    34.          foreach (DataRow dr in dataTable.Rows)
    35.          {  
    36.             name = dr["username"].ToString();
    37.          }
    38.       }
    39.    }
    40.    
    41.    void OnGUI()
    42.    {
    43.       GUI.Label (new Rect (10, 10, 200, 20), "Username from database: "+name);
    44.    }
    45. }
    Here is the error:
    Code (csharp):
    1.  
    2. TdsInternalException: Server does not exist or connection refused.
    3. System.Data.SqlClient.SqlConnection.Open ()
    4. NetConnection.Start ()   (at Assets/NetConnection.cs:26   at Mono.Data.Tds.Protocol.TdsComm..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion) [0x00000])
    5. Mono.Data.Tds.Protocol.Tds..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    6. Mono.Data.Tds.Protocol.Tds70..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout)
    7. (wrapper remoting-invoke-with-check) Mono.Data.Tds.Protocol.Tds70:.ctor (string,int,int,int)
    8. Mono.Data.Tds.Protocol.TdsConnectionPoolManager.CreateConnection (Mono.Data.Tds.Protocol.TdsConnectionInfo info)
    9. Mono.Data.Tds.Protocol.TdsConnectionPool.CreateConnection ()
    10. Mono.Data.Tds.Protocol.TdsConnectionPool.GetConnection ()
    11. System.Data.SqlClient.SqlConnection.Open ()    at System.Net.Dns.GetHostByAddressFromString (System.String address, Boolean parse) [0x00000]
    12. System.Net.Dns.GetHostEntry (System.Net.IPAddress address)
    13. System.Net.Dns.GetHostEntry (System.String hostNameOrAddress)
    14. Mono.Data.Tds.Protocol.TdsComm..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
     
  11. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,307
    You're connection string won't work with Mono - at least not with Mono 1.2.5. Look at my connection string - including the comment (I've changed some relevant data but I guess it's still understandable - 1234 is the port because I'm using a non-standard port, you can probably omit that if you use 1433, which IIRC is the standard port).

    Code (csharp):
    1.         "Database=MyDatabaseName;"
    2.         + "Server=my.database.url,1234;"
    3.         + "uid=MyUser;pwd=MyPassword;"
    4. //        + "Pooling=false;" // usually use pooling - that was just for testing...
    5. //        + "Server=12.34.56.78,1234;" // IP-Addresses don't work with Mono.Data!!!
    6. //        + "Network=dbmssocn;" // not supported in Mono
    7. //        + "Application Name=My Application Name;" // not supported in Mono
    8.  
    I guess that should help ;-)

    For more info, see:
    http://www.mono-project.com/SQLClient

    ... I'm using it with SQL Server 2008 and it works; but I hope this documentation doesn't reflect the current state of the Mono SQL Server provider (it looks a bit dated to me).
     
  12. firas darwiche

    firas darwiche

    Joined:
    Oct 4, 2006
    Posts:
    130
    Code (csharp):
    1.  
    2. using System;
    3. using System.Collections.Generic;
    4. using System.Text;
    5. using System.Data;
    6. using System.Data.SqlClient;
    7. using UnityEngine;
    8.  
    9. public class Class1 : MonoBehaviour
    10. {
    11.     public void Start()
    12.     {
    13.         string connstring = "server=firaslaptop;Database=OnlineAuthorLibraryTesting;uid=sa;pwd=sa;";
    14.         SqlConnection conn = new SqlConnection(connstring);
    15.         conn.Open();
    16.         conn.Close();
    17.     }
    18. }
    19.  
    this code runs perfectly well in a vs console app.

    but in unity it gives me the "conn refused" error.

    i'm using sql server 2000

    authentication = sql server and windows

    remote server connection is set to : allow other sql servers to connect remotely...using RPC

    what could be the problem?

    thank you for all help.
     
  13. geyapingcn

    geyapingcn

    Joined:
    Jul 29, 2010
    Posts:
    25