November Happy Hour will be moved to Thursday December 5th.

Custom DB tables and stored procedures for DXC site

Vote:
 

Hi,

I have some custom tables and stored procedures that I need to deploy to DXC and I understand I can acheive this using an initialisation module or EF and code first migrations.

Does anyone have an opinion on which method is 'better'? Can anyone point me to some examples for each approach (I haven't really been able to find anything useful so far).

Thanks in advance,

Mark

#174763
Feb 03, 2017 13:39
Vote:
 

Hi,

You don't use one over the other. We use an initializable module to run EF code first migrations automatically. You can follow all EF migrations guides to set everything up, but instead of doing the migrations in Application_Start in global, you do them in an initializable module.

#174799
Feb 03, 2017 22:37
Vote:
 

Hi Johan,

I see, from the documentation it hints that it's one or the other, thanks for the clarification.

I'm having a few issues setting this up and it's difficult to know whether it's worked or not. I'm using VS 2015 and I see from the publish dialog on the Settings screen there is an option to 'Update database' and a link to 'Configure database updates'. Clicking the link provides the ability to add a SQL script, do you know if this will work? All my changes are already scripted and this seems like a better option as I can control what database changes get deployed easier (although I guess this could cause issues as it gets deployed through the DXC environments?).

Thanks,

Mark

#174941
Feb 08, 2017 11:45
Vote:
 

Hi,

I can't tell you really what the best approach is. But I prefer to have the migrations in my code, with a fluent API. This is what we have in our module:

using System.Data.Entity;
using EPiServer.Framework;
using EPiServer.Framework.Initialization;

[ModuleDependency(typeof(EPiServer.Web.InitializationModule))]
public class MigrationInitialization : IInitializableModule
{
    public void Initialize(InitializationEngine context)
    {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<TheDbContextHere, TheDbMigrationConfigurationHere>());
    }

    public void Uninitialize(InitializationEngine context)
    {
        // Nothing
    }
}

This will make sure appropriate migrations are executed on startup. TheDbMigrationConfigurationHere class will be generated when you enable code-first migrations for your DB context.

#174944
Edited, Feb 08, 2017 12:08
Vote:
 

I would say this is more an Entity Framework specific question and you will probably find or get better answers at Stack Overflow.

#174945
Feb 08, 2017 12:10
Vote:
 

Hi Johan,

Thanks for the update and your code sample.

At the moment I don't know if it's not working as a result of the EF setup or my initialization module (code below). I've checked lots of EF samples and as far as I can tell mine is set up as expected.

    [InitializableModule]
    [ModuleDependency(typeof(EPiServer.Web.InitializationModule))]
    public class DatabaseInitializationModule : IInitializableModule
    {
        public void Initialize(InitializationEngine context)
        {
	    //DBContext = partial class in separate project that inherits from DbContext defines DbSet of tables and has OnModelCreating method
	    //MigrationConfiguration = public class in same project as context file in Migrations folder has empty Seed method
	    Database.SetInitializer(new MigrateDatabaseToLatestVersion<DBContext, MigrationConfiguration>());
        }

        public void Uninitialize(InitializationEngine context)
        {
            //Add uninitialization logic
        }
    }

Is there any way to troubleshoot or debug the initialization module to see if it's being hit on delpyment to DXC?

Thanks,

Mark




 

#175021
Feb 09, 2017 15:36
Vote:
 

Migrations are tricky. Especially the "initial create" one. I'm pretty sure that your issues are in the EF setup. You can debug locally, just delete the MigrationsHistory table and all tabels you have in your context. Then you should get the same errors locally as in Azure when no migrations have been applied.

#175022
Feb 09, 2017 15:45
Vote:
 

Presumably I'll need to run the Update-Database command from the NuGet console against my class library to test this locally once I've deleted the tables from the database.

#175023
Feb 09, 2017 15:51
Vote:
 

No, that's the tricky part. You want your migrations to do that for you, since you don't want to do that manually in Azure.

#175024
Feb 09, 2017 15:52
Vote:
 

Ok, so I just need to run the site, the initialization module should kick in and then hopefully I should see an error. Is there any way to debug this to see what's going on? Will adding some break points to my DbContext file work?

#175025
Feb 09, 2017 15:56
Vote:
 

You can add a breakpoint in the initialization module. But you need to restart the application for it to kick in (since it's only running once on application startup).

I've never debugged the migrations step, so sorry can't help you there.

#175026
Feb 09, 2017 16:01
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.