(Unity 5.2.1f1) using old .net 2.0.50727.1433, dosent support mssql localdb.. alternative fix here !

[LeopardX]

In VS 2015 you can only select .net 3.5 and the version is 2.0.50727.8662 which is newer than what unity uses, im making a mssql express localdb file database dll, and works fine in my test project, but just will not open the database when its in unity, i belive the problem is due to unity using the older 1433 revsion… is there any hope that unity will update to at least the latest version of 3.5, because i dont see a work around for this… ive tried both named pipe sql connection and localdb method both work in my project, none work in unity!

Updated to Unity 5.2.2 same issue old .net.

** FIXED ** Unity 5.2.2 - 26/Oct/2015

Ok just for future refrance, like Dustin said mono dose not support many things to do with sql express, but you can install mssql express 2014, not the localdb one, some setting up is required and here it is…

Page to get MSQL express and tools http://www.microsoft.com/en-gb/server-cloud/products/sql-server-editions/sql-server-express.aspx

Install the sql server using defults. and install Managment studio which you use to configure the database etc
open managment studio, and connect to your server defult will be localhost\sqlexpress
Then create your database and tables as needed.
Create a new user login in managment studio and assosiate it with the database you created giving it approprate access.

Right click on the root of the list in managment studio, typically yourpcname\sqlexpress and choose properties, click on security, and select SQL Server and Windows Authentication mode. (Mono dosent support windows auth)

Your all setup now using managment studio… now load Sql server configuration manager, open up sql server network configuration and select protocols for sqlexpress, and enable TCP/IP. (Mono only supports TCP/IP)

Restart the SQL server for changes to happen.
Then connect to your server and database using this connection string…

Data Source=localhost\sqlexpress;Initial Catalog=database;Persist Security Info=True;User ID=username;Password=yourpass

Then it should work and this is the only way it dose work, least it dose for me…

Its a great shame that mono framwork is old, because i really wanted to use localdb file version of MSQL express, for one its a smaller download, two no configuration is needed, you just install it and then connect to it… i wanted an easy database method for my users to use with minumum effort in setting up… but now thats not possible… so i guess ill have to live with it for now.

Any questions just ask me, ill be happy to help…

Unity’s Mono .net framework is very old, ive worked out its definatly older than 7 years, which is pretty bad…

Check out Dustin’s explanation bellow on why the .net in unity hasent been kept up to date.

[Dustin-Horne]

Do you have all of the appropriate assemblies in your project? For example, Unity’s .NET version doesn’t have the System.Data stuff for SQL Server so you’ll have to add it.

[LeopardX]

@Dustin Horne

Do you have all of the appropriate assemblies in your project? For example, Unity’s .NET version doesn’t have the System.Data stuff for SQL Server so you’ll have to add it.

Hi well its on the same system that has everything, so it has the correct .net version assembiles, all i can think of is that its related to unity having an older version, and if it didont have the system.data it would error our much sooner than when it tries to open the database connection, i doubt it would even work from the word go.

I mean unity dosent support system.data by defult… which may be another reason why unity is causing it to fail even if a dll compiled and suports it.

[Dustin-Horne]

@LeopardX

Hi well its on the same system that has everything, so it has the correct .net version assembiles, all i can think of is that its related to unity having an older version, and if it didont have the system.data it would error our much sooner than when it tries to open the database connection, i doubt it would even work from the word go.

I couldn’t really tell you then. I compile assemblies just fine with VS2015 to .NET 3.5 and don’t run into an issue. Are you sure your localdb instance is being spun up correctly in MSSQL Express? Is the firewall preventing you from connecting to the instance? Running it from Visual Studio might work fine because it’s attaching for you, I dunno.

[LeopardX]

@Dustin Horne

I couldn’t really tell you then. I compile assemblies just fine with VS2015 to .NET 3.5 and don’t run into an issue. Are you sure your localdb instance is being spun up correctly in MSSQL Express? Is the firewall preventing you from connecting to the instance? Running it from Visual Studio might work fine because it’s attaching for you, I dunno.

I have also had no issue in general making dlls in 3.5 etc… but the database, code and everything is fine as ive tested the dll and connections, dabase using a windows form… but once i try to attach it to unity it fails on the connection, and ive tried diferent methods of connection strings just in case unity was being fussy about something… everything is tested and works fine, it should work in unity but its not…

[Dustin-Horne]

@LeopardX

I have also had no issue in general making dlls in 3.5 etc… but the database, code and everything is fine as ive tested the dll and connections, dabase using a windows form… but once i try to attach it to unity it fails on the connection, and ive tried diferent methods of connection strings just in case unity was being fussy about something… everything is tested and works fine, it should work in unity but its not…

But Unity may be being sandboxed by the firewall and not being allowed connections to the database.

[LeopardX]

@Dustin Horne

But Unity may be being sandboxed by the firewall and not being allowed connections to the database.

Worth a go i guess, i turned off the firewall nope still same, I didont think it was because the MSSQL server im using is local only, it dosent accept remote connections, the idea was using a local database for the server to hold things like possition, rotation, scale and type of thosands of objects in the game, as well as settings and paramaters about the world and users who login, for me a database is very important to have for the server.

Its definatly something to do with Unity, since all other tests outside of Unity work, so the question is what is diferent from using the dll in Unity to using the dll in a windows form all of wich use .net 3.5, the only thing that is diferent is maybe the way Unity uses dlls and its framework revision and assembiles it supports by defult, something is going on there, ive reported this to Unity now so they can check it.

[Dustin-Horne]

@LeopardX

the only thing that is diferent is maybe the way Unity uses dlls and its framework revision and assembiles it supports by defult, something is going on there, ive reported this to Unity now so they can check it.

Well, that number that you’re seeing on those DLLs is a build number and not something that’s likely to make a difference. The big issue I think here is that Unity actually uses a subset of the .NET framework. There are some bits that aren’t supported / included by default (though some can be brought in). It may actually be the case that something is missing and an exception actually is happening but it’s getting swallowed internally so you’re never seeing it.

[LeopardX]

@Dustin Horne

Well, that number that you’re seeing on those DLLs is a build number and not something that’s likely to make a difference. The big issue I think here is that Unity actually uses a subset of the .NET framework. There are some bits that aren’t supported / included by default (though some can be brought in). It may actually be the case that something is missing and an exception actually is happening but it’s getting swallowed internally so you’re never seeing it.

I agree about the subset version, my dll wouldent work on that because its just a lightwaight version of .net, even when you select the other it still dosent have everything, but like you say thats where the dll comes in and extends the features that is not in Unity framwork by defult, I like your thoughts on the exception, it is possible…

The reason i qote the revision numbers is that one there diferent, and two its an older revision, becuase there was a time in 3.5 that it couldent understand sertain connection strings and you had to use named pipe connection strings, but today 3.5 can do both localdb connection strings and others… so i figured maybe its a revision that old, so then i tested it with named pipe but no luck, so then it puzzled me and through doubt on my original thoughts of what i thought the problem was… but i stuck with it cause it seems good enough place to start.

Theres really not much I can do as there is only two main connection string methods to try like…

public string sqlconstring = @"Server=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""c:\database.mdf"";Integrated Security=True";

or the named pipe way like this…

public string sqlconstring = @"Server=np:\\.\pipe\LOCALDB#269B6302\tsql\query;AttachDbFilename=""c:\database.mdf"";Integrated Security=True";

Like ive hard coded the database location to make sure both my project and unity find the database, with the named pipe ive oviously looked up the default MSSQLLocalDB instance on the sql express and started the instance to make sure its running since thats the only way to get the named pipe for it, yet this still fails on unity.

It could be what you were commenting on Dustin, but anyways ive told unity about it and ill work with them to give them whatever they need to find out whats happening, so ill let you know in about 2 weeks when and if i get a reply.

Note i later changed it from c: to e: since im using windows 10 and it has protection on writing things to the root.

[Dustin-Horne]

Well your connection strings are also wrong. You’ve got quotes that don’t belong in there. You could try this:

@"Server=.\SQLExpress;AttachDbFilename=c:\database.mdf;Trusted_Connection=Yes;"

I also notice that you’re not specifying a database name, which you probably should be. I’m not sure which version of SQL Express / Server you’re running. Another possible format (for SQLExpress 2012) is:

@"Server=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyFolder\MyData.mdf;"

[Dustin-Horne]

Nevermind… it doesn’t matter. Support was added to 3.5 after the fact. It was not supported until .NET 3.5.1 through a framework update, so Unity’s Mono version wouldn’t support it:

https://support.microsoft.com/en-us/kb/2664825

[LeopardX]

@Dustin Horne

Nevermind… it doesn’t matter. Support was added to 3.5 after the fact. It was not supported until .NET 3.5.1 through a framework update, so Unity’s Mono version wouldn’t support it:

An update is available that adds support for Microsoft SQL Server Express LocalDB to the .NET Framework 3.5.1 in Windows 7 SP1 and in Windows Server 2008 R2 SP1 - Microsoft Support

i did see something about an update that was avalable with vs 2012 that let it use localdb’s, how old is Unity’es framework then, thats like maybe 5 years ago probably more(Or whenver vs 2012 was out i guess), this is very sad and confirms what i was hopeing wasent, no way to get this to work in Unity then, unless Unity update there framework…

You would have thought supporting dll’s framworks would be very important, because its the only way for us to extend and do things that unity cant do or has no internal tools to do it, is no one making dlls or something and thats why they not bothering keeping it up to date.

[LeopardX]

Ok just for future refrance, like Dustin said mono dose not support many things to do with sql express, but you can install mssql express 2014, not the localdb one, some setting up is required and here it is…

Page to get MSQL express and tools http://www.microsoft.com/en-gb/server-cloud/products/sql-server-editions/sql-server-express.aspx

Install the sql server using defults. and install Managment studio which you use to configure the database etc
open managment studio, and connect to your server defult will be localhost\sqlexpress
Then create your database and tables as needed.
Create a new user login in managment studio and assosiate it with the database you created giving it approprate access.

Right click on the root of the list in managment studio, typically yourpcname\sqlexpress and choose properties, click on security, and select SQL Server and Windows Authentication mode. (Mono dosent support windows auth)

Your all setup now using managment studio… now load Sql server configuration manager, open up sql server network configuration and select protocols for sqlexpress, and enable TCP/IP. (Mono only supports TCP/IP)

Restart the SQL server for changes to happen.
Then connect to your server and database using this connection string…

Data Source=localhost\sqlexpress;Initial Catalog=database;Persist Security Info=True;User ID=username;Password=yourpass

Then it should work and this is the only way it dose work, least it dose for me…

Its a great shame that mono framwork is old, because i really wanted to use localdb file version of MSQL express, for one its a smaller download, two no configuration is needed, you just install it and then connect to it… i wanted an easy database method for my users to use with minumum effort in setting up… but now thats not possible… so i guess ill have to live with it for now.

Any questions just ask me, ill be happy to help…

Unity’s Mono .net framework is very old, ive worked out its definatly older than 7 years, which is pretty bad…

[Dustin-Horne]

@LeopardX

Unity’s Mono .net framework is very old, ive worked out its definatly older than 7 years, which is pretty bad…

To understand this you’d have to know a bit of the history. In order to get the high level of cross platform support that Unity has, it partnered with (read as purchased licensing from) Xamarin. No, as Xamarin updated its .NET profile, the only way for Unity to update on the mobile devices is to pay very heavy licensing fees to Xamarin to update.

I don’t know the details but it must be an unreasonable amount because Unity hasn’t shied away from licensing 3rd party stuff and even including it in the free version in the past. But since the Xamarin licensing was stuck on a specific Mono profile, that means that dependency exists for all platforms.

Fast forward to the end of 2013… Unity had been working on WebGL and a new compiler (IL2CPP). In an ideal world they could have used Microsoft’s .NET Native but it was not yet ready and IL2CPP had been in the works for awhile. Then Apple came out with the new 64-bit requirement.

Again, this would have resulted in a Xamarin licensing upgrade, so instead Unity went ahead and started pushing out IL2CPP for iOS builds. This has been a little bit of a painful process because Apple forced the move before it was ready, but for the most part it has been successful and continues to get better. Now, Unity is working on pushing IL2CPP to more platforms (WebGL, iOS and now experimentally Android). Once it is ready on all platforms, this will allow Unity to upgrade the .NET profile because it no longer will have a dependency on Xamarin.

Bare in mind that, while we’ll finally get into the .NET 4 (or maybe 5 by then) world, there will still be a few things that probably aren’t usable. The dynamic keyword for example (and it probably should be avoided anyway) probably will be a no-show because of AOT compatibility issues.

So, long story short… we will be getting a .NET profile upgrade, but there are a lot of other small pieces that have to go into place to make it work.

[LeopardX]

@Dustin Horne

To understand this you’d have to know a bit of the history. In order to get the high level of cross platform support that Unity has, it partnered with (read as purchased licensing from) Xamarin. No, as Xamarin updated its .NET profile, the only way for Unity to update on the mobile devices is to pay very heavy licensing fees to Xamarin to update.

I don’t know the details but it must be an unreasonable amount because Unity hasn’t shied away from licensing 3rd party stuff and even including it in the free version in the past. But since the Xamarin licensing was stuck on a specific Mono profile, that means that dependency exists for all platforms.

Fast forward to the end of 2013… Unity had been working on WebGL and a new compiler (IL2CPP). In an ideal world they could have used Microsoft’s .NET Native but it was not yet ready and IL2CPP had been in the works for awhile. Then Apple came out with the new 64-bit requirement.

Again, this would have resulted in a Xamarin licensing upgrade, so instead Unity went ahead and started pushing out IL2CPP for iOS builds. This has been a little bit of a painful process because Apple forced the move before it was ready, but for the most part it has been successful and continues to get better. Now, Unity is working on pushing IL2CPP to more platforms (WebGL, iOS and now experimentally Android). Once it is ready on all platforms, this will allow Unity to upgrade the .NET profile because it no longer will have a dependency on Xamarin.

Bare in mind that, while we’ll finally get into the .NET 4 (or maybe 5 by then) world, there will still be a few things that probably aren’t usable. The dynamic keyword for example (and it probably should be avoided anyway) probably will be a no-show because of AOT compatibility issues.

So, long story short… we will be getting a .NET profile upgrade, but there are a lot of other small pieces that have to go into place to make it work.

Ah big thanks for the history on that, I did think it odd with Unity developing other new things and doing great stuff, that there must have been some reason why .net was behind, and now i know…

Im glad that one day it will be updated and glad they should no longer have to depend on Xamarin, by the time my project really kicks off and becomes developed enough, im sure by then i could use localdb and give backwards compatibility to the old way or db upgrade path for my users.

At the moment ive been investigating on automating the sql server setup and database creation to minimize the hassle for users, i think i can do everything almost except turn on TCP/IP for the server, so that’s not too bad.

Cheers for your help and info Dustin!

[Dustin-Horne]

@LeopardX

At the moment ive been investigating on automating the sql server setup and database creation to minimize the hassle for users, i think i can do everything almost except turn on TCP/IP for the server, so that’s not too bad.

I don’t know how data heavy your application is, but you might think about looking at SQLite or one of the alternatives. There are assets to get it up and running with Unity… it’s file based, easy to administer with some simple GUI tools available, and it would allow you to publish your standalone for Linux / OSX as well as Windows because it would remove that SQL Express dependency.

[LeopardX]

@Dustin Horne

I don’t know how data heavy your application is, but you might think about looking at SQLite or one of the alternatives. There are assets to get it up and running with Unity… it’s file based, easy to administer with some simple GUI tools available, and it would allow you to publish your standalone for Linux / OSX as well as Windows because it would remove that SQL Express dependency.

Thank you, ill definatly check that out, I do plan to port the game later down the road so that should make things easyer!

Off to read up on the SQLite docs!

[Dustin-Horne]

@echo17 has a good SQLite asset in the asset store. Looks like it doesn’t support Windows Store or WP8 but it can probably be modified to do so. Of course, you could always abstract your data layer and use a different system for those platforms since Unity supports specifying different DLLs for different platforms:

Unity Asset Store - The Best Assets for Game Making

There are other SQLite assets as well but I don’t know anything about them personally.

[LeopardX]

@Dustin Horne

@echo17 has a good SQLite asset in the asset store. Looks like it doesn’t support Windows Store or WP8 but it can probably be modified to do so. Of course, you could always abstract your data layer and use a different system for those platforms since Unity supports specifying different DLLs for different platforms:
https://www.assetstore.unity3d.com/en/#!/content/3845

There are other SQLite assets as well but I don’t know anything about them personally.

All good stuff ive bookmarked that cheers Dustin, saves me a lot of truble, also shame it dosent look like that supports linux though, im sure ill work something out, i also found this also http://answers.unity3d.com/questions/743400/database-sqlite-setup-for-unity.html

Which gets it working for Windows, I never knew unity came with Mono.Data.Sqlite.dll, I would have never have known about SQLite, and its great that theres source code avalabe for it too, im in the middle of testing it out, i got it working in unity just a basic query returning some results, but works… in a bit im going to run a few more tests and some stress testing to see how it performs!