Tuesday 19 October 2010

Experimenting with MVC and the Entity Framework – Part 4 - Authentication and Authorisation

Authentication and authorisation.

.Authentication = Who is it.

Authorisation = can they do it.

So – reading through the NerDinner section on authorisation and authentication then it looks like I can use good old FORMSAuth and APSNETDB to provide authentication and authorisation store out of the box.

I’ve been using these for a good few years now so don’t expect any issues.

I may change this later to:

o use a STS (Security Token Service) such as the excellent thinktecture Starter STS so I can secure my web services to a WS standard using the same backend store.

o Allow authentication from other systems such as facebook, google etc.

[more]

So I change my web.config to give a different name to the database as I know in my production database I have a few of these already! (Actually the database does support multiple applications but its much more difficult to migrate and maintain applications if they all use the same database).

<add name="ApplicationServices" connectionString="data source=.\SQL2008EXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb_victor.mdf;User Instance=true" providerName="System.Data.SqlClient" />

Ran the app and went to the register page.

clip_image002[9]

Database was created for me – worked a treat!

clip_image004[10]As you can see it now knows who I am!

Logged off and tried to add a comment – as expected it took me to the login screen. I had forgotten my password already so I was registered again.

I was not taken to the add comment screen – where I was originally heading.

I could not get my password back!

I’ll tackle the password retrieval first.

Password retrieval

Change the web.config to force unique emails and allow password reset:

<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="true" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/" />

And then I added a class into the existing account model page:

public class PasswordRetrieveModel

{

[Required]

[DisplayName("User name or Email address")]

public string UserName { get; set; }

}

This class models the attributes required to retrieve our password.

I then added in a strongly typed view to show the details:

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<victor.Models.PasswordRetrieveModel>" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">

RetrievePassword

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

<h2>RetrievePassword</h2>

<% using (Html.BeginForm()) { %>

<%= Html.ValidationSummary(true,"The password could not be reset") %>

<fieldset>

<legend>Fields</legend>

<div class="editor-label">

<%= Html.LabelFor(m => m.UserName) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(m => m.UserName) %>

<%= Html.ValidationMessageFor(m => m.UserName) %>

</div>

</fieldset>

<p>

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

</p>

<% } %>

</asp:Content>

and edited the model adding in password reset methods on the IMembershipService interface:

/// <summary>

/// reset the password

/// </summary>

/// <param name="p">username or email address</param>

/// <returns>flag indicating if password sent to the user.</returns>

bool ResetPassword(string p);

And an implementation on the AccountMembershipService class:

public class AccountMembershipService : IMembershipService

{

private static String rawMessage = @"You have requested a password reset for {0}.

Your username is: {1}

Your new password is: {2} ";

public bool ResetPassword(String p)

{

bool ret = false;

// first find the user by username or email address.

String username = null;

MembershipUser user = _provider.GetUser(p, true);

if (null == user)

{

username = _provider.GetUserNameByEmail(p);

if (null != username)

{

user = _provider.GetUser(username, true);

}

}

else

{

username = user.UserName;

}

// if we've found a user then send out his (reset) password

if (null != username)

{

user.UnlockUser();

String newpass = _provider.ResetPassword(username, null);

SmtpClient mailClient = new SmtpClient();

MailAddress address = new MailAddress(user.Email);

String message = String.Format(rawMessage,

HttpContext.Current.Request.Url.Host.ToString(),

user.UserName,

newpass );

mailClient.Send(ConfigurationSettings.AppSettings["fromemail"],

user.Email,

"Password Reset",

message);

ret = true;

}

return ret;

}

}

Finally I added new methods into the account controller:

// *****************************************

// URL: Password Request

// *****************************************

public ActionResult RetrievePassword()

{

return View();

}

[HttpPost]

public ActionResult RetrievePassword(PasswordRetrieveModel model)

{

ActionResult ret = null;

if (MembershipService.ResetPassword(model.UserName))

{

return View("PasswordRetrieved");

}

else

{

ModelState.AddModelError("", "The user was not found using the given Username or EMail address.");

ret = View();

}

return ret;

}

And added a section in the web.config to configure the smtp service.

<system.net>

<mailSettings>

<smtp deliveryMethod="Network">

<network host="localhost"/>

</smtp>

</mailSettings>

I usually set my local smtp service to allow relaying and relay to my real smtp server or just examine the mail in the iis dirctories where its droped (C:\Inetpub\mailroot\Queue ) to test. That way when I deploy to production (where the relaying works) I don’t need to change anything.

The relaying properties can be found in the Access tab of the smtp server properties in inetmgr.

clip_image006[8]

clip_image008[8]

Redirecting after registration

First thing to do is to pass the url to the registration page as part of the query string.

Please enter your username and password. <%= Html.ActionLink("Register", "Register", new { ReturnURL = Request.QueryString["ReturnURL"] })%> if you don't have an account.

I have to say at this point I have no idea how this works! Specifically the new { ReturnURL = Request.QueryString["ReturnURL"] } – how is the ReturnURL type declared?

The help says

“routeValues

Type: System..::.Object
An object that contains the parameters for a route. The parameters are retrieved through reflection by examining the properties of the object. The object is typically created by using object initializer syntax.

I read up on object initializer syntax on msdn but still don’t understand how the type of ReturnURL is not being delcared. Maybe the compiler is now smart eneugh to work out the type?

However – it does appear in the query string and so I can ammend the register method to take and act on this paramater–

[HttpPost]

public ActionResult Register(RegisterModel model, string returnUrl)

{

if (ModelState.IsValid)

{

// Attempt to register the user

MembershipCreateStatus createStatus = MembershipService.CreateUser(model.UserName, model.Password, model.Email);

if (createStatus == MembershipCreateStatus.Success)

{

FormsService.SignIn(model.UserName, false /* createPersistentCookie */);

if (!String.IsNullOrEmpty(returnUrl))

{

return Redirect(returnUrl);

}

else

{

return RedirectToAction("Index", "Home");

}

}

else

{

ModelState.AddModelError("", AccountValidation.ErrorCodeToString(createStatus));

}

}

OK – pretty much all core functionality is there now. I just need to add venue matching – so we don’t duplicate venues, tidy up the screens with some help and put them in the right places, then, hopefully, I’ll have something to put live!

Experimenting with MVC and the Entity Framework – Part 3 – Creating the comments.

This follows on from part 2.

So now to add in the creation of comments. Having read through the CRUD implementation in Nerddinner and using partials again, I decided to go straight for a partial for my comments creation, as I would almost certainly require an edit and maybe a view details too.

[more]

clip_image002

So I first created a partial using the add view option and then added a create page – again using the add view.

clip_image004

But this time the view was not strongly typed and empty.

I then set the Create View to display the partial

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">

Create

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

<h2>Create</h2>

<% Html.RenderPartial("CommentForm"); %>

</asp:Content>

A first bash at the post.

I new this wouldn’t work due to the FC constraint on the comments table requiring a venue – but I wanted to see what would happen:

[HttpPost]

public ActionResult Create(FormCollection collection)

{

Comment comment = new Comment();

try

{

UpdateModel<Comment>(comment);

victorEntities ve = new victorEntities();

ve.AddToCommentSet(comment);

ve.SaveChanges();

return RedirectToAction("Index");

}

catch

{

return View();

}

}

As expected – it blew up but due to the way this is coded – ie the catch “swallows” all exceptions – no error is presented to the user!

In my opinion this is an abuse of exceptions. Exceptions should not be used for normal program flow as:

1) When you are calling a method you have no way of determining what exceptions it throws/you need to handle (unlike java where you must explicitly declare the exceptions thrown – althogh this is not great as it forces the calling code to explicitly handle those exceptions in some way even if they can’t or declare that they throw them).

2) Exceptions should be just that – Exceptions. Business logic should be explicitly handled by code. In this case lack of input is definitely expeced. Exceptions should be for things beyond your control such as a file dissapearing or running out of memory.

My preference is always to handle exceptions in a global manner (usualy in the global.asax) – basically displaying something nice to the user and logging all the details you can. In theory we should never get any but they may happen due to

· Bugs

· Hardware problems such as networking errors.

· Scaleability issuses

You can then monitor these in test and production and take necesasry.

I will add error logging at a later date.

So I re-engineered the method to

[HttpPost]

public ActionResult Create(FormCollection collection)

{

ActionResult ret = View();

Comment comment = new Comment();

if(TryUpdateModel<Comment>(comment))

{

victorEntities ve = new victorEntities();

ve.AddToCommentSet(comment);

ve.SaveChanges();

ret = RedirectToAction("Index");

}

return ret;

}

Notice – I also added in a single exit point – makes the code much easier to read and helps reduce resources left open (such as files etc.).

Adding in the venue.

A comment is not valid without a venue which must also have a type.

The Nerd Dinner sample does not cover aggregates – they never do – so I thought I’d just have a stab at it anyway.

Firstly I created an empty object in the GET

//

// GET: /Comments/Create

public ActionResult Create()

{

Comment ncomment = new Comment();

Venue venue = new Venue();

ncomment.Venue = venue;

return View(ncomment);

}

And then following the instructions on the ObjectContext.AddObject Method I changed the POST method to add the venue first to the entities first:

//

// POST: /Comments/Create

[HttpPost]

public ActionResult Create(FormCollection collection)

{

ActionResult ret = View();

Comment comment = new Comment();

if(TryUpdateModel<Comment>(comment))

{

victorEntities ve = new victorEntities();

ve.AddToVenueSet(comment.Venue );

ve.AddToCommentSet(comment);

ve.SaveChanges();

ret = RedirectToAction("Index");

}

return ret;

}

Finally I added in the html on the form for the venue name to test it.

<div class="editor-label">

<%= Html.LabelFor(model => model.Venue.vcrName) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(model => model.Venue.vcrName) %>

<%= Html.ValidationMessageFor(model => model.Venue.vcrName)%>

</div>

I checked in the debugger and the name was correctly set to what was entered. An exception was thrown that the foreign key constraint to VenueType was broken – which was expected.

Adding a venue type dropdown.

clip_image006

Again – I looked through the NerdDinner sample and decided that in MVC speak I needed to either use ViewData or a view model.

ViewData involves creating a SelectList in the controller –

[Authorize]

public ActionResult Edit(int id) {

    Dinner dinner = dinnerRepository.GetDinner(id);

    ViewData["Countries"] = new SelectList(PhoneValidator.AllCountries, dinner.Country);

    return View(dinner);

}

and displaying it in the View.

<%= Html.DropDownList("Country", ViewData["Countries"] as SelectList) %>

A view model involves creating a new Class based on the items you want to display.

The ViewData appears to be more of a “quick and dirty” solution that is not type safe and requires the setting of the view data in multiple places – e.g. both the get and the post.

So I opted to do the right thing – a philosophy that I’ve found has always worked well!

Whilst doing this I learn’t something new. Visual Studio 2005 featured a code snippet for properties which was fantastic. However, it always seemed broken in 2008. Since I had a bit of time I decided to try and get it back only to discover I no longer needed to declare my class attribute as it would be automatically generated. If I wanted my getter or setter to do something I can always change the code to a specific “backing” store (such as an attribute) later. This is called “Automatic Properties” – this guy was also looking for his code snippet but this lead me to this nice blog on automatic properties.

Here’s what I did to get it all working –

1) Created a new model class specific to the data I was modelling.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace victor.Models

{

public class CommentFormViewModel

{

/// <summary>

/// The venue types to display.

/// </summary>

public SelectList VenueTypes { get; set; }

/// <summary>

/// The view comment

/// </summary>

public Comment ViewComment { get; set; }

public CommentFormViewModel(Comment comment)

{

victorEntities ve = new victorEntities();

ViewComment = comment;

VenueTypes = new SelectList(ve.VenueTypeSet.ToList<VenueType>(),

"intVenueTypeId",

"vcrName",

comment.Venue.Type);

}

public CommentFormViewModel()

{

}

}

}

2) Changed the View Form to display extra attributes and inherit from the new model class:

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl<victor.Models.CommentFormViewModel>" %>

<% using (Html.BeginForm()) {%>

<%= Html.ValidationSummary(true) %>

<fieldset>

<legend>Fields</legend>

<div class="editor-label">

<%= Html.LabelFor(model => model.ViewComment.Venue.Type.intVenueTypeId ) %>

</div>

<div class="editor-field">

<%= Html.DropDownListFor(model => model.ViewComment.Venue.Type.intVenueTypeId ,Model.VenueTypes ) %>

<%=Html.ValidationMessageFor(model => model.ViewComment.Venue.Type)%>

</div>

<div class="editor-label">

<%= Html.LabelFor(model => model.ViewComment.Venue.vcrName) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(model => model.ViewComment.Venue.vcrName)%>

<%= Html.ValidationMessageFor(model => model.ViewComment.Venue.vcrName)%>

</div>

3) Updated my create model to return the new Model

// GET: /Comments/Create

public ActionResult Create()

{

Comment ncomment = new Comment();

Venue venue = new Venue();

ncomment.Venue = venue;

CommentFormViewModel model = new CommentFormViewModel(ncomment);

return View(model);

}

4) Updated my create post method to use the new model, set the venue type based on the ID and complete some other mandatory fields to make the whole thing work.

// POST: /Comments/Create

[HttpPost]

public ActionResult Create(FormCollection collection)

{

ActionResult ret = View();

CommentFormViewModel fvm = new CommentFormViewModel();

if(TryUpdateModel<CommentFormViewModel>(fvm))

{

victorEntities ve = new victorEntities();

fvm.ViewComment.dteCreated = DateTime.Now;

fvm.ViewComment.dteLastUpdated = DateTime.Now;

fvm.ViewComment.vcrUserUpdated = HttpContext.User.Identity.Name;

fvm.ViewComment.Venue.dteCreated = DateTime.Now;

fvm.ViewComment.Venue.dteLastUpdated = DateTime.Now;

fvm.ViewComment.Venue.vcrUserUpdated = HttpContext.User.Identity.Name;

// set the venue type to an existing one

fvm.ViewComment.Venue.Type = ve.VenueTypeGetByID(

fvm.ViewComment.Venue.Type.intVenueTypeId).ElementAt<VenueType>(0);

ve.AddToVenueSet(fvm.ViewComment.Venue );

ve.AddToCommentSet(fvm.ViewComment);

ve.SaveChanges();

ret = RedirectToAction("Index");

}

return ret;

}

I wasn’t particularly happy with this solution as I new I’d be repeating code in my Edit method if and when I get one.

I will try and work out a better solution later on. See Concerns below.

Things left to do

I feel this is getting close to a beta release now. There are three things I want to do before I release the first version:

1) Introduce authentication/registration to be able to post comments.

2) Search for an existing venue when the user adds in a comment

3) Replace the CRUD operations with stored procedures.

4) Tidy up the fields to only show those required.

5) Carry out a security review

6) Investigate producing some unit tests.

Conclusions

  • Do not use the boiler plate code as the exception handling is very dodgy! Specifically it will “swallow” all your exceptions.
  • Use a specific model for your form to model behaviour or data types specific to your view.

Concerns

· One of MVC’s alleged advantages is it “It makes it easier to manage complexity by dividing an application into the model, the view, and the controller”. Personally – I always separate out the UI and business layer anyway and split the business layer into Business Process objects, Business Objects and a Data Access layer. It would seem to me that the Entity Framework has combined my DAL and Business objects and the processes are residing in my model. My concern is that these processes by default have been created as part of the User Interface, and not as a general API in a separate assembly as I would normally do. This will, IMO, make these harder to reuse for example, when I want to expose my business processes (the controller) as web services.

· There seems to be some strange stuff going on in the UI –

<div class="editor-label">

<%= Html.LabelFor(model => model.ViewComment.Venue.vcrTown) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(model => model.ViewComment.Venue.vcrTown)%>

<%= Html.ValidationMessageFor(model => model.ViewComment.Venue.vcrTown)%>

</div>
I need to try and understand what this “model => model.ViewComment.Venue.vcrTown” is doing and how. I’ve a feeling its something called Lambda expressions.

Next up – authentication & authorisation

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!

 

clip_image004

[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.

clip_image006

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!.

clip_image008

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

clip_image002

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.

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.

clip_image002

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!

Friday 1 January 2010

Windows 8 and Dell Duo–welcome to the revolution.

The revolution

Here’s why I think Microsoft have a winner.  I have held back from buying a proper tablet for the last few years because I’m always carrying a work laptop.  I know I’d end up lumping a laptop and a tablet around and then, eventually, dump the tablet.

Now users can have it all.  Windows 8 will end up on all work devices and hey presto – they can just carry that around and use it on the train, bus and at home for Facebook, twitter and reading the FT.  The same device for business and consumer.

And when it comes to buying for the home – they’ll just opt for what they know – but pure tablet –Windows RT.

image

The fancy new start menu – the heart of Windows 8.  I can run tablet apps like the lovely FT App and Visual Studio on the same device.

image

Tim Berners-Lee (we are not worthy) on the beautiful FT Metro App.  He tells us we should be using HTML5 Web Apps.  I think the FT did!

The hardware

Dell Inspiron duo Laptop Computer

A few years ago, I blogged about my Dell Duo, it has always been an OK notebook but in tablet mode let down by a complete lack of usability.  Basically standard windows apps just haven’t got big enough buttons to use as tablet apps.

The upgrade

Then, a couple of weeks ago, I decided to upgrade to Windows 8 pro RTM.   So, I downloaded my SDN copy, burnt a dvd and, fingers crossed, upgraded.  This is the first time I’ve upgraded a windows – I normally only change OS when I change machine or, occasionally, a rebuild.

Took about 5 hours to upgrade, which was worrying, but it worked.

The result

At first I thought windows 8 was just windows 7 with a big start menu.  But then I started to use some of the “Metro” apps in tablet mode.  It’s brilliant.  My Duo now works great in tablet mode, nice UI and Microsoft have really thought through the whole gestures and menu placement.

image

Metro app

OK, I have a couple of niggles, my Bluetooth doesn’t work anymore and my little Duo seems to struggle with performance sometimes – particularly with networking.  Not sure if it’s underpowered for Windows 8 or there are still some gremlins in there.

Windows 8 professional supports Metro apps, which are available only from the windows store – which are like AppStore apps like you run on iPads, iPods and iPhones and Market apps on your android.

There are not millions of Apps yet, barely hundreds, but expect me (@stuartm9999) and @exciledderryman to sort this.

and

Windows 8 professional also allows you to run all your traditional windows apps.

But that’s not where the store ends – Microsoft are also going to release a Windows 8 RT version (for ARM processers).  This version will only run Metro apps.  With all the windows “legacy crap” dumped expect this to run great as a pure tablet.

Microsoft are due to release there own hardware – the Windows Surface with Pro and RT option and other hardware vendors like Samsung withe their Ativ have also committed to Windows 8 RT .