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:
Filter class:
'RegisterServices' method on 'NinjectMVC3.cs':
Usage:
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.
Labels:
Dependency Injection,
MVC,
Ninject
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:
Next, in our 'RegisterServices' method, we need to bind the filter attribute using extension methods such as 'WhenActionHas', 'WhenControllerHas' or 'WhenActionMethodHas'. For example:
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:
GreetingFilter and it's attribute class:
'RegisterServices' method on 'NinjectMVC3.cs':
Usage:
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.
Labels:
Dependency Injection,
MVC,
Ninject
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.
Then we create our filter class. Because it is an action filter, it will use IActionFilter to be inherited from.
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.
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..
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..
Labels:
Dependency Injection,
MVC,
Ninject
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:
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:
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:
Here is the generated content of 'TeamRepository.cs' file (it includes 'ITeamRepository' interface and 'TeamRepository' class on the same file):
and part of 'TeamController.cs' class:
Next, we need to uncomment this on 'TeamController.cs':
Finally, bind our interface to the concrete class on 'NinjectMVC3.cs':
That's all that we need to do!
If you missed the last step, you will get this error:
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.MVC3If 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 -RepositoryThis 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:
Labels:
Dependency Injection,
MVC,
MVCScaffolding,
Ninject
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).
Then we also need to add virtual properties on the ‘Team’ class to indicate relationship with the ‘Match’ class.
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.
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);
}
. . .
Labels:
Code First,
Entity Framework
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
Labels:
SQL
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
Labels:
SQL
Friday, 12 August 2011
Try Catch Template
Below is an example of an SQL Try Catch template:
For more information about @@TRANCOUNT: http://msdn.microsoft.com/en-us/library/ms187967.aspx
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
Labels:
SQL
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
Labels:
SQL
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
Labels:
SQL
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
Labels:
SQL
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')
Labels:
SQL
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.
Reference:
http://msdn.microsoft.com/en-us/library/ms188048.aspx
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
Labels:
SQL
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:
Reference:
http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/
Further reference:
http://msdn.microsoft.com/en-us/library/ms174393.aspx
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
Labels:
SQL
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.
- Always use ‘SELECT’ instead of ‘SET’ to get @@ERROR and @@ROWCOUNT
Related article:
http://vyaskn.tripod.com/differences_between_set_and_select.htm
- 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
Labels:
SQL
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 ENDThe 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 CountryThe result is:
Labels:
SQL
Monday, 6 June 2011
Example of Using PARTITION Clause
SELECT ContactName, Country, COUNT(*) OVER (PARTITION BY Country) FROM Customers ORDER BY CountryThis 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
Labels:
SQL
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 CustomersDENSE_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
Labels:
SQL
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
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:
However, if we use 'Inner Join' clause like this:
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.
Labels:
SQL
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.
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}.
Put formally, there are two multivalued dependencies in this relation: {course}-->>{book} and equivalently {course}-->>{lecturer}.
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. |
Labels:
SQL
Subscribe to:
Posts (Atom)







