none
Unable to create SQLServer LocalDB user datafiles in a different location RRS feed

  • Question

  • I have a C# Click Once application that uses SQLServer LocalDB.  At present, it creates the user's data directory in the user's local Documents folder.  I want to be able to allow the user to designate the data directory within the application, for example to select a cloud folder so that the data file can be accessed from different locations by that user (I am not trying to institute a multi-user solution as LocalDB is clearly not designed for this). 

    However, if I change the location of the data directory in the application's launch code, the SQL database fails to initialise in the new location (unless I have previously copied across the relevant .mdf and other files to the new location), throwing the SQL exception:

    System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Cannot attach the file 'C:\Users\XXXXXX\Documents\Test\PMM\PMM.mdf' as database 'PMM'.

    My existing connection string is:

    <add name="PMMEntities" connectionString="metadata=res://*/PMMData.csdl|res://*/PMMData.ssdl|res://*/PMMData.msl;provider=System.Data.SqlClient;provider connection string=&quot;
          data source=(localdb)\v11.0; AttachDBFilename=|DataDirectory|\PMM.mdf; initial catalog=PMM;Integrated security=True;MultipleActiveResultSets=True; App=EntityFramework&quot;"
          providerName="System.Data.EntityClient" />

    and my start-up code is:

    public MainWindow()
            {
                InitializeComponent();
    
                try
                {
                    //Create PMM data directory in user's documents folder if not exists
                    string newDirectory = @"C:\Users\" + Environment.UserName + @"\Documents\Test\PMM";
                    System.IO.Directory.CreateDirectory(newDirectory);
                    AppDomain.CurrentDomain.SetData("DataDirectory", newDirectory);
                }
                catch
                {
                    //Ignore, if create directory fails, SQLLocalDB will default to User AppData folder
                }
                Database.SetInitializer<PMMEntities>(new CreateDatabaseIfNotExists<PMMEntities>());

    where \Documents\Test is a new empty directory created to hold the datafiles.

    (I should mention that I open a temporary SQL connection with a different connection string to update the database for new users once the MainWindow has been activated but this is then closed before the app resumes.) 

    Why will SQL not initialise the datafiles in the new location?


    Mike Whalley

    Saturday, March 28, 2020 12:07 PM

Answers

All replies

  • Hi Mike,

    Could you please try to remove the Initial Catalog property in connection string?  Please check the below links to see if they could help you.

    How to change Database file location with EntitiyFramework CodeFirst auto migration and LocalDB
    Cannot attach the file *.mdf as database

    If you have more issues, please create a thread in Visual C# forums. People there will help you more effectively.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, March 30, 2020 9:13 AM
  • Cathy

    Perfect, thank you - that worked and all now good. 

    Appreciate your quick response.


    Mike Whalley

    Monday, March 30, 2020 2:25 PM
  • Cathy

    Perfect, thank you - that worked and all now good. 

    Appreciate your quick response.


    Mike Whalley

    Hi Mike,

    I am so glad to hear that you have resolved your issue. Please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, March 31, 2020 1:22 AM