Creating a new database at runtime

Hi

I'm trying to figure out how to create a new database when starting up an MVC4 web site. So far, I have managed to create a CE database while running the migrations but I dont seem to be able to do create an express db as easily:

 private static void checkDB(Migrator migrator, ProviderType pt, string connectionString)
{
  try
  {
    Trace.WriteLine(migrator.CurrentVersion().ToString());
  }
  catch (Exception ex) // should really just catch the correct exception here but which one?
  {
    Trace.WriteLine(ex);

    if (pt == ProviderType.SqlServerCE)
    {
      var se = new System.Data.SqlServerCe.SqlCeEngine(connectionString);
      se.CreateDatabase();

      Trace.WriteLine("created new SQLCE 4 database");
    }
    else if (pt == ProviderType.SqlServer2005)
    {          
      // how to create a sql database?
    }
  }
}

Problems:

1) the connection string may or may not contain the path to the database file. If it did, or I new thins some other way then I could have an empty mdf file in the project resources and just write this out to disk (which would work well for a auto attach db in the app_data folder).

2) I cant easily debug this stuff as it's called during the app_start and the debugger doesn't seem to attach soon enough to catch any break points here.

I'm sure someone must have done this already ... ?

Ta

The way we do it in the designer is to connect to SQL Server using the standard SqlConnection, specifying Initial Catalog=master, then execute a CREATE DATABASE command. We cleverly solve issues like 'path to the database file' by ignoring them completely -- we just take the database name from the connection string in the .lsmodel -- but the CREATE DATABASE docs in Books Online have the details if this is a consideration for you.

I guess I was hoping that LightSpeed would create the database for me, given that it will create the schema using migrations.

Version 5? :}

This seems to work well enough:

try
  {
    Trace.WriteLine(migrator.CurrentVersion().ToString());
  }
  catch (SqlException sex)
  {
    if (pt == ProviderType.SqlServer2005 && sex.Errors.Count == 1 && sex.Errors[0].Number == 15350)
    {
      // get the file name from the connection string - must be a auto attach database that has a file name
      // eg: Server=.\SqlExpress;AttachDbFilename=|DataDirectory|Fred.mdf;User Instance=true;Trusted_Connection=true;
      var rx = new Regex(@"AttachDbFilename=\|.*\|(?<filename>[^;]*)");

      var matches = rx.Matches(connectionString);

      if (matches.Count > 0 && matches[0].Captures.Count > 0)
      {
        var filename = Path.Combine(dbFilePath, matches[0].Groups[1].Value);
        File.WriteAllBytes(filename, Resources.EmptyMDFFile);
      }
    }
    else
    {
      throw;
    }
  }