How to query your uCommerce database with LINQPad

I've been a massive fan of LINQPad for a few years now. I use it as a scratch pad most days; especially when I need to get my head around a snippet of code without booting up Visual Studio.

For those of you not familiar with LINQPad, it's a small, stand-alone program which allows you to run code and output the results whether they're from a database or not. It's auto completion functionality makes it a great database access program and well worth the $58 price tag.

One thing I've wanted to do for a while however is test uCommerce queries. Although I've not got around to making it so you can test the API, Søren showed me a neat way of being able to query the database in almost the same way (so you can see what NHibernate is returning).

How To Setup LINQPad to Query uCommerce Directly

1. Reference the necessary Assemblies

You will need a couple of extra assemblies to query uCommerce from LINQPad. To reference additional assemblies in LINQPad, with your blank query open, press the "F4" key to open the Query Properties dialogue which looks like this:

Start off by referencing "System.Configuration" by clicking the "Add" button and searching for it in the list of available assemblies.

Next you will need to reference the following dlls from your uCommerce installation:

• Castle.Core.dll
• FluentNHibernate.dll
• Iesi.Collections.dll
• NHibernate.ByteCode.Castle.dll
• NHibernate.Caches.SysCache2.dll
• NHibernate.dll
• UCommerce.dll
• UCommerce.Infrastructure.dll

Finally, you will need to download Rhino.Mocks and add a reference to the Rhino.Mocks.dll.

2. Include the additional Namespaces

With the Query Properties window still open (F4 if you closed it), switch over to the "Additional Namespace Imports" tab and add the following Namespaces:

• NHibernate
• NHibernate.Linq
• NHibernate.Transform
• Rhino.Mocks
• System.Configuration
• System.Linq
• UCommerce.EntitiesV2
• UCommerce.Infrastructure.Configuration
• UCommerce.Security

It should now look like this:

Click ok to close LINQPad's Query Properties window.

3. Start Coding against the uCommerce database

Using the "Language" drop down Change your query language to "C# Program" which should add a little skeleton code to your window:

Replace all this code with the snippet below:

void Main()
{
	var sessionProvider = GetSessionProvider();
	using (ISession session = sessionProvider.GetSession())
	{
		var query = session.Query<Product>();
		// Your query, this just gets the first Product as an example
		query.Select(s => new { s.ProductId, s.Sku, s.Name }).FirstOrDefault().Dump();
	}
}

// Define other methods and classes here
private const string CONNECTIONSTRING = "## YOUR CONNECTION STRING ##";

private SessionProvider GetSessionProvider()
{
	var commerceConfigProviderStub = MockRepository.GenerateStub<CommerceConfigurationProvider>();
	commerceConfigProviderStub
		.Stub(x => x.GetRuntimeConfiguration())
		.Return(new RuntimeConfigurationSection
		{
			EnableCache = true,
			CacheProvider = "NHibernate.Caches.SysCache2.SysCacheProvider, NHibernate.Caches.SysCache2",
			ConnectionString = CONNECTIONSTRING
		});
	
	var userServiceStub = MockRepository.GenerateStub<IUserService>();	
	var sessionProvider = new SessionProvider(commerceConfigProviderStub, userServiceStub);	
	return sessionProvider;
}

Update your connection string and then you should be able to query the database using NHibernate and the uCommerce runtime.

I will work on a way of setting up the uCommerce API within LINQPad (so you can write code such as "UCommerce.EntitiesV2.Product.FirstOrDefault(p => p.ProductId = 1)") but it looks a little more involved as it also requires the various configuration files.