step‐by‐step guide to creating custom database tables in Umbraco 8 using NPoco - FadiZahhar/umbraco8showandtell GitHub Wiki

🧰 Prerequisites

Ensure you have:

• An Umbraco 8 project set up and running.
• Access to the project's codebase.
• Basic knowledge of C# and Umbraco's structure.([Stack Overflow][1])

---

📁 Step 1: Define Your POCO Model

Create a class that represents your custom table. Use NPoco attributes to map the class to a database table.([Umbraco][2])

Example:

using NPoco;
using Umbraco.Core.Persistence.DatabaseAnnotations;

[TableName("MyCustomTable")]
[PrimaryKey("Id", AutoIncrement = true)]
[ExplicitColumns]
public class MyCustomModel
{
    [Column("Id")]
    [PrimaryKeyColumn(AutoIncrement = true)]
    public int Id { get; set; }

    [Column("Name")]
    public string Name { get; set; }

    [Column("Email")]
    public string Email { get; set; }
}

This class defines a table named MyCustomTable with three columns: Id, Name, and Email.

---

🛠️ Step 2: Create a Migration to Build the Table

Umbraco 8 uses a migration framework to handle database schema changes. Create a migration class that creates your custom table.([Stack Overflow][1])

Example:

using Umbraco.Core.Migrations;
using Umbraco.Core.Migrations.Expressions.Create;

public class CreateMyCustomTable : MigrationBase
{
    public CreateMyCustomTable(IMigrationContext context) : base(context) { }

    public override void Migrate()
    {
        if (!TableExists("MyCustomTable"))
        {
            Create.Table<MyCustomModel>().Do();
        }
    }
}

This migration checks if the table exists and creates it if it doesn't.

---

🧱 Step 3: Set Up a Migration Plan

Define a migration plan that includes your migration. This plan tells Umbraco which migrations to run.([Umbraco][2])

Example:

using Umbraco.Core.Migrations;

public class MyCustomMigrationPlan : MigrationPlan
{
    public MyCustomMigrationPlan() : base("MyCustomTable")
    {
        From(string.Empty)
            .To<CreateMyCustomTable>("mycustomtable-migration");
    }
}

This plan starts from an empty state and applies the CreateMyCustomTable migration.([Umbraco][3])

---

⚙️ Step 4: Register the Migration with a Composer and Component

Create a component that runs your migration plan and a composer to register the component with Umbraco.([Umbraco][2])

Component:

using Umbraco.Core;
using Umbraco.Core.Composing;
using Umbraco.Core.Migrations;
using Umbraco.Core.Scoping;
using Umbraco.Core.Services;
using Microsoft.Extensions.Logging;

public class MyCustomComponent : IComponent
{
    private readonly IScopeProvider _scopeProvider;
    private readonly IMigrationBuilder _migrationBuilder;
    private readonly IKeyValueService _keyValueService;
    private readonly ILogger<MyCustomComponent> _logger;

    public MyCustomComponent(
        IScopeProvider scopeProvider,
        IMigrationBuilder migrationBuilder,
        IKeyValueService keyValueService,
        ILogger<MyCustomComponent> logger)
    {
        _scopeProvider = scopeProvider;
        _migrationBuilder = migrationBuilder;
        _keyValueService = keyValueService;
        _logger = logger;
    }

    public void Initialize()
    {
        var upgrader = new Upgrader(new MyCustomMigrationPlan());
        upgrader.Execute(_scopeProvider, _migrationBuilder, _keyValueService, _logger);
    }

    public void Terminate() { }
}

Composer:

using Umbraco.Core;
using Umbraco.Core.Composing;

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

The composer registers the component, which runs the migration when Umbraco starts.([Umbraco][3])

---

🔄 Step 5: Use the Custom Table in Your Code

With the table created, you can now use Umbraco's IScopeProvider to interact with your custom table.([Umbraco][3])

Example:

using Umbraco.Core.Scoping;

public class MyCustomService
{
    private readonly IScopeProvider _scopeProvider;

    public MyCustomService(IScopeProvider scopeProvider)
    {
        _scopeProvider = scopeProvider;
    }

    public void AddRecord(string name, string email)
    {
        using (var scope = _scopeProvider.CreateScope())
        {
            var db = scope.Database;
            var record = new MyCustomModel { Name = name, Email = email };
            db.Insert(record);
            scope.Complete();
        }
    }
}

This service adds a new record to your custom table.([Umbraco][4])

---

✅ Summary

• Define your POCO model with NPoco attributes.
• Create a migration to build the table.
• Set up a migration plan.
• Register the migration with a component and composer.
• Use IScopeProvider to interact with your custom table.([Umbraco][2], [Umbraco][3])