access server side data to a property editor - Using Umbraco and getting started

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

Dibs 202 posts 991 karma points

Jun 26, 2019 @ 14:39
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
Copy Link
Dibs 202 posts 991 karma points

Jun 28, 2019 @ 14:15

0

Cant read last edit by @NickJonas

Copy Link
Nik 1617 posts 7263 karma points MVP 7x c-trib

Jun 28, 2019 @ 14:46
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
Copy Link
Dibs 202 posts 991 karma points

Jul 05, 2019 @ 10:31

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

Copy Link
Bjarne Fyrstenborg 1285 posts 4039 karma points MVP 8x c-trib

Jul 05, 2019 @ 11:22
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
Copy Link

Dibs 202 posts 991 karma points

Jul 05, 2019 @ 14:39

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

Copy Link

Bjarne Fyrstenborg 1285 posts 4039 karma points MVP 8x c-trib

Jul 08, 2019 @ 09:03
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
Copy Link
Dibs 202 posts 991 karma points

Jul 08, 2019 @ 13:29

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

Copy Link
is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?

access server-side data to a property editor