erictopia.com

Building a MVC2 Template, Part 19, Finishing NHibernate

Oct 25

Eric.Net, Development Asp.Net Mvc, Fluent NHibernate, Nehemiah Project, NHibernate, Ninject, TeamCity 5 Comments

This post will be fairly short. It will also tie together the two previous posts into our solution. If I haven’t left anything out, by the end of this article your solution will compile without errors and all the specifications will pass testing. We’ll start by creating the tables to hold our data. In part 18 we created the mapping from the tables we are going to create  to our POCOs. Then we’ll need to tell Ninject which provider repository to inject whenever one is requested.

So lets get started!

Creating Our Tables

The first thing we need to do is create three tables in our Jerusalem.mdf database. We could create all three tables using Visual Studio, but we need to modify the index on the User table. So fire up SQL Server Management Studio (SSMS). If you don’t have a copy you can download the express edition from here. The express version should be similar to the screen shots below.

Inside SSMS you will need to attach to the mdf. So left click the Databases item to get the context menu.

Select Attach as shown in the image above.

After clicking the Add button (image above) you will need to navigate to the Jerusalem.mdf file (image below).

Once the database is selected click OK twice. Your MDF file should now be attached and we can run queries. If this fails, close the Nehemiah solution and try again. Click the New Query button (shown below) to open a new query window. Make sure the correct database is selected.

The first table is the User table. When you create the primary index on a table using Visual Studio (or SSMS for that matter), that index defaults to clustered. Since we are using GUIDs for our primary key and most (all?) of our lookups will be done using the UserName field. We want to change the primary index to non-clustered and make the UserName index clustered. The script below will accomplish this.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[User](
	[UserId] [uniqueidentifier] NOT NULL,
	[ApplicationName] [varchar](50) NULL,
	[Email] [varchar](255) NULL,
	[UserName] [varchar](50) NULL,
	[Password] [varchar](64) NULL,
	[PasswordQuestion] [varchar](255) NULL,
	[PasswordAnswer] [varchar](50) NULL,
	[Approved] [bit] NOT NULL CONSTRAINT [DF_User_Approved]  DEFAULT ((0)),
	[LockedOut] [bit] NOT NULL CONSTRAINT [DF_User_LockedOut]  DEFAULT ((0)),
	[Online] [bit] NOT NULL CONSTRAINT [DF_User_Online]  DEFAULT ((0)),
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL CONSTRAINT [DF_User_FailedPasswordAnswerAttemptCount]  DEFAULT ((0)),
	[FailedPasswordAnswerAttemptStartWindow] [datetime] NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL CONSTRAINT [DF_User_FailedPasswordAttemptCount]  DEFAULT ((0)),
	[FailedPasswordAttemptWindowStart] [datetime] NULL,
	[LastActivityDate] [datetime] NULL,
	[LastLockedOutDate] [datetime] NULL,
	[LastLoginDate] [datetime] NULL,
	[LastPasswordChangedDate] [datetime] NULL,
	[PasswordResetKey] [varchar](80) NULL,
	[PasswordResetRequestDate] [datetime] NULL,
	[Comment] [varchar](255) NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_User_CreationDate]  DEFAULT (getutcdate()),
 CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [CK_UserEmail] UNIQUE NONCLUSTERED
(
	[ApplicationName] ASC,
	[Email] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [CK_UserName] UNIQUE CLUSTERED
(
	[ApplicationName] ASC,
	[UserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Second table to create is the Role table. Run the script below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Role](
	[RoleId] [int] IDENTITY(1,1) NOT NULL,
	[ApplicationName] [varchar](50) NULL,
	[RoleName] [varchar](30) NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
	[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [CK_Role] UNIQUE NONCLUSTERED
(
	[ApplicationName] ASC,
	[RoleName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Our third table is the many-to-many table, UserInRole.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInRole](
	[UserId] [uniqueidentifier] NOT NULL,
	[RoleId] [int] NOT NULL,
 CONSTRAINT [PK_UserInRole] PRIMARY KEY CLUSTERED
(
	[UserId] ASC,
	[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UserInRole]  WITH CHECK ADD  CONSTRAINT [FK_UserInRole_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([RoleId])
GO
ALTER TABLE [dbo].[UserInRole] CHECK CONSTRAINT [FK_UserInRole_Role]
GO
ALTER TABLE [dbo].[UserInRole]  WITH CHECK ADD  CONSTRAINT [FK_UserInRole_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[UserInRole] CHECK CONSTRAINT [FK_UserInRole_User]

While we’re at it, lets go ahead and create the Profile table by running the script below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Profile](
	[UserId] [uniqueidentifier] NOT NULL,
	[ApplicationName] [varchar](50) NULL,
	[UserName] [varchar](50) NULL,
	[Authenticated] [bit] NOT NULL CONSTRAINT [DF_Profile_Authenticated]  DEFAULT ((0)),
	[LastActivityDate] [datetime] NULL,
	[LastUpdateDate] [datetime] NULL,
	[TimeZone] [varchar](256) NULL,
 CONSTRAINT [PK_Profile] PRIMARY KEY CLUSTERED
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Profile]  WITH CHECK ADD  CONSTRAINT [FK_Profile_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_User]

All of our provider tables have now been created in our application database. Since our specifications are being tested against a SQL server database, we don’t want to test them against our application database.  We need a test database that won’t interfere with a production web site. So add a new MDF file to the main project in the same folder as the Jerusalem.mdf file. Name this database Specs.mdf and run the three SQL scripts above on the new database. Now detach both databases.

If you were to compile the solution now (but wait, don’t do it yet), it should compile without error and all specifications will pass their tests.  However running the application would fail.  We need to tell Ninject which provider repository to use.  Since we are using the NHibernate version we also need to tell Ninject which session factory to use. The updated Global.asax.cs file is listed below.

using System.Reflection;
using System.Web.Mvc;
using System.Web.Routing;
using Ninject;
using Ninject.Modules;
using Ninject.Web.Mvc;
using Service.Logging;
using Service.Logging.NLog;
using Service.Logging.Log4Net;
using Nehemiah.Data;
using Nehemiah.Data.NHibernate;

namespace Nehemiah
{
    // Note: For instructions on enabling IIS6 or IIS7 classic mode,
    // visit http://go.microsoft.com/?LinkId=9394801
    public class MvcApplication : NinjectHttpApplication    // System.Web.HttpApplication
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                "Default", // Route name
                "{controller}/{action}/{id}", // URL with parameters
                new { controller = "Home", action = "Index", id = UrlParameter.Optional } // Parameter defaults
            );

            routes.MapRoute(
                "ErrorHandler", // Route name
                "{*path}",      // URL
                new { controller = "Error", action = "Index" }
            );

        }

        // Replaced with the method OnApplicationStarted when using Ninject
        //protected void Application_Start()
        //{
        //    AreaRegistration.RegisterAllAreas();
        //    RegisterRoutes(RouteTable.Routes);
        //}

        protected override void OnApplicationStarted()
        {
            AreaRegistration.RegisterAllAreas();
            RegisterRoutes(RouteTable.Routes);

            // Use Ninject to register all of our controllers.
            //RegisterAllControllersIn(Assembly.GetExecutingAssembly());
            NehemiahKernel.Get<ILogger>().Info("Application started");
        }

        protected override IKernel CreateKernel()
        {
            return NehemiahKernel;
        }

        IKernel NehemiahKernel
        {
            get { return new StandardKernel(new WebModule()); }
        }

        public class WebModule : NinjectModule
        {
            public override void Load()
            {
                // Todo: Put your bindings here.
                //Bind<ILogger>().To<NLogLogger>().InSingletonScope();
                Bind<ILogger>().To<Log4NetLogger>().InSingletonScope();

                // Use the NHibernate data repository
                Bind<IProviderRepository>().To<NHibernateRepository>().InRequestScope();
                Bind<IFluentSessionFactory>().To<SQL2005SessionFactory>().InRequestScope();

            }
        }

    }
}

Testing

Okay, now you can compile the solution, which should run the tests, but just once! Open the Role table in the Specs.mdf database. If there are, and there should be, records in this table, you will want to open the ProviderRepositorySpecs.cs file in the Nehemiah.Specs project and comment out several lines that add these roles to the database.

                // Add a couple of Roles to the database - Comment out after the roles are created in the database
                //Repository.Add(applicationName, new Role(applicationName, rolenameAdmin));
                //Repository.Add(applicationName, new Role(applicationName, rolenameRegistered));
                //Repository.Add(applicationName, new Role(applicationName, rolenameNoUsers));
                //Repository.Add(applicationName, new Role(applicationName, rolenameNew1));
                //Repository.Add(applicationName, new Role(applicationName, rolenameNew2));
                //Repository.Add(applicationName, new Role(applicationName, rolenameDelete));

Everything should now be complete. If all the tests passed commit your changes to version control and check the Team City build. Congratulations, you have just added NHibernate to your MVC2 template.

In the next post we’ll add the Profile Provider support to NHibernate.

Previous Articles in this Series

• Part 1, Introduction
• Part 2, Version Control
• Part 3, Automated Builds
• Part 4, BDD with MSpec
• Part 5, Writing Specs
• Part 6, Writing Specs Continued
• Part 7, Custom Web Errors
• Part 8, Adding a Custom Membership Provider
• Part 9, Adding a Custom Role Provider
• Part 10, Adding a Custom Profile Provider
• Part 11, Finishing the Custom Membership Provider
• Part 12, Finishing the Custom Role Provider
• Part 13, Finishing the Custom Profile Provider
• Part 14, Logging Services
• Part 15, Adding Ninject
• Part 16, Fun with NHibernate
• Part 17, Adding Provider Repository Specifications
• Part 18, Adding NHibernate