MVC 2, Remote SQL and OS OpenDate Part 1

Using MVC2, remote SQL Connection and OS OpenData Map

I have only had the opportunity to use MVC a couple of times and there are some very good tutorials available. What I wanted to do here was to bring a few of these samples together in a complete micro site.

References:
Nerd Diner
Scottgu
bytes

Tools Used:

Visual Studio 2008 with SP1
ASP.NET MVC 2 (using the Web Platform Installer)
SQL 2005 Express Edition

Services Used:
OS Opendata (Map service)

Mini Brief:

Develop a microsite to allow self administration of the locations of BioDiesel sites. This should use the Ordnancesurvey OS Opendata Map API to display the sites, but also to act as a Geocoder for Post Code lookup.

Only registered users are to be allowed to add, edit or delete sites. Guests can search and view sites.


Process Flow

1. Set up database
1.1. Set up Data Tables

2. Create Model

3. Create Controls
3.1. Index
3.2. Details
3.3. Edit
3.4. Create

4. Create Views
4.1. Index
4.2. Details
5. Create Partial Views
5.1. Edit
5.2. Create

Part 1 - Setting up the database

After creating a new ASP.NET MVC 2 Website we need to alter the web.config to select the correct database to use. By default the MVC framework tries to use a local SQL Express. We are using a remote SQL Express server, which mimics a real business requirement.

To ensure that the MVC Login works we are initially going to set up the database using the aspnet_regsql.exe utility, setup details can be found in many places on the web.

Original Setting





Part 1.1 - Setting up the Data Tables

We need to add two tables to our database, the first one is to hold the location of the BioFuel station.

CREATE TABLE [dbo].[BioStation](
[StationID] [int] IDENTITY(1,1) NOT NULL,
[StationName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[DateAdded] [datetime] NOT NULL,
[Description] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL,
[Street] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[District] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Town] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[County] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Postcode] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[latitude] [float] NOT NULL,
[longitude] [float] NOT NULL,
CONSTRAINT [PK_BioStation] PRIMARY KEY CLUSTERED
(
[StationID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Table two is to hold the details of each stations Bio Fuel prices

CREATE TABLE [dbo].[BioPrice](
[FuelType] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[StationID] [int] NOT NULL,
[PriceDate] [datetime] NOT NULL,
[PricePerLitre] [decimal](5, 2) NOT NULL,
CONSTRAINT [PK_BioPrice] PRIMARY KEY CLUSTERED
(
[FuelType] ASC,
[StationID] ASC,
[PriceDate] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

The table needs to be joined to maintain data integrity

ALTER TABLE [dbo].[BioPrice] WITH CHECK ADD CONSTRAINT [FK_BioPrice_BioStation] FOREIGN KEY([StationID])
REFERENCES [dbo].[BioStation] ([StationID])
GO
ALTER TABLE [dbo].[BioPrice] CHECK CONSTRAINT [FK_BioPrice_BioStation]

We need to add some default test data, this cam be done using the "Show Table Data" in Visual studio, or as I prefer, using the Management studio. This is easy until we look at the Longitude and Latitude. It would be really usefull if there was only on LongLat system, and in a real system there would be a requirement to break out the location information into a separate table with a primary key of LongLat Type. I am going to be using the OS Opendata so I am going to save the LongLat information as OSGB type.

Part 2 - Creating the Model

Initially I want to use LINQ to SQL, more to give me a better understanding of LINQ but also with the BioPrices table able to hold historic price details, setting up LINQ should be more like a real situation. We need to start by adding the correct class to our Model.



Once the class has been installed we can then add the data tables directly.


Once we have the LINQ to SQL class we need to make a new class to help do some of the work. Create a new BioStationRepository.Class object

using System;
using System.Collections.Generic;
using System.Linq;

namespace BioFuels.Models
{
public class BioStationRepository
{
private BioStationsDataContext db = new BioStationsDataContext();

//Query Method
#region queries
public IQueryable FindAllStations()
{
return db.BioStations;
}

public IQueryable FindStationsByCounty(string County)
{
return from bioStation in db.BioStations
where bioStation.County == County
select bioStation;
}

public BioStation GetStation(int id)
{
return db.BioStations.SingleOrDefault(s => s.StationID == id);
}
#endregion

#region Add methods
public void AddBioStation(BioStation station)
{
db.BioStations.InsertOnSubmit(station);
}

public void AddBioStationPrice(BioPrice price)
{
db.BioPrices.InsertOnSubmit(price);
}

#endregion

public void save()
{
db.SubmitChanges();
}
}
}

With the Index and Details stub, right mouse click and select Add View. This will create a Folder called BioDiesel and the aspx pages required for the views.

Integrating validation is key to ensuring a fully compliant and smooth running web site. Part of this is to do with prevention of SQL Inject attacks. LINQ to SQL handles escaping characters in SQL so helps to prevent this form of attack.

We need to create validation classes for the BioStation and BioPrices classes. Starting with the BioStation Class, create a new partial class (partial so the new class will be absorbed by the class created earlier).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq;

namespace BioFuels.Models
{
public partial class BioStation
{
public bool IsValid
{
get
{
return (GetRuleViolations().Count() == 0);
}
}

public IEnumerable GetRuleViolations()
{
if (string.IsNullOrEmpty(StationName))
yield return new RuleViolation("Station Name required", "StationName");

if (string.IsNullOrEmpty(Description))
yield return new RuleViolation("Description required", "Description");

if (string.IsNullOrEmpty(Street))
yield return new RuleViolation("Street Address required", "Street");

if (string.IsNullOrEmpty(District))
yield return new RuleViolation("District required", "District");

if (string.IsNullOrEmpty(Town))
yield return new RuleViolation("Town required", "Town");

if (string.IsNullOrEmpty(County))
yield return new RuleViolation("County required", "County");

if (string.IsNullOrEmpty(Postcode))
yield return new RuleViolation("Postcode required", "Postcode");

yield break;
}

partial void OnValidate(ChangeAction action)
{
if (!IsValid)
throw new ApplicationException("Rule violations prevent saving");
}

}

public partial class BioPrice
{
public bool IsValid
{
get
{
return (GetRuleViolations().Count() == 0);
}
}

public IEnumerable GetRuleViolations()
{
if (string.IsNullOrEmpty(FuelType))
yield return new RuleViolation("FuelType required", "FuelType");

if (PricePerLitre <= 0)
yield return new RuleViolation("Price required", "PricePerLitre");

yield break;
}

partial void OnValidate(ChangeAction action)
{
if (!IsValid)
throw new ApplicationException("Rule violations prevent saving");
}

}

public class RuleViolation
{
public string ErrorMessage { get; set; }
public string PropertyName { get; set; }

public RuleViolation(string errorMessage, string propertyName)
{
ErrorMessage = errorMessage;
PropertyName = propertyName;
}
}
}



Part 3 - Controlling the data

We need to control the data for Stations and for the Fuel prices, remembering not all BioStations will have any fuel prices.

Start of by creating a new Controller and calling it BioStationController and select the default "Add action methods…"

Index ActionResult
In keeping with many lists we don't always want to list every available item. Therefore we need to provide a "Filter". To do this we need to add attributes to the method signature.

//
// GET: /BioStation/

public ActionResult Index(string county)
{

ViewData["Message"] = "All BioStations";

var stations = bioStationRepository.FindAllStations().ToList();

if (!string.IsNullOrEmpty(county))
stations = bioStationRepository.FindStationsByCounty(county).ToList();

if (stations == null || stations.Count == 0)
{
ViewData["Message"] = "There are no BioStations listed in " + county;
return View("NotFound");
}
else
return View("Index", stations);
}

There is no way to reach the filter unless we describe the route in the Global.asax.

routes.MapRoute(
"AllBioStations",
"BioStation/County/{county}",
new { controller = "BioStation", action = "Index", id = UrlParameter.Optional }
);

Details ActionResult
We need to alter the Details method to return a sngle BioStation Detail

//
// GET: /BioStation/Details/5

public ActionResult Details(int id)
{
ViewData["Message"] = "Full Details of BioStation";
BioStation station = bioStationRepository.GetStation(id);

if (station == null)
{
ViewData["Message"] = "BioStationID: " + id + " Not Found!";
return View("NotFound");
}
else
return View("Details", station);
}

Edit ActionResult

It may be necessary to edit a BioStation entry, we already have the stub made for the ActionResult Edit(int id). Now we need to do some work in the stub, but first we need to Add View. The is done in two part the first stub retrieves the record to be edited, the second stub updates the record.

//
// GET: /BioStation/Edit/5

public ActionResult Edit(int id)
{
ViewData["Message"] = "Edit Details of BioStation";
BioStation station = bioStationRepository.GetStation(id);

if (station == null)
{
ViewData["Message"] = "BioStationID: " + id + " Not Found!";
return View("NotFound");
}
else
return View("Edit", station);

}

The next part updates the record but we are not concerned with validation at this stage. Therefore we need to build the Update Logic.

//
// POST: /BioStation/Edit/5

[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
BioStation station = bioStationRepository.GetStation(id);
try
{

station.DateAdded = DateTime.Now;
UpdateModel(station);

return RedirectToAction("Details", new { id = station.StationID });
}
catch
{
return View();
}
finally
{
bioStationRepository.save();
}
}



Part Two to follow shortly ( or when I get some time)

Technical Questions part 1

1. What is Object Oriented Programming?
Object-oriented programming (OOP) is a programming paradigm that uses "objects" – data structures consisting of datafields and methods together with their interactions – to design applications and computer programs.

2. What are the main principals of OOP?
Programming techniques may include features such as data abstraction, encapsulation, modularity, polymorphism, and inheritance.

3. What is the difference between a Value Type and a Reference Type?
Variables that are based on value types directly contain values. Assigning one value type variable to another copies the contained value. This differs from the assignment of reference type variables, which copies a reference to the object but not the object itself.

Unlike with reference types, you cannot derive a new type from a value type. However, like reference types, structs can implement interfaces.

Unlike reference types, a value type cannot contain the null value. However, the nullable types feature does allow for values types to be assigned to null.

Each value type has an implicit default constructor that initializes the default value of that type.

4. What is an Abstract Class?
An abstract class cannot be instantiated. The purpose of an abstract class is to provide a common definition of a base class that multiple derived classes can share. For example, a class library may define an abstract class that is used as a parameter to many of its functions, and require programmers using that library to provide their own implementation of the class by creating a derived class.

5. What is an Interface?
An interface has the following properties:
• An interface is like an abstract base class: any non-abstract type inheriting the interface must implement all its members.
• An interface cannot be instantiated directly.
• Interfaces can contain events, indexers, methods and properties.
• Interfaces contain no implementation of methods.
• Classes and structs can inherit from more than one interface.
• An interface can itself inherit from multiple interfaces.

6. What is Inheritance?
A class that derives from another class (the base class) automatically contains all the public, protected, and internal members of the base class except its constructors and destructors.

7. Does C# allow for multiple Inheritances?
No

8. What is Encapsulation?
According to the principle of encapsulation, a class or struct can specify how accessible each of its members is to code outside of the class or struct. Methods and variables that are not intended to be used from outside of the class or assembly can be hidden to limit the potential for coding errors or malicious exploits.

9. Name the access modifiers in C#.
It is important to limit the accessibility of your code so that only the intended client code can reach it. You specify how accessible your types and their members are to client code by using the access modifiers:

a. public
The type or member can be accessed by any other code in the same assembly or another assembly that references it.

b. private
The type or member can only be accessed by code in the same class or struct.

c. protected
The type or member can only be accessed by code in the same class or struct, or in a derived class.

d. internal
The type or member can be accessed by any code in the same assembly, but not from another assembly.

e. protected internal
The type or member can be accessed by any code in the same assembly, or by any derived class in another assembly.

Technical Questions Part 2

Part 1 of the Technical Questions concentrated on the basics of the questions, such as “What is OOP”? Part 2 of this blog is looking at some of the more specific questions I have been asked.

I have not sectioned or categorised the list of questions as I have just gone through them as I remember – I may look at grouping them in the future, but you know what it’s like, just as you start typing another set of questions comes into your head.

Some of the questions also highlight some of the difficulties the questioner has in phrasing the questions. Take for instance a question I was asked a few days ago, “Is XML case sensitive”?

The answer isn’t as simple as the question is asked because it was out of context. XML on its own is NOT case sensitive. It does, however, have to conform to “well formed” document parameters. The case used in the open tag must be identical to that of the closed tag, and a few other rules it must comply with. The question assumes that XML is a program language, such as Java or C# which have different rules over case sensitivity. XML is a text document that has a structure that conforms to a set of rules.

In the case of this type of question it is always good to ask for clarification, explaining what you understand about XML being well formed.

1. In C# what is Boxing and when is it used?
Boxing and unboxing enable value types to be treated as objects. Boxing a value type packages it inside an instance of the Object reference type. This allows the value type to be stored on the garbage collected heap. Unboxing extracts the value type from the object.

In relation to simple assignments, boxing and unboxing are computationally expensive processes. When a value type is boxed, an entirely new object must be allocated and constructed. To a lesser degree, the cast required for unboxing is also expensive computationally.

2. What is the difference between a string and StringBuilder?
Both String and StringBuilder are classes used to handle strings

And then we get into understanding about Mutable and Non-Mutable objects.
string = Non-Mutable, it's contents cannot be altered or length of the string changed without making a new string.

StringBuilder = Mutable, the size and content of the string can be altered in the same memory space without the need to create a new object.

The most common operation with a string is concatenation. This activity has to be performed very efficiently. When we use the "String" object to concatenate two strings, the first string is combined to the other string by creating a new copy in the memory as a string object, and then the old string is deleted. This process is a little long. Hence we say "Strings are immutable".

When we make use of the "StringBuilder" object, the Append method is used. This means, an insertion is done on the existing string. Operation on StringBuilder object is faster than String operations, as the copy is done to the same location. Usage of StringBuilder is more efficient in case large amounts of string manipulations have to be performed.

3. What is an Assembly?
An assembly may be an exe, a dll, an application having an entry point, or a library. It may consist of one or more files. It represents a group of resources, type definitions, and implementation of these types. They may contain references to other assemblies. These resources, types & references are compacted in a block of data called manifest.

4. What is a Session?
The session object is used to store the data specific to a user for the entire length of a user's visit to a website.

5. What is a Thread?
When we want to run one or more instances of a method, we make use of threading.