I'm trying to do a database query from my Controller, then return the required data to my View so I can see a list of Product names with the corresponding COUNT within a webgrid.

To be honest I still don't understand asp.net-mvc fully but hopefully someone could help out or at least lead me in the right direction thanks!

Controller Code:

    public ActionResult Index()
    {

        string allProdQuery = "SELECT Products.Id, Products.Name, Products.EditDate "
            + "COUNT(Steps.Id) AS StepCount "
            + "FROM Products LEFT OUTER JOIN Steps "
            + "ON Products.Id = Steps.ProductId "
            + "GROUP BY Products.Id, Products.Name, Products.EditDate";

        IEnumerable<InstSetsDBEntities> pData = db.Database.SqlQuery<InstSetsDBEntities>(allProdQuery);

        return View(pData.ToList());
    }

View Code:

@{
    ViewBag.Title = "Index";

    var grid = new WebGrid(source: ViewBag.pData, rowsPerPage: 20);
 }

<h2>Index</h2>

@Html.ActionLink("Create New", "Create")
    <h3>All Products | <a href="~/rcAdmin/New">Create Product</a> </h3>
    <div id="grid">
        @grid.GetHtml(
                        tableStyle: "rcGrid",
                        headerStyle: "head",
                        alternatingRowStyle: "alt",
                        footerStyle: "pgr",
                        columns: grid.Columns(
                            grid.Column("Name", "Product Name", style: "rcGB"),
                            grid.Column("StepCount", "Steps", style: "rcGB"),
                            grid.Column("EditDate", "Last Edited", style: "rcGB"),
                            grid.Column(format:@<text><a href="EditProduct.cshtml?id=@item.Id" title="Edit Product"><img src="images/edit_page.gif" class="rcGC" alt="Edit"/>Manage</a></text>)

                    )
                )
</div>

Database Structure:

(DB) - (TABLES) - (ROWS)

InstSetsDB

  • Products
    • Id
    • Name
    • EditDate
  • Steps
    • Id
    • ProductId

Related posts

Recent Viewed