Merge aspnetdb.mdf with my own database (automatically generated)

asp.net-mvc, aspnetdb, connection-string, database

Solution

I had the same problem but this link got me on the track to something that worked at least for me. I hope this helps someone at least! :)

- Create a database

- Add the aspnet tables to the new database

- Fix the database connections in web.config so they point to the same database

- Write some sql that removes all tables except the ones that start with "aspnet_"

- Add the sql to the database initializer you write by your self

- Add a call to the database initializer in Global.asax.cs

1. Create a database

I usually do this with SQL Server Management Studio. The database I used for this example code is SQL Server 2008R2 but I have done the same with SQL Server Express that you use.

2. Add the aspnet tables to the new database

I use the following tool which if you use it without any command line arguments works like a wizard. %windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe

3. Fix the database connections so they point to the same database

The following two lines are from the test application I made. Notice that the name of the second connectionstring (MyHealthContext) is identical to the name of the DbContext I am using for my code first classes.

DbContext:

public class MyHealthContext : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<PersonAttribute> PeopleAttributes { get; set; }
}

Web.config

<add name="ApplicationServices" connectionString="Server=localhost\mssql2008r2;Database=MyHealth;Integrated Security=True;" providerName="System.Data.SqlClient"/> 
<add name="MyHealthContext" connectionString="Server=localhost\mssql2008r2;Database=MyHealth;Integrated Security=True;" providerName="System.Data.SqlClient"/>

4. SQL that removes all but the aspnetdb-tables

DECLARE @cmdDropConstraints VARCHAR(4000)
DECLARE @cmdDropTables      VARCHAR(4000)

-- ======================================================================
-- DROP ALL THE FOREIGN KEY CONSTRAINTS FROM THE TABLES WE WANT TO DROP
-- ======================================================================
DECLARE cursorDropConstraints CURSOR FOR 
    SELECT 
        'ALTER TABLE ['+ s.name + '].[' + t.name + '] DROP CONSTRAINT [' + f.name +']' 
    FROM 
        sys.foreign_keys f 
        INNER JOIN sys.tables t ON f.parent_object_id=t.object_id 
        INNER JOIN sys.schemas s ON t.schema_id=s.schema_id 
    WHERE 
        t.is_ms_shipped=0
        AND t.name NOT LIKE 'aspnet_%'
        AND t.name <> 'sysdiagrams'

OPEN cursorDropConstraints
WHILE 1=1
BEGIN
    FETCH cursorDropConstraints INTO @cmdDropConstraints
    IF @@fetch_status != 0 BREAK
    EXEC(@cmdDropConstraints)
END
CLOSE cursorDropConstraints
DEALLOCATE cursorDropConstraints;

-- ======================================================================
-- DROP ALL THE RELEVANT TABLES SO THAT THEY CAN BE RECREATED
-- ======================================================================
DECLARE cursorDropTables CURSOR FOR 
    SELECT 
        'DROP TABLE [' + Table_Name + ']'
    FROM 
        INFORMATION_SCHEMA.TABLES
    WHERE
        Table_Name NOT LIKE 'aspnet_%'
        AND TABLE_TYPE <> 'VIEW'
        AND TABLE_NAME <> 'sysdiagrams'

OPEN cursorDropTables
WHILE 1=1
BEGIN
    FETCH cursorDropTables INTO @cmdDropTables
    IF @@fetch_status != 0 BREAK
    EXEC(@cmdDropTables)
END
CLOSE cursorDropTables
DEALLOCATE cursorDropTables;

5. Code for the database initializer:

Replace the "SQL CODE GOES HERE" below with the sql from step 4

public class MyHealthInitializerDropCreateTables : IDatabaseInitializer<MyHealthContext>
{
    public void InitializeDatabase(MyHealthContext context)
    {
        bool dbExists;
        using (new TransactionScope(TransactionScopeOption.Suppress))
        {
            dbExists = context.Database.Exists();
        }

        if (dbExists)
        {
            // Remove all tables which are specific to the MyHealthContext (not the aspnetdb tables)
            context.Database.ExecuteSqlCommand(@"SQL CODE GOES HERE");

            // Create all tables which are specific to the MyHealthContext (not the aspnetdb tables)
            var dbCreationScript = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();
            context.Database.ExecuteSqlCommand(dbCreationScript);

            Seed(context);
            context.SaveChanges();
        }
        else
        {
            throw new ApplicationException("No database instance");
        }
    }

    protected virtual void Seed(MyHealthContext context)
    {
        //TODO: Add code for seeding your database with some initial data...
    }
}

6. Code that hooks in your new database initializer

To make sure that the custom database initializer isn't accidentily run in the production environment i added a #if DEBUG statement since I always compile my code in release mode before publishing.

    protected void Application_Start()
    {
        //TODO: Comment out this database initializer(s) before going into production
        #if DEBUG
        Database.SetInitializer<MyHealthContext>(new MyHealthInitializerDropCreateTables()); // Create new tables in an existing database
        #endif

        AreaRegistration.RegisterAllAreas();

        RegisterGlobalFilters(GlobalFilters.Filters);
        RegisterRoutes(RouteTable.Routes);
    }

Problem

Original source