Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

  • Beau D'Amore 26 posts 66 karma points
    Jun 15, 2014 @ 18:16
    Beau D'Amore

    Using Custom SQL Tables in Umbraco 7


    I need help doing just that. I have a GPS Runner's app in iOS that uses a number of tables I've added manually, to the Umbraco MSSQL DB via:

    1. SQL Manager to add the tables. (These tables are the GPS tracking data from each user.)

    2. Visual Studio 2012 to add a 'LINQ To SQL classes' .dbml like you would in a regular MVC 4 web app

    I've already tied a new API I've written into U7 for handling all the HTTP interactions with iOS and these added tables, Stored Procedures, and table types. Done, works great.

    Now, how do I go about bringing up this extra/custom data into my U7's membership area I've also created?

    How do I merge in my tables to be 'Umbraco-friendly' objects like I use them in Entity Framework? IS this possible?

    Am I using the MVC methodology correctly inside U7?

    (I have watched all the V7 videos on, FYI.. this need isn't covered I believe...)

    I much appreciate anyone advice or direction. Much appreciate..


  • Beau D'Amore 26 posts 66 karma points
    Jun 15, 2014 @ 18:21
    Beau D'Amore

    let me elaborate:

    At the top of Partial Macros I create in the UI via Developers/Partial View Macro Files (ADD) is:


    @inherits UmbracoTemplatePage

    What I am using to access my tables in a standard MVC 4 app, is something like:


    @model IEnumerableTRACK>



    @model MaryRuns.Models.TRACK

    BOTH of which work in my project, but I cannot pass them appropriate models from their parent templates when they are also using "@inherits UmbracoTemplatePage"...

    where in the rabbit hole do I turn?

    How do i get to these extra tables I have? (notably TRACK)

  • John Perryn 71 posts 182 karma points
    Jun 22, 2014 @ 06:48
    John Perryn

    Hi Beau

    I have a similar problem. Basically I need to load some large ( >1m rows) flat files into umbraco and then manage them via the back end with a few macros. 

    I have been unable to locate an example or package that even comes close, so I am seeking advice and guidance. My mental model of how V7 works is fuzzy, but it seems to work against using a straight MVC solution. My targeted application has too much data (probably over 20m rows) to consider using the umbraco content tree. The data is (mostly) read only genealogical data. WebGrids with paging, sorting, and filtering look like part of the solution. But should I try to (somehow) use umbraco core functionality or (somehow) work around it? Perhaps build an MVC API and do the work in a javascript grid?

    My data can be set up via SQL Management Studio. Using a naming convention It could be managed in the back end (eg enabled, disabled) and some scaffloded partial-views built that would allow the content editors to add searchable tables as needed. Another layer could reveal the available tables, show counts and facets, and allow a faceted "drill down" across multiple tables prior to passing control to the targeted results. An example (not mine...!) is here 

    At this point in time the lack of adequate documentation and practical examples is holding me back - so any sugestions would be most welcome.

    If all goes well I would envisage being able to offer a "data table" package back to the umbraco community.


    John P

  • Beau D'Amore 26 posts 66 karma points
    Jun 22, 2014 @ 15:19
    Beau D'Amore

    If you are looking to access your tables via PartialViews, then I don't see a problem. What I am trying to do, it all but imporssible (with the lack of help/documentation)

    Use a SurfaceController: (add a new controller and inherit from SurfaceController)

    and then call it via HTML.Action from your view.

    Keep all your logic in the controller (obviously), and have your views work with strongly-typed models. You'll likely have to use 'ViewModels' which is a practice of making a custom model with the necessary data you wish to show in your view, populate it in your controller, and have your view strongly-typed to this 'voewmodel'

    FYI, I am using a LINQ to SQL Classes dbml file with a class that inherits from DBContext and uses DBSets in order to access the data via objects, rather than SQL. If you need this code as an example, I can email you a zip if you like.

    But this thread is for solving MY problem ;)

  • John Perryn 71 posts 182 karma points
    Jun 22, 2014 @ 16:07
    John Perryn

    Much appreciated and sorry that I am too new at this to be of much use to you.

    A code example would be most welcome. My address is

    Do you think that the suggestion for a "data table" package has merrit?

    Thanks again

    John P

  • Lennart Stoop 304 posts 836 karma points
    Jun 22, 2014 @ 16:54
    Lennart Stoop

    Hi Beau,

    Here's the setup I'm using to display data from custom SQL tables, i.e. listing of publications

    1) In the page template (Views\Publications.cshtml) I'm using a macro:

    @inherits Umbraco.Web.Mvc.UmbracoTemplatePage
         Layout = "Master.cshtml";

    2) In the macro partial (Views\MacroPartials\PublicationOverview.cshtml) I'm using a surface controller

    inherits Umbraco.Web.Macros.PartialViewMacroPage
    @Html.Action("Index", "PublicationOverviewSurface")

    3) In the partial (Views\Partials\PublicationOverview.cshtml) I'm binding to a custom VM

    @model MyWebProject.Models.PublicationOverviewViewModel
    @foreach (var p in Model.Publications.OrderBy(p => p.ExpenseDate))
        // display publication row

    The VM is created in the Surface controller, using a repository (I'm guessing you're also using a repository with EF?)

    public class PublicationOverviewSurfaceController : SurfaceController
        protected PublicationRepository pubRepo = new PublicationRepository();
        public ActionResult Index()
            var model = new PublicationOverviewViewModel();
            var sc = new PublicationSearchCriteria();
            model.Publications = pubRepo.GetPublications(sc);
            return PartialView("PublicationOverview", model);



Please Sign in or register to post replies

Write your reply to: