Monday, 30 January 2012

How to Get Enum Elements' Values and Descriptions

Below is an example of how to populate a collection from an Enumeration elements' values and Description attributes (names if Description attributes are not exist).

Say that we have the following Enum:
public enum EnumStatus
{
    [Description("Not Submitted")]
    NotSubmitted = 0,

    Requested = 1,

    [Description("Pending Approval")]
    PendingApproval = 2,

    Approved = 3,

    Rejected = 4
}

Then the code to get each element's value and Description attribute (or name) is:
var type = typeof(EnumStatus);
foreach (var field in type.GetFields().Where(f => f.FieldType == type))
{
    var attribute = Attribute.GetCustomAttribute(field, typeof(System.ComponentModel.DescriptionAttribute)) 
        as System.ComponentModel.DescriptionAttribute;
    var value = field.GetValue(Activator.CreateInstance(type));
    myCollections.Add(
        new
        {
            Id = (int)value,
            Name = attribute != null ? attribute.Description : value.ToString() // or Enum.GetName(typeof(EnumStatus), value)
        }
    );
}

Friday, 20 January 2012

Windows Installer Issue when Installing over Previous Version Application

Recently I worked on Windows Setup Project to deploy a new version of an existing application. The application and installer was developed using Visual Studio 2008. I'm using Visual Studio 2010 to work on the project. All of the projects including the Windows Setup are still using the same .NET framework and prerequisites.

When I try to install the application (it has a setup.exe, an msi and some other files that have been packaged together using IExpress) over the older version on a machine, a few issues occurred even tough the installer said that the installation was successful.

The issues were the application's shortcuts were disappeared and all unmodified files in the installation directory were gone, only the modified ones were still there. What I mean by unmodified files are the files that were exist in both version and have not been updated/changed in the new version.

According to these articles; http://connect.microsoft.com/VisualStudio/feedback/details/559575/problem-with-installing-and-removing-previous-versions-after-upgrading-my-setup-project-to-vs2010 and http://social.msdn.microsoft.com/Forums/en-US/winformssetup/thread/b87f1aea-d15a-484b-8cdc-0d212784f941/, the problem occurs because all of the files' component GUIDs are changed when the setup project is migrated from Visual Studio 2008 to Visual Studio 2010. A workaround for this is to re-sequence 'RemoveExistingProducts' right after 'InstallInitialize' in the installation processes sequence table of the application's msi file.

Here is the detail of the process:
1. Use 'Orca' to open the new version applicaton's msi file. 'Orca' is a tool for creating and editing Windows Installer packages and merge modules. 'Orca' can be downloaded from http://msdn.microsoft.com/en-us/library/windows/desktop/aa370557%28v=vs.85%29.aspx.
2. Right click the msi file then select 'Edit with Orca'.
3. Select 'InstallExecuteSequence' table on the left pane window.
4. Then on the right panel, find 'RemoveExistingProduct' in the 'Action' column, see the blue colour highlighted row.
5. Double click the 'Sequence' value (the yellow colour highlighted cell) then change the value to 1525.
6. Save the changes.

Then try the installation again.

Friday, 13 January 2012

Template of a Stored Procedure with Savepoint

Savepoint is used for selective roll back. Using savepoint, a transaction can roll back to a selected location that has been marked. When it is rolled back, in the end the transaction must be completed by using 'commit transaction' or rolled back altogether.

Savepoint name should be unique even though duplicate is allowed. If a roll back is occurred where there is a duplicate, the transaction will be rolled back to the latest savepoint.

CREATE PROCEDURE [Procedure_Name]
AS
BEGIN

-- generate a unique savepoint name by appending procedure name (OBJECT_NAME(@@procid)) and nested level (@@nestlevel)
-- we could also use only @@nestlevel as it will always be unique in an active connection
-- savepoint name's maximum length is limited to 32 characters only
DECLARE @savepoint NVARCHAR(32) = CAST (OBJECT_NAME(@@procid) AS NVARCHAR(29)) +
           CAST (@@nestlevel AS NVARCHAR(3))

-- this is to check whether nested transactions exist when entering this procedure,
--  the value will be used later for checking condition
DECLARE @entryTrancount INT = @@trancount

BEGIN TRY
 BEGIN TRANSACTION
 SAVE TRANSACTION @savepoint
 
 --do something here
 
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 -- transaction is uncommittable (XACT_STATE() = -1) and no nested transactions exist (@entryTrancount = 0)
 IF XACT_STATE() = -1 AND @entryTrancount = 0
  ROLLBACK TRANSACTION
 -- otherwise if transaction is committable
 ELSE IF XACT_STATE() = 1    
  BEGIN
   ROLLBACK TRANSACTION @savepoint
   COMMIT TRANSACTION
  END
   
 DECLARE @ERROR_MESSAGE NVARCHAR(4000)
 SET @ERROR_MESSAGE = 'Error occured in procedure ''' + OBJECT_NAME(@@procid)
       + ''', Original Message: ''' + ERROR_MESSAGE() + ''''
 RAISERROR (@ERROR_MESSAGE, 16, 1)
 RETURN -100
END CATCH
END

According to MSDN, XACT_STATE function returns three values:
1 - The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
0 - There is no active user transaction for the current request.
-1 - The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.


References and further reading:
http://msdn.microsoft.com/en-us/library/ms188378%28v=SQL.105%29.aspx
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson
http://msdn.microsoft.com/en-us/library/ms189797.aspx
http://dosql.com/cms/index.php?option=com_content&view=article&id=101:trancount-and-xactstate&catid=40:microsoft-sql-server&Itemid=41

Monday, 12 December 2011

SQL Server Trigger Template

This a template for creating an After/Instead Of trigger in SQL Server 2005/2008:
CREATE TRIGGER <schema>.<tablename>$[InsteadOf]<actions>[<purpose>]Trigger
ON <schema>.<tablename>
[AFTER|INSTEAD OF] <comma delimited actions> AS
BEGIN

 DECLARE @rowsAffected INT,  --stores the number of rows affected
         @msg VARCHAR(2000)  --used to hold error message
 SET @rowsAffected = @@ROWCOUNT
 
 IF @rowsAffected = 0 RETURN
 
 SET NOCOUNT ON --to avoid the rowcount messages
 SET ROWCOUNT 0 --in case client has modified the rowcount
 
 BEGIN TRY
  --[validation section]
  
  --[modification section]

  --[perform action] --for INSTEAD OF trigger
 END TRY
 BEGIN CATCH
  IF @@trancount > 0
   ROLLBACK TRANSACTION
  
  --log the error
  EXECUTE utility.ErrorLog$insert  --this is only one example to do logging
  
  DECLARE @ERROR_MESSAGE NVARCHAR(4000)
  SET @ERROR_MESSAGE = ERROR_MESSAGE()
  RAISERROR (@ERROR_MESSAGE, 16, 1)
 END CATCH
END

Below is an example of using a table and a procedure to do error logging:
CREATE TABLE utility.ErrorLog(
 ERROR_NUMBER int NOT NULL,
 ERROR_LOCATION sysname NOT NULL,
 ERROR_MESSAGE varchar(4000),
 ERROR_DATE datetime NULL
  CONSTRAINT dfltErrorLog_error_date DEFAULT (getdate()),
 ERROR_USER sysname NOT NULL
  --use original login to capture the user name of the actual user
  --not a user that has been impersonated
  CONSTRAINT dfltErrorLog_error_user_name DEFAULT (original_login())
)
GO

CREATE PROCEDURE utility.ErrorLog$insert
(
 @ERROR_NUMBER int = NULL,
 @ERROR_LOCATION sysname = NULL,
 @ERROR_MESSAGE varchar(4000) = NULL
) AS
BEGIN
 BEGIN TRY
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  SELECT ISNULL(@ERROR_NUMBER, ERROR_NUMBER()),
      ISNULL(@ERROR_LOCATION, ERROR_MESSAGE()),
      ISNULL(@ERROR_MESSAGE, ERROR_MESSAGE())
 END TRY
 BEGIN CATCH
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  VALUES (-100, 'utility.ErrorLog$insert',
      'An invalid call was made to the error log procedure')
 END CATCH
END

Reference:
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson

Monday, 5 December 2011

Calling Controller Action Method with HTTP GET Ajax Request

In this post, we will see how to do Ajax request using HTTP GET method with JSON in ASP.NET MVC3. HTTP GET method to retrieve data in JSON format should never be used for getting sensitive information, the POST method should be used instead. For more information of how to use the POST method, you can read my previous post; Ajax with jQuery and JSON in ASP.NET MVC 3 or Passing Object in JSON to Controller.

First we prepare our controller method that will respond to the Ajax call:
public JsonResult GetDetailsUsingHttpGet(int id)
{
    Team team = teamRepository.Find(id);
    var anonymousTeam = new { TeamId = team.TeamId, Name = team.Name };

    //'JsonRequestBehavior.AllowGet' must be set, otherwise we will get an error message '500 error'
    return Json(anonymousTeam, JsonRequestBehavior.AllowGet);
}
Since MVC version 2, a controller method that is responding to a GET request is not allowed to return JSON format data for security reason. We need to explicitly state that this operation is allowed by setting 'JsonRequestBehavior.AllowGet' in the returning Json() method.

Then our JavaScript codes:
$(document).ready(function () {
    $("#ajaxBtnGetOne").click(function (event) {
        $.ajax({
            type: "GET",
            url: "/Teams/GetDetailsUsingHttpGet/1",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: AjaxGETSucceeded,
            error: AjaxFailed
        });
    });  
});
function AjaxGETSucceeded(objdata) {
    alert('success');
    alert(objdata.TeamId + ' - ' + objdata.Name);
    $('#ajaxDiv').html(objdata.TeamId + ' - ' + objdata.Name);
}
function AjaxFailed(result) {
    alert('an error has occured: ' + result.status + ' ' + result.statusText);
    alert(result.responseText);
}
Note that in the Ajax function we pass a parameter through its 'url' setting; in this example is '1' that will be passed as an 'id' parameter.

Monday, 28 November 2011

Passing Object in JSON to Controller

In this post, we will see how to pass object(s) in JSON from a jQuery Ajax function to a controller method in ASP.NET MVC 3. For more detailed explanation about the jQuery function and receiver controller method, you may want to see my previous post.

First we prepare our class which structure will be similar as the structure of the object(s) in JSON that is going to be passed. This class will also be the data type of the object(s) received by the receiver method through its parameter:
public class TeamViewModel
{
    public int TeamId { get; set; }
    public string Name { get; set; }
}

Then our JavaScript codes (jQuery version used at the time of writing is 1.5.1):
$(document).ready(function () { 
    //pass an object 
    $("#ajaxBtnPostTwo").click(function (event) {
        $.ajax({
            type: "POST",
            url: "/Teams/ProcessObjectUsingHttpPost",
            data: "{ 'TeamId':'10', 'Name':'TopTeam' }",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: ObjectWithAjaxSucceeded,
            error: AjaxFailed
        });
    });

    //pass a collection of objects
    $("#ajaxBtnPostThree").click(function (event) {
        var teamlist = [ { TeamId: 5, Name: 'Team Five'},
                            { TeamId: 6, Name: 'Team 6'},
                            { TeamId: 7, Name: 'Team seven'} ]

        $.ajax({
            type: "POST",
            url: "/Teams/ProcessListObjectsUsingHttpPost",
            data: JSON.stringify(teamlist),
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: ObjectWithAjaxSucceeded,
            error: AjaxFailed
        });
    });
});

function ObjectWithAjaxSucceeded(data) {
    alert('success');
    //write content to a div
    $('#ajaxDiv').html(data);
}
function AjaxFailed(result) {
    alert('an error has occured: ' + result.status + ' ' + result.statusText);
    alert(result.responseText);
}
Note that the first ajax function is passing an object while the second one is passing a collection of objects. On the first method, the object is specified directly in the JSON string. While on the second method, JSON.stringify() method is used to convert the JavaScript objects into JSON string.

Finally, our controller methods:
[AcceptVerbsAttribute(HttpVerbs.Post)]
public JsonResult ProcessObjectUsingHttpPost(TeamViewModel team)
{
    return Json(String.Format("{0} -processed- <br/> {1} -processed-",
                            team.TeamId, team.Name));
}

[AcceptVerbsAttribute(HttpVerbs.Post)]
public JsonResult ProcessListObjectsUsingHttpPost(List<TeamViewModel> teams)
{
    StringBuilder sb = new StringBuilder();
    foreach (TeamViewModel team in teams)
    {
        sb.AppendFormat("{0} -processed- , {1} -processed <br />", team.TeamId, team.Name);
    }
    return Json(sb.ToString());
}
The first one receives a single object while the second one receives a collection of objects. The framework automatically serialize the JSON data into the data type (class) that we have specified above; ie. TeamViewModel.

On the next post we will see how to do Ajax call with HTTP GET method.

Monday, 21 November 2011

Ajax with jQuery and JSON in ASP.NET MVC 3

In this article, we'll see how to do Ajax request using HTTP POST method in ASP.NET MVC 3. The recent MVC framework allows this to be done easily.

First we prepare our html controls.
<input id="ajaxBtnPostOne" value="Ajax Button Post 1" param="1" type="button" />
<div id='ajaxDiv'></div>

Then the javascript codes. Make sure you have jQuery library in the application. In this article, I use jQuery 1.5.1.
$(document).ready(function () {
    $("#ajaxBtnPostOne").click(function (event) {
        $.ajax({
            type: "POST",
            url: "/Teams/GetDetailsUsingHttpPost", 
            data: "{ 'id':'" + $('#ajaxBtnPostOne').attr('param') + "'}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: AjaxSucceeded,
            error: AjaxFailed
        });
    });
});

function AjaxSucceeded(data) {
    alert('success');
    //write content to a div
    $('#ajaxDiv').html(data);
}

function AjaxFailed(result) {
    alert('an error has occured: ' + result.status + ' ' + result.statusText);
    alert(result.responseText);
}
Note that we use jQuery function $.ajax() to bind to the 'on clicked' event of the html button we have above.
The settings on this method:
- type: we want use HTTP POST.
- url: this is the url where the Ajax call will be posted to. In this case is the location of the server side method 'GetDetailsUsingHttpPost' that we are going to create below.
- data: this is the data that will be passed to the method.
- dataType: we want to use JSON in this case.
- success: what to do when the Ajax call is returned successfully, in this case we execute a method.
- error: what to do if an error occurred, in this case we execute a method.

Then we have the server side receiver method. In MVC, we can simply use a controller method to handle an Ajax call. No extra configuration is needed.
[AcceptVerbsAttribute(HttpVerbs.Post)]
public JsonResult GetDetailsUsingHttpPost(int id)
{
    Team team = teamRepository.Find(id);
    return Json("Team Name = " + team.Name);
}
Note that '[AcceptVerbsAttribute(HttpVerbs.Post)]' attribute is used so that the method will only respond to a POST method. When Ajax call is used with JSON to query sensitive information, it is recommended to use POST method. Please see http://haacked.com/archive/2009/06/25/json-hijacking.aspx for more details.

Also notice that the method accept an integer parameter however the passed data is a string; the framework does the conversion automatically. Note that the variable name ('id') used in the 'data' setting of the javascript function is the same as the one accepted by the method.

To return JSON data, we just need to put the data to be returned inside Json() method and specify the controller method's return type as JsonResult.

Say now we want to return an object instead of a string. To do this, we can simply use an anonymous type or a class. If we choose to use a class then prepare our class, eg. TeamViewModel:
public class TeamViewModel
{
    public int TeamId { get; set; }
    public string Name { get; set; }
}
No extra configuration is needed to return an anonymous type or a class' object to the Ajax call. Here is our modified controller method:
/*modified to return an object instead of string data*/
[AcceptVerbsAttribute(HttpVerbs.Post)]
public JsonResult GetDetailsUsingHttpPost(int id)
{
    Team team = teamRepository.Find(id);
    TeamViewModel teamVM = new TeamViewModel();
    teamVM.TeamId = team.TeamId;
    teamVM.Name = team.Name;
    return Json(teamVM);

    // we could also have used an anonymous type instead
    //var anonymousTeam = new { TeamId = team.TeamId, Name = team.Name, City = "sydney" };
    //return Json(anonymousTeam);
}
We need to change the js function that is called when the Ajax call is successful:
function AjaxSucceeded(objdata) {
    alert('success');
    alert(objdata.TeamId + ' - ' + objdata.Name);
    $('#ajaxDiv').html(objdata.TeamId + ' - ' + objdata.Name);
}
Notice that the received object has similar structure as the returned object's type from the controller method; ie. it has 'TeamId' and 'Name' members. It also preserve the case sensitivity of the properties; if we use small case letters 'objdata.teamid' instead of 'objdata.TeamId', this will be rendered as 'undefined'.

On the next post, we will see how to pass object(s) in the Ajax function.

Friday, 11 November 2011

More Advanced Use of AutoMapper - Part 2

Custom type converters
// Source and destination classes
public class Source
{
    public string Value1 { get; set; }
    public string Value2 { get; set; }
    public string Value3 { get; set; }
}
public class Destination
{
    public int Value1 { get; set; }
    public DateTime Value2 { get; set; }
    public Boolean Value3 { get; set; }
}


// Custom type converter classes
  //custom type converter class uses 'ITypeConverter' interface and has 'Convert' method
public class DateTimeTypeConverter : ITypeConverter<string, DateTime>
{
    public DateTime Convert(ResolutionContext context)
    {
        return System.Convert.ToDateTime(context.SourceValue);
    }
}
public class TypeTypeConverter : ITypeConverter<string, Boolean>
{
    public Boolean Convert(ResolutionContext context)
    {
        return System.Convert.ToBoolean(context.SourceValue);
    }
}


// Specify mappings
  //using .NET built in function
Mapper.CreateMap<string, int>().ConvertUsing(Convert.ToInt32);

  //using custom type converter classes
Mapper.CreateMap<string, DateTime>().ConvertUsing(new DateTimeTypeConverter());
Mapper.CreateMap<string, Boolean>().ConvertUsing<TypeTypeConverter>();

Mapper.CreateMap<Source, Destination>();


// Usage
var source = new Source
{
    Value1 = "5",
    Value2 = "01/01/2000",
    Value3 = "true"
};
Destination result = Mapper.Map<Source, Destination>(source);
With custom type converter, the mapping is applied automatically by AutoMapper when any source and destination types match. This mapping has a global scope.



Custom value resolvers
// Source and destination classes
public class SourceRsolvr
{
    public int Value1 { get; set; }
    public int Value2 { get; set; }
}
public class DestinationRsolvr
{
    public int Total { get; set; }
}


// Custom resolver classes
  //custom resolver class derives from 'ValueResolver' and overrides 'ResolveCore' method, we can also create a
  //custom resolver class that derives from 'IValueResolver' but this is rarely used

  //a custom resolver without argument
public class CustomResolverOne : ValueResolver<SourceRsolvr, int>
{
    protected override int ResolveCore(SourceRsolvr source)
    {
        return source.Value1 + source.Value2;
    }
}

  //a custom resolver with argument
public class CustomResolverTwo : ValueResolver<SourceRsolvr, int>
{
    private readonly Expression<Func<int, bool>> _func;

    public CustomResolverTwo(Expression<Func<int, bool>> func)
    {
        _func = func;
    }

    protected override int ResolveCore(SourceRsolvr source)
    {
        var list = new[] { source.Value1, source.Value2};
        return list.Where(_func.Compile()).Sum();
    }
}


// Mapping
  // using custom resolver without argument
Mapper.CreateMap<SourceRsolvr, DestinationRsolvr>()
        .ForMember(dest => dest.Total, 
                   opt => opt.ResolveUsing<CustomResolverOne>()
                             .ConstructedBy(() => new CustomResolverOne()));

  // another example of using custom resolver with argument
//Mapper.CreateMap<SourceRsolvr, DestinationRsolvr>()
//        .ForMember(dest => dest.Total, 
//                   opt => opt.ResolveUsing<CustomResolverTwo>()
//                             .ConstructedBy(() => new CustomResolverTwo(x => x > 5)));


// Usage
var source = new SourceRsolvr
{
    Value1 = 5,
    Value2 = 7
};
var result = Mapper.Map<SourceRsolvr, DestinationRsolvr>(source);
Unlike custom type converter, custom value resolver needs to be specified in the configuration of any destination class' member that would like to apply it. Thus it will only be implemented to specific members that are configured for it.

Reference:
https://github.com/AutoMapper/AutoMapper/wiki

Friday, 4 November 2011

More Advanced Use of AutoMapper - Part 1

Projection
public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

public class PersonViewModel
{
    public string FullName { get; set; }
    public int DayOfBirth { get; set; }
    public int MonthOfBirth { get; set; }
    public int YearOfBirth { get; set; }
    public string Email { get; set; }
}


Mapper.CreateMap<Person, PersonViewModel>()
    .ForMember(d => d.DayOfBirth, o => o.MapFrom(s => s.DateOfBirth.Day))
    .ForMember(d => d.MonthOfBirth, o => o.MapFrom(s => s.DateOfBirth.Month))
    .ForMember(d => d.YearOfBirth, o => o.MapFrom(s => s.DateOfBirth.Year))
    .ForMember(d => d.FullName, o => o.MapFrom(s => s.FirstName + " " + s.LastName))
    .ForMember(d => d.Email, o => o.Ignore());
AutoMapper needs to know how to map to ALL of the destination class members. It is fine if some source class' members do not have any match. In this case the source class Person has a PersonId member, however it is not used in the destination class. No mapping configuration is required, AutoMapper will automatically ignore it. However for the destination class member Email which do not have a match in the source class, it is necessary to specify a mapping configuration.


Collections
After a map is created, we can map a collection of source class' objects into a collection of destination class' objects without extra configuration.
var sources = new[]
    {
        new Person{PersonId =10, DateOfBirth=new DateTime(1910,10,1), FirstName="Johnny", LastName="King"},
        new Person{PersonId =11, DateOfBirth=new DateTime(1911,11,2), FirstName="Katherine", LastName="Wood"},
        new Person{PersonId =12, DateOfBirth=new DateTime(1912,12,3), FirstName="Sam", LastName="Bourke"}
    };
List<PersonViewModel> listPersonViewModels = Mapper.Map<Person[], List<PersonViewModel>>(sources);
Collection types supported are: IEnumerable, IEnumerable<T>, ICollection, ICollection<T>, IList, IList<T>, List<T> and Arrays.


Nested mappings
No mapping configuration is needed to map a nested class. As long as all of the destination class' members have matches, we just need to specify one more mapping for each nested class.
public class Outer
{
    public int Value { get; set; }
    public Nested InnerClass { get; set; }
}
public class Nested
{
    public int InnerValue { get; set; }
}

public class OuterViewModel
{
    public int Value { get; set; }
    public NestedViewModel InnerClass { get; set; }
}
public class NestedViewModel
{
    public int InnerValue { get; set; }
}


Mapper.CreateMap<Outer, OuterViewModel>();
// need to specify mapping for the nested class as well
Mapper.CreateMap<Nested, NestedViewModel>();


// usage
var source = new Outer
{
    Value = 5,
    InnerClass = new Nested { InnerValue = 15 }
};
var dest = Mapper.Map<Outer, OuterViewModel>(source);

Reference:
https://github.com/AutoMapper/AutoMapper/wiki

Tuesday, 25 October 2011

Using View Model with AutoMapper in MVC

Tools used when writing this post: MVC3, MvcScaffolding 1.0.0, EntityFramework 4.1.10331.0 and AutoMapper 2.0.0.

In this post, we will see a simple example of how to map a view model to a model class with AutoMapper. To understand more about view model, you may want to read http://rachelappel.com/use-viewmodels-to-manage-data-amp-organize-code-in-asp.net-mvc-applications. For more information about AutoMapper, see "AutoMapper - Getting Started".

Say we have a model class called Arena that is used by a database context class for Entity Framework. Then we have a view model for this class called ArenaViewModel that is created to handle slightly different form validation requirements from the original model class. Below are the model class and its view model:
public class Arena
{
    public int ArenaId { get; set; }
        
    [StringLength(150)]
    public string Name { get; set; }

    [StringLength(350)]
    public string Location { get; set; }
        
    public int NumberOfSeats { get; set; }
}

public class ArenaViewModel
{
    public int ArenaId { get; set; }

    //input requirement: field is required and can only have maximum 10 characters length 
    [Required]
    [StringLength(10)]
    public string Name { get; set; }

    //input requirement: field is required and can only have maximum 10 characters length
    [Required]
    [StringLength(10)]
    public string Location { get; set; }
                
    //input requirement: allow empty value (as this will be translated to 0 when saving to database)
    public int? NumberOfSeats { get; set; }
}

Then to set up AutoMapper, first copy and paste the class below to our project. Specify the mapping profile with a correct profile class name that we are going to create (line 6).
public class AutoMapperConfiguration
{
    public static void Configure()
    {
        // specify the mapping profile
        Mapper.Initialize(x => x.AddProfile<ViewModelProfile>());

        // Put this in unit testing later!
        // verify mappings
        Mapper.AssertConfigurationIsValid();
    }
}

Next, create the profile class. A profile class is used to centralized many mapping configurations in one place. A different profile can have different mapping configurations and formatting rules of similar entities in the other profile.
public class ViewModelProfile : Profile
{
    public override string ProfileName
    {
        get { return "ViewModel"; }
    }

    protected override void Configure()
    {
        // specify all mapping configurations here

        CreateMap<ArenaViewModel, Arena>();
    }
}
Note that this class is derived from Profile base class.

Then call
AutoMapperConfiguration.Configure();
from Application_Start() method on Global.asax.cs.

Say we already have a view called AddArenaInfo.cshtml that uses ArenaViewModel to add a new arena information:
@model MvcScaffoldTest.ViewModels.ArenaViewModel
@using (Html.BeginForm())
{
    @Html.ValidationSummary(true)
    <div class="editor-label">
        @Html.LabelFor(model => model.Name)
    </div>
    <div class="editor-field">
        @Html.EditorFor(model => model.Name)
        @Html.ValidationMessageFor(model => model.Name)
    </div>

    <div class="editor-label">
        @Html.LabelFor(model => model.Location)
    </div>
    <div class="editor-field">
        @Html.EditorFor(model => model.Location)
        @Html.ValidationMessageFor(model => model.Location)
    </div>

    <div class="editor-label">
        @Html.LabelFor(model => model.NumberOfSeats)
    </div>
    <div class="editor-field">
        @Html.EditorFor(model => model.NumberOfSeats)
        @Html.ValidationMessageFor(model => model.NumberOfSeats)
    </div>

    <p>
        <input type="submit" value="Create" />
    </p>
}

Then we would be able to do this on our controller:
[HttpPost]
public ActionResult AddArenaInfo(ArenaViewModel arenaVwMdl)
{
    if (ModelState.IsValid)
    {
        //map source to destination
        var arenaMdl = Mapper.Map<ArenaViewModel, Arena>(arenaVwMdl);
        context.Arenas.Add(arenaMdl);
        context.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(arenaVwMdl);
}
AutoMapper will map those two objects automatically. We don't need to specify extra mapping configurations in this case because both of the classes have similar properties that can be easily matched by AutoMapper.

References:
http://mhinze.com/2009/07/06/automapper-in-nerddinner/
http://elegantcode.com/2009/10/06/automapper-introduction-and-samples-from-netdug/

Friday, 21 October 2011

A VBScript Example to Copy Files to User Temp Folder

Below is an example of VBScript codes to copy files from current folder to '[User Profile]\Local Settings\Temp' folder then execute an executable file under the folder:
Set objShell = WScript.CreateObject("WScript.Shell")
currentFolder = objShell.CurrentDirectory

Dim filesys
Set filesys = CreateObject("Scripting.FileSystemObject")

Set appShell = CreateObject("Shell.Application")


'Set the destination folder to be checked/created

'these commented codes would use a folder under 'Program Files'
'Const PROGRAM_FILES = &H26&
'Set objFolder = appShell.Namespace(PROGRAM_FILES)
'Set objFolderItem = objFolder.Self
'Wscript.Echo objFolderItem.Path    'testing
'destinationFolderPath = objFolderItem.Path & "\MyApp\Install" 

'using '[User Profile]\Local Settings\Temp' folder, this value can be retrieved by using
'   filesys.GetSpecialFolder(2)     or      objShell.ExpandEnvironmentStrings("%temp%")
'testing - Wscript.Echo filesys.GetSpecialFolder(2)   ' or objShell.ExpandEnvironmentStrings("%temp%")
destinationFolderPath = objShell.ExpandEnvironmentStrings("%temp%") & "\MyApp.TMP"


'Check if folder exists
Dim newfolder
If Not filesys.FolderExists(destinationFolderPath) Then
    'if not then create one
    Set newfolder = filesys.CreateFolder(destinationFolderPath)
Else
    'clear all existing files under the destination folder
    filesys.DeleteFile(destinationFolderPath & "\*"), true  ' true - delete read only files
End If


'Copy all files under current folder to the destination folder
filesys.CopyFile currentFolder & "\*.*", destinationFolderPath & "\", true  ' true - overwrite existing


'Run installer
objShell.Run ("""" & destinationFolderPath & "\setup.exe" & """")
'objShell.Run ("""" & destinationFolderPath & "\setup.exe" & """"), 0, true     ' 0 - Hides the window and activates another window.
                                                                                ' true - the script should wait for the program to finish executing before continuing to the next statement


'Finalise
set appShell = Nothing
set filesys = Nothing
set objShell = Nothing
Wscript.Quit

Some Notes about Windows Installer, IExpress and VBScript


Choosing a deployment strategy for Visual Studio 2010
http://msdn.microsoft.com/en-us/library/e2444w33%28VS.100%29.aspx


ClickOnce
ClickOnce is a deployment technology that enables you to create self-updating Windows-based applications that can be installed and run with minimal user interaction. ClickOnce deployment overcomes three major issues in deployment:

- Difficulties in updating applications. With Microsoft Windows Installer deployment, whenever an application is updated, the user must reinstall the whole application; with ClickOnce deployment, you can provide updates automatically. Only those parts of the application that have changed are downloaded, and then the full, updated application is reinstalled from a new side-by-side folder.

- Impact to the user's computer. With Windows Installer deployment, applications often rely on shared components, with the potential for versioning conflicts; with ClickOnce deployment, each application is self-contained and cannot interfere with other applications.

- Security permissions. Windows Installer deployment requires administrative permissions and allows only limited user installation; ClickOnce deployment enables non-administrative users to install and grants only those Code Access Security permissions necessary for the application.
[http://msdn.microsoft.com/en-us/library/142dbbz4.aspx]


Walkthrough of creating an installer using Windows Setup Project
http://www.codeproject.com/KB/dotnet/Win_App_Setup_Project.aspx


Product Code and Package Code
If the new MSI file has the same ProductCode and PackageCode as a product that’s already installed, Windows indicates that you must repair or remove the product.

If the new MSI file has the same ProductCode as an installed product but a different PackageCode, you’ll receive a message indicating that another version of the product is already installed.

Repair does not use your new MSI file to repair the product, nor does it update what you previously installed. Instead, it repairs the existing installed product. That is, it behaves as if you went to the original MSI file used to install the existing product, selected the context menu, and chose repair. (Note: Repair can also be initiated from Add/Remove programs.)
[http://www.simple-talk.com/dotnet/visual-studio/updates-to-setup-projects/]


Creating bootstrapper packages
http://msdn.microsoft.com/en-us/library/ms165429.aspx


The difference between MSI and EXE file
http://www.symantec.com/connect/articles/understanding-difference-between-exe-and-msi


DISABLEADVTSHORTCUTS property of Setup.EXE
Setting the DISABLEADVTSHORTCUTS property disables the generation of shortcuts supporting installation-on-demand and advertisement. Setting this property specifies that these shortcuts should instead be replaced by regular shortcuts.
http://msdn.microsoft.com/en-us/library/windows/desktop/aa368297%28v=vs.85%29.aspx


Installation-on-demand and advertisement
With traditional installation technology, it is necessary to exit an application and rerun setup to perform an installation task. This commonly occurred when a user wanted a feature or product not chosen during the first run of setup. This often made the process of product configuration inefficient because it required the user to anticipate the functionality required before they ever used the product.

Installation-on-demand makes it possible to offer functionality to users and applications in the absence of the files themselves. This notion is known as advertisement. The Windows Installer has the capability of advertising functionality and to make installation-on-demand of application features or entire products possible. When a user or application activates an advertised feature or product, the installer proceeds with installation of the needed components. This shortens the configuration process because additional functionality can be accessed without having to exit and rerun another setup procedure.
[http://msdn.microsoft.com/en-us/library/windows/desktop/aa369293%28v=vs.85%29.aspx]


IExpress – How to use
http://home.wanadoo.nl/kixtart/download/IExpress.pdf

When IExpress extracts the files to a temporary directory and runs an installation command it watches the command it ran. When this process is finished, IExpress removes its temporary files from the drive.
[http://www.picturestoexe.com/forums/index.php?/topic/1983-maybe-you-didnt-know/]
http://www.mdgx.com/INF_web/iexpress.htm


SED file for IExpress - Overview
http://www.mdgx.com/INF_web/cdfinfo.htm


Running VBScript file from IExpress
http://markalexanderbain.suite101.com/distributing-a-vbscript-application-a90365


How to install shortcuts using VBScript:
http://ss64.com/nt/shortcut.html
http://www.vbforums.com/showthread.php?t=234891
http://www.appdeploy.com/messageboards/tm.asp?m=26758&mpage=1&key=梆

How to install application using VBScript:
http://www.symantec.com/connect/blogs/installing-application-using-vbscript
http://www.symantec.com/connect/downloads/vbscript-install-application-exit-code
http://msdn.microsoft.com/en-us/library/d5fk67ky%28v=vs.85%29.aspx
http://technet.microsoft.com/en-us/library/ee692649.aspx

VBScript examples for file management:
http://activexperts.com/activmonitor/windowsmanagement/adminscripts/filesfolders/files/
http://technet.microsoft.com/en-us/library/ee176983.aspx
http://www.computerperformance.co.uk/ezine/ezine139.htm

VBScript examples for special folders
http://www.activexperts.com/activmonitor/windowsmanagement/scripts/desktop/specialfolders/

Tuesday, 18 October 2011

Dependency Injection for Filters with Ninject in MVC3 - Backward Compatibility

Tools used when writing this article: MvcScaffolding ver 1.0.0, Ninject ver 2.2.1.4 and Ninject.MVC3 ver 2.2.2.0.

This post will describe the third way to implement dependency injection for filters in MVC3.

For the preferred way to implement filter on actions or controllers using 'BindFilter' method, please see my previous post. The second way is also described on my other post.

In this post, we will see how to implement DI using 'Inject' attribute. This way of implementing is only meant for backward compatibility for MVC prior to version 3. For more information you can see https://github.com/ninject/ninject.web.mvc/wiki/Dependency-injection-for-filters and https://github.com/ninject/ninject.web.mvc/wiki/Injection-of-filter-attributes.

Setting up the DI for filter using this way is simpler, however we could not have a centralized location to specify where the filter applies. The reason is that we need to put the filter attribute on every controller(s) or action(s) where we want the filter to apply. So, if the filter is applied on lots of controllers or actions they could be harder to track.

First we prepare our filter class with a public service property that will be dependency injected. This property access modifier must be 'public', otherwise the DI will not work because the DI is going to be done through property injection. Then put '[Inject]' attribute notation on the property. When this notation is used, Ninject will automatically apply the injection to the property. Next, bind the service interface with its implementation class inside 'RegisterServices' method on 'NinjectMVC3.cs' file. Finally we can put the '[filterattribute]' notation on controller(s) or action(s) where we want the filter to apply.

Below are the complete codes:

Service interface and its implementation class:
public interface IGreetingService
{
    string GetGreeting();
}

public class GreetingServiceImplThree : IGreetingService
{
    public string GetGreeting()
    {
        return "Greeting - Backward Compatibility";
    }
}

Filter class:
public class GreetingFilterBackwardCompAttribute: ActionFilterAttribute
{
    [Inject]
    public IGreetingService _service { get; set; }

    public override void OnActionExecuted(ActionExecutedContext filterContext)
    {
        ViewResult result = filterContext.Result as ViewResult;
        if (result != null)
        {
            result.ViewData["Greeting"] += _service.GetGreeting();
        }
    }
}

'RegisterServices' method on 'NinjectMVC3.cs':
private static void RegisterServices(IKernel kernel)
{
    . . .
    //bind the service interface with an implementation class
    kernel.Bind<IGreetingService>().To<GreetingServiceImplThree>();
    . . .
}   
Note here we just need to bind the service interface only, not the filter.

Usage:
[GreetingFilterBackwardComp]
public ViewResult Index()
{
    . . .
} 
To test, render 'ViewData["Greeting"]' somewhere on the view or shared view.

Monday, 10 October 2011

Dependency Injection for Filters with Ninject in MVC3 - Using Filter Attribute

Tools used when writing this article: MvcScaffolding ver 1.0.0, Ninject ver 2.2.1.4 and Ninject.MVC3 ver 2.2.2.0.

This post will describe the second way to implement dependency injection for filters in MVC3.

For the preferred way to implement filter on actions or controllers using 'BindFilter' method, please see my previous post.

In this post, we will see that we can also implement dependency injection with Filter Attributes. The steps to do this are almost similar to the steps to implement using the 'BindFilter' method (described on my previous post referred above). Except now a new attribute class that derived from 'FilterAttribute' needs to be created. Then we need to modify our filter binding in the 'RegisterServices' method to use extension methods such as 'WhenActionHas', 'WhenControllerHas' or 'WhenActionMethodHas' to refer to the attribute class. After that, the filter can be applied to a specific action or controller by putting '[filterattribute]' notation. We'll see this in more details below.

Based on our previous example, we need to add an attribute class that derived from 'FilterAttribute' base class. Nothing change on the main filter class (ie. GreetingFilter class). Below is the code for the attribute class:
public class GreetingFilterAttribute : FilterAttribute { }
Note that it is just an empty class without implementation.

Next, in our 'RegisterServices' method, we need to bind the filter attribute using extension methods such as 'WhenActionHas', 'WhenControllerHas' or 'WhenActionMethodHas'. For example:
kernel.BindFilter<GreetingFilter>(FilterScope.Action, 0)
.WhenActionMethodHas<GreetingFilterAttribute>();
The main difference in implementing DI this way compared to the first way that is described on my previous post is that inside this 'RegisterServices' method, we just need to tell the kernel to look for a filter attribute. The details of the location(s) where the filter applies is specified using '[filterattribute]' notation (on some controllers or actions). While on the first way, the specific location(s) where the filter applies is specified in the binding as well.

Then lastly, we put '[GreetingFilter]' notation on particular action(s) or controller(s) where we want the filter to apply.

Below are the complete codes:

Service interface and its implementation class:
public interface IGreetingService
{
    string GetGreeting();
}

public class GreetingServiceImplTwo : IGreetingService
{
    public string GetGreeting()
    {
        return "Greeting - Using Filter Attribute";
    }
}

GreetingFilter and it's attribute class:
public class GreetingFilter : IActionFilter
{
    private readonly IGreetingService _service;

    public GreetingFilter(IGreetingService greetingService)
    {
        this._service = greetingService;
    }

    public void OnActionExecuting(ActionExecutingContext filterContext)
    {
        //at the moment this is empty because we don't need this method
    }

    public void OnActionExecuted(ActionExecutedContext filterContext)
    {
        ViewResult result = filterContext.Result as ViewResult;
        if (result != null)
        {
            result.ViewData["Greeting"] += _service.GetGreeting();
        }
    }
}

public class GreetingFilterAttribute : FilterAttribute { }

'RegisterServices' method on 'NinjectMVC3.cs':
private static void RegisterServices(IKernel kernel)
{
    . . .
    //bind the service interface with an implementation class
    kernel.Bind<IGreetingService>().To<GreetingServiceImplTwo>();

    //bind the filter attribute
    kernel.BindFilter<GreetingFilter>(FilterScope.Action, 0)
        .WhenActionMethodHas<GreetingFilterAttribute>();
    . . .
}   

Usage:
[GreetingFilter]
public ActionResult Create()
{
    . . .
} 
To test, render 'ViewData["Greeting"]' somewhere on the view or shared view.

Sunday, 2 October 2011

Dependency Injection for Filters with Ninject in MVC3

Tools used when writing this article: MvcScaffolding ver 1.0.0, Ninject ver 2.2.1.4 and Ninject.MVC3 ver 2.2.2.0.

There are three ways (as far as I know) to do dependency injection for filters in MVC3 using Ninject.

On this post, we'll see the preferred way first. The second way which uses filter attribute is described on my next post. While the third one which should only be used for backward compatibility can be seen on my other post.

First, we create a filter class that inherits from one of these four interfaces; IAuthorizationFilter, IActionFilter, IResultFilter or IExceptionFilter. Each interface requires us to implement particular methods defined for it. For example when we use IActionFilter, we need to implement OnActionExecuting and OnActionExecuted methods. For more information about this, see this MSDN article "Filtering in ASP.NET MVC".

Then bind the filter in the 'RegisterService' method inside 'NinjectMVC3.cs' file using 'BindFilter' method. Here we specify where the filter will apply (which controller or action or a combination of both). I'm assuming that you have used NuGet to implement Ninject in your project. That way, you will have 'NinjectMVC3.cs' file generated for you.

If you haven't implemented Ninject on your project, you can see my post "Building MVC Application using Dependency Injection with MVCScaffolding and Ninject" on how to install it.

Let's go through an example. In this example, we are going to implement a simple action filter that returns a greeting message through ViewData.
First, we create a simple service interface and an implementation class.
public interface IGreetingService
{
    string GetGreeting();
}

public class GreetingServiceImplOne : IGreetingService
{
    public string GetGreeting()
    {
        return "Greeting..";
    }
}

Then we create our filter class. Because it is an action filter, it will use IActionFilter to be inherited from.
public class GreetingFilter : IActionFilter
{
    private readonly IGreetingService _service;

    public GreetingFilter(IGreetingService greetingService)
    {
        this._service = greetingService;
    }

    public void OnActionExecuting(ActionExecutingContext filterContext)
    {
        //at the moment this is empty because we don't need this method
    }

    public void OnActionExecuted(ActionExecutedContext filterContext)
    {
        ViewResult result = filterContext.Result as ViewResult;
        if (result != null)
        {
            result.ViewData["Greeting"] += _service.GetGreeting();
        }
    }
}
Notice the class includes a private service member and a constructor to inject a service object to itself.

Lastly, we need to bind the filter inside 'RegisterServices' method in 'NinjectMVC3.cs' class. In this case we state that the filter would only apply to the Index action of PlayersController.
private static void RegisterServices(IKernel kernel)
{
    kernel.Bind<IGreetingService>().To<GreetingServiceImplOne>();

    //filter is applied to Index action of PlayersController
    kernel.BindFilter<GreetingFilter>(FilterScope.Action, 0)
        .When((controllerContext, actionDescriptor) =>
            actionDescriptor.ControllerDescriptor.ControllerType == typeof(PlayersController) &&
            actionDescriptor.ActionName == "Index");
            
    //this one here is similar as the one above            
    /*kernel.BindFilter<GreetingFilter>(FilterScope.Action, 0)
        .When((controllerContext, actionDescriptor) => 
            actionDescriptor.ControllerDescriptor.ControllerName == "Players" &&
            actionDescriptor.ActionName == "Index");*/

    //this is another example where the filter to be applied to all actions of MatchesController
    //kernel.BindFilter<GreetingFilter>(FilterScope.Action, 0).WhenControllerType<MatchesController>();
}        
Notice that, first we bind the service interface with its implementation class. Then we configure the filter using 'BindFilter' method to particular controller(s) or action(s). The commented codes show some more configuration examples.

To see more possible filter configuration see these official Ninject MVC documentation for filters; https://github.com/ninject/ninject.web.mvc/wiki/Filter-configurations and https://github.com/ninject/ninject.web.mvc/wiki/Conditional-bindings-for-filters

To test our work, just try to render our ViewData on some view pages. In my case I use a shared page for all of my views which is '_Layout.cshtml' (under 'Views/Shared' folder), so I just need to render the ViewData there and test the result.

Easy..

Friday, 16 September 2011

Building MVC Application using Dependency Injection with MVCScaffolding and Ninject

Tools used when writing this article: MvcScaffolding ver 1.0.0, EntityFramework ver 4.1.10331.0, Ninject ver 2.2.1.4 and Ninject.MVC3 ver 2.2.2.0.

We can build an MVC application with Dependency Injection on its controllers quickly with MVCScaffolding and Ninject extension for MVC3 framework (Ninject.MVC3). In fact, when we install Ninject.MVC3, we will get both Ninject and Ninject.MVC3 packages. We would also need Entity Framework installed in our project to complete this exercise.

To check all installed packages in the project with NuGet:
PM> Get-Package

Assuming we already have MvcScaffolding and Entity Framework installed (installing MvcScaffolding will automatically add EF to the project if it not exists yet), what's missing is Ninject.MVC3.
To install Ninject.MVC3, do this:
PM> Install-Package Ninject.MVC3
If successful, it will add two packages to the project (Ninject and Ninject.MVC3).
It will also add 'NinjectMVC3.cs' class under 'App_Start' folder.

Prepare our model class, in this case I use a model called 'Team'. Then run the scaffolder:
PM> Scaffold Controller Team -Repository
This will add a database context file (if not exist yet), a cs file 'Models\TeamRepository.cs', a controller 'Controllers\TeamsController.cs' and some CRUD views.

Here is the generated content of 'TeamRepository.cs' file (it includes 'ITeamRepository' interface and 'TeamRepository' class on the same file):
public class TeamRepository : ITeamRepository
    {
        MvcScaffoldTestContext context = new MvcScaffoldTestContext();

        public IQueryable<Team> All
        {
            get { return context.Teams; }
        }

        public IQueryable<Team> AllIncluding(params Expression<Func<Team, object>>[] includeProperties)
        {
            IQueryable<Team> query = context.Teams;
            foreach (var includeProperty in includeProperties) {
                query = query.Include(includeProperty);
            }
            return query;
        }

        public Team Find(int id)
        {
            return context.Teams.Find(id);
        }

        public void InsertOrUpdate(Team team)
        {
            if (team.TeamId == default(int)) {
                // New entity
                context.Teams.Add(team);
            } else {
                // Existing entity
                context.Entry(team).State = EntityState.Modified;
            }
        }

        public void Delete(int id)
        {
            var team = context.Teams.Find(id);
            context.Teams.Remove(team);
        }

        public void Save()
        {
            context.SaveChanges();
        }
    }

    public interface ITeamRepository
    {
        IQueryable<Team> All { get; }
        IQueryable<Team> AllIncluding(params Expression<Func<Team, object>>[] includeProperties);
        Team Find(int id);
        void InsertOrUpdate(Team team);
        void Delete(int id);
        void Save();
    }

and part of 'TeamController.cs' class:
  private readonly ITeamRepository teamRepository;

  // If you are using Dependency Injection, you can delete the following constructor
        public TeamsController() : this(new TeamRepository())
        {
        }

        public TeamsController(ITeamRepository teamRepository)
        {
   this.teamRepository = teamRepository;
        }

        . . .

Next, we need to uncomment this on 'TeamController.cs':
/*public TeamsController() : this(new TeamRepository())
{
}*/

Finally, bind our interface to the concrete class on 'NinjectMVC3.cs':
private static void RegisterServices(IKernel kernel)
{
    kernel.Bind<ITeamRepository>().To<TeamRepository>();
}   
This means whenever our application encounters 'ITeamRepository' interface, it will refer to/use 'TeamRepository' class.

That's all that we need to do!

If you missed the last step, you will get this error:

Friday, 2 September 2011

Entity Framework Code First - a Model that has Multiple Members Referring to another Single Model

Says we want to create a ‘Match’ model that will have two ‘Teams’.

The first thing that we need to do is to add two properties on the ‘Match’ class. In this example, we name them ‘HostTeamId’ and ‘GuestTeamId’ (line 9 & 12).

Then we need to add two virtual members to the class which are ‘HostTeam’ and ‘GuestTeam’ (line 15 & 18). I like to specify the ‘ForeignKey’ attribute explicitly to make it more readable even tough it may work if I omit those (not sure, I haven’t tried on this multiple references case).
public class Match
    {
        public int MatchId { get; set; }

        [Required]
        public string Name { get; set; }

        [Required]
        public int HostTeamId { get; set; }

        [Required]
        public int GuestTeamId { get; set; }

        [ForeignKey("HostTeamId")]
        public virtual Team HostTeam { get; set; }

        [ForeignKey("GuestTeamId")]
        public virtual Team GuestTeam { get; set; }

    }

Then we also need to add virtual properties on the ‘Team’ class to indicate relationship with the ‘Match’ class.
public class Team
    {
        public int TeamId { get; set; }

        [Required]
        public string Name { get; set; }

        public string City { get; set; }

        public DateTime Founded { get; set; }

        public virtual ICollection<Player> Players { get; set; }
        public virtual ICollection<Match> HomeMatches { get; set; }
        public virtual ICollection<Match> AwayMatches { get; set; }
    }

Finally we need to implement the override method ‘OnModelCreating’ on our data context class. We need to specify both two referring members of ‘Match’ class ('HostTeam' and 'GuestTeam') with correct relationship to 'Team' class. In this case I use 'WithMany' and 'HasForeignKey' methods.
. . .
        public DbSet<MvcScaffoldTest.Models.Team> Teams { get; set; }

        public DbSet<MvcScaffoldTest.Models.Match> Matches { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Match>()
                    .HasRequired(m => m.HostTeam)
                    .WithMany(t => t.HomeMatches)
                    .HasForeignKey(m => m.HostTeamId)
                    .WillCascadeOnDelete(false);

            modelBuilder.Entity<Match>()
                    .HasRequired(m => m.GuestTeam)
                    .WithMany(t => t.AwayMatches)
                    .HasForeignKey(m => m.GuestTeamId)
                    .WillCascadeOnDelete(false);
        }
        . . .

Friday, 26 August 2011

Isolation Levels vs Read Phenomena

Isolation level
Dirty reads
Non-repeatable reads
Phantoms
Read Uncommitted
may occur
may occur
may occur
Read Committed
-
may occur
may occur
Repeatable Read
-
-
may occur
Serializable
-
-
-

Source: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Read_phenomena

Friday, 19 August 2011

Using Table Variable Inside a Loop

Be careful when using a table variable inside a loop. If it's not cleared, the rows inside the table variable might accumulate after each iteration. It seems that in T-SQL, a variable declared inside a loop is not automatically re-initialised.
declare @counter integer = 0
while @counter < 10
begin
	declare @tableVar table (code integer)

    -- to test, comment out this line
	delete @tableVar -- need to clear otherwise previous results will accumulate
	
	insert into @tableVar values (@counter)
	
	select * from @tableVar
	
	set @counter = @counter + 1	
end

Friday, 12 August 2011

Try Catch Template

Below is an example of an SQL Try Catch template:
BEGIN TRY 

	BEGIN TRANSACTION
	
	-- put the query here	
	
	COMMIT TRANSACTION
	
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
    
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
Note that the script in the Catch block checks if @@TRANCOUNT > 0 before doing ROLLBACK TRANSACTION. This means, only do rollback when there's at least one active transaction on the current connection. In other word, if it exists at least one BEGIN TRANSACTION that has not been committed yet (by using COMMIT TRANSACTION) on the current connection.

For more information about @@TRANCOUNT: http://msdn.microsoft.com/en-us/library/ms187967.aspx