Copied to clipboard

Flag this post as spam?

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


  • Dan Diplo 1459 posts 5623 karma points MVP 3x c-trib
    Dec 21, 2015 @ 12:04
    Dan Diplo
    1

    How do you use DatabaseSchemaHelper to create SQL tables etc. with PetaPoco?

    I've been dynamically creating SQL tables using PetaPoco in Umbraco 7. Some example code to give an idea of what I've been doing:

    public void OnApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
    {
        //Get the Umbraco Database context
        var db = applicationContext.DatabaseContext.Database;
    
        //Check if the DB table does NOT exist
        if (!db.TableExist("JobAlertQueue"))
        {
            //Create DB table - and set overwrite to false
            db.CreateTable<JobAlertQueueItem>(false);
        }
    }
    

    So in this example it checks whether a table exists and, if not, creates one. This all works fine. However, since Umbraco 7.3.x it appears the CreateTable<T> extension has been deprecated in favour of DatabaseSchemaHelper.

    So now I get compiler warnings like:

    'Umbraco.Core.Persistence.PetaPocoExtensions.CreateTable<T>(Umbraco.Core.Persistence.Database, bool)' is obsolete: 'Use the DatabaseSchemaHelper instead'
    'Umbraco.Core.Persistence.PetaPocoExtensions.TableExist(Umbraco.Core.Persistence.UmbracoDatabase, string)' is obsolete: 'Use the DatabaseSchemaHelper instead'
    

    However I can't find any documentation on how to use the DatabaseSchemaHelper to do this. I'm not even sure how you get an instance of the class - does it follow the singleton convention like the ApplicationContext.Services or do you need to construct it? If the latter, how do you construct it - the signature is confusing:

    DatabaseSchemaHelper(Database db, ILogger logger, ISqlSyntaxProvider syntaxProvider);
    

    How do I get an instance of an SqlSyntaxProvider?

    Anyone know how to migrate to using DatabaseSchemaHelper?

  • Morten Christensen 594 posts 2747 karma points admin hq c-trib
    Dec 21, 2015 @ 12:24
    Morten Christensen
    4

    Hi Dan,

    Something like this should work

    var logger = LoggerResolver.Current.Logger;
    var dbContext = ApplicationContext.Current.DatabaseContext;
    var helper = new DatabaseSchemaHelper(dbContext.Database, logger, dbContext.SqlSyntax);
    helper.CreateTable<JobAlertQueueItem>(false);
    

    HTH,

    Morten

  • Morten Christensen 594 posts 2747 karma points admin hq c-trib
    Dec 21, 2015 @ 12:27
    Morten Christensen
    123

    Example refactored:

    public void OnApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
    {
        //Get the Umbraco Database context
        var ctx = applicationContext.DatabaseContext;
        var db = new DatabaseSchemaHelper(ctx.Database, applicationContext.ProfilingLogger.Logger, ctx.SqlSyntax);
    
        //Check if the DB table does NOT exist
        if (!db.TableExist("JobAlertQueue"))
        {
            //Create DB table - and set overwrite to false
            db.CreateTable<JobAlertQueueItem>(false);
        }
    }
    
  • Chris Wilson 100 posts 377 karma points
    Mar 18, 2016 @ 11:34
    Chris Wilson
    0

    Thanks Morten - came across this one this morning and you saved me a lot of digging!

    /Chris

  • Marc Stöcker 100 posts 546 karma points c-trib
    Apr 29, 2016 @ 09:24
    Marc Stöcker
    0

    Thanks Morten, exactly the boilerplate I was hoping to find. :)

    Marc.

  • Dan Diplo 1459 posts 5623 karma points MVP 3x c-trib
    Dec 21, 2015 @ 13:18
    Dan Diplo
    0

    Thanks, Morten, that's exactly what I was looking for! #h5yr

  • Jonathan Roberts 336 posts 938 karma points
    Jul 04, 2016 @ 14:02
    Jonathan Roberts
    0

    Hi, Is DatabaseSchemaHelper still in the latest Umbraco: 7.4.3?

    Jon

  • Alex Skrypnyk 5329 posts 19591 karma points MVP 3x admin c-trib
    Aug 11, 2016 @ 21:59
    Alex Skrypnyk
    0

    Hi Jon,

    Yes, it is there.

    Thanks

  • Martin Kyukov 26 posts 176 karma points
    Oct 13, 2016 @ 08:55
    Martin Kyukov
    1

    Great answer Morten, it worked for me too.

    Thank you.

  • Tim Geyssens 6419 posts 15036 karma points MVP 2x c-trib
    Oct 13, 2016 @ 09:10
    Tim Geyssens
    0

    Yup same here, thanks for the details :)

  • Jason Elkin 26 posts 203 karma points
    Oct 17, 2018 @ 15:54
    Jason Elkin
    3

    Found this really helpful but also noticed that we don't actually need to call db.TableExist("JobAlertQueue").

    Looking at the source I can see that db.CreateTable<T>(false) will make a call to db.TableExist(tableName) and use the bool passed in to determine whether or not to overwrite the table.

    So we could (and probably should) simplify it to:

    public void OnApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
    {
        //Get the Umbraco Database context
        var db = applicationContext.DatabaseContext.Database;
    
        //setting overwrite to false only creates the table if it doesn't exist 
        db.CreateTable<JobAlertQueueItem>(false);
    
    }
    

    This stops us having to hard-code the table name as it will get picked up from the type that was passed in .

  • Marc Stöcker 100 posts 546 karma points c-trib
    Oct 22, 2018 @ 09:46
    Marc Stöcker
    0

    Thanks for taking the time to let us know, Jason! This really simplifies it greatly. I really like that I don't need to pass the table name as a string anymore.

    Maybe you could update the solution of this thread to Jasons's answer, @Dan Diplo? Don't know if that is even possible, though.

  • Bjarne Fyrstenborg 1140 posts 3225 karma points MVP 3x c-trib
    Oct 22, 2018 @ 12:18
    Bjarne Fyrstenborg
    1

    Recently there has also been added a new overload TableExist<T>() https://github.com/umbraco/Umbraco-CMS/pull/2492 , but for older versions of Umbraco you can also do something like the following.

    If you create a dto class like this, you have the reference to the table name in the class and can access this in e.g. application startup event:

    [TableName("jobAlertQueue")]
    [PrimaryKey("Id", autoIncrement = true)]
    [ExplicitColumns]
    internal class JobAlertQueueDto
    {
        [Column("Id")]
        public int Id { get; set; }
    
        [Column("Name")]
        public string Name { get; set; }
    }
    

    or something like this:

    [TableName(TableName)]
    [PrimaryKey(PrimaryKey, autoIncrement = true)]
    public class JobAlertQueueDto
    {
        public const string TableName = "jobAlertQueue";
    
        public const string PrimaryKey = "Id";
    
        [Column(PrimaryKey)]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int Id { get; set; }
    
        [Column("Name")]
        public string Name { get; set; }
    }
    

    and then use it like this:

    // Check if the DB table does NOT exist
    if (!dbHelper.TableExist(JobAlertQueueDto.TableName))
    {
        // Create DB table
        dbHelper.CreateTable<JobAlertQueueDto>(false);
    }
    

    /Bjarne

  • Dave Woestenborghs 3205 posts 10708 karma points MVP 4x admin c-trib
    Oct 22, 2018 @ 10:07
    Dave Woestenborghs
    0

    Personally I prefer to use migrations for that :

    https://cultiv.nl/blog/using-umbraco-migrations-to-deploy-changes/

    Dave

  • Marc Stöcker 100 posts 546 karma points c-trib
    Oct 22, 2018 @ 10:12
    Marc Stöcker
    0

    Yes, that's a very good approach. Especially because it solves the all problems that will otherwise pop up down the road, when the projekt is live and needs updates/fixes.

    On the other hand, sometimes checking and creating a simple table at startup is all you need. :)

  • Dave Woestenborghs 3205 posts 10708 karma points MVP 4x admin c-trib
    Oct 22, 2018 @ 10:25
    Dave Woestenborghs
    0

    But in a migration you can still do the same.

    https://github.com/umbraco/Umbraco-CMS/blob/1bb593d264a085f94a3ce3bd710392b350561430/src/Umbraco.Tests/Migrations/CreateTableMigrationTests.cs#L52

    Without the dependency on the database context.

    Dave

Please Sign in or register to post replies

Write your reply to:

Draft