How to define DataDirectory for ConnectionString in console application to work with EntityFramework Code First Migrations

app-config c# datadirectory entity-framework localdb

Question

I try to set location MyProject\App_Data\Cos.mdf for the database in App.config:

 <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\Cos.mdf;Initial Catalog=Cos;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

In Program.cs I wrote:

 static void Main(string[] args) {

        string relative = @"..\..\App_Data\Cos.mdf";
        string absolute = Path.GetFullPath(relative);

        AppDomain.CurrentDomain.SetData("DataDirectory", absolute); 
        Console.WriteLine(absolute);
        Console.ReadKey();
 }

The displayed path is(I paste it to show that I didn't make a mistake):

enter image description here

but then when I type in Package Manager Console enable-migrations change AutomaticMigrations to true, then type update-database I get error:

Cannot attach the file 'C:\Users\s8359_000\Documents\Visual Studio 2013\Projects\Projekt5 — kopia\Projekt5\bin\Debug\Cos.mdf' as database 'Cos'.

Why does .NET tries to create my database in Debug directory?! I went through 15 subjects on StackOverflow on this topic and it looks like everybody just duplicates the answers which don't work.

EDIT AFTER ANSWER OF SRUTZKY Yes you are right there is error. I tried few more combination after your answer, unfortunately none worked.

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\baza.mdf;Initial Catalog=baza;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

and the Main

  static void Main(string[] args) {

        Console.WriteLine("BEFORE:" + AppDomain.CurrentDomain.GetData("DataDirectory"));
        string relative = @"..\..\App_Data\Cos.mdf";
        string absolute = Path.GetFullPath(relative);
        absolute = Path.GetDirectoryName(@absolute);
        AppDomain.CurrentDomain.SetData("DataDirectory", @absolute);
        Console.WriteLine(@absolute);
        Console.WriteLine(AppDomain.CurrentDomain.GetData("DataDirectory"));
        Console.ReadKey();
}

then I get in console:

enter image description here

and after deleting Migrations directory and enable-migrations, automatic migrations to true, update-database I get:

PM> update-database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. System.Data.SqlClient.SqlException (0x80131904): A file activation error occurred. The physical file name '\baza.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass1a.b__19(DbConnection conn) at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.b__32() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) at System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) at System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) at System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase() at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run() at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate) at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner) at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force) at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0() at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command) ClientConnectionId:23ca49c1-4797-4bc3-8f16-f34fd77f2cbe A file activation error occurred. The physical file name '\baza.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. PM>

1
10
1/5/2015 6:50:55 PM

Accepted Answer

Problem 1 (of 2)

When you set the value of DataDirectory, it needs to be a directory, not a file. You are passing in the value of the absolute variable which is:

C:\Users\s8359_000\Documents\Visual Studio 2013\Projects\Projekt5 — kopia\Projekt5\App_Data\Cos.mdf

and which contains the filename. That is not valid. DataDirectory is a substitution value, so specifying:

AttachDbFilename=|DataDirectory|\Cos.mdf

in the connection string would translate into:

C:\Users\s8359_000\Documents\Visual Studio 2013\Projects\Projekt5 — kopia\Projekt5\App_Data\Cos.mdf\Cos.mdf

That is not a valid path. So it appears that .NET sees that the value of DataDirectory is not valid and does not use it and hence starts in the current working directory.

Use Path.GetDirectoryName(relative) instead of Path.GetFullPath(relative) to set the value of absolute and it should work as it will set the value of DataDirectory to be:

C:\Users\s8359_000\Documents\Visual Studio 2013\Projects\Projekt5 — kopia\Projekt5\App_Data

The MSDN page for Connection Strings has some additional details towards the bottom, in the section titled, "Support for the |DataDirectory| Substitution String..."

Problem 2 (of 2)

  1. "DataDirectory" is set in the AppDomain.
  2. Console Apps have their own AppDomains that are created when they start and go away when they exit.
  3. Package Manager (where you are running Update-Database) does not have access to the AppDomain of your console app where you are setting the value of "DataDirectory".
  4. You need to either:
    1. programatically set "DataDirectory" in Package Manager, or
    2. programatically run "update-database" within the context of your console app

I don't know how to programatically interact with Package Manager, but I did manage to figure out how to programatically fire the "update-database" process. Just add the following line just after you set the value of "DataDirectory":

Database.SetInitializer(new
    MigrateDatabaseToLatestVersion<YourDataContextName, Configuration>()
 );

You will also need at least one, if not two, using statements:

  • using System.Data.Entity;
  • using ProjectName.Migrations; // namespace of Migrations\Configuration.cs

Please note that this alone does not create the database. Any pending changes will be published when you first access the database via the DbContext.

Example:

using System.Data.Entity;
using Projekt5.Migrations;

....

string relative = @"..\..\App_Data\Cos.mdf";
string absolute = Path.GetDirectoryName(absolute);
AppDomain.CurrentDomain.SetData("DataDirectory", absolute);
Database.SetInitializer(new
    MigrateDatabaseToLatestVersion<Projekt5Context, Configuration>()
 );
// database not created yet

using (var db = new Projekt5Context())
{
  db.Things.Add(new Thing { Name = "OMG This works!" });
  db.SaveChanges();
} 
// database CREATED!

Also, you might need to call the following, one time, via Package Manager (it doesn't do anything immediately to the database so the connection string is not accessed):

Add-Migration InitialMigration

For more info, please see the MSDN page for Code First Migrations.

Once this line of code to call SetInitializer is there with MigrateDatabaseToLatestVersion, it does just that: each time it runs (which is why this is done at the beginning of the console app) it syncs any changes between what is in the "model" (that is now compiled into the Assembly) and the database, making sure that the database has the latest version. This assumes that any new tables are represented in the DbContext class. But no additional Package Manager commands need to be ran.

8
1/5/2015 5:19:50 PM

Popular Answer

You can set DataDirectory for Update-Database in Configuration class which was created by Enable-Migrations command:

internal sealed class Configuration : DbMigrationsConfiguration<DataContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        var dataDirPath = "<YourPath>";
        AppDomain.CurrentDomain.SetData("DataDirectory", dataDirPath);
    }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow