I need to display the records from a custom table in my Umbraco database using AJAX. So I've set up an Umbraco Web API Controller to do this, hoping to get a JSON dump of all of the data from that table via a simple AJAX POST. I'm interacting with the custom table (called adminNotifications) elsewhere on the site so for 'ease' I've created a Poco class, like this:
POCO class:
namespace MyProject.Pocos
{
[TableName("adminNotification")]
[PrimaryKey("id", autoIncrement = true)]
[ExplicitColumns]
public class AdminNotification
{
[Column("id")]
[PrimaryKeyColumn(AutoIncrement = true)]
public int id { get; set; }
[Column("adminNotificationTypeId")]
public int adminNotificationTypeId { get; set; }
[Column("installerId")]
public int installerId { get; set; }
[Column("manufacturerId")]
public int manufacturerId { get; set; }
}
}
I've then created the WebApi Controller, like this:
WebAPI Controller:
namespace MyProject.Controllers
{
public class AdminDashboardNotificationsApiController : UmbracoApiController
{
// GET: /AdminDashboardNotificationsApi/
[System.Web.Http.AcceptVerbs("GET", "POST")]
[System.Web.Http.HttpGet]
public IEnumerable<dynamic> ListPendingNotifications()
{
// If admin is logged in
if (umbraco.BusinessLogic.User.GetCurrent() != null)
{
//Connect to custom table DB
var db = ApplicationContext.Current.DatabaseContext.Database;
//Get list of notifications to iterate over
var notifications = db.Query<dynamic>("SELECT * FROM dbo.adminNotification").ToList();
return notifications;
}
else
{
// Not logged in so forbid
throw new HttpResponseException(HttpStatusCode.Forbidden);
}
}
}
}
This builds okay but a breakpoint returns the 'db' variable as null. I'm wondering if the context of a WebAPI Controller is not suitable for grabbing the current database? If this is true then is it possible to use my db Poco class in this scenario (if so, how?) or should I just revert to the old SQL Connection methods?
I never tried to fetch data in the Web API, but maybe it doesn't work because it's stateless. I did use a surface controller to fetch data with PetaPoco from a custom table and that does work.
public class JsonController : SurfaceController
{ public ActionResult GetLocation(int locationId)
{
return Json(GetDbLocation(), JsonRequestBehavior.AllowGet);
} }
The UmbracoApiController exposes the DatabaseContext, so within it you should be able to do something like "this.DatabaseContext.Database". Its getting the DatabaseContext of the UmbracoContext, which should work fine within WebApi - thats what the v7 backoffice is built around, so I'd imagine its simply an issue around not getting the DatabaseContext through the correct context (hope this isn't too confusing :)).
Got it! Basically, using <dynamic> doesn't work. Instead I used my POCO class
namespace MyProject.Controllers
{
public class AdminDashboardNotificationsApiController : UmbracoApiController
{
// GET: /AdminDashboardNotificationsApi/
[System.Web.Http.AcceptVerbs("GET", "POST")]
[System.Web.Http.HttpGet]
public IEnumerable<AdminNotification> ListPendingNotifications()
{
// If admin is logged in
if (umbraco.BusinessLogic.User.GetCurrent() != null)
{
//Connect to custom table DB
var db = ApplicationContext.DatabaseContext.Database;
//Get list of notifications to iterate over
var notifications = db.Query<AdminNotification>("SELECT * FROM dbo.adminNotification").ToList();
return notifications;
}
else
{
// Not logged in so forbid
throw new HttpResponseException(HttpStatusCode.Forbidden);
}
}
}
}
Incidentally, using var db = ApplicationContext.DatabaseContext.Database; and var db = this.DatabaseContext.Database; seems to do the same thing - in this situation I can use the two interchangeably.
Nice to see you got it working. Did you know you can also write strongly typed queries with PetaPoco. I looked at the Umbraco source code for some examples. After that I got with something like this:
var sql = new Sql();
sql.Select("*")
.From<LocationDto>()
.Where<LocationDto>(x => x.DebtorId == debtorId)
.Where<LocationDto>(x => !x.Deleted)
.OrderBy<LocationDto>(x => x.SortOrder);
return Database.Fetch<LocationDto>(sql);
Return records from custom table via WebApi, in JSON, using PetaPoco
Hi,
I need to display the records from a custom table in my Umbraco database using AJAX. So I've set up an Umbraco Web API Controller to do this, hoping to get a JSON dump of all of the data from that table via a simple AJAX POST. I'm interacting with the custom table (called adminNotifications) elsewhere on the site so for 'ease' I've created a Poco class, like this:
POCO class:
I've then created the WebApi Controller, like this:
WebAPI Controller:
This builds okay but a breakpoint returns the 'db' variable as null. I'm wondering if the context of a WebAPI Controller is not suitable for grabbing the current database? If this is true then is it possible to use my db Poco class in this scenario (if so, how?) or should I just revert to the old SQL Connection methods?
Many thanks for any pointers folks.
I never tried to fetch data in the Web API, but maybe it doesn't work because it's stateless. I did use a surface controller to fetch data with PetaPoco from a custom table and that does work.
public class JsonController : SurfaceController {public ActionResult GetLocation(int locationId) { return Json(GetDbLocation(), JsonRequestBehavior.AllowGet); }
}
You can call the surface controller like this:
$.ajax({ url: '/umbraco/surface/json/GetLocation/', type: 'POST', dataType: 'json', data: '{ "locationId": ' + $(this).find("option:selected").val() + ' }', contentType: 'application/json; charset=utf-8', success: function (data) { console.log(data); } });Jeroen
Ah, that's interesting, thanks Jeroen. I'll try using a surface controller instead and see how that goes. Many thanks.
Hi Dan,
The UmbracoApiController exposes the DatabaseContext, so within it you should be able to do something like "this.DatabaseContext.Database". Its getting the DatabaseContext of the UmbracoContext, which should work fine within WebApi - thats what the v7 backoffice is built around, so I'd imagine its simply an issue around not getting the DatabaseContext through the correct context (hope this isn't too confusing :)).
Let me know if it works,
Morten
Got it! Basically, using
<dynamic>doesn't work. Instead I used my POCO classIncidentally, using
var db = ApplicationContext.DatabaseContext.Database;andvar db = this.DatabaseContext.Database;seems to do the same thing - in this situation I can use the two interchangeably.Thanks chaps :)
Nice to see you got it working. Did you know you can also write strongly typed queries with PetaPoco. I looked at the Umbraco source code for some examples. After that I got with something like this:
var sql = new Sql(); sql.Select("*") .From<LocationDto>() .Where<LocationDto>(x => x.DebtorId == debtorId) .Where<LocationDto>(x => !x.Deleted) .OrderBy<LocationDto>(x => x.SortOrder); return Database.Fetch<LocationDto>(sql);Jeroen
Nice! I didn't know that; very neat!
It are some extension methods which are in the Umbraco source code. You need the following namespace:
So it's not part of PetaPoco, but an improved version that's only in Umbraco.
Jeroen
is working on a reply...