Experimenting with MVC and the Entity Framework – Part 1

If you’ve got an hour – watch this http://channel9.msdn.com/posts/matthijs/ASPNET-MVC-2-Basics-Introduction-by-Scott-Hanselman/

Introduction

For some time now many of my techy colleagues have been pestering me to have a look at MVC and some of the Object Relational technologies emerging from Microsoft.

I have been telling them I’m not interested.

This was a bit of a lie, I am interested but I’m also old. - old enough to have seen some of these magic bullets before.

[more]

I was once involved in a project which all of a sudden had to adopt an MVC framework. In this case it was Apache Struts. Now this is now a pretty mature product but at the time it was new, buggy and the support was very limited. In fact, from what I remember, just about everything in the technology stack was in flux. EJB 2.0 had just come out, the Web Logic server was new, we were using some shaky open source LDAP server etc.

I hasten to add I was not the architect on the project else I would have stuck with the C++ on Sybase that was working for another year or two. Luckily the dot-com bust happened, the bank shed all its contractors and I went next door to another bank for more money on a more stable platform – those were the days.

Well – more stable in terms of the UI technology. However, the other bank had a corporate policy to adopt an Object Relational tool. I believe the Object Relational mapping later became a java standard.

This worked well for development. We built a great business object model for netting trades. Both me (as technical lead) and the architect were heavily into OO. We mapped it to the database schema and it sailed through test.

Then we hit production – firstly we had performance problems. The database was accessed using parameterised queries generated by the tool. The business process of handling a trade was in a transaction that spanned the incoming message Q and the database.

The performance was abysmal and the system kept on deadlocking.

Still, a few indexes and some hand cranking into stored procedures fixed this.

The second issue was harder. Every so often the Object cache in the third party tool would get out of sync with the database. A value displayed to the user would get “stuck” – although fortunately the underlying data in the database was OK.

We flew a support engineer from Canada to try and resolve it. Sat him down until a problem occurred, attached the java command line debugger to the process and gave him the keyboard so he could take diagnostics. At this point he said “I’ve never actually been involved with any coding before!”

Also – this bank later went on to make a few investment mistakes and collapsed creating a global banking crisis. This was not my fault or the fault of MVC.

So, when informed “there’s a great new MVC framework in town and it works great with a new Object Relational mapping technology that means you don’t need to know SQL anymore” my enthusiasm was not exactly high.

Aims

• to assess the suitability of MVC and the Entity Framework to the Enterprise
• to investigate these new technologies
• to produce a working application
• to have a bit of fun

The project

Some people say I moan a lot. I have been compared to Victor Meldrew – a character from a UK sitcom. To an extent, I agree, like Victor I get flabbergasted by the stupid things that seem to go wrong.

So – here’s the concept – a website called either Victor Meldrew or ImNeverGoingBackThere where people can say what went wrong with places they went to.

Probably stick to simple stuff to start with and then see if people use it

Basic Use Cases.

Display a list of recent gripes.

*The user is displayed a list of recent gripes. Each gripe consists of a venue type, venue name, venue location.

Search for gripes

Comment on a gripe.

Add a gripe.

Tools

Although I have a full licence for Visual Studio P.E. 20xx and SQL server developer 20xx – I decided to use the free versions to make the project useable by all.  I did not use 2010 as this is not supported on XP.  Also, I’ve stopped lugging my laptop around and wanted the whole thing on a memory stick.

So tools are –

Visual Web Developer 2008 Express Edition available free from Microsoft.  I did not use VS 2010 as s

SQL Server 2008 Express.

Windows XP SP2 and Windows 7

I will probably have to deploy on IIS6, Windows server 2003 and SQL Server 2005 as this is what’s installed in the production environment I have in mind.

Approach

I like to start all technical projects with an initial schema design, then build basic object layer on top and implement use cases to fill out the details.

I intend to blog each of these iterations

Initial Schema

Just to finish this blog with a bit of techie stuff, here is the initial schema.

Notes on the schema.

By convention we always create a “non natural primary key”.

Or, in English, create an integer column for the primary key which is an identity – i.e. created by the database. This column is names in<Table Name>Id

This is very good practice as:

1. The keys created are very good indexes as they are numbers.
2. Its easy to see from a table definition the foreign key relationships e.g. when I look at tblComment I can see instantly that intVenueId refers to tblVenue. This also makes writing stored procedures much quicker.
3. A change in business logic or requirements will not suddenly make my key non unique.
4. The schema is more amenable to change (or refactoring as we like to call it now). We can move any of the other columns during design/development without worrying about the PK.
5. Generating the number in SQL as an identity means the number is guaranteed unique.
6. Identity is efficient to generate.

I am sure there are other reasons too.

Put the PK, foreign key and all other constraints in from the start.

So many times I’ve worked on projects where “they’ll put in the constraints at the end”. What when you’re up against the deadlines and neck deep in testing and refactoring as opposed to when your doing design!

Code, technologies, users, operating systems all come and go – data stays around for years, sometimes decades. It’s going to get dirty but the more you can make the database stop this the longer it’ll take.

Create some string user defined types.

I usually define 3 user define types in SQL so I don’t need to worry about mismatches in string lengths between my stored procedures and tables.

ShortString – nvarchar(10)

MediumString nvarchar(100)

LongString – nvarchar(255)

I do this using -

CREATE TYPE ShortString FROM nvarchar(10)

Etc.

Standard columns for audit.

I also add in at least three columns for audit purposes.

I might also add in a couple of other columns on each table

intSessionId – the users login session.

intRequestId - a number for the page request

These two can be used when I want to see what data was updated by a particular user for a particular session/login and during a particular change request.

Normally I would use SQL management studio to create the database and initial schema but this is not in my toolset – so I used VS. Unsurprisingly it was pretty much the same.

In part 2 I Display some data!