create custom tables in umbraco

Go to solution

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

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

Bilal Haidar 144 posts 410 karma points

Sep 15, 2016 @ 12:55

Hi, I am trying to create a custom table in Umbraco using the following code. However, it fails to create the table. There are no logs even.

 protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
    {
        // create database tables
        CreateTables(applicationContext);
    }
    public static void CreateTables(ApplicationContext applicationContext)
    {
        //Get the Umbraco Database context
        var dbContext = applicationContext.DatabaseContext;

        //Get the Database Helper
        var helper = new DatabaseSchemaHelper(dbContext.Database, applicationContext.ProfilingLogger.Logger, dbContext.SqlSyntax);

        //Check if the DB table does NOT exist
        if (!helper.TableExist("Comment"))
        {
            //Create DB table - and set overwrite to false
            helper.CreateTable<Comment>(false);
        }
    }

Thanks /Bilal

Copy Link

Michaël Vanbrabandt 863 posts 3348 karma points c-trib

Sep 15, 2016 @ 13:00
0
Hi Bilal,

can you show us your Comment DTO class?

Make sure you have added the namespace:
```
using Umbraco.Core.Persistence;
```
And added the attribute:
```
[TableName("Comment")]
```
to your Comment class.

/Michaël
Copy Link

Bilal Haidar 144 posts 410 karma points

Sep 15, 2016 @ 13:13

Here you go:

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

namespace bumbraco.Logic.Models
{
    [TableName("Comment")]
    [PrimaryKey("CommentId", autoIncrement = true)]
    [ExplicitColumns]
    public class Comment
    {
        [Column("id")]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int CommentId { get; set; }

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

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

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

        [Column("Message")]
        [SpecialDbType(SpecialDbTypes.NTEXT)]
        public string Message { get; set; }
    }
}

Copy Link

Michaël Vanbrabandt 863 posts 3348 karma points c-trib

Sep 15, 2016 @ 13:23

0

Can you add a breakpoint in your CreateTables to see what happens during every step?

/Michaël

Copy Link
Bilal Haidar 144 posts 410 karma points

Sep 15, 2016 @ 13:26
0
For some reason, VS is getting stuck on the line of
```
        var helper = new DatabaseSchemaHelper(dbContext.Database, applicationContext.ProfilingLogger.Logger, dbContext.SqlSyntax);
```
And suddenly it opens the page without letting me continue debugging. Any idea why?

Thanks
Copy Link
Michaël Vanbrabandt 863 posts 3348 karma points c-trib

Sep 15, 2016 @ 13:29

0

Can you show me the full code of your events class?

Copy Link
Bilal Haidar 144 posts 410 karma points

Sep 15, 2016 @ 13:46
0
I was looking at the wrong database. I was using Compact Edition. The table was actually created!

However, now when I try to add a new record I receive this message:
```
Cannot insert explicit value for identity column in table 'Comment' when IDENTITY_INSERT is set to OFF. 
```
Is there a way to set the IDENTITY_INSERT to ON using the DTO class?

Thanks
Copy Link
Michaël Vanbrabandt 863 posts 3348 karma points c-trib

Sep 15, 2016 @ 13:57
100
You have a issue in your DTO class.

You have added the attribute:
```
[PrimaryKey("CommentId", autoIncrement = true)]
```
But then on your property you add the attribute:
```
[Column("id")]
```
And your property is called:
```
public int CommentId { get; set; }
```
Change it to id or commentId, don't mix them up.

Hope this helps!

/Michaël
Copy Link
Bilal Haidar 144 posts 410 karma points

Sep 16, 2016 @ 06:09

0

Hi Michael, Can you pls have a look at a related post here: https://our.umbraco.org/forum/extending-umbraco-and-using-the-api/80071-using-constraint-with-custom-data-table-cannot-insert-nulls

Thanks

Copy Link
is working on a reply...

Please Sign in or register to post replies