Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Cory Colt 33 posts 124 karma points
    Aug 02, 2019 @ 19:11
    Cory Colt
    0

    Create Custom Database Table in Umbraco 8

    This has been so incredibly frustrating trying to get everything working in Umbraco 8, so much so that I'm almost to the point where I'm either going to downgrade or else find a different CMS.

    Prior to v8 it was seemingly a lot simpler to create a custom database table, but I can't seem to figure this out in v8. It's even harder when there seems to be a striking lack of documentation relating to this.

    I saw one post referring to use migrations, but I must be missing something because I can't even seem to find any documentation surrounding how Umbraco implements migrations. I found a brief explanation of migrations in v8 here but I don't know where the code he even shows is supposed to live in order for it to get executed.

    Can someone PLEASE explain the process on how I can create a custom database table in Umbraco 8?

  • Simon Ulmbrant 11 posts 104 karma points
    Aug 03, 2019 @ 08:15
    Simon Ulmbrant
    102

    Hi,

    You can do it like this:

    1 ) Implement a Component:

    public class MyCustomComponent : IComponent
        {
            private readonly IScopeProvider scopeProvider;
            private readonly IMigrationBuilder migrationBuilder;
            private readonly IKeyValueService keyValueService;
            private readonly ILogger logger;
    
            public MyCustomComponent(
                IScopeProvider scopeProvider,
                IMigrationBuilder migrationBuilder,
                IKeyValueService keyValueService,
                ILogger logger)
            {
                this.scopeProvider = scopeProvider;
                this.migrationBuilder = migrationBuilder;
                this.keyValueService = keyValueService;
                this.logger = logger;
            }
    
            public void Initialize()
            {
                var upgrader = new Upgrader(new DatabaseMigrationPlan());
                upgrader.Execute(scopeProvider, migrationBuilder, keyValueService, logger);
            }
    
            public void Terminate()
            {
                throw new NotImplementedException();
            }
        }
    

    2 ) Add a Composer:

    public class MyCustomComposer : IUserComposer
        {
            public void Compose(Composition composition)
            {
                composition.Components()
                    .Append<MyCustomComponent>();
            }
        }
    

    3 ) Add MigrationPlan and MigrationBase:

    public class MyCustomMigrationPlan : MigrationPlan
        {
            public MyCustomMigrationPlan()
                :base("CustomDatabaseTable")
            {
                From(string.Empty)
                    .To<MigrationCreateTables>("first-migration");
            }
        }
    
    public class MigrationCreateTables : MigrationBase
    {
        public MigrationCreateTables(IMigrationContext context)
            :base(context)
        {
    
        }
    
        public override void Migrate()
        {
            if (!TableExists("MyCustomDatabaseTable"))
            {
                Create.Table<MyCustomDatabaseModel>().Do();
            }
        }
    }
    

    4 ) Implement your database model:

    [TableName("MyCustomDatabaseTable")]
        [PrimaryKey("Id", AutoIncrement = true)]
        [ExplicitColumns]
        public class MyCustomDatabaseModel
        {
            [Column("id")]
            [PrimaryKeyColumn(AutoIncrement = true)]
            public int Id { get; set; }
    
            [Column("MyCustomColumn")]
            public string MyCustomColumn { get; set; }
        }
    
  • Cory Colt 33 posts 124 karma points
    Aug 05, 2019 @ 15:48
    Cory Colt
    0

    Simon,

    This is perfect! I was able to get this working without any issue. I really appreciate you taking the time to lay this out like this for me. I do have one other question for you, in v8 is it still correct for me to use Surface Controllers to now insert data into this newly created table?

    Respectfully,

    Cory Colt

  • thao-arthaus 2 posts 22 karma points
    Jan 15, 2020 @ 15:49
    thao-arthaus
    0

    Hi Simon, I'm very new to Umbraco, and trying to understand this. Could you please advise me where this code is supposed to live in order for it to get executed? There are 4 steps in your instruction. Does that mean i need to create each file for each step (in total 4 files). Where should i place them? Do i need to install any packages for these to run?

  • Rodolphe Toots 10 posts 90 karma points
    Jan 22, 2020 @ 16:12
    Rodolphe Toots
    0

    You dont need to install any packages

    Your code can reside anywhere in your project, Umbraco will run your code throuch the composer mechanism. The code will run when umbraco starts.

    Preferrably put the composer in some app initializer folder and your database table model in your models folder or subfolder in your Core project in your solution.

  • Rodolphe Toots 10 posts 90 karma points
    Jan 23, 2020 @ 11:25
    Rodolphe Toots
    0

    Hi Simon! Great post, I have successfully done this. But now, if i want to add a new column, do you know how I go by then? Cant find any documentation for that.

    I suppose I should add a step in the migration plan. But do I need to create a new MigrationBase called "MigrationAlterTables" and there add some code for altering the table? And how should the alter table code look like?

  • Rodolphe Toots 10 posts 90 karma points
    Jan 25, 2020 @ 18:47
    Rodolphe Toots
    0

    Replying to my own question here :-)

    Yes, create a new class derived from MigratonBase where code for altering the table is added in the Migrate() method

    public class MyCustomMigration2 : MigrationBase
    {
        public MyCustomMigration2(IMigrationContext context) : base(context)
        {
        }
    
        public override void Migrate()
        {
            if (!ColumnExists("MyTAbleName", "MyColumnName"))
            {
                Alter.Table("MyTableName").AddColumn("MyColumnName").AsBoolean().Nullable().Do();
    
            }
        }
    }
    

    Then add a second step in your migrationplan like this:

    public class MyCustomMigrationPlan : MigrationPlan
    {
        public MyCustomMigrationPlan() : base("CustomDatabaseTable")
        {
            From(string.Empty)
                .To<MyCustomMigration1>("first-migration");
                .To<MyCustomMigration2>("second-migration");
        }
    }
    
Please Sign in or register to post replies

Write your reply to:

Draft