1

Currently my SQL database is on 

C:\Users\Slaven\KasaMP.mdf

I want to move it into my projects directory [maybe "database" folder(?)] and make correct changes on my connectionstring. My goal is to be able to open this project with .mdf file attached on any computer. Current ConnectionString I use is EntityFramework generated and I am not sure what is the approach to make this CS point to different location.

ConnectionString:

<connectionStrings>
    <add name="KasaMPEntities" connectionString="metadata=res://*/OsnovniPodaci.Model.csdl|res://*/OsnovniPodaci.Model.ssdl|res://*/OsnovniPodaci.Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\v11.0;AttachDbFilename=|DataDirectory|\KasaMP.mdf;initial catalog=KasaMP;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;"
      providerName="System.Data.EntityClient" />
  </connectionStrings>

My projects path:

C:\Users\Slaven\Documents\visual studio 2013\Projects\PCKasa\KasaMP

I found in other posts this following line but I'm not sure what it does:

AppDomain.CurrentDomain.SetData("DataDirectory", System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database"));

I know I have to place this attribute somewhere in the ConnectionString

AttachDbFileName=|DataDirectory|\KasaMP.mdf

Any suggestions on how to correctly do this? ^ ^

solujic
  • 924
  • 1
  • 18
  • 43

1 Answers1

2

In a WinForms application the DataDirectory substitution string point to the folder where the application starts. In case of a Visual Studio session this folder is the BIN\DEBUG or BIN\RELEASE folder (possibly with the x86 variant)

This works well inside Visual Studio but you should be aware that, in your customer PC and without changing the config setting, the folder where you should have the MDF is the same of your app.
But, sadly this location has no write permissions (like C:\program files). An essential requirement for any database app.

So your best bet is to place this file in the CommonApplicationData folder that you can retrieve using Environment.SpecialFolder.CommonApplicationData enum (usually it is C:\PROGRAMDATA in latest version of Windows)

string folder = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData);
string myAppFolder = Path.Combine(folder, "MyReservedAppDataFolder");
Directory.CreateDirectory(myAppFolder);
AppDomain.CurrentDomain.SetData("DataDirectory", myAppFolder);

All this should be done BEFORE any data access related code in your application. Of course you can leave the setting as it is now without making any change.

Steve
  • 213,761
  • 22
  • 232
  • 286
  • Thanks for suggestion but I don't know how to change the location of .mdf from my SQL Server xD – solujic Feb 13 '17 at 10:20
  • I tried this: https://blog.sqlauthority.com/2012/10/28/sql-server-move-database-files-mdf-and-ldf-to-another-location/ but I got a bunch of errors – solujic Feb 13 '17 at 10:21
  • 1
    I don't understand. Your config file uses a LocalDb instance. Are you telling me that your customer uses an installed instance of Sql Server or Sql Server Express? In that case the connection string is totally different and you need to add your MDF file to the installed instance of Sql Server – Steve Feb 13 '17 at 10:35
  • Currently I'm trying to place the MDF file inside my project... LocalDB instance sure but it's inside my user and that won't work when soembody else opens my project on their Visual Studio - I'm not on the customers part yet – solujic Feb 13 '17 at 10:47
  • Uhm LocalDb database are not meant to be shared and used concurrently between many users (Perhaps it is still possible to host them on a network path but I am not sure if more than one user can work with it concurrently). What about installing Sql Server Express on you network and use its functionality to share the database between your colleagues? – Steve Feb 13 '17 at 11:02
  • So you're saying that LocalDB in general are not meant to be multi-user DB's? I want to embed .mdf file in my project - why is this big deal lol sorry but I never done something like this I thought it wouldn't be a problem – solujic Feb 13 '17 at 12:27
  • You can read the answer at this question http://stackoverflow.com/questions/9655362/localdb-deployment-on-client-pc _it is not a data-sharing feature_ – Steve Feb 13 '17 at 12:36
  • LocalDB is born to be a development tool that doesn't require the steps to install a full fledged Sql Server (not free) or its little brother Sql Server Express (free, but read the limitations) It is useful if you want to build a single user application but for a multi-user scenario then it is better to use Sql Server Express with its more powerful capabilities in terms of system administration. (users, logins, permissions etc...) – Steve Feb 13 '17 at 12:39
  • Welp that's a whole other issue but thank you for bringing it up... I'm gonna have to research some more even though I was sure LocalDB would do the trick for my project :/ – solujic Feb 13 '17 at 12:52
  • seems I didn't quite udnerstand qorking on .mdf file and n actual server or the difference between LovalDB and SQLExpress, I'm still trying to figure this mess out xD! – solujic Feb 13 '17 at 15:54
  • Did you install SqlServer Express? Configure it to accept both windows and Sql Server logins. Remember to install also Sql Server Management Studio. Configure the server to accept connection by TCP/IP Then open the studio and right click on the server, Attach your MDF file and create a user and password allowed to login. After that change your connection string to SERVERNAME\SQLEXPRESS setting catalog, user name and password. I am sorry but it is beyond my possibility to explain how to manage an Sql Server (Express) Instance – Steve Feb 13 '17 at 16:37
  • 1
    Yes I do have Sql Server Menagement Studio and SqlServer Express I was just connecting to LocalDB though... Thanks for pointers I'll figure it out – solujic Feb 14 '17 at 08:07