How to connect to a MSSQL server through Unity editor/standalone?

Questions & Answers
1

Hello.

I am trying to connect to a MS SQL database through Unity. However, when I try to open a connection, I get an IOException: Connection lost.

I have imported System.Data.dll from Unity\Editor\Data\Mono\lib\mono\2.0. I am using the following code:

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

public class SQL_Controller : MonoBehaviour {

    string conString = "Server=myaddress.com,port;" +
            "Database=databasename;" +
            "User ID=username;" +
            "Password=password;";

    public string GetStringFromSQL()
    {
        LoadConfig();
        string result = "";
        
        SqlConnection connection = new SqlConnection(conString);
        connection.Open();
        Debug.Log(connection.State);
        SqlCommand Command = connection.CreateCommand();
        Command.CommandText = "select * from Artykuly2";
        SqlDataReader ThisReader = Command.ExecuteReader();
        while (ThisReader.Read())
        {
            result = ThisReader.GetString(0);
        }
        ThisReader.Close();
        connection.Close();

        return result;
    }
}

This is the error I get:

IOException: Connection lost
Mono.Data.Tds.Protocol.TdsComm.GetPhysicalPacketHeader ()
Mono.Data.Tds.Protocol.TdsComm.GetPhysicalPacket ()
Mono.Data.Tds.Protocol.TdsComm.GetByte ()
Mono.Data.Tds.Protocol.Tds.ProcessSubPacket ()
Mono.Data.Tds.Protocol.Tds.NextResult ()
Mono.Data.Tds.Protocol.Tds.SkipToEnd ()
Rethrow as TdsInternalException: Server closed the connection.
Mono.Data.Tds.Protocol.Tds.SkipToEnd ()
Mono.Data.Tds.Protocol.Tds70.Connect (Mono.Data.Tds.Protocol.TdsConnectionParameters connectionParameters)
Mono.Data.Tds.Protocol.Tds80.Connect (Mono.Data.Tds.Protocol.TdsConnectionParameters connectionParameters)
System.Data.SqlClient.SqlConnection.Open ()

Please disregard any security risks with this approach, I NEED to do this for testing, security will come later.
Thank you for your time.

2

Pull in the Mono.Data, Mono.Data.Tds, and Mono.Data.TdsClient dlls too.

3

Here is some recommended syntax:

sample:

DataSet ds = new DataSet();
                int rows_returned;
                string sqlQuery =
                            "SELECT TOP 1 " +
                                "[ID]," +
                                "[Name]" +
                            "FROM " +
                                "[dbo].[YourTableNameHere] " +
                            "WHERE " +
                                "[ID] = '" + ID + "'";

                using (SqlConnection connection = new SqlConnection(cn))
                using (SqlCommand cmd = connection.CreateCommand())
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    cmd.CommandText = sqlQuery;
                    cmd.CommandType = CommandType.Text;
                    connection.Open();
                    rows_returned = sda.Fill(ds);
                    connection.Close();
                }

                if(rows_returned > 0)
                {
                    return ds;
                }
                else
                {
                    return null;
                }