Get data from custom table into view, using petapoco
Hi,
I have a site with several custom tables, one of which is called 'job'. I have a PetaPoco class (called 'PostAJob') describing this table and am doing various interactions to write data to this table (e.g upon Contour form submission some data is copied to the custom jobs table via PetaPoco). This all works nicely.
However, I now need to pull out a selection of data from this custom table into a view and I'm getting confused how to set this up. I initially thought I needed a model, but then the PetaPoco class is essentially that, right? So then I thought perhaps just a surface controller would suffice, so I've generated a surface controller like this:
namespace MyProject.Controllers.SurfaceControllers
{
public class JobSurfaceController : SurfaceController
{
/// <summary>
/// Renders all jobs submitted by customer
/// </summary>
public IEnumerable<PostAJob> GetAllJobsByCustomer()
{
Member member = Member.GetCurrentMember();
int memberId = member.Id;
var db = ApplicationContext.DatabaseContext.Database;
return db.Query<PostAJob>("SELECT * FROM job WHERE customerId=@0", memberId);
}
}
}
So in theory this will return an enumerable of the 'PostAJob' object which I can iterate through and output into my view.
However, I'm having trouble hooking this up in the view. I've created a partial called ViewCustomerJobs.cshtml, containing the following code:
@using MyProject.Controllers.SurfaceControllers
@using MyProject.Models
@using MyProject.Code
@{
var listOfJobs = GetAllJobsByCustomer();
foreach(var job in listOfJobs){
<p>@job.formRecordId</p>
}
}
This is erroring saying that GetAllJobsByCustomer does not exist in the current context. I'm confused as to what model to reference in this view and how to get the data from the controller into the view to iterate over and output.
If anyone could point me in the right direction with this I'd be most grateful, thanks.
It might be a good idea to have a look at the Hybrid Framework. There you can see how extra data is added to an existing model. For example the news over page:
You could also try to use an Html.Action which goes to the surface controller and returns the model which you need. An example can be found on the contact page:
Thanks Jeroen - that's very interesting. I almost used the Hybrid Framework for this project actually but wanted to do if from scratch to try to understand what's going on a bit better.
So in the first example of the news articles, you have a 'master' model (NewsOverviewModel) and I see you add the individual news item to that, then pass the whole thing back to the view, which makes total sense. But in my scenario I don't really have a model to use as a base as the only things I need to return is a collection of job items pulled via the PetaPoco class. So on line 22 here: https://github.com/jbreuer/Hybrid-Framework-Best-Practices/blob/master/development/Umbraco.Extensions/Controllers/NewsOverviewController.cs I don't know what I'd use to instantiate the model? Can it be done without?
I've seen this blog post: http://www.it-potato.com/2013/08/using-peta-poco-in-umbraco-6/ which does something very similar to how I've set this up, but it doesn't indicate how you'd then get those items from the controller into the view. Still puzzled I'm afraid.
You could also create an empty model which does nothing else, but it might be better to look at the second example. With the Html.Action you can create a custom model which doesn't inherit from anything in your surface controller and pass that to the partial view. See this blog about Partial View vs Html.Action: http://pratapreddypilaka.blogspot.nl/2011/11/htmlpartial-vs-htmlaction-mvc-razor.html
Thanks Jeroen. I'm trying to implement it now with Html.Action - it seems neater than having an empty model. I've changed the controller to this:
public ActionResult GetAllJobsByCustomer()
{
Member member = Member.GetCurrentMember();
int memberId = member.Id;
var db = ApplicationContext.DatabaseContext.Database;
IEnumerable<PostAJob> jobs = db.Query<PostAJob>("SELECT * FROM job WHERE customerId=@0", memberId);
return PartialView("ViewCustomersJobs", jobs);
}
However, it now complaining that the type of the model isn't correct, as it's my custom model rather than an Umbraco RenderModel:
The model item passed into the dictionary is of type 'Umbraco.Core.Persistence.Database+<Query>d__7`1[MyProject.Pocos.PostAJob]', but this dictionary requires a model item of type 'Umbraco.Web.Models.RenderModel'.
Thanks Jeroen, I did try that but it still doesn't like the type:
The model item passed into the dictionary is of type 'Umbraco.Web.Models.RenderModel', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[MyProject.Pocos.PostAJob]'.
The surface controller (inside Controllers/SurfaceControllers):
JobSurfaceController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using MyProject.Models;
using Umbraco.Web.Mvc;
using umbraco.cms.businesslogic.member;
using MyProject.Pocos;
using Umbraco.Web.Models;
namespace MyProject.Controllers.SurfaceControllers
{
public class JobSurfaceController : SurfaceController
{
public ActionResult GetAllJobsByCustomer()
{
Member member = Member.GetCurrentMember();
int memberId = member.Id;
var db = ApplicationContext.DatabaseContext.Database;
IEnumerable<PostAJob> jobs = db.Query<PostAJob>("SELECT * FROM job WHERE customerId=@0", memberId);
return PartialView("ViewCustomersJobs", jobs);
}
}
}
Sorry to open this old case up but it fits nicely with my issue.
Above works perfectly, but can you give me an example of creating some <a> links in the partial view ViewCustomersJobs, which links to an edit page? Now you only output the list:
@{ foreach(var job in Model){ <p>@job.formRecordId</p> } }
I would like each link to go to a controller/action with an id or model, so I can display an edit form and subsequently do a POST to save the record.
I get what you are trying to do but is it really a custom feature in Umbraco to link to a template (action) with a parameter?
The above example is perfect for showing a custom object coming from the db and the example shows how to output the list but then what?..
If each item could be a link to the same action (or just controller and then default to the index action) with a parameter, it would be easy to pick up that parameter and show the appropriate data.
SurfaceControllers are autorouted so why won't Umbraco find enclosed e.g.<a href = /job/edition/3>?
In a nutshell - my response would be yes it is a custom feature to link to a template / action in this way, but I've not been working with Umbraco long enough to say so with any authority.
When you feed in a URL Umbraco will try to match it to a piece of content (in your content tree) through the request pipeline - in order to do this it runs a series of content finders that attempt to match the Url passed to a piece of content in the tree, if it cannot do so then all the content finders fail and the request pipeline returns a 404 error.
Therefore as I understand it, and someone may be able to enlighten further, to map a page request to a Url (that is a piece of umbraco content or otherwise) what you can do is either:
Create a custom route as I highlighted in my previous post
Create a content finder to match the Url to a particular piece of content as part of the pipeline - but this still requires a fair bit of custom code to achieve what you want - and you can't use the @Html.ActionLink helper to generate the links like you required in your original post.
To achieve the latter approach create a single piece of umbraco content in your tree under job / edition. Maybe make it accessible via the url /job/edition/company - make this node use a new document type (explain why later) - maybe call it CompanyDetail
Now in code you will need to create a content finder with the following code...
public class CompanyContentFinder : IContentFinder
{
public bool TryFindContent(PublishedContentRequest contentRequest)
{
var urlParts = contentRequest.Uri.GetAbsolutePathDecoded().Split(new[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
//Check if the Url Parts
// Starts with /job/edition/*
if (urlParts.Length > 2
&& urlParts[0].ToLower() == "job"
&& urlParts[1].ToLower() == "edition"
)
{
var id = 0;
Int32.TryParse(urlParts[2], out id);
// If the next part of the url is not an integer, we do not have a valid url, return false
if (Int32.Equals(id, 0))
{
return false;
}
// We have a valid url, but not necessarily a valid object - the controller will handle this later and redirect to 404 then if required
//Set the Published Content Node to be the the company node - can get properties off it
contentRequest.PublishedContent = contentRequest.RoutingContext.UmbracoContext.ContentCache.GetByRoute("/job/edition/company");
//Return true to say found something & stop pipeline & other contentFinder's from running
return true;
}
//Not found any content node to display/match - so run next ContentFinder in Pipeline
return false;
}
}
Now register your content finder with Umbraco (like with the custom route answer I gave)
public class MyUmbracoHandler : ApplicationEventHandler
{
protected override void ApplicationStarting(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
{
base.ApplicationStarting(umbracoApplication, applicationContext);
if (!ContentFinderResolver.Current.ContainsType<CompanyContentFinder>())
{
ContentFinderResolver.Current.InsertTypeBefore<ContentFinderByNiceUrl, CompanyContentFinder>();
}
}
}
With the content finder registered - you can now create a custom controller to hijack to request to your company node in your tree....
So if you named your document type CompanyDetail like suggested above, your controller would look like this - basically to hijack a request to a custom controller your controller must follow the naming convention
public class CompanyDetailController: RenderMvcController
{
public override ActionResult Index(RenderModel model)
{
/* Extract the id of the company from the Url of the current page */
var companyId = 0;
var urlParts = Request.Url.GetAbsolutePathDecoded().Split(new[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
if (urlParts.Length > 2 && Int32.TryParse(urlParts[2], out companyId))
{
/* Now return the request to the umbraco page, you can pass back the original model as so here
* and maybe store the data retrieved from your database in ViewData. Or you could return a custom model
* which inherits from the RenderModel so that data from your database is directly accessible from your
* Model object in your Umbraco Template View.
*/
return base.Index(model);
}
return new HttpNotFoundResult("The requested company could not be found.");
}
}
Then you can just generate your Urls in your view since you know the path and the Id from your enumeration of your database list. It won't be using the nice @Html.ActionLink - but you will have a nice Url by which to link to in the format that you want.
I appreciate that again this is quite a bit of custom code - but I have used both methods mentioned now to link to content that is provided by an external data source in this way and both seem to work but have their pro's and con's.
Get data from custom table into view, using petapoco
Hi,
I have a site with several custom tables, one of which is called 'job'. I have a PetaPoco class (called 'PostAJob') describing this table and am doing various interactions to write data to this table (e.g upon Contour form submission some data is copied to the custom jobs table via PetaPoco). This all works nicely.
However, I now need to pull out a selection of data from this custom table into a view and I'm getting confused how to set this up. I initially thought I needed a model, but then the PetaPoco class is essentially that, right? So then I thought perhaps just a surface controller would suffice, so I've generated a surface controller like this:
So in theory this will return an enumerable of the 'PostAJob' object which I can iterate through and output into my view.
However, I'm having trouble hooking this up in the view. I've created a partial called ViewCustomerJobs.cshtml, containing the following code:
This is erroring saying that
GetAllJobsByCustomer does not exist in the current context
. I'm confused as to what model to reference in this view and how to get the data from the controller into the view to iterate over and output.If anyone could point me in the right direction with this I'd be most grateful, thanks.
Hello,
It might be a good idea to have a look at the Hybrid Framework. There you can see how extra data is added to an existing model. For example the news over page:
https://github.com/jbreuer/Hybrid-Framework-Best-Practices/blob/master/development/Umbraco.Extensions/Controllers/NewsOverviewController.cs
https://github.com/jbreuer/Hybrid-Framework-Best-Practices/blob/master/development/Umbraco.Site/Views/NewsOverview.cshtml
You could also try to use an Html.Action which goes to the surface controller and returns the model which you need. An example can be found on the contact page:
https://github.com/jbreuer/Hybrid-Framework-Best-Practices/blob/master/development/Umbraco.Site/Views/Contact.cshtml
https://github.com/jbreuer/Hybrid-Framework-Best-Practices/blob/master/development/Umbraco.Extensions/Controllers/ContactController.cs
https://github.com/jbreuer/Hybrid-Framework-Best-Practices/blob/master/development/Umbraco.Site/Views/Partials/ShowDates.cshtml
Just forget the third true parameter on the Html.Action. That's for caching which is not related :-).
Jeroen
This blog on how the Hybrid Framework works might also help: http://24days.in/umbraco/2013/hybrid-framework/
Jeroen
Thanks Jeroen - that's very interesting. I almost used the Hybrid Framework for this project actually but wanted to do if from scratch to try to understand what's going on a bit better.
So in the first example of the news articles, you have a 'master' model (NewsOverviewModel) and I see you add the individual news item to that, then pass the whole thing back to the view, which makes total sense. But in my scenario I don't really have a model to use as a base as the only things I need to return is a collection of job items pulled via the PetaPoco class. So on line 22 here: https://github.com/jbreuer/Hybrid-Framework-Best-Practices/blob/master/development/Umbraco.Extensions/Controllers/NewsOverviewController.cs I don't know what I'd use to instantiate the model? Can it be done without?
I've seen this blog post: http://www.it-potato.com/2013/08/using-peta-poco-in-umbraco-6/ which does something very similar to how I've set this up, but it doesn't indicate how you'd then get those items from the controller into the view. Still puzzled I'm afraid.
You could also create an empty model which does nothing else, but it might be better to look at the second example. With the Html.Action you can create a custom model which doesn't inherit from anything in your surface controller and pass that to the partial view. See this blog about Partial View vs Html.Action: http://pratapreddypilaka.blogspot.nl/2011/11/htmlpartial-vs-htmlaction-mvc-razor.html
Jeroen
Thanks Jeroen. I'm trying to implement it now with Html.Action - it seems neater than having an empty model. I've changed the controller to this:
And in the view I have:
However, it now complaining that the type of the model isn't correct, as it's my custom model rather than an Umbraco RenderModel:
Any ideas?
Thanks!
Hello,
Try adding this to your partial view:
So something like this:
By doing that you tell MVC that you expect an IEnumerable<PostAJob> instead of the RenderModel.
Jeroen
Thanks Jeroen, I did try that but it still doesn't like the type:
Can you please show me your complete view, surface controller and partial view? It should work :-).
Jeroen
Yeah, sure...
The view (inside 'Views'):
Dashboard-customer.cshtml
The partial (inside Views/Partials):
ViewCustomersJobs.cshtml
The surface controller (inside Controllers/SurfaceControllers):
JobSurfaceController.cs
You almost got it ;-).
You're not using the Html.Action yet which I mentioned.
Change your Dashboard-customer.cshtml to this:
After that it should work :-).
P.S. for you PetaPoco you can also use strongly typed queries. More here: http://our.umbraco.org/forum/developers/api-questions/49152-Return-records-from-custom-table-via-WebApi,-in-JSON,-using-PetaPoco?p=0#comment175393
Jeroen
Ahhh, when I'd tried Html.Action I'd done so in the partial not the view.
It now works, so thank you so much! There's a beer at Codegarden with your name written all over it :)
By the way, I'd seen the strongly-typed PetaPoco stuff, but it's one small step at a time for me at the moment.
Sorry to open this old case up but it fits nicely with my issue.
Above works perfectly, but can you give me an example of creating some <a> links in the partial view ViewCustomersJobs, which links to an edit page? Now you only output the list:
@{
foreach(var job in Model){
<p>@job.formRecordId</p>
}
}
I would like each link to go to a controller/action with an id or model, so I can display an edit form and subsequently do a POST to save the record.
Thanks! :-)
Hi Lars,
I've posted a reply to your other thread you've posted in the Razor section of the forums - hopefully that points you in the right direction.
Kind regards,
Tim
Hi Tim and thanks!
I get what you are trying to do but is it really a custom feature in Umbraco to link to a template (action) with a parameter?
The above example is perfect for showing a custom object coming from the db and the example shows how to output the list but then what?..
If each item could be a link to the same action (or just controller and then default to the index action) with a parameter, it would be easy to pick up that parameter and show the appropriate data.
SurfaceControllers are autorouted so why won't Umbraco find enclosed e.g.<a href = /job/edition/3>?
In a nutshell - my response would be yes it is a custom feature to link to a template / action in this way, but I've not been working with Umbraco long enough to say so with any authority.
When you feed in a URL Umbraco will try to match it to a piece of content (in your content tree) through the request pipeline - in order to do this it runs a series of content finders that attempt to match the Url passed to a piece of content in the tree, if it cannot do so then all the content finders fail and the request pipeline returns a 404 error.
Therefore as I understand it, and someone may be able to enlighten further, to map a page request to a Url (that is a piece of umbraco content or otherwise) what you can do is either:
To achieve the latter approach create a single piece of umbraco content in your tree under job / edition. Maybe make it accessible via the url /job/edition/company - make this node use a new document type (explain why later) - maybe call it CompanyDetail
Now in code you will need to create a content finder with the following code...
Now register your content finder with Umbraco (like with the custom route answer I gave)
With the content finder registered - you can now create a custom controller to hijack to request to your company node in your tree....
So if you named your document type CompanyDetail like suggested above, your controller would look like this - basically to hijack a request to a custom controller your controller must follow the naming convention
Then you can just generate your Urls in your view since you know the path and the Id from your enumeration of your database list. It won't be using the nice @Html.ActionLink - but you will have a nice Url by which to link to in the format that you want.
For more reading about what's going on above:
I appreciate that again this is quite a bit of custom code - but I have used both methods mentioned now to link to content that is provided by an external data source in this way and both seem to work but have their pro's and con's.
Thanks for the explanation and code Tim :)
This was just what I needed enable my project to route and display records I have in a custom table.
Ver
is working on a reply...