0

I have developed an application that uses SQL database. My challange is to be able to ship the database with the app itself. When i package my mdf file with the setup, it gets deployed. However it is not able to connect to it. I have made following as prerequisites enter image description here

When installing, it downloads and installs all the prerequisites. I am also using following connection string to connect to it. 

<connectionStrings><add name="MyDBContext" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;    Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\MyDB.mdf; MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /></connectionStrings>

This setup works fine when installed on my machine. Any idea how this will work on simple machines?

Salman
  • 3,137
  • 4
  • 23
  • 31
  • Where are you installing the .mdf file? Is it into the MSSQL\DATA ? or somewhere else? You need to call AppDomain.SetData to the path where you put the .mdf file, if it's not finding it. http://stackoverflow.com/questions/12187068/where-is-datadirectory-defined – Colin Smith Apr 12 '17 at 13:08
  • So my .mdf file is in App_Data folder in my project and it gets deployed (or copied) into the installation folder on target machine where setup is run. So it is in .exe folder. – Salman Apr 12 '17 at 13:24

1 Answers1

1

Finally I got it working after changing many things. First off, the connection string itself. It was 

<connectionStrings><add name="MyDBContext" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;    Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\MyDB.mdf; MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /></connectionStrings>

But since only SQL Express 2012 is being installed on the destination machine (See the picture in question above) during setup, this was not going to work. So changed it such that it uses SQLServer Express's default instance name

<connectionStrings><add name="MyDBContext" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;    Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MyFolder\MyDB.mdf; User Instance=True; Integrated Security=SSPI; MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /></connectionStrings>

Please note that i have added a folder after |DataDirectory|. Idea is to create a folder within AppData so it would end up creating a folder in C:\users\me\AppData\MyFolder\MyDB.mdf

I also set the User Instance as true because otherwise it wasn't working from AppData folder.

Next I added the code to update the DataDirectory to my desired location, which is in AppData folder as

AppDomain.CurrentDomain.SetData("DataDirectory",Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));

I did this change in the main of my program file

Salman
  • 3,137
  • 4
  • 23
  • 31