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

Question

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.

Answer 1

Answer by jeffblumenthal · Jan 10, 2017 at 07:46 AM

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

Add comment · Share

Answer 2

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;
                 }

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

2 Replies

Your answer

Follow this Question

Related Questions