Experimenting with MVC and the Entity Framework – Part 2 – Displaying some data

This is part 2 following on from Part 1.Here I will display some data from my database!

 

[more]

I pretty much used the Nerd Dinner sample to work out what I needed to do. I first of all got the venue types list, details and delete working using the entity framework mapped to stored procedures for CRUD operations. I’ll go into this for Comments in a later blog.

Using my experience gained I moved on to the comments screen. Most of the development here was pretty straight forward following the instructions for Nerd Dinner.

Creating the Entity Model.

I added in a new ADO.net entity model by right clicking in the solution explorer, selecting add new and the “Data” category. I then added entities for each of my tables.

At first glance this object model looks sufficient for the job. Typically I would de-normalise my relational model in my object model but there doesn’t seem to be anything to de-normalise!.

I then created a new View (Views/Comment/Index.aspx) strongly typed to the Comment object and a new controller – CommentController.

Initially the Index method displayed all the items in the table:

//

// GET: /Comments/

public ActionResult Index()

{

victorEntities e = new victorEntities();

// get the venue and location

return View("Index",

e.CommentSet.ToList<Comment>());

}

Mapping query to a stored procedure.

As I previously stated in Part 1 – I intend to use stored procedures for my database access as:

• they are more secure
• they are more scalable
• they allow separation of database schema and application code making the application more maintainable
• I don’t want to learn LINQ or Entity SQL!

For this particular project the last is probably the most important – but over the last 20 years I’ve witnessed many times the bad consequences of mixing application code and database queries.

I followed the instructions here http://msdn.microsoft.com/en-us/library/dd456824.aspx to import a stored procedure as a function that returns a complex type.

I wrote my stored procedure to return not only the comment but also the associated venue row as well as I wanted to display this:

ALTER PROCEDURE dbo.usp_CommentGetByVenueAndLocation

(

@p_vcrVenueName MediumString,

@p_vcrLocation MediumString

)

AS

SELECT * from tblComment c

INNER JOIN tblVenue v

ON c.intVenueId = v.intVenueId

WHERE (@p_vcrVenueName IS NULL OR v.vcrName like @p_vcrVenueName + '%')

AND (

@p_vcrLocation IS NULL

OR vcrAddressLine1 like @p_vcrLocation + '%'

OR vcrAddressLine2 like @p_vcrLocation + '%'

OR vcrTown LIKE @p_vcrLocation + '%'

OR vcrCountry LIKE @p_vcrLocation + '%'

)

I added a form for the input on the view:

<h2>Search</h2>

<% Html.BeginForm(); %>

<%= Html.Label("Venue") %>

<%= Html.TextBox("txtVenue") %>

<%= Html.Label("Location") %>

<%= Html.TextBox("txtLocation") %>

<input type="submit" value="Search" />

<% Html.EndForm(); %>

And modified the controller method to read the input from the form values colleciton -

public ActionResult Index(FormCollection formValues)

{

victorEntities e = new victorEntities();

// get the venue and location

String location = formValues["txtLocation"];

String venue = formValues["txtVenue"];

return View("Index",

e.CommentGetByVenueAndLocation(venue,location));

}

However, when it came to displaying the Venue attributes I found the venue object had not been loaded and I could find no way of doing so.

So I forced each row to load in the view as it iterates through – really inefficient.

<% foreach (var item in Model) {

item.VenueReference.Load();%>

<tr>

I also found that the columns returned from the stored proc had to exactly match the object attributes even if a mapping had been provided!

I had a poke around the web and the only thing close to what was required was this blog.

This seems a lot of work and the stored proc is not exactly efficient either.

Normally I hand crank my business objects and write a create method for each of my business objects (entities) which takes an IDataReader and so can call this to create any composite objects that are returned from stored procedures as well as providing lazy load via a get by primary key method/procedure for lazy loading.

Whilst doing this research I reached the conclusion that stored procedure support in the entity framework was pretty new and not very mature

Conclusions so far:

• I would not use Entity Framework on any enterprise project until stored procedure mapping was more mature.

Source

If anyone is interested here is the project as it stands.

This is pretty rough and ready development stuff.