Howto: simply read SQL Server database to MVC 4 view?

Question

I am relatively new to MVC. I am trying to just display the contents of a remote SQL Server table on a page. It is READ ONLY, and nearly everything I find online is utilizing the Entity Framework or something similar which is overkill for what I am after. I just need to print all the rows from a table out to a view.

So my questions are:

1. should I just hack the sqlconn and everything into the view that I want the data printing out to?
2. Or should I add the sql in that view's controller?
3. Create a model and somehow get that data back into the view?

I know step #3 is the 'correct' way to do it, however I am looking for something simple and quick. :)

Thanks everyone!

Answer 1

You say EF is overkill, but that's the easiest way to do this. Maybe use Linq to SQL, since that's more lightweight. This is all there is to it:

1. Generate the EF / L2S entity classes
2. Instantiate the database context in the controller, and get all records
3. Return the IEnumerable records to the view
4. In the view, use @Html.DisplayForModel()

Here's a simple example. Note that returning the database entity classes is considered bad practice, you should map / automap them to a View Model type class first.

Home Controller

public ActionResult Index()
{
    MyEntityModel[] items = MyDatabaseContext.GetAllRows();
    return View(items);
}

Home/Index View

@model IEnumerable<MyEntityModel>

@foreach (MyEntityModel item in Model)
{
    @Html.DisplayFor(m => item)
}

---

Without EF / L2S it's almost as easy, but you'd have to create your own entity / wrapper class for the database records and populate them manually.

There are also scaffolding projects for MVC that will generate repository and controller classes, as well as Razor views. See for example MVC Scaffolding on NuGet.

Answer 2

Once you get used to forcing yourself to use Entity Framework for even your "small" applications then and only then will you truly understand that is it the simplest way.

Sure, if you come from a background of memorized ADO.NET dataset/datatable/datareaders etc.. then sure you have projects with "canned" operations that you port over and modify but it is truly just old and cost you more time in the long run.

1. Use EF
2. Use multiple projects in a solution
3. Use a repository pattern if you can, it pays off in dividends for reasons you will discover
4. Keep your controllers "skinny"
5. Dont' use a "ViewModel" just to use / do it.

The SOC of having View and Controller are critical, but the Models folders does not need to be used, you can wire things up without it if you want, just pay attention to where your "model" is and abiding by SOLID principle etc..