Copied to clipboard

Flag this post as spam?

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


  • Marcio Goularte 372 posts 1344 karma points
    Feb 27, 2019 @ 13:55
    Marcio Goularte
    0

    how to create custom tables, Migration, UmbracoDatabase, Npoco

    Guys, I do not know if others have the same difficulty. Before asking a question here I always see the documentation and examples from the community and I did not find answers.

    How do you use NPoco in Umbraco? I know how to use Npoco, mapping, columns, etc. But how to create a table in database? V7 I used migration and run with ApplicationEventHandler, https://gist.github.com/tcmorris/eae1fd6d5de0d600a3e3f7f74a05016f

    but and now?

    In this post: https://our.umbraco.com/forum/umbraco-8/95162-v8-create-table-based-on-poco-with-attributes

    they talk to use new version of migration.

    The only example I found was this: https://www.zpqrtbnk.net/posts/migrations-in-v8/

    public class MyProjectUpgradeComponent : UmbracoUserComponent
    {
        public override Initialize(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger)
        {
            var plan = new MigrationPlan("MyProject");
            plan.From(string.Empty)
                .To<Migration1>("state-1")
                .To<Migration2>("state-2")
                .To<Migration3>("state-3");
    
            var upgrader = new Upgrader(plan);
            upgrader.Execute(scopeProvider, migrationBuilder, keyValueService, logger);
        }
    }
    

    But this class UmbracoUserComponent does not exist in Umbraco.Core. In the IComponent implementation I do not have the MigrationBuilder parameter to pass the Upgrader method execute.

    https://our.umbraco.com/documentation/Tutorials/Porting-Packages-V8/#new-composer--component-way

    the Initialize method does not have these parameters IScopeProvider, IMigrationBuilder

    how to run the migration in new way?

    The ApplicationContext no longer exists. Now we have "Current", which seems to be much better. But I did not find anything about UmbracoDatabase. Previously we had ApplicationContext.Current.DatabaseContext.Database. I found only SqlContext but without the UmbracoDatabase object. How do I query in the database? insert? update? delete?

    I found this example: https://our.umbraco.com/forum/umbraco-8/95162-v8-create-table-based-on-poco-with-attributes#comment-300870

    using (var scope = Current.ScopeProvider.CreateScope())
    {
        var sql = scope.SqlContext.Sql()
            .Select<Record>(x=>x.Form)
            .From<Record>()
            .Where<Record>(x => x.UniqueId == recordId);
    
        var formId = scope.Database.ExecuteScalar<Guid>(sql);
    
        scope.Complete();
    
        return GetForm(formId);
    }
    

    is that so? Is this practice?

    I think there was a little more documentation for the basic things we always do in the umbraco.

    I know it's early, I even apologize for being rushed. But I wanted to start a project already with umbraco 8 and having a table in the custom database is something we always do. And the documentation does not address this. purpose of this post is to elucidate these my doubts to contribute with the documentation

  • Kevin Jump 2306 posts 14669 karma points MVP 7x c-trib
    Feb 27, 2019 @ 14:52
    Kevin Jump
    4

    Hi Marcio,

    Yes, the blog post you found is slightly out of date in that the namespaces and some classes have changed since it was written.

    as of v8 you can do the following (assuming you have a class with the NPoco table/column settings as you want it)

    Create a migration class

        public class MigrationCreateTables : MigrationBase
        {
            public MigrationCreateTables(IMigrationContext context) 
                : base(context)
            { }
    
            public override void Migrate()
            {
                if (!TableExists("myTable"))
                    Create.Table<MyNPocoTableClass>().Do();
            }
        }
    

    Create a migration plan

    public class MyMigrationPlan : MigrationPlan
    {
        public TranslationMigrationPlan() 
            : base("MyApplicationName")
        {
            From(string.Empty)
                .To<MigrationCreateTables>("first-migration");
        }
    }
    

    Have a component to start the migration plan (this is where UmbracoUserComponent has gone!)

    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()
        {
            // perform any upgrades (as needed)
            var upgrader = new Upgrader(new MyMigrationPlan());
            upgrader.Execute(scopeProvider, migrationBuilder, keyValueService, logger);
    
        }
    

    Register the component in the compositions (so that it is called by Umbraco)

        public class MyAppComposer: IUserComposer
        {
            public void Compose(Composition composition)
            {
                // component for startup
                composition.Components()
                    .Append<MyCustomComponent>();
            }
        }
    
  • David Armitage 503 posts 2071 karma points
    Jan 06, 2020 @ 14:57
    David Armitage
    0

    Hi Kevin,

    I am just trying to implement t your solutions but having a little trouble with the TranslationMigrationPlan.

    I used your code for this and it is complaining that the method must have a return type. Is there something in the method you can see is slightly off?

    public class MyMigrationPlan : MigrationPlan
    {
        public TranslationMigrationPlan() : base("MyApplicationName")
        {
            From(string.Empty).To<MigrationCreateTables>("first-migration");
        }
    }
    

    Thanks

    David

  • Kevin Jump 2306 posts 14669 karma points MVP 7x c-trib
    Jan 06, 2020 @ 15:07
    Kevin Jump
    0

    Hi David

    yes - the constructor name needs to be the same as the class name, i guess i cut and pasted it wrong from an existing project. so function name should be MyMigrationPlan()

    public class MyMigrationPlan : MigrationPlan
    {
        public MyMigrationPlan () 
            : base("MyApplicationName")
        {
            From(string.Empty)
                .To<MigrationCreateTables>("first-migration");
        }
    }
    
  • David Armitage 503 posts 2071 karma points
    Jan 06, 2020 @ 15:10
    David Armitage
    0

    Ok great. I will give this another go tomorrow.

    Thanks for your help.

  • David Armitage 503 posts 2071 karma points
    Jan 07, 2020 @ 03:29
    David Armitage
    0

    Hi Kevin,

    That worked a treat. Thanks for you help.

    I also found this link in the meantime which also helped. https://our.umbraco.com/documentation/Extending/database/

    Copying the whole script into a class worked fine.

    Hope it helps someone else.

  • Marcio Goularte 372 posts 1344 karma points
    Feb 27, 2019 @ 18:45
    Marcio Goularte
    0

    thank you very much Kevin.

    now I understand how it works. I did not know I could use a constructor MyCustomComponent like that.

    What about ApplicationContext.Current.DatabaseContext.Database, do you know how to access the Umbracodatabase instance? is actually using the practice of the sample code I showed:

    using (var scope = Current.ScopeProvider.CreateScope())
    {
        var sql = scope.SqlContext.Sql()
            .Select<Record>(x=>x.Form)
            .From<Record>()
            .Where<Record>(x => x.UniqueId == recordId);
    
        var formId = scope.Database.ExecuteScalar<Guid>(sql);
    
        scope.Complete();
    
        return GetForm(formId);
    }
    
  • Kevin Jump 2306 posts 14669 karma points MVP 7x c-trib
    Feb 27, 2019 @ 21:01
    Kevin Jump
    104

    Hi

    yeah, ideally you should be looking at using dependency injection to get the scope in your class

    public class MyService
    {
    
        private readonly IScopeProvider scopeProvider;
    
        public MyService(IScopeProvider scopeProvider)
        {
            this.scopeProvider = scopeProvider;
        }
    
        public MyObject GetList(int id)
        {
            using (var scope = scopeProvider.CreateScope(autoComplete: true))
            {
                var sql = scope.SqlContext.Sql()
                    .Select("*")
                    .Where<MyObject>(x => x.Id == id);
    
                return scope.Database.SingleOrDefault<MyObject>(sql);
            }
        }
    }
    

    and you register your class in the composer (you created for the migrations)

    composition.Register<MyService>();
    

    or

    composition.RegisterUnique<MyService>();
    

    Register will be loaded as needed, RegisterUnique is more like a singleton pattern, only one instance will be loaded and will have a lifetime of the application.

    This example is flattened a bit because you might want to have a service/repository pattern but that's the basics of it.

  • Marcio Goularte 372 posts 1344 karma points
    Feb 27, 2019 @ 23:36
    Marcio Goularte
    0

    great!! thank you Kevin

  • Nigel Wilson 944 posts 2076 karma points
    Mar 18, 2019 @ 17:31
    Nigel Wilson
    0

    Hi Marcio

    Sorry to jump onto your forum post, but I am working on migrating the Tag Manager package to V8.

    I have one last issue to solve and I will be able to build the package and see if it works (fingers crossed).

    My issue relates to IScopeProvider - in terms of your code are you able to show me your code where you call MyService.GetList(12345) ?

    What I am trying to understand is how to instantiate a new Scope provider to pass to the constructor of MyService.

    Hope this makes sense.

    Cheers

    Nigel

  • Kevin Jump 2306 posts 14669 karma points MVP 7x c-trib
    Mar 18, 2019 @ 17:48
    Kevin Jump
    2

    Hi Nigel,

    the Dependency Injection built into Umbraco 8 does the instantiation of the Scope provider and other elements for you.

    to utilize this you need to create a class that inherits IUserComposer, to set up your service.

    For details see the documentation : https://our.umbraco.com/Documentation/Implementation/Composing/

    but in summary you have a class that looks a bit like:

    public class myComposer : IUserComposer
        {
            public void Compose(Composition composition)
            {
               composition.Register<MyService>();
            }
    }
    

    Then when you are using your service, you can use the Dependency injection to call set it up

    so for example in an ApiController you might have :

    public class myApiController : UmbracoAuthorizedApiController
    {
        private readonly MyService _myService
    
        public uSyncDashboardApiController(MyService myService)
        {
            _myService = myService;
        }
    }  
    

    you could then use _myservice throughout the class, and the scope provider will have been injected to your service by the Dependency Injection.

  • Nigel Wilson 944 posts 2076 karma points
    Mar 21, 2019 @ 01:10
    Nigel Wilson
    0

    Hi Kevin

    Premature excitement... discovered my code was bung!

    So currently I have the following controller for my TagManager Tree

    public class TagManagerTreeController : TreeController
        {
            private TagManagerAPIController tmapictlr;
    
            public TagManagerTreeController()
            {            
                tmapictlr = new TagManagerAPIController();
            }
    ...
    }
    

    But tmapictlr = new TagManagerAPIController(); isn't happy as my APIController is as follows:

    [PluginController("TagManager")]
        public class TagManagerAPIController : UmbracoAuthorizedJsonController, IUserComposer
        {
            private IScopeProvider _scopeProvider;
    
            public TagManagerAPIController(IScopeProvider scopeProvider)
            {
                _scopeProvider = scopeProvider;
            }
    ...
    }
    

    Quite simply I don't understand how to create / pass a "scopeProvider" to my constructor method.

    Are you able to please help ?

    Thanks

    Nigel

  • Rasmus Eeg 91 posts 457 karma points c-trib
    Aug 10, 2019 @ 12:31
    Rasmus Eeg
    0

    Hi Nigel,

    You are should not create a new instance of our controller anywhere else then through web request. Which is already covered by MVC.

    What you should do instead, is expose your business logic through a custom service, like discussed above.

    Hope this helps :)

Please Sign in or register to post replies

Write your reply to:

Draft