Copied to clipboard

Flag this post as spam?

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


  • Dibs 202 posts 991 karma points
    Jun 26, 2019 @ 14:39
    Dibs
    0

    access server-side data to a property editor

    Dear Umbraco Team

    I am trying to create a property editor and retrieve data from custom table in the Umbraco database, going through this tutorial https://our.umbraco.com/Documentation/Tutorials/Creating-a-Property-Editor/part-4

    Problem is UmbracoContext.Application does not exist ?

    here is my controller code

    [Umbraco.Web.Mvc.PluginController("dummy")]
    public class PersonApiController : UmbracoAuthorizedJsonController
    {
    
        public IEnumerable<Person> GetAll()
        {
            var db = UmbracoContext.Application.DatabaseContext.Database;
            var d = UmbracoContext.Application
            // build a query to select everything the people table
            var query = new Sql().Select("*").From("people");
            // fetch data from DB with the query and map to Person object
            return db.Fetch<Person>(query);
        }
    }
    

    Has this changed for Umbraco 8 ? Anyone give me pointers how to get data from a custom table to consume via property editors ?

    Thanks Dibs

  • Dibs 202 posts 991 karma points
    Jun 28, 2019 @ 14:15
    Dibs
    0

    Cant read last edit by @NickJonas

  • Nik 1591 posts 7148 karma points MVP 6x c-trib
    Jun 28, 2019 @ 14:46
    Nik
    0

    Hi Dibs,

    Database access in v8 has changed alot compared to how it was done in v7. There are a few more steps that you need to do, even just for reading.

    For ease I'd add the following member field and constructor to your controller:

    private readonly IScopeProvider scopeProvider;
    
    public PersonApiController(IScopeProvider scopeProvider)
    {
        this.scopeProvider = scopeProvider ?? throw new System.ArgumentNullException(nameof(scopeProvider));
    }
    

    This will use the built in Dependency injection to pass in an instance of the scope provider that you will need to access the database.

    Then to query your database you will need to do something like this:

    List<Person> people;
    using(var scope = scopeProvider.CreateScope())
    {
          var query = scope.Database.Query<Person>();
          people = query.ToList();
          scope.Complete();
    }
    

    This, I think, should give you all the people in your Person table :-)

    Nik

  • Dibs 202 posts 991 karma points
    Jul 05, 2019 @ 10:31
    Dibs
    0

    Hi Nik

    Cheers for the update, update code to match yours, i get

    The specified table does not exist. [ person ]

    I created the table manually in the Umbraco database.

    Am i missing a step to create custom tables in Umbraco ?

    I have come across articles mentioning Npoco, it's wiki says mapping is done out of the box.

    Thanks Dibs

  • Bjarne Fyrstenborg 1280 posts 3990 karma points MVP 7x c-trib
    Jul 05, 2019 @ 11:22
    Bjarne Fyrstenborg
    0

    Can you show how the class Person is defined?

    It seems it isn't decorated with an attribute specifying the table name people, so by default it fetch data from a table matching the class name.

    So you should either rename the database table to person, rename the class Person to People or decorate the Person class with something like [TableName("people")]

    For example:

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

    /Bjarne

  • Dibs 202 posts 991 karma points
    Jul 05, 2019 @ 14:39
    Dibs
    0

    Hi Bjarne

    Here is my class

    using NPoco;
    
    namespace dummy.Model
    {
        [TableName("person")]
        [PrimaryKey("id")]
        [ExplicitColumns]
        public class Person
        {
            [Column("id")]
            public int Id { get; set; }
            [Column("name")]
            public string Name { get; set; }
            [Column("Town")]
            public string Town { get; set; }
            [Column("country")]
            public string Country { get; set; }
        }
    }
    

    and my controller

    public class PersonApiController : UmbracoAuthorizedJsonController
    {
        private readonly IScopeProvider _scopeProvider;
    
        public PersonApiController(IScopeProvider scopeProvider)
        {
            _scopeProvider = scopeProvider ?? throw  new System.ArgumentNullException(nameof(scopeProvider));
        }
    
        public IEnumerable<Person> GetAll()
        {
            List<Person> people;
            using (var scope = Current.ScopeProvider.CreateScope())
            {
                var query = scope.Database.Query<Person>();
                people = query.ToList();
                scope.Complete();
    
                return (people);
            }
        }
    }
    

    Thanks Dibs

  • Bjarne Fyrstenborg 1280 posts 3990 karma points MVP 7x c-trib
    Jul 08, 2019 @ 09:03
    Bjarne Fyrstenborg
    100

    Okay, in your initial thread you fetched data from a table people, but maybe you have renamed this?

    var query = new Sql().Select("*").From("people");
    

    You should be able to fetch the data something like this:

    public IEnumerable<Person> GetAll()
    {
         using (var scope = Current.ScopeProvider.CreateScope(autoComplete: true))
         {
               var sql = new Sql("SELECT * FROM person");
               var people = scope.Database.Fetch<Person>(sql);
               return people;
         }
    
         return null;
     }
    

    If you only need to read data you can use autoComplete: true (default is false, where you also need to scope.Complete(); inside the using statement.

    /Bjarne

  • Dibs 202 posts 991 karma points
    Jul 08, 2019 @ 13:29
    Dibs
    0

    Hi Bjarne

    your code does return data if i change the table name to a built in Umbraco table i.e umbracoUser.

    Its my custom tables that throw up cant find, creating new test tables throw same error tables cant be found ?

    I do create the tables manually i.e sql create table. Then create the model class and add data notations.

    Am i missing a step when i create the tables ?

    Thanks Dibs

Please Sign in or register to post replies

Write your reply to:

Draft