umbraco template razor db fetch

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

Danny 12 posts 82 karma points

Nov 16, 2019 @ 13:09
0

Umbraco Template Razor db fetch

Using Umbraco And Getting Started

Umbraco 7
Hi there,

I want to select som rows from my custom table within the page template (cshtml)

It's not a problem for me to select rows with just one field like this: Umbraco v7.8
```
UmbracoDatabase db = ApplicationContext.DatabaseContext.Database;
var query = new Sql("SELECT City FROM MyTable);
var queryResults = db.Fetch<string>(query);
foreach (var item in queryResults) 
{
    <div>@item</div>
}
```
How do i select multiple columns for a query like this:
```
var query = new Sql("SELECT Name, Email, City FROM MyTable);
```
on my old Umbraco v7.5, I can easily do like this, but it doesnt work in Umbraco 7.8
```
var db = Database.Open("umbracoDbDSN");
var items = db.Query("SELECT * FROM MyTable");

foreach (var item in items)
{ 
    <div>@item.Name</div>
    <div>@item.City</div>
    <div>@item.Email</div>
}
```
Best regards
Copy Link
Bo Jacobsen 597 posts 2395 karma points

Nov 16, 2019 @ 18:09
0
Hi Danny.

What about this
```
var query = new Umbraco.Core.Persistence.Sql().Select("*").From("MyTable");
List<object> queryResults = Umbraco.Web.UmbracoContext.Current.Application.DatabaseContext.Database.Fetch<object>(query);
```
if you use a poco item you can use that as an object like Database.Fetch<PocoItem>(query)
Copy Link

Bo Jacobsen 597 posts 2395 karma points

Nov 16, 2019 @ 18:16

Here is a poco exsample.

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

namespace yourProject.Path
{
    [TableName("MyTable")]
    [PrimaryKey("id", autoIncrement = true)]
    public class PecaPocoItem
    {
        [Column("id")]
        [PrimaryKeyColumn(Name = "PK_MyTable_Id")]
        public int Id { get; set; }

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

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

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

    public class Installer : ApplicationEventHandler
    {
        protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
        {
            var dbContext = applicationContext.DatabaseContext;
            var db = new DatabaseSchemaHelper(dbContext.Database, applicationContext.ProfilingLogger.Logger, dbContext.SqlSyntax);

            if (!db.TableExist("MyTable"))
            {
                db.CreateTable<PecaPocoItem>(false);
            }
        }
    }
}

Copy Link

Danny 12 posts 82 karma points

Nov 16, 2019 @ 23:26

Thx Bo, I got it work like this:

List<object> queryResults = ApplicationContext.DatabaseContext.Database.Fetch<object>(query);

How do I fetch a column value?

foreach (var item in queryResults) 
{
    var city = @item.Value("City");  //doesnt work!
    <div>@city</div>
}

Please also advice me how to do the SQL Where. Is this correct?

var query = new Umbraco.Core.Persistence.Sql().Select("*").From("Cities").Where("PostalCode=1234");

Copy Link

Bo Jacobsen 597 posts 2395 karma points

Nov 17, 2019 @ 00:39

There is 3 things, as i can see you can do.

Use reflection to get the property from an object.
Try make it dynamic (I dunno if that works)
Use my Peta Poco exsample in my reply, further up. (Properly the best choice)

Reflection

 var city = item.GetType().GetProperty("City").GetValue(item, null);

Dynamic

List<dynamic> queryResults = ApplicationContext.DatabaseContext.Database.Fetch<dynamic>(query);
foreach (var item in queryResults) 
{
    <div>@item.City</div>
}

In order to use your where statement, you can do this.

var query = new Umbraco.Core.Persistence.Sql().Select("*").From("Cities").Where("PostalCode = @safeParam", new { safeParam = 1234 });

// If your PostalCode is a string, use this instead
var query = new Umbraco.Core.Persistence.Sql().Select("*").From("Cities").Where("PostalCode = @safeParam", new { safeParam = "1234" });

Copy Link

Danny 12 posts 82 karma points

Nov 17, 2019 @ 01:12

0

Thank you Bo. The dynamic method working fine.

Now question according performance. Do I get better performance by achieving this via the "UserControl (Macro)" instead this "CSHTML" inline Razor approach?

Or the performancewise is the same?

Copy Link
Bo Jacobsen 597 posts 2395 karma points

Nov 17, 2019 @ 12:29

0

I actually don't know.

But if you wanna improve performance, you should cache your data. So instead of getting data directly from the database, you would get it from the cache.

You would then have to keep your cache updated.

Copy Link
paco 25 posts 155 karma points

Feb 02, 2021 @ 16:07

0

Hello Bo,

How can I do if I need an AND/OR of two or more conditions in Where clause of my query?

Thanks, Paco

Copy Link
is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?