Friday, 21 October 2011

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.

Thursday, 4 November 2010

Normalisation

2NF

When a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.

A table for which there are no partial functional dependencies on the primary key is typically, but not always, in 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.
Multiple candidate keys occur in the following table:

Electric Toothbrush Models
Manufacturer
Model
Model Full Name
Manufacturer Country
Forte
X-Prime
Forte X-Prime
Italy
Forte
Ultraclean
Forte Ultraclean
Italy
Dent-o-Fresh
EZbrush
Dent-o-Fresh EZBrush
USA
Kobayashi
ST-60
Kobayashi ST-60
Japan
Hoch
Toothmaster
Hoch Toothmaster
Germany
Hoch
X-Prime
Hoch X-Prime
Germany

Even if the designer has specified the primary key as {Model Full Name}, the table is not in 2NF. {Manufacturer, Model} is also a candidate key, and Manufacturer Country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two tables:

Electric Toothbrush Manufacturers
Manufacturer
Manufacturer Country
Forte
Italy
Dent-o-Fresh
USA
Kobayashi
Japan
Hoch
Germany

Electric Toothbrush Models
Manufacturer
Model
Model Full Name
Forte
X-Prime
Forte X-Prime
Forte
Ultraclean
Forte Ultraclean
Dent-o-Fresh
EZbrush
Dent-o-Fresh EZBrush
Kobayashi
ST-60
Kobayashi ST-60
Hoch
Toothmaster
Hoch Toothmaster
Hoch
X-Prime
Hoch X-Prime

 

BCNF

Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF.
Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.

A relation is in BCNF, if and only if, every determinant is a candidate key.


4NF

Multivalued Dependency
If the column headings in a relational database table are divided into three disjoint groupings X, Y, and Z, then, in the context of a particular row, we can refer to the data beneath each group of headings as x, y, and z respectively. A multivalued dependency X-->>Y signifies that if we choose any x actually occurring in the table (call this choice xc), and compile a list of all the xcyz combinations that occur in the table, we will find that xc is associated with the same y entries regardless of z.

A trivial multivalued dependency X-->>Y is one where either Y is a subset of X, or X and Y together form the whole set of attributes of the relation.

A functional dependency is a special case of multivalued dependency. In a functional dependency X-->Y, every x determines exactly one y, never more than one.


Consider this example of a database of teaching courses, the books recommended for the course, and the lecturers who will be teaching the course:

Teaching database
Course
Book
Lecturer
AHA
Silberschatz
John D
AHA
Nederpelt
William M
AHA
Silberschatz
William M
AHA
Nederpelt
John D
AHA
Silberschatz
Christian G
AHA
Nederpelt
Christian G
OSO
Silberschatz
John D
OSO
Silberschatz
William M

Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa.
Put formally, there are two multivalued dependencies in this relation: {course}-->>{book} and equivalently {course}-->>{lecturer}.


Example
For an example see the ‘Example’ section on http://en.wikipedia.org/wiki/Fourth_normal_form


My Normalisation Guide

1NF
All attributes must be atomic.
All instances of an entity must contain the same number of values.
All instances of an entity must be different from one another.
2NF
Only relevant when a composite candidate key (primary key or other candidate key) exists in the entity.
The entity must be in 1NF.
All attributes must be a fact about the entire key.
3NF
The entity must be in 2NF.
Non-key attributes cannot describe other non-key attributes.
BCNF
Only in rare cases 3NF entity does not satisfy BCNF (3NF table with two or more overlapping candidate keys may or may not be in BCNF).
Every determinant is a key.
If a table has no non-key attributes then it satisfy all previous normal forms up to BCNF.
4NF
The entity must be in BCNF.
Either every multivalued dependency X -->> Y is trivial or for every nontrivial multivalued dependency X -->> Y, X is a superkey (or in other words, no attribute can have multiple values issue in the entity).
5NF
The entity must be in 4NF.
Try to breakdown any ternary relationship into binary relationships.