This question is interesting, and it is actually a bunch of questions bunched up together. It took me a while to figure out why I was getting so many questions around this topic but I finally figured it out.

Most of the sessions around MVC and even most of the samples you find online are built over an Entity Framework or a Linq to SQL model. My session demos also rely on Entity Framework as a quick and reliable way of getting around the nasty subject of building a database access layer and speed up development (well, actually the Entity Framework does a bit more than that, but let’s keep it simple).

So, I received 3 interesting questions after my last session:

·        In my application I need to display data from custom queries, it doesn’t come straight from a table in my database. Can I do it in MVC?

·        In my application I need to display the results of a stored procedure or SQL Views I built. Can I do it in MVC?

·        In my application I need to display data coming from an Oracle database. Can I do it in MVC?

Well, let me jump in there quickly and answer “Yes, yes and yes”. But we are really confusing things here…

ASP.NET MVC builds on the notion that you have a model (which represents your data) that is accessed and manipulated by a controller action. The result of that interaction will be forwarded to a view for rendering the results (hence MVC = Model/View/Controller).

In my sessions, as in most samples you come across with, the model is built using Entity Framework. That doesn’t mean you can’t do it in the traditional way, where we used to build database access layers ourselves and have our model be built over our custom developed business objects.

Even using Entity Framework, you may choose to encapsulate some logic in your own objects without providing “direct” access to the entity framework generated classes.

Let’s see an example to make it more clear:

public ActionResult Details(int id)

{

   using (StackFAQEntities faq = new StackFAQEntities())

   {

        var questionList = from q in faq.Questions

                       where q.Id == id

                       select q;

 

        Question myQuestion = questionList.First();

        return View(myQuestion);

   }

}

 

This is a sample of code from the application I built in one of my demos. It’s basically interacting with my model in order to get one Question object to send to a view that will display the details of that question. The StackFAQEntities and Question objects were generated by Entity Framework, but they could have come from anywhere really.

Imagine I refactor the code to something like this:

public ActionResult Details(int id)

{

    FaqModel myFaq = new FaqModel();

    Question myQuestion = myFaq.GetQuestionByID(id);

    return View(myQuestion);

}


Where FaqModel is a newly created class that looks like this:

public Question GetQuestionByID(int id)

{

    using (StackFAQEntities faq = new StackFAQEntities())

    {

            var questionList = from q in faq.Questions

                           where q.Id == id

                           select q;

 

        if (questionList.Count() > 0)

            return questionList.First();

        else

            return null;

    }


So, we have pretty much the same functionality, but as you can see our Details action is simply calling a generic method that returns a Question object. Obviously we are still using Entity Framework: not only we are using it to perform the query, but the Question class was generated by it. Let’s take care of those two “little” details:

public class Question

{

    public int id;

    public string Title;

    public string Body;

    public int Votes;

    public int Views;

    public DateTime DateCreated;

    public List<Answer> Answers;

}


So, I created my own implementation of the Question class. Let’s refactor the GetQuestionByID method so we completely lose the dependency from Entity Framework in my code:

public Question GetQuestionByID(int id)

{

    string connectionString = "...";

   

    SqlConnection con = new SqlConnection(connectionString);

    SqlCommand com = con.CreateCommand();

   

    com.CommandText = "select title, body, votes, views, DateCreated from                Question where Id=@id;

    com.Parameters.Add(new SqlParameter("@id", id));

   

    Question q = new Question2();

 

    con.Open();

    SqlDataReader dr = com.ExecuteReader();

    if (dr.HasRows)

    {

        dr.Read();

        q.Id = id;

        q.Title = dr.GetString(dr.GetOrdinal("title"));

        q.Body = dr.GetString(dr.GetOrdinal("body"));

        q.Votes = dr.GetInt32(dr.GetOrdinal("votes"));

        q.Views = dr.GetInt32(dr.GetOrdinal("views"));

        q.DateCreated = dr.GetDateTime(dr.GetOrdinal("DateCreated"));

        q.Answers = null; //we could change the query to retrieve the answers

        dr.Close();

    }

    con.Close();

 

    return q;

}


Ugly code, but suits our needs. I guess this makes it easy to understand how you can use ASP.NET MVC without any sort of abstraction layer like the Entity Framework. We are simply using a query to get some sort of information and then using the result to hydrate a business object we defined in our code (the Question object).

We could have changed the query to fetch items from a SQL View or to execute a stored procedure. We could have opted to direct our query to an Oracle Server instead of SQL Server. Heck, we could do this without a database even and read this information from a XML file or a WCF service.

To sum it up, your Model can be whatever you want. You can build objects that represent your data, or if you feel inclined to, you can simply pass a string like “field1;field2;field3” and then do the parsing on the View itself. Obviously, this is not recommended as you should prefer using strongly-typed Views to allow for a better experience and to be able to benefit from the scaffolding templates included in Visual Studio.

Let me just add that, anyway, Entity Framework has full support for Stored Procedures and SQL Views so that takes care of the first two questions up there. As for the third, doing a quick Bing search I was able to find a few Entity Framework providers for Oracle so you still have that option if you prefer (as I do) to use the Entity Framework.

Until next time!