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>
}
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)
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);
}
}
}
}
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" });
Now question according performance.
Do I get better performance by achieving this via the "UserControl (Macro)" instead this "CSHTML" inline Razor approach?
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.
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
How do i select multiple columns for a query like this:
on my old Umbraco v7.5, I can easily do like this, but it doesnt work in Umbraco 7.8
Best regards
Hi Danny.
What about this
if you use a poco item you can use that as an object like
Database.Fetch<PocoItem>(query)
Here is a poco exsample.
Thx Bo, I got it work like this:
How do I fetch a column value?
Please also advice me how to do the SQL Where. Is this correct?
There is 3 things, as i can see you can do.
Reflection
Dynamic
In order to use your where statement, you can do this.
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?
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.
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
is working on a reply...