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

Friday 5 August 2011

Checking if a Column Does Not Exist on a Table

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN
	-- alter table or other query
	-- . . .
END

Friday 29 July 2011

Declared Variable has NULL as Default Value!

When we declare a variable, by default it has NULL as its default value. So, need to be careful when checking the variable's value.

-- similar result for CHAR
DECLARE @varString VARCHAR(20)	
SELECT @varString
-- result is NULL

-- similar result for DECIMAL, FLOAT and DATETIME
DECLARE @varNumber INTEGER
SELECT @varNumber
-- result is NULL

Friday 22 July 2011

Dropping a Function if Exists in Database

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'function_name') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION function_name
GO

or

IF object_id(N'function_name', N'FN') IS NOT NULL
    DROP FUNCTION function_name
GO

Friday 15 July 2011

NOT IN Clause and NULL Values

When ANSI_NULLS setting is ON, be careful when using NOT IN clause if any of the values listed for the clause has NULL value. The scripts below will explain:
create table Table1 (x integer)
insert into Table1 Values (1)
insert into Table1 Values (2)
insert into Table1 Values (3)
insert into Table1 Values (4)

create table Table2 (x integer)
insert into Table2 Values (1)
insert into Table2 Values (Null)
insert into Table2 Values (2) 
insert into Table2 Values (5)

create table Table3 (x integer)
insert into Table3 Values (Null)
insert into Table3 Values (Null)
insert into Table3 Values (Null)

-- These scripts will not return any result (it would if ANSI_NULLS is OFF):
select * from Table1
where x NOT IN (select x from Table2)

select * from Table1 where x NOT IN (select x from Table3)


-- However, these ones are fine:
select * from Table1
where x IN (select x from Table2)

select * from Table1
where x IN (select x from Table3)


-- This one is also fine:
select * from Table2
where x NOT IN (select x from Table1)
-- (returns '5', however if ANSI_NULLS is OFF this would return 'NULL' and '5')

Friday 8 July 2011

SET ANSI_NULLS ON/OFF

When SET ANSI_NULLS is ON, any kind of comparisons against a null value evaluate to UNKNOWN. This is the ISO standard. In this case, any comparison against a NULL value must use IS NULL or IS NOT NULL to return TRUE/FALSE value.

When SET ANSI_NULLS is OFF, a NULL value can be compared against another NULL value with usual comparison operator ( '=' or '<>' ).

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

-- Create table t1 and insert values.
CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO


-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns nothing
SELECT * FROM t1 
WHERE a = @varname

-- returns nothing
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a = @varname

-- returns rows (0,1)
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- Drop table t1.
DROP TABLE t1

Reference:
http://msdn.microsoft.com/en-us/library/ms188048.aspx

Friday 1 July 2011

SET QUOTED_IDENTIFIER ON/OFF

When this is set to 'ON', any string enclosed with double quotes ( “ ) is treated as a T-SQL Identifier (such as table name, procedure name or column name) and the T-SQL rules for naming identifiers will not apply to it. To define a normal string literal, enclose it with single quotes ( ' ).

When this is set to 'OFF', any string enclosed with either single quotes or double quotes will be treated as a literal.

The default behavior is 'ON' in any database.

Example:
SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SELECT "identity","order" 
FROM "select"
ORDER BY "order";
GO

Reference:
http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/

Further reference:
http://msdn.microsoft.com/en-us/library/ms174393.aspx

Friday 24 June 2011

SET and SELECT Differences

- SET is the ANSI standard for variable assignment, SELECT is not.

- SELECT can be used to assign values to more than one variable at a time. SET can only assign a value to one variable at a time.

- When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

- When assigning a variable from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (the variable will have its' previous value if it has been assigned before).

- Be careful with SET and CASE WHEN statement. When any conditions do not match, CASE WHEN will return ‘NULL’ if ELSE is not specified. Therefore the variable will have a ‘NULL’ value.
DECLARE @errorCode AS INTEGER 
SET @errorCode = 0	
SET @errorCode = CASE
				 WHEN 'A'='B'
				 THEN 1000
				 END
SELECT @errorCode
-- @errorCode will return NULL

- Always use ‘SELECT’ instead of ‘SET’ to get @@ERROR and @@ROWCOUNT
SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR

Related article:
http://vyaskn.tripod.com/differences_between_set_and_select.htm

Friday 17 June 2011

CASE WHEN Statement Examples

-- this is similar to ' SELECT * FROM Customers WHERE Country = 'France' '
SELECT * FROM Customers
WHERE CASE WHEN Country = 'France' THEN 1 END = 1

Nested CASE WHEN examples:
declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
		WHEN @test = 'debug'
		THEN	CASE WHEN Country = 'France' AND City = 'Marseille' 
				THEN 1 
				END  
		END )	

declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
			WHEN @test = 'debug' 
			THEN	CASE WHEN Country = 'France'  
					THEN	CASE
							WHEN  City = 'Marseille'
							THEN 1
							ELSE NULL
							END
					END  
			END )	
This one actually will give the same result as the previous query (2nd example on this article). Below is the result:

Using LIKE clause:
declare @test as varchar(50)
set @test = 'abcdef'
SELECT	CASE 
		WHEN @test LIKE '%bc%' THEN 1
		WHEN @test LIKE '%de%' THEN 2
		ELSE 0
		END	
The result is '1' because the first condition is the first match.

An example of further filtering the rows' cities given their countries:
SELECT * FROM CUSTOMERS
WHERE City = (	CASE WHEN Country = 'France' THEN 'Marseille'
					 WHEN Country = 'UK' THEN 'London'
					 WHEN Country = 'Spain' THEN 'Madrid'
					 END  )
ORDER BY Country
The result is:

Monday 6 June 2011

Example of Using PARTITION Clause

SELECT ContactName, Country, COUNT(*) OVER (PARTITION BY Country) FROM Customers
ORDER BY Country
This will generate the same result as this query:
SELECT ContactName, Country, 
		(SELECT COUNT(*) FROM Customers C1 WHERE C2.Country = C1.Country) 
FROM Customers C2
ORDER BY Country


Thursday 2 June 2011

RANK( ) and DENSE_RANK( ) example

SELECT Country, 
		DENSE_RANK() OVER (ORDER BY Country) AS [Dense_Rank result],
		RANK() OVER (ORDER BY Country) AS [Rank result]
FROM Customers
DENSE_RANK - value is based on the item's uniqueness, same items have same values
RANK - value is based on the starting row number for the new item, same items have same values




Monday 30 May 2011

Left Outer Join Clause with Search Condition

Putting a search condition on an 'Outer Join' clause will yield a different result compared to when it is on a 'Where' clause.

For example, using 'Left Outer Join' on 'Categories' and 'Products' tables
SELECT * FROM Categories C
LEFT JOIN Products P ON C.CategoryID = P.CategoryID AND C.CategoryName ='Seafood'
will yield a different result compared to this query
SELECT * FROM Categories C
LEFT JOIN Products P ON C.CategoryID = P.CategoryID
WHERE C.CategoryName ='Seafood'

The first one will yield a table with rows on the 'Products' table's side that do not match the condition CategoryName ='Seafood' to have 'NULL' values, while the matched rows will have their information.
While on the second query, the filter is applied further after the outer joined's result is formed.

The same thing also applies to this query:
SELECT * FROM Categories C
LEFT JOIN Products P ON C.CategoryID = P.CategoryID AND P.ProductName ='Ikura'
This will return a table with only rows on the 'Products' table's side that match ProductName ='Ikura' to have values. Other rows on the 'Products' table's side will return 'NULL' values.

However, if we use 'Inner Join' clause like this:
SELECT * FROM Categories C
JOIN Products P ON C.CategoryID = P.CategoryID AND C.CategoryName ='Seafood'
it will return the same result if the search condition was on the 'Where' clause.