- Home /
Question by
KotiaCoder · Aug 26, 2016 at 04:45 AM ·
networkingsqlsqldatabase
How to connect to a MSSQL server through Unity editor/standalone?
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.
Comment
Answer by jeffblumenthal · Jan 10, 2017 at 07:46 AM
Pull in the Mono.Data, Mono.Data.Tds, and Mono.Data.TdsClient dlls too.
Answer by mholmes · Apr 02, 2018 at 09:11 PM
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;
}