Sign in Register
Go to solution
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
    Danny
    0

    Umbraco Template Razor db fetch

    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 606 posts 2404 karma points
    Nov 16, 2019 @ 18:09
    Bo Jacobsen
    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 606 posts 2404 karma points
    Nov 16, 2019 @ 18:16
    Bo Jacobsen
    0

    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
    Danny
    0

    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 606 posts 2404 karma points
    Nov 17, 2019 @ 00:39
    Bo Jacobsen
    100

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

    1. Use reflection to get the property from an object.
    2. Try make it dynamic (I dunno if that works)
    3. 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
    Danny
    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 606 posts 2404 karma points
    Nov 17, 2019 @ 12:29
    Bo Jacobsen
    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
    paco
    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
Please Sign in or register to post replies

Write your reply to:

Draft