best practice for using database repository in view - Razor

Saied 349 posts 674 karma points

Jul 09, 2015 @ 15:41

Best practice for using Database Repository in View?

I currently have my view setup like this, but I am trying to avoid putting var repo = new ProductRepository(); in the view. Is there a best practice when getting data from a database to put in a view?

    @using Product.Data

    @{
        Layout = "Master.cshtml";
        var repo = new ProductRepository();
    }

    @foreach (var product in repo.GetAll())
    {

        <div class="row">

            <div class="col-xs-12">


                <div style="color:white;">Make: @product.Make<br />
Model: @product.Model<br />
Year: @product.Year<br />
Engine: @product.Engine</div>

            </div>
        </div>

        <hr style="border:1px solid white;"/>
    }

Copy Link

Gary Devenay 39 posts 245 karma points

Jul 09, 2015 @ 15:55

Hi Saied,

A quick question to determine what the best practice should be:

What is the purpose of your ProductRepository? Is it possible that I could see the contents of the repo.GetAll() method?

Your data access methods should most definitely be called from a Controller Action, usually using Route Hijacking. But it is also possible that you have written a database access layer that is not required, and which Umbraco already provides for you.

Thanks, Gary

Copy Link

Saied 349 posts 674 karma points

Jul 09, 2015 @ 15:59

Sure, I will show you the ProductRepository. I did install PetaPoco from Nuget, but are you saying that I can use the built in one from Umbraco to query data from servers and databases besides the Umbraco CMS? Here is code:

using System.Collections.Generic;
using PetaPoco;

namespace Product.Data
{
    public class ProductRepository
    {
        private readonly Database _db = new Database("productsDB");

        public IEnumerable<Product> GetAll()
        {
            const string sql = @"SELECT p.Id, mk.Make, m.Model, y.Year, e.Engine
                        FROM dbo.Products p
                        INNER JOIN Engine e on e.id = p.EngineId
                        INNER JOIN Model m on m.Id = p.ModelId
                        INNER JOIN Make mk on mk.Id = p.MakeId
                        INNER JOIN [Year] y on y.Id = p.YearId";

            return _db.Query<Product>(sql);
        }
    }
}

Now that I am using the one that is built in with Umbraco, my ProductRepository looks like this:

using System.Collections.Generic;
using Umbraco.Core.Persistence;

namespace Product.Data
{
    public class ProductRepository
    {
        readonly Database _db = new Database("productsDB");


        public IEnumerable<Product> GetAll()
        {
            const string sql = @"SELECT p.Id, mk.Make, m.Model, y.Year, e.Engine
                        FROM dbo.Products p
                        INNER JOIN Engine e on e.id = p.EngineId
                        INNER JOIN Model m on m.Id = p.ModelId
                        INNER JOIN Make mk on mk.Id = p.MakeId
                        INNER JOIN [Year] y on y.Id = p.YearId";

            return _db.Query<Product>(sql);
        }
    }
}

Copy Link

Saied 349 posts 674 karma points

Jul 09, 2015 @ 16:04

Gary,

I actually took a first step and got rid of my PetaPoco dependency that I downloaded from Nuget and used the Umbraco provided one. Should I wrap my _db.Query... call in a using statement? I still however have the repo new'd up in the View.

Thanks, Saied

Copy Link

Ismail Mayat 4511 posts 10092 karma points MVP 2x admin c-trib

Jul 09, 2015 @ 16:05

Saied,

The database tables are in the umbraco database then you can use data layer provided by umbraco if db is external to umbraco then use peta poco as you are doing. I would route hijack as suggested by Gary and add the data to viewbag then get it in the view.

Regards

Ismail

Copy Link

Saied 349 posts 674 karma points

Jul 09, 2015 @ 16:17

Ismail,

I understand the concept of getting into the ViewBag, but is this good or common practice? Is this the typical way of doing it?

Copy Link

Gary Devenay 39 posts 245 karma points

Jul 09, 2015 @ 16:10

Hi Saied,

As described by Ismail, if your products are not in Umbraco as nodes then using your own dependency of PetaPoco is the way to go. As for avoiding database calls from the view, you will want to hijack the Umbraco route (using the link I provided above) and write a RenderMvcController that will look along the lines of the following:

public class ProductController : RenderMvcController
{
    private readonly ProductRepository _repository;

    public ProductController()
    {
        this._repository = new ProductRepository();
    }

    public ActionResult Index()
    {
        IEnumerable<Product> products = this._repository.GetAll();

        return CurrentTemplate(products);
    }
}

Though following the guide above should give you a better idea of the exact changes that you will have to make to your Controller/View

Gary

Copy Link

Saied 349 posts 674 karma points

Jul 09, 2015 @ 16:15

Thanks Gary,

Just as a side note, would it be a good idea to create a interface to my Repository and then inject that in my controller constructor?

As far as the View is concerned, would it just be placing an @Html.Action to render out the content?

Copy Link

Saied 349 posts 674 karma points

Jul 09, 2015 @ 16:47

Gary, I implemented your controller above, but when I try to display my data in a view, I am getting the error:

No route in the route table matches the supplied values.  I created a partial view that looks like this:

@model IEnumerable<Product>


<table>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Make)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Model)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Year)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Engine)
        </th>

    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Make)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Model)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Year)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Engine)
        </td>
    </tr>
}

</table>

Then in my Product.cshtml, I am calling it like this:

@{
    Layout = "Master.cshtml";
}

@Html.Action("Index","Product")

Copy Link

Gary Devenay 39 posts 245 karma points

Jul 09, 2015 @ 16:19

If you are comfortable with using a dependency injection framework to follow that pattern then that is my preferred approach, but it is not a requirement to consider your code 'good practice'. It is still perfectly valid to initialise the concrete implementation within the class.

Copy Link

Gary Devenay 39 posts 245 karma points

Jul 09, 2015 @ 16:49

Saied,

My controller was an untested example, I would really recommend that you take a look through the link posted above and follow those examples as there may be a few other steps required to make this work correctly.

Copy Link

is working on a reply...

Flag this post as spam?

Best practice for using Database Repository in View?