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?
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; }
}
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?
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?
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.
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?
Nice, thank you Simon & Rodolphe. Are you able to share a source. Like you wrote ... where is documentation? ASP.NET? Or are there any Umbraco specifics? Thanks in advance.
I have done this by calling a custom UmbracoApiController which calls a custom service holding the database fetch, insert, delete functions. Will see if I can get an example tomorrow.
How are folks doing inserts into custom tables? In U7 I was doing like this in a SurfaceController form post:
var db = ApplicationContext.DatabaseContext.Database;
db.Insert(model);
The only "Insert" statement I see now is in MigrationBase, but it doesn't seem right to use that for regular inserts into the custom table beyond once the migration has run.
The way I have worked with it is by adding a service layer adding the database context and making the database calls, mainly by calling an api which then make the insert/update/fetch/delete either synchronous or asynchronous.
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?
Hi,
You can do it like this:
1 ) Implement a Component:
2 ) Add a Composer:
3 ) Add MigrationPlan and MigrationBase:
4 ) Implement your database model:
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
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?
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.
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?
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
Then add a second step in your migrationplan like this:
This is great help for what I are doing but I have to questions
How do I store my data in the table?
Can I trigger if the table is changed so I can do something else?
Nice, thank you Simon & Rodolphe. Are you able to share a source. Like you wrote ... where is documentation? ASP.NET? Or are there any Umbraco specifics? Thanks in advance.
I have done this by calling a custom UmbracoApiController which calls a custom service holding the database fetch, insert, delete functions. Will see if I can get an example tomorrow.
Underground, Umbraco use EF and NPoco as ORM
Hi !! Can you explain in an easy way??
Hi, sorry for late reply. You can find documentation here.
https://our.umbraco.com/documentation/extending/database/
I have found it too a moment ago ... ;-)
How are folks doing inserts into custom tables? In U7 I was doing like this in a SurfaceController form post:
The only "Insert" statement I see now is in MigrationBase, but it doesn't seem right to use that for regular inserts into the custom table beyond once the migration has run.
The way I have worked with it is by adding a service layer adding the database context and making the database calls, mainly by calling an api which then make the insert/update/fetch/delete either synchronous or asynchronous.
Thanks Simon, Can you provide some sample code for how you are accessing database context and making an Insert?
Here is an example service:
public class MyService { private readonly IScopeProvider scopeProvider; private readonly IProfilingLogger profilingLogger; public MyService(IScopeProvider scopeProvider, IProfilingLogger profilingLogger) { this.scopeProvider = scopeProvider; this.profilingLogger = profilingLogger; }public bool AddMyMobelToDb(MyModel model) { try { using (var scope = scopeProvider.CreateScope(autoComplete: true).Database) { scope.Insert(model); scope.CompleteTransaction(); } return true; } catch (Exception ex) { return false; } }I know this is not the answer but I would prefer to have "none-umbraco data" in a seperate database.
is working on a reply...