**UPDATE – As we moved forward with our project this method turned out to be a little less awesome than I thought when I put it together. I’ve left this post here though as I am not ashamed of my failures! There is also some neat stuff in here with setting up the identity fields using nHibernate.
Recently I needed to create a generic re-usable system to generate system wide unique counters using SQL server tables and identity fields to handle the number generation. One of the key requirements was to be able to specify the identity seed, but in a re-usable way of course.
Generic Class and Class Map
So lets start this off with a base class to define the counter object:
**Update 2011.12.09 – I’ve posted an Oracle specific version of the Country/State/Province lookup SQL.
Every time I build a contact form/database I hit this problem. I need a set of tables for Country, State/Province lookup. “But, that’s like 60 records and I don’t wanna type out every state and province!”.
Every time, I hit the Google looking for SQL scripts – and I can’t seem to find anything, or I find that one script for mySQL. The last time I did this, I created a set of SQL scripts and I saved them. I forgot that I saved them – and made another SQL script. Doh.
This time when I was working on the new contact form for this site, I decided to write this blog post in hopes that the next time I need to do this – when I search Google my blog might come up and remind me not to re-invent the wheel. So I’ve included a zip with the SQL script here. This script is for SQL server 2005 – but will translate over to Oracle very easily.
If you find any problems with the script, or feel like adding more countries, feel free to pass those changes along to me!