A better SQLCriterion for NHibernate

by Remco Ros 23. July 2009 15:48

I was using the NHibernate Criteria API lately, and needed some SQL like this:

WHERE root.Quantity > child.Quantity + ?

As far as I know, you cannot add calculations using the Criteria API, so you would have to use a SQLCriterion (or HQL, but i cannot use it in this case). Downside of SQLCriterion is, is that SQLCriterion uses column names, not property names.

This leads to all kinds of problems when working with associations. It is possible to add '{alias}' to your query for the root entity alias. But this isn't possible for other (child) relations. I ended up writing a better SqlCriterion, which replaces {propertyName} and {alias.PropertyName} with the correct column names.

The tests:

namespace RawSoft.NHibernateEx.Tests.Criterion
{
	using System.Reflection;
	using Model;
	using NHibernate;
	using NHibernate.Cfg;
	using NHibernate.Engine;
	using NHibernate.Impl;
	using NHibernate.Loader.Criteria;
	using NHibernateEx.Criterion;
	using NUnit.Framework;

	[TestFixture]
	public class BetterSqlCriterionFixture
	{
		protected ISessionFactory factory;

		[SetUp]
		public virtual void SetUp()
		{
			Configuration cfg = new Configuration();
			cfg = cfg.Configure("hibernate.cfg.xml");
			Assembly dm = Assembly.GetAssembly(typeof(Foo));
			cfg.AddAssembly(dm);

			factory = cfg.BuildSessionFactory();
		}

		[Test]
		public void Replaces_propertynames_with_columnnames()
		{
			using (ISession session = factory.OpenSession())
			{
				var criteria = (CriteriaImpl)session.CreateCriteria();
				var criteriaQuery = new CriteriaQueryTranslator(
					(ISessionFactoryImplementor)factory,
					criteria, criteria.EntityOrClassName, "sql_alias");

				var crit = new BetterSqlCriterion("{Quantity} > {Quantity2} + 3");

				var where = crit.ToSqlString(criteria, criteriaQuery, null).ToString();
				Assert.That(where, Is.EqualTo("sql_alias.Quantity > sql_alias.Quantity2 + 3"));
			}
		}

		[Test]
		public void Replaces_aliased_propertynames_with_columnnames()
		{
			using (ISession session = factory.OpenSession())
			{
				var criteria = (CriteriaImpl)session.CreateCriteria();
				criteria.CreateAlias("Bar", "b");
				var criteriaQuery = new CriteriaQueryTranslator(
					(ISessionFactoryImplementor)factory,
					criteria, criteria.EntityOrClassName, "sql_alias");

				var crit = new BetterSqlCriterion("{Quantity} > {b.Quantity} + 1");
                
				var where = crit.ToSqlString(criteria, criteriaQuery, null).ToString();
				Assert.That(where, Is.EqualTo("sql_alias.Quantity > b1_.Quantity + 1"));
			}
		}

		[Test]
		[ExpectedException(typeof(QueryException))]
		public void non_existing_property_throws_exception()
		{
			using (ISession session = factory.OpenSession())
			{
				var criteria = (CriteriaImpl)session.CreateCriteria();
				var criteriaQuery = new CriteriaQueryTranslator(
					(ISessionFactoryImplementor)factory,
					criteria, criteria.EntityOrClassName, "sql_alias");

				var crit = new BetterSqlCriterion("{NonExisting} > {Quantity} + 1");

				// should throw
				crit.ToSqlString(criteria, criteriaQuery, null).ToString();
			}
		}
	}
}

The better SqlCriterion:

namespace RawSoft.NHibernateEx.Criterion
{
	using System;
	using System.Collections.Generic;
	using System.Text.RegularExpressions;
	using NHibernate;
	using NHibernate.Criterion;
	using NHibernate.Engine;
	using NHibernate.SqlCommand;
	using NHibernate.Type;
	using NHibernate.Util;

	[Serializable]
	public class BetterSqlCriterion : AbstractCriterion
	{
		private string _sql;
		private readonly TypedValue[] _typedValues;

		public BetterSqlCriterion(string sql)
			: this(sql, ArrayHelper.EmptyObjectArray, ArrayHelper.EmptyTypeArray)
		{
		}

		public BetterSqlCriterion(string sql, object value, IType type)
			: this(sql, new[] { value }, new[] { type })
		{
		}

		public BetterSqlCriterion(string sql, object[] values, IType[] types)
		{
			_sql = sql;
			_typedValues = new TypedValue[values.Length];
			for (int i = 0; i < _typedValues.Length; i++)
			{
				_typedValues[i] = new TypedValue(types[i], values[i], EntityMode.Poco);
			}
		}

		/// 
		/// Render a SqlString for the expression.
		/// 
		/// 
		/// A SqlString that contains a valid Sql fragment.
		/// 
		public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary enabledFilters)
		{
			criteriaQuery.AddUsedTypedValues(GetTypedValues(criteria, criteriaQuery));

			var columns = Regex.Matches(_sql, @"\{(.*?)\}");

			foreach (Match column in columns)
			{
				var columnName = column.Groups[1].Value;

				var columnNames = CriterionUtil.GetColumnNames(columnName, null, criteriaQuery, criteria, null);
				if (columnNames.Length != 1)
				{
					throw new HibernateException(string.Format("You can only alias single-column properties: {0}", column.Value));
				}
				_sql = _sql.Replace(column.Value, columnNames[0].ToString());
			}

			return SqlString.Parse(_sql);
		}

		/// 
		/// Return typed values for all parameters in the rendered SQL fragment
		/// 
		/// 
		/// An array of TypedValues for the Expression.
		/// 
		public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
		{
			return _typedValues;
		}

		/// 
		/// Return all projections used in this criterion
		/// 
		/// 
		/// An array of IProjection used by the Expression.
		/// 
		public override IProjection[] GetProjections()
		{
			return null;
		}

		/// 
		/// Gets a string representation of the .  
		/// 
		/// 
		/// A String that shows the contents of the .
		/// 
		/// 
		/// This is not a well formed Sql fragment.  It is useful for logging what the 
		///             looks like.
		/// 
		public override string ToString()
		{
			return this._sql;
		}
	}
}

kick it on DotNetKicks.com

Tags: ,

Microsoft IIS Search Engine Optimization Toolkit screenshots

by Remco Ros 4. June 2009 14:26

This is sooooo cool:

Here some screenshots from an analyses of mobielbereikbaar.nl (click to zoom).

 

 

 

Tags: , ,

ASP.Net | IIS | SEO

Castle Windsor as component container for DotNetNuke

by Remco Ros 10. March 2009 14:48

In my previous post I discussed some ideas I have for a DotNetNuke module development framework on top of the standard DotNetNuke library. It should provide clear guidance in writing custom DNN modules using the Model View Presenter pattern and supports transparent use of NHibernate and Fluent NHibernate as an alternative to DNN’s DAL+.

One of the requirements where that it should automatically register basic services, repositories, views and presenters in a dependency container using Convention over Configuration and that the framework itself should be container independent (using ie. CommonServiceLocator).

While scanning trough DotNetNuke’s source I noticed it provides a clean abstraction for a component (DI) container: DotNetNuke.ComponentModel.IContainer and DotNetNuke.ComponentModel.AbstractContainer.

The default implementation DNN provides (SimpleContainer) is not sufficient for the things I wanted to do with it (auto registration, custom lifetime management, etc.). So instead of using CommonServiceLocator in my framework, I went with writing a custom implementation of IContainer utilizing the Castle Windsor container.

Following is an example implementation, feel free to use the code in your own project:

You should know how to change the default container DotNetNuke uses. If not, then you probably won’t need it and can continue reading just for fun ?

As you can see this is called CastleSimpleContainer. In DotNetNukeMVP a more extended implementation is provided to allow a common way for more flexible registration of components.

    1 // --------------------------------------------------------------------------------------------------------------------- 
    2 // <copyright file="CastleSimpleContainer.cs" company="RawSoft">
    3 //   Copyright (c) RawSoft.  All rights reserved.
    4 // </copyright>
    5 // <summary>
    6 //   Defines the CastleSimpleContainer type.
    7 // </summary>
    8 // ---------------------------------------------------------------------------------------------------------------------
    9 namespace DotNetNukeMVP.Castle
   10 {
   11     using System;
   12     using System.Collections;
   13     using System.Collections.Generic;
   14 
   15     using DotNetNuke.ComponentModel;
   16 
   17     using global::Castle.Core;
   18     using global::Castle.Windsor;
   19 
   20     public class CastleSimpleContainer : AbstractContainer
   21     {
   22         private readonly string containerName;
   23 
   24         private readonly IWindsorContainer container;
   25 
   26         private readonly IDictionary<string, IDictionary> componentDependencies = new Dictionary<string, IDictionary>();
   27 
   28         public CastleSimpleContainer()
   29             : this(new WindsorContainer())
   30         {
   31         }
   32 
   33         public CastleSimpleContainer(IWindsorContainer container)
   34             : this(string.Format("Container_{0}", Guid.NewGuid()), container)
   35         {
   36         }
   37 
   38         public CastleSimpleContainer(string name, IWindsorContainer container)
   39         {
   40             this.containerName = name;
   41             this.container = container;
   42         }
   43 
   44         public IWindsorContainer InnerContainer
   45         {
   46             get
   47             {
   48                 return this.container;
   49             }
   50         }
   51 
   52         public override string Name
   53         {
   54             get
   55             {
   56                 return this.containerName;
   57             }
   58         }
   59 
   60         public override void RegisterComponentSettings(string name, IDictionary dependencies)
   61         {
   62             componentDependencies[name] = dependencies;
   63         }
   64 
   65         public override object GetComponent(string name)
   66         {
   67             if (!this.container.Kernel.HasComponent(name))
   68             {
   69                 return null;
   70             }
   71 
   72             try
   73             {
   74                 return this.container.Resolve(name);
   75             }
   76             catch
   77             {
   78             }
   79 
   80             return null;
   81         }
   82 
   83         public override object GetComponent(string name, Type contractType)
   84         {
   85             if (!this.container.Kernel.HasComponent(contractType))
   86             {
   87                 return null;
   88             }
   89 
   90             try
   91             {
   92                 return this.container.Resolve(name, contractType);
   93             }
   94             catch
   95             {
   96             }
   97 
   98             return null;
   99         }
  100 
  101         public override IDictionary GetComponentSettings(string name)
  102         {
  103             if (componentDependencies.ContainsKey(name))
  104             {
  105                 return componentDependencies[name];
  106             }
  107 
  108             return null;
  109         }
  110 
  111         public override void RegisterComponent(string name, Type contractType, Type componentType, ComponentLifeStyleType lifestyle)
  112         {
  113             this.container.AddComponentLifeStyle(name, contractType, componentType, GetLifeStyleType(lifestyle));
  114         }
  115 
  116         public override void RegisterComponentInstance(string name, Type contractType, object instance)
  117         {
  118             this.container.Kernel.AddComponentInstance(name, contractType, instance);
  119         }
  120 
  121         public override object GetComponent(Type contractType)
  122         {
  123             try
  124             {
  125                 return this.container.Resolve(contractType);
  126             }
  127             catch
  128             {
  129             }
  130 
  131             return null;
  132         }
  133 
  134         private static LifestyleType GetLifeStyleType(ComponentLifeStyleType lifeStyleType)
  135         {
  136             LifestyleType scope = lifeStyleType == ComponentLifeStyleType.Singleton
  137                                         ? LifestyleType.Singleton
  138                                         : LifestyleType.Transient;
  139             return scope;
  140         }
  141     }
  142 }

Tags: , ,

DotNetNuke

DotNetNukeMVP: Model View Presenter framework for DotNetNuke (part 1)

by Remco Ros 13. February 2009 00:55

I recently dived into DotNetNuke for some clients, to integrate an E-Commerce platform and develop some custom modules. One major downside to developing modules in DotNetNuke is that you cannot Unit Test them (due to the design of ASP.Net Webforms). This, of course, can be overcome with patterns like ‘Model View Controller’ and (for WebForms more appropriate) ‘Model View Presenter’.

I used the MVP pattern before in projects that use the Composite Web Guidance from Microsoft Pattern&Practices, so I was already familiar with the do’s and don’ts. 

Btw: Phil Haack has a nice article on MVP in ASP.Net here, google if you need more information.

For my first custom DNN module I wanted to try to apply MVP with to the module. I can say it works pretty well, thanks to the transparent developing experience with DotNetNuke.

Because I will be developing a lot more DotNetNuke modules in the near future, I needed some kind of standard stack of frameworks and tools for developing these modules.

Here are some of the requirements:

  • Auto registration of all views and presenters using an DI container (using Convention over Configuration)
  • Container independent (trough CommonServiceLocator).
  • Passive views: views don’t call the presenter directly. A presenter subscribes for events on the view.
  • As less code as possible in the view. (automatic presenter instantiation. Again, using Convention over Configuration and auto registration)
  • As less code as possible to setup the framework. We really don’t want to alter DotNetNuke core code too much. Configuring and initializing should be a matter of 1 or 2 lines (in globals.asax).
  • I want to provide an alternative Data Access Layer than DotNetNuke does. There are a lot of good ORM solutions, I want one. I thought a moment about making the framework independent of this DAL implementation. But that is something I cannot do at the moment (deliver fast, code less).

    I’m going to use NHibernate. I really like it and using FluentNHibernate you can do really neat stuff: Mapping your entities in code for easy refactoring / Auto mapping using Convention over Configuration / etc.

    For NHibernate / FluentNH, I’ll let me inspire by the SharpArchitecture project. It’s a really nice base arhictecture for ASP.Net MVC applications (tough it can be used for other presentation frameworks too).
  • I also want to provide some NUnit base fixtures and helper classes to aid in easy testing your views and presenters.

Now some code:

I have a very basic ASPX file:

    1 <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="SampleModule.ascx.cs" Inherits="DotNetNukeMVP.SampleModule.SampleModule" %>
    2 What is your name? <asp:TextBox runat="server" ID="tbName"></asp:TextBox>
    3 <asp:Button runat="server" ID="btnSubmit" Text="Submit" /><br />
    4 <asp:Literal runat="server" ID="ltMessage"></asp:Literal>

And the corresponding view:

   24 public partial class SampleModule : PortalModuleView<SampleModulePresenter, ISampleModuleView>, ISampleModuleView
   25 {
   26     public event EventHandler<EventArgs> SampleButtonClick;
   27 
   28     public string Name
   29     {
   30         get { return tbName.Text; }
   31         set { tbName.Text = value; }
   32     }
   33 
   34     public string Message
   35     {
   36         get { return ltMessage.Text; }
   37         set { ltMessage.Text = value; }
   38     }
   39 
   40     protected override void OnInit(EventArgs e)
   41     {
   42         base.OnInit(e);
   43         btnSubmit.Click += new EventHandler(SampleButtonClick);
   44     }
   45 }

This view, inherits from PortalModuleView (DotNetNuke modules inherit from PortalModuleBase, that’s where the name comes from). Basically, what PortalModuleView does, is create the presenter, initialize it, setting some properties, etc.

Now the Unit test:

   32 [Test]
   33 public void Can_instantiate_view()
   34 {
   35     var view = new SampleModule();
   36 
   37     Assert.That(view, Is.InstanceOfType(typeof(ISampleModuleView)));
   38     Assert.That(view.Presenter, Is.Not.Null);
   39     Assert.That(view.Presenter.View, Is.SameAs(view));
   40 }

As you can see, the view is pretty minimalistic. It contains only the properties and events defining the view and an override of OnInit to bind the button event handler to the SampleButtonClick event.

The presenter (view.Presenter) gets automagically instantiated and initialized by PortalModuleView.

On to the presenter’s unit tests:

   45 [Test] 
   46 public void SampleButtonClick_should_shout_Hello()
   47 {
   48     // setup view and create presenter
   49     var view = Stub<ISampleModuleView>();
   50     var presenter = CreatePresenter<SampleModulePresenter, ISampleModuleView>(view);
   51 
   52     // We fill the textbox with "remco" (SampleModuleView.Name)
   53     view.Name = "remco";
   54 
   55     // Click the button!
   56     view.Raise(x => x.SampleButtonClick += null, view, EventArgs.Empty);
   57 
   58     // Assertions
   59     Assert.That(view.Message, Is.EqualTo("Hello remco"));
   60 }

(I’m using Rhino Mocks here). I think the comment are self explanatory.

Finally the presenter:

   14 public class SampleModulePresenter : Presenter<ISampleModuleView>
   15 {
   16     public override void Initialize()
   17     {
   18         base.Initialize();
   19         View.SampleButtonClick += View_SampleButtonClick;
   20     }
   21 
   22     private void View_SampleButtonClick(object sender, System.EventArgs e)
   23     {
   24         View.Message = "Hello " + View.Name;
   25     }
   26 }

In the next post I will show some more in-depth code of DotNetNukeMVP and out-line my ideas of an alternative Data Access Layer for DotNetNuke.

Tags: ,

ASP.Net | DotNetNuke | MVP

How to: change default company name in Assembly information.

by Remco Ros 12. February 2009 23:06

When creating a new project in Visual Studio, it creates a default AssemblyInfo.cs for you.

I noticed that both the default company name and copyright notice attribute contained ‘Microsoft’ as its company name (this only happens on my Dell M6400 notebook).
I got sick of changing it everytime, so I did some googling and found the following answer on eggheadcafe.

Visual Studio uses the company name you setup when installing Windows. Why the hell does it put in Microsoft then, you’ll ask… Most likely because my Dell notebook came pre-installed and I never changed it.

The following registry key contains the registered owner and company name of your Windows installation:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion

for 64bit, you also have to modify the WOW64 node:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion

Tags: , ,

Visual Studio

Note to self: read some more about Domain Driven Design

by Remco Ros 9. February 2009 20:51

I’ve read a lot about Domain Driven Design. I already use some of the concepts (Value Objects / Entities / Services / TDD / etc.), but after reading this post. I think I should read some more or even buy Eric Evans book…

edit:

I’ll use this post to gather some links for later reference:

Tags:

Domain Driven Design

Illustrator HTML form element symbols for Web page mockups and wireframes

by Remco Ros 6. February 2009 13:30

When creating mockups or wireframes for webpages, I often need HTML form elements (like textboxes, buttons, checkboxes, etc.).

I just found this website where you can download some very usefull photoshop files containing HTML form elements for Vista / XP / Mac OS X and Internet Explorer / Firefox / Safari.

I usually create storyboards in Adobe Illustrator. So I created ‘HTML Form elements.ai’. A symbols library containing the basic HTML form elements from designerstoolbox.com.

You can download the file below.

HTML Form Elements.ai (59,05 kb)

Edit: per request I added an Illustrator CS (1) PDF compatible file:

HTML Form Elements.ai (69,07 kb)

Tags: , , ,

Illustrator | User Interface Design

How to: attach a SQL Server login to a database user.

by Remco Ros 5. February 2009 20:40

I sometimes need to attach a SQL Server login to a lost database user.

This is the way to do it:

 

   1: sp_change_users_login 
   2:     @Action = 'Update_One',
   3:     @UserNamePattern = 'username', 
   4:     @LoginName = 'login name'
   5:     

 

See also: http://msdn.microsoft.com/en-us/library/ms174378.aspx

Tags: ,

SQL Server

How to: Remove users from the ASP.Net membership database

by Remco Ros 5. February 2009 15:37

Method 1, using aspnet_Users_DeleteUser:

This stored procedure takes a bitmask parameter (@TablesToDeleteFrom). Set to 15 to remove all membership data for this user (including roles, profile, etc.):

   1: DECLARE @RC int
   2: DECLARE @ApplicationName nvarchar(256)
   3: DECLARE @UserName nvarchar(256)
   4: DECLARE @TablesToDeleteFrom int
   5: DECLARE @NumTablesDeletedFrom int
   6:  
   7: SET @ApplicationName = '/'
   8: SET @UserName = 'userToDelete'
   9: SET @TablesToDeleteFrom = 15
  10:  
  11: EXECUTE @RC = [telashop].[dbo].[aspnet_Users_DeleteUser] 
  12:    @ApplicationName
  13:   ,@UserName
  14:   ,@TablesToDeleteFrom
  15:   ,@NumTablesDeletedFrom OUTPUT

 

Method 2, remove directly from tables:

   1: DECLARE @UserId uniqueidentifier
   2: SET @UserId = 'THE GUID OF THE USER HERE'
   3:  
   4: DELETE FROM aspnet_Profile WHERE UserID = @UserId
   5: DELETE FROM aspnet_UsersInRoles WHERE UserID = @UserId
   6: DELETE FROM aspnet_PersonalizationPerUser WHERE UserID = @UserId
   7: DELETE FROM dbo.aspnet_Membership WHERE UserID = @UserId
   8: DELETE FROM aspnet_users WHERE UserID = @UserId

Tags: ,

ASP.Net