EF，Code First，如何在特定位置（HDD）上初始化数据库， [英] EF, Code First, how to initialize database on a specific location (HDD)

问题描述

有没有人知道如何在EF CodeFirst数据库初始化期间精确的.mdf / .log文件位置？

does anybody know how to spedify exact .mdf/.log file location during EF CodeFirst database initialization?

对于调试和测试场景位置在生产中也很有用），我想在应用程序启动期间重新部署数据库，但也想在特定位置（例如，硬盘驱动器）创建数据库文件。

For a debug and testing scenario (but specifying exact DB files location can be useful in production as well), I'd like to re-deploy database during application start-up, but also I'd like to create DB files on a specific location (e.g. secong hard drive).

public class MyCustomInitialized : DropCreateDatabaseAlways<MyContext>
{
    public override void InitializeDatabase(MyContext context)
    {
        if (context.Database.Exists())
        {
            // set the database to SINGLE_USER so it can be dropped
            context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "ALTER DATABASE [" + context.Database.Connection.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");

            // drop the database
            context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "USE master DROP DATABASE [" + context.Database.Connection.Database + "]");

            // Doesn't work yet
            var directoryPath = ConfigurationManager.AppSettings["DatabaseDirectoryPath"];
            var fileName = ConfigurationManager.AppSettings["DatabaseFileName"];

            if(!Database.Exists())  {
                var qry = string.Format(
                "USE master CREATE DATABASE {0}" + Environment.NewLine +
                "ON PRIMARY" + Environment.NewLine +
                "( NAME = {0}, FILENAME = '{1}')"  + Environment.NewLine +
                "LOG ON"  + Environment.NewLine +
                "( NAME = {2}, FILENAME = '{3}')"  + Environment.NewLine
                , this.Database.Connection.Database,
                  Path.Combine(directoryPath, fileName + ".mdf"),
                  this.Database.Connection.Database + "_log",
                  Path.Combine(directoryPath, fileName + "_log.ldf"));

                // -- EXCEPTION HERE, 
                Database.ExecuteSqlCommand(qry);


                Database.ExecuteSqlCommand("USE " + this.Database.Connection.Database);
        }

        base.InitializeDatabase(context);
    }
}

我得到的exeption：

The exeption I get:

无法打开登录请求的数据库DatabaseName。登录
失败。用户SomeUser的登录失败。
当前命令发生严重错误。

Cannot open database "DatabaseName" requested by the login. The login failed. Login failed for user 'SomeUser'. A severe error occurred on the current command. The results, if any, should be discarded.

如果在初始化期间可以由EF创建数据库，则可以覆盖此初始化？

If the database can be created by EF during initialization, how this initialization can be overriden?

感谢，
汤姆

推荐答案

能够获得以下工作，在数据库存在和不存在的情况下，然而，有一个问题，代码第一表创建没有被触发（即这只是一个部分解决方案 - 仍然有一个步骤需要实际获得EF来创建Code First表）。

I was able to get the below working, in both the database exists and non-exists scenarios, however, there is one issue in that the Code First table creation isn't being triggered (i.e. this is only a partial solution - there is still a step needed to actually get EF to create the Code First tables).

（我的上下文被称为 Model1 ）

(My context was called Model1)

public class MyCustomInitialized : DropCreateDatabaseAlways<Model1>
{
    public override void InitializeDatabase(Model1 context)
    {
        if (context.Database.Exists())
        {
            // set the database to SINGLE_USER so it can be dropped
            context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,
                "ALTER DATABASE [" + context.Database.Connection.Database +
                "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");

            // drop the database
            context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,
                "USE master DROP DATABASE [" + context.Database.Connection.Database + "]");
        }
        var directoryPath = @"c:\temp";
        var fileName = "Foo";

        var qry = string.Format(
            "USE master CREATE DATABASE {0}" + Environment.NewLine +
            "ON PRIMARY" + Environment.NewLine +
            "( NAME = {0}, FILENAME = '{1}')" + Environment.NewLine +
            "LOG ON" + Environment.NewLine +
            "( NAME = {2}, FILENAME = '{3}')" + Environment.NewLine
            , context.Database.Connection.Database,
            Path.Combine(directoryPath, fileName + ".mdf"),
            context.Database.Connection.Database + "_log",
            Path.Combine(directoryPath, fileName + "_log.ldf"));

        using (var conn = new SqlConnection(@"data source=.\SQLExpress;initial catalog=master;integrated security=True;"))
        using (var cmd = new SqlCommand(qry, conn))
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }

        -- TODO trigger the table creation
        this.Seed(context);
        context.SaveChanges();
    }
}

一些注意事项：

• 数据库属于上下文


• 您将无法在Context连接上重新创建数据库，因为当删除数据库时，EF连接字符串中的初始目录将会消失。我刚刚使用一个新的Ado连接做Drop，否则无法打开数据库\FOO \登录请求。我还会考虑将上下文连接的DROP移动到主连接。

• Database belongs to the context
• You won't be able to recreate the database on the Context connection, since when you drop the database, the Initial Catalog in your EF connection string will have disappeared. I've just used a new Ado Connection to do the Drop, else "Cannot open database \"FOO\" requested by the login". I would also consider moving the DROP of the Context connection into a master connection.

调用

base.InitializeDatabase(context);

导致Db被删除并重新创建，因为 DropCreateDatabaseAlways 策略，其中包括：

Causes the Db to be dropped and recreated because of the DropCreateDatabaseAlways strategy which includes:

context.Database.Delete();
context.Database.Create(DatabaseExistenceState.DoesNotExist);

因此

-- missing something here, viz creating the tables
this.Seed(context);
context.SaveChanges();

这篇关于EF，Code First，如何在特定位置（HDD）上初始化数据库，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！