CODEWITHSUNDEEP
c#sql-serverdatabaseentity-framework
Craig Graham
Craig Graham

Reputation: 1191

Programmatically create DB from scratch in arbitrary location

I'm working on a simple desktop app that uses SQL Server Express/LocalDB. I've a data directory in an arbitrary nonpriviledged location and in there I want to create a database file. I initially created the database and generated a model for EF to use; now I want to use that model to recreate the database wherever I want it.

I've found a variety of posts doing similar, but they seem to be deleting and recreating an existing database via a context that's working to start with, for testing purposes. I'm wanting to start from an empty directory.

Using extracts from the code in here I'm able to physically create a database file on the disk, using the SQL statements to create the new .mdf and .ldf files. But they don't have the schema; if I start a context instance off from the .mdf file and then try counting the number of rows in a table, I get an exception thrown because the table doesn't exist.

If I try calling ctx.Database.Create() then I get the error that the database can't be created because it already exists. Which of course it does, just with no tables.

If I don't use the raw SQL queries initially to create the new, empty, database and I try to create the context as follows, with filespec pointing to a non-existent .mdf file in a valid directory, the .Create() always throws an exception "Database '' cannot be created because it already exists"

string connectionString
        = "Data Source=(LocalDB)\\v11.0;AttachDbFilename="
        + fileSpec;

EventsListDBEntities ctx = new EventsListDBEntities();
ctx.Database.Connection.ConnectionString = connectionString;
ctx.Database.Create();
ctx.Database.Initialize(true);

How can I get EF to create the tables in my empty DB, or to create the files from scratch?

Upvotes: 4

Views: 2699

Answers (2)

Craig Graham
Craig Graham

Reputation: 1191

After lots of experimentation, here's the code I've ended up with that does the job.

string connectionString
      = "Data Source=(LocalDB)\\v11.0;AttachDbFilename="
      + fileSpec + ";database=EventsListDB";

/* We can't go straight into the context and create the DB because 
 * it needs a connection to "master" and can't create it. Although this
 * looks completely unrelated, under the hood it leaves behind something
 * that EF can pick up and use- and it can't hurt to delete any references 
 * to databases of the same name that may be lurking in other previously
 * used directories.
 */

SqlConnectionStringBuilder masterCSB = new SqlConnectionStringBuilder(connectionString);
masterCSB.InitialCatalog = "master";
masterCSB.AttachDBFilename = "";

using (var sqlConn = new SqlConnection(masterCSB.ToString()))
{
    sqlConn.Open();
    using (var cmd = sqlConn.CreateCommand())
    {
        bool done = false;
        int attempt = 0;
        do
        {
            try
            {
                cmd.CommandText =
                    String.Format(
                        "IF EXISTS (Select name from sys.databases " + 
                        "WHERE name = '{0}') " +
                        "DROP DATABASE {0}", "EventsListDB");
                cmd.ExecuteNonQuery();
                done = true;
            }
            catch (System.Exception ex)
            {
                /* We sometimes get odd exceptions that're probably because LocalDB hasn't finished starting. */
                if (attempt++ > 5)
                {
                    throw ex;
                }
                else Thread.Sleep(100);

            }
        } while (!done);
    }
}

/* Now we can create the context and use that to create the DB. Note that
 * a custom constructor's been added to the context exposing the base
 * constructor that can take a connection string- changing the connection
 * string after the default constructor reads it from App.config isn't 
 * sufficient.
 */
EventsListDBEntities ctx = new EventsListDBEntities(connectionString);
ctx.Database.Create();
int numRecords = ctx.EventLists.Count(); //See if it really worked.

Upvotes: 1

Martin Noreke
Martin Noreke

Reputation: 4146

Try using the following instead:

string connectionString
        = "Data Source=(LocalDB)\\v11.0;AttachDbFilename="
        + fileSpec;

EventsListDBEntities ctx = new EventsListDBEntities();
ctx.Database.Connection.ConnectionString = connectionString;
ctx.Database.CreateIfNotExists(); // Change this line.
ctx.Database.Initialize(true);

https://msdn.microsoft.com/en-us/library/system.data.entity.database.createifnotexists%28v=vs.113%29.aspx#M:System.Data.Entity.Database.CreateIfNotExists

Upvotes: 1

Related Questions