Creating a drop down list from an enum in ASP.NET MVC

Thought I’d share some work we’ve done in our MVC projects to ease the generation of drop down lists from enum types which makes life a hell of a lot easier for us when working with enums in views.

The basic premise focuses around the method below which is represented all over the web really (a lot of people seem to have come up with the solution at around the same time it seems) which is given an enum:

public enum UserType
{
	Visitor = 1,
	NonDepositor,
	DepositedOnce,
	DepositedTwice,
	Regular,
	LapsedRegular,
	LapsedNonDepositor
}

We can create a simple enum to select list convertor with the following:

public static SelectList ToSelectList<TEnum>(this TEnum enumObj)
{
	var values = (from TEnum e in Enum.GetValues(typeof(TEnum))
					select new { ID = e, Name = e.ToString() }).ToList();

	return new SelectList(values, "Id", "Name", enumObj);
}

Caveat: I didn’t invent this, it’s a pattern that’s published in a lot of places (stack overflow and other peoples blogs).

Making it look pretty

This may well work fine for a lot of your use cases or indeed for simple admin/internal systems, but our use cases dictated we extend this a little.  First and foremost was getting friendly strings out of this for the display value (our users like Words Separated With Spaces – curious that).

You could easily go with a simple regex on the ‘ToString()’ part of that code – something like:

public static string PascalCaseToPrettyString(this string s)
{
	return Regex.Replace(s, @"(\B[A-Z]|[0-9]+)", " $1");
}

And your call in the ‘ToSelectList’ method above would just be ‘ToString().PascalCaseToPrettyString()’ (for info: the regex above will take all uppercase characters or collections of numbers that aren’t at a word boundary and put a space in front of them).  This would give us something like ‘Deposited Once’ as opposed to ‘DepositedOnce’

Again, this may well suit exactly what you want, but what if the description you want to show to the user really doesn’t match what you want as the enum value.  For this, we look to the [Description] attribute and would decorate up our enum as follows:

public enum UserType
{
	[Description("Visitor (Not logged in)")]
	Visitor = 1,
	[Description("Non-depositing player (Created account, no deposits)")]
	NonDepositor,
	[Description("Single depositing player")]
	DepositedOnce,
	[Description("Twice depositing player")]
	DepositedTwice,
	[Description("Regular depositing player (Has 3 or more deposits)")]
	Regular,
	[Description("Lapsed Regular (Not logged in for the past 12 weeks)")]
	LapsedRegular,
	[Description("Lapsed Non-Depositor (Not deposited, not logged in for the past 12 weeks)")]
	LapsedNonDepositor
}

In this case we can simply extend our ‘PascalCaseToPrettyString’ concept a little further with:

public static string GetDescriptionString(this Enum val)
{
	try
	{
		var attributes = (DescriptionAttribute[])val.GetType().GetField(val.ToString()).GetCustomAttributes(typeof(DescriptionAttribute), false);

		return attributes.Length > 0 ? attributes[0].Description : val.ToString().PascalCaseToPrettyString();
	}
	catch (Exception)
	{
		return val.ToString().PascalCaseToPrettyString();
	}
}

This will attempt to grab the DescriptionAttribute from the enum value if there is one.  This will handle both situations (with and without Description attribute) nicely, and falls back to at least something that looks nice to the user if a description attribute isn’t present).  Our ‘ToSelectList()’ method will then just update to call .GetDescriptionString()’ instead of ‘ToString()’ for the value  (you will have to change the enum call like so):

public static SelectList ToSelectList<TEnum>(this TEnum enumObj)
		{
	var values = (from TEnum e in Enum.GetValues(typeof(TEnum))
					select new { ID = e, Name = (e as Enum).GetDescriptionString() }).ToList();

	return new SelectList(values, "Id", "Name", enumObj);
}

And we’re left with:

image

So far so good – what next?

The next steps are really edge cases, though it was useful to extend the helper in our use cases to deliver flexibility in all cases where we needed it.

Filtering

There are situations where you want to include only those options that are applicable based upon some other selection parameter or indeed some particular use case.  For this we can use a Func delegate along the lines of:

public static SelectList ToSelectList(this TEnum enumObj, Func predicate = null)
{
	IEnumerable values = (from TEnum e in Enum.GetValues(typeof(TEnum))
									select e);

	if (predicate != null)
		values = (from TEnum e in values
					where predicate(e)
					select e);

	var outputs = (from TEnum e in values
					select new { ID = e, Name = (e as Enum).GetDescriptionString() });

	return new SelectList(outputs, "Id", "Name", enumObj);
}

And in our views we can do something along the lines of:

<p>@Html.DropDownListFor(model => model.BankBalanceState, Model.BankBalanceState.ToSelectList( x => x != UserType.LapsedNonDepositor &&
				                                                                                    x != UserType.LapsedRegular))</p>

Adding ‘Please select’ as the first option

A simple one, though it saves you from having to jump through a few hoops if it’s important to have the ‘please select’ option at the top of the list.  This one requires a little more change to our helper method:

public static SelectList ToSelectList(this TEnum enumObj, Func predicate = null, bool addPleaseSelect = false)
{
	IEnumerable values = (from TEnum e in Enum.GetValues(typeof(TEnum))
									select e);

	if (predicate != null)
		values = (from TEnum e in values
					where predicate(e)
					select e);

	var outputs = (from TEnum e in values
					select new SelectListItem { Value = e.ToString(), Text = (e as Enum).GetDescriptionString() });

	if (addPleaseSelect)
	{
		var pleaseSelect = new List { new SelectListItem { Text = "--- please select ---", Value = "" } };
		outputs = pleaseSelect.Concat(outputs).ToList();
	}

	return new SelectList(outputs, "Value", "Text", enumObj);
}

Which leaves us with:

image

Shuffling the values

Another edge case though one that was useful to us in a number of situations was the shuffling of the values within the list.  We achieved this using a simple extension method:

public static ICollection ShuffleList(this ICollection list)
{
	return list.OrderBy( x => Guid.NewGuid()).ToList();
}

And included it in the updated ToSelectList like so:

public static SelectList ToSelectList(this TEnum enumObj, Func predicate = null, bool addPleaseSelect = false, bool shuffleList = false)
{
	IEnumerable values = (from TEnum e in Enum.GetValues(typeof(TEnum))
									select e);

	if (predicate != null)
		values = (from TEnum e in values
					where predicate(e)
					select e);

	if (shuffleList)
		values = values.ToList().ShuffleList();

	var outputs = (from TEnum e in values
					select new SelectListItem { Value = e.ToString(), Text = (e as Enum).GetDescriptionString() });

	if (addPleaseSelect)
	{
		var pleaseSelect = new List { new SelectListItem { Text = "--- please select ---", Value = "" } };
		outputs = pleaseSelect.Concat(outputs).ToList();
	}

	return new SelectList(outputs, "Value", "Text", enumObj);
}

Which is called from the view like so:

<p>@Html.DropDownListFor(model => model.BankBalanceState, Model.BankBalanceState.ToSelectList(shuffleList: true))</p>

Other extensions to this?

We’ve come up with a few more updates to this – one to force presentation via the enum numeric value (oddly in an enum, -1 is rendered after 1 and this isn’t always what you’d hope for).  We’ve also updated it for our multi-tenant websites to support localisation of enum values (though there’s enough work in this to provide an entirely separate blog post).  We’ve also added an optional parameter to ignore the current value of the enum (default to the first value in the select list rather than the selected enum) – again, an edge case, though I’m sure folks can see use cases themselves for this.

Hopefully that was useful – had been meaning to write it up for a while now (we’ve been using it in production now for over a year and it performs quite happily and there seem to be no bottlenecks/issues with it).

Grab the code

I’ve put the finished solution onto github if anyone wants to grab it and modify it themselves.  If anyone has suggestions on improvements feel free to send a pull request.

Overriding ToString() on your objects using reflection

Just a very quick one, more as a reminder to myself on something I’d setup, though as per, would love any feedback.

On a current project we’re using exception driven development, and upon an exception, we’re throwing our own custom business exceptions, and building up the Exception.Data collection with the properties in the objects as they were at the point of exception.

We have a lot of DTO objects down at the dal layer, some with a lot of properties, and we didn’t want to keep having to do:

UserNotFoundException ex = new UserNotFoundException();
ex.Data.Add(“Username”, userDto.Username);
ex.Data.Add(“UserId”, userDto.UserId);
ex.Data.Add(“AccountStatus”, userDto.AccountStatus);
...

With that in mind, I started working on what reflection could bring to the table and perhaps giving our Dto objects a base type to derive from.

Here’s the initial stab at what I’ve arrived at:

public class BaseDto
{ public override string ToString()
{
PropertyInfo[] propertyInfos = this.GetType().GetProperties();

Array.Sort(propertyInfos, (propertyInfo1, propertyInfo2) => propertyInfo1.Name.CompareTo(propertyInfo2.Name));

StringBuilder output = new StringBuilder();
foreach (PropertyInfo propertyInfo in propertyInfos)
{
output.AppendFormat("{0}: {1}\n", propertyInfo.Name, propertyInfo.GetValue(this, null));
}

return output.ToString();
}
}

Jobs a good ‘un.  I can now just use:

UserNotFoundException ex = new UserNotFoundException();
ex.Data.Add(“userDto”, userDto.ToString());

and all properties will be enumerated and documented in that one property of the .Data dictionary.

<meerkat>simples!</meerkat>

I hasten to add, this seems to work a treat in testing – I’ve got more rigorous investigation to do to make sure it presents a way forward for us, but thought I’d post it anyway in case anyone found it useful.

The Performance of Exceptional Things

Following up from my previous blog post, I’ve had some cracking feedback from a number of people both for and against the use of exceptions – it’s one of those areas (as so many are in coding) that really does seem to have its own holy war.

On one side, those that are against the use of exceptions for ‘program flow’ (though I suspect if I looked at use cases in detail, I probably would be too) and see exceptions more for exceptional circumstances.  The approach favoured by this group tends to be in returning state and programming defensively to avoid exceptions wherever possible.

I totally agree with that final statement – if I have a method ‘IsLoggedIn’ and the user isn’t, then a simple ‘false’ will do and I’ll program defensively in that method to ensure that simple things like NullReferenceExceptions etc. aren’t thrown.

The other group like seem to like the concept of Business Exceptions as a means of handling logic, though (like me) they all wondered about the performance of that approach.

My Use Case

In the example code I put together for the last post, I used the business process of logging in the customer as a use case.  I could have equally used the concept of payments into the site, though obviously a far more significant use case that would have had me writing demo code long after it made sense to do so!

In my exceptions (User Not Found, Password Mismatch, Account in various ‘no play’ states), I’ve just done an analysis of yesterdays traffic to our site (which is hitting approx 1.8-2million unique visitors per month), and we have the following errors (all day):

  • User Not Found – 1842
  • Password Mismatch – 1125
  • Account Self Excluded / Account Cooling Off / Account Disabled / Account Closed – 240

So basically, 3207 things that in our new software will throw exceptions throughout a 24hr period, or 134 per hour, or 2.3 per minute.

Obviously there are payment type errors to take into account, which I suspect will be busier, lets say up to 20-30 exceptions per minute (tops).

So just how heavy are these exceptions?

I’ve updated the hosted code I used in the previous post, and have created two approaches to getting user data – one via models, one via exceptions.  The main web navigation at the top of the page will allow you to test with exceptions or test with models.

I basically setup a test to fail login (User Not Found), and iterated through it 10,000 times, and the code is in there both for exceptions and testing returning models.

I then iterated over those 10,000 tests 10 times each.

Yup, I know this isn’t really as indicative a test as it demonstrates best possible outcomes (the exceptions being repeatedly called will obviously do some form of optimisation that is beyond me!), but it’s helpful as one measure when the core thing people mention is performance.

And yup, there *is* a performance hit when throwing exceptions – no denying it.

But when you look at the code, failing login and returning a model (single run of 10,000 fails) averages out at 289.6ms, whereas with Exceptions, the same 10,000 iteration comes out at 624.1ms.  That makes a single exception (my maths is shite, so happy to be corrected on this) take 0.034ms more to throw.

Oops! Ignore the ticks figures below – I actually (stupidly) divided Ticks by 10,000 rather than Stopwatch.Frequency, so they’ll be slightly out – the milliseconds figures reflect reality though.

  Measured in Ticks     Measured in Milliseconds  
Run Exceptions Models   Exceptions Models
1 2150098 1009757   628 290
2 2165310 1018790   624 287
3 2144660 1018190   622 288
4 2136548 1012047   623 293
5 2139677 1009204   621 289
6 2154162 1011982   627 289
7 2146923 1019645   623 290
8 2167315 1026824   623 289
9 2148493 1011428   626 291
10 2156894 1008608   624 290
Avg Ticks 2151008 1014648      
           
Avg Ms 215.1008 101.4648   624.1 289.6
           
Ms per iteration 0.02151008 0.010146   0.06241 0.02896
           
Cost Increase for Ex   0.011364     0.03345

Where are the real stats?

Well, this is where my naivety kicks in and I really must defer to clever people.  Odd to think I’m a senior dev when I can’t effectively dig any further into it than where I’m at currently, but I’ve found a few cracking posts that really help me see that I’m happy with the approach we’re taking with regards to Business Exceptions (I promise to post when this goes live to let you know if the performance hit took our site down though!).

Blog 1 – Rico Mariani

Rico is (as they say) the man, and he really knows his stuff – he certainly sits on the ‘don’t do this’ side of the holy war, and has good reasons.  He highlights that iterative testing like the above is certainly a ‘best case’ and wouldn’t demonstrate typical usage.

http://blogs.msdn.com/ricom/archive/2006/09/25/771142.aspx

Blog 2 – Jon Skeet

I like this one, it kinda supports our approach! lol.  In particular, a great quote from him:

“If you ever get to the point where exceptions are significantly hurting your performance, you have problems in terms of your use of exceptions beyond just the performance.”

http://yoda.arachsys.com/csharp/exceptions2.html

Blog 3 – Krzysztof Cwalina

This is *exactly* how I see our approach to exceptions, and I agree with Jon Skeet, I couldn’t have put it even 10% as good as Krzysztof has.  His bullet point list of Do’s and Don’ts is brilliant.

http://blogs.msdn.com/kcwalina/archive/2005/03/16/396787.aspx

Code Project Post – Vagif Abilov

I thought this one interesting as he’s gone into far more detail in terms of the tests than I have, and his conclusions are interesting.

http://www.codeproject.com/KB/exception/ExceptionPerformance.aspx

Blog 4 – Eric Lippert

Not one so much on performance, as a ‘don’t throw exceptions when you don’t need to’, and there are often ways around throwing exceptions if you code ‘well’.

http://blogs.msdn.com/ericlippert/archive/2008/09/10/vexing-exceptions.aspx

Blog 5 – Krzysztof Cwalina

Another that I’ve linked to just for the quote which very much reflects my thinking:

“One of the biggest misconceptions about exceptions is that they are for “exceptional conditions.” The reality is that they are for communicating error conditions. From a framework design perspective, there is no such thing as an “exceptional condition”. Whether a condition is exceptional or not depends on the context of usage, — but reusable libraries rarely know how they will be used. For example, OutOfMemoryException might be exceptional for a simple data entry application; it’s not so exceptional for applications doing their own memory management (e.g. SQL server). In other words, one man’s exceptional condition is another man’s chronic condition.”

http://blogs.msdn.com/kcwalina/archive/2008/07/17/ExceptionalError.aspx

Exception Management Guidance – Multiple authors

Some good feedback re: exceptions in this post.

http://www.guidanceshare.com/wiki/.NET_2.0_Performance_Guidelines_-_Exception_Management

Closing

I’ve updated the code on Google Code at: http://code.google.com/p/business-exception-example/ to cover both Exceptions and Models if anyone wants a looksy.

Again though, really interested in hearing thoughts on this.  I think from the performance testing I’ve done and the posts I’ve read, I’m happy with our approach, but I’m equally happy for someone to come along and shout NOOOOOOO! and tell me why I’m an idiot 🙂

Over to you guys, and thanks for all the feedback thus far!

Business Exceptions in c# (as I understand them!)

Thought I’d best caveat the post as this really is just a collection of thoughts from a number of very clever people, and I’ve come to wonder over the past few days (since #dddscot) whether this is a good way to handle business exceptions or not.

My approach has been born out of a cracking talk by Jeffrey Richter at DevWeek this year (see the summary post elsewhere in my blog) where he talked about exception within your software and (as @plip did at dddscot this year) about embracing them.  He talked about exceptions in the following way though:

  1. Exceptions are not just for exceptional circumstances
  2. They are there as a means of saying ‘something hasn’t worked as expected, deal with it’
  3. They should be thrown when they can reliably be managed (be that logging or something else)
  4. They should be useful/meaningful

In my other post, I used the example of ProcessPayment as a method, and the various things that could go wrong during that method, but I thought I’d bring together a simple app that demonstrates how we are using exceptions currently.

The reason for this post

There was a lot of discussion after #dddscot about how folks handle this sort of thing, and really, there were some very clever people commenting!  It’s kinda made me nervous about the approach we’re taking, you all know the crack:

Dev1: “And that new method works even if the input is X, Y, and A?”

Dev2: “It did until you asked me, but now I’m going to have to test it all again!”

Ahhh, self doubt, you have to love it 🙂

Though I digress – basically, I would love to get some feedback from the community on this one.

Business information – what are the options?

Ok, if we take a simple method call, something like:

ProcessLogin(username, password)

How can we find out if that method fails for whatever reason?  If it does fail, why does it fail?  Was the username wrong?, is their account disabled?, did the password not match up?  This is a relatively straight forward method which is why I’ve chosen it for the demo, though there are any number of things that can go wrong with it.

Option 1 – returning an enum or something that can identify the type of error

So the method signature could be:

public ProcessLoginResult ProcessLogin(string username, string password) {
	// stuff
}

public enum ProcessLoginResult {
	Success,
	Fail_UsernameMismatch,
	Fail_PasswordMismatch,
	Fail_AccountDisabled,
	Fail_AccountCoolingOff,
	Fail_AccountSelfExcluded,
	Fail_AccountClosed
}

You may feel like that’s a lot of fail states, but these are what I work with in my current environment so they have to be included.

Obviously then we have something from the calling code like:

var result = ProcessLogin(username, password);

if (result != ProcessLoginResult.Success) {
	switch(result) {
		case ProcessLoginResult.UsernameMismatch:
		case ProcessLoginResult.PasswordMismatch:
			ModelState.AddModelError("General", "We have been unable to verify your details, etc. etc.");
			break;
		case ProcessLoginresult.[errorstate1]
			return RedirectToAction("ErrorState1", "ErrorPages");
		case ... [for each extra error state]
	}
}

There are obvious pro’s to this approach from my point of view – one is that we’re not throwing exceptions!  People talk a lot about the performance overhead in actually throwing new exceptions – there’s generally a sucking in of teeth as they do this.  I personally have no idea how “expensive” they are to raise, and it’s certainly something I’ll have to look into.

The difficulty here for me though is two-fold:

  1. If I want the richness of business information to return from my methods on failure, I need to come up with (almost) an enum per method to define the states that it can return with?
  2. If I have a different method (e.g. GetUserById(userId)) my only option is to setup the method signature with the user as an out param or pass it down by reference.

Option 2 – Business Exceptions

And this is the approach I’ve taken, though again – feedback very much appreciated!  Each of the possible fail states becomes a potential exception.  So the ProcessLogin method becomes:

/// 
/// Processes the login.  Steps are:
///  - Check the existence of the user
///  - Check the password matches (yup, we'd be hashing them here, no need for the demo)
///  - Check the account status
/// 
/// The username.
/// The password.
/// 
public MyCompanyUser ProcessLogin(string username, string password)
{
	MyCompanyUser user;

	try
	{
		user = dal.GetUserByUsername(username);
	}
	catch (MyCompanyUserNotFoundException)
	{
		//TODO: LOGGING
		throw; // but then pass the exception up to the UI layer as it is most easily able to deal with it from a user perspective
	}

	if (user.Password != password)	
	{
		//TODO: LOGGING
		MyCompanyUserWrongPasswordException ex = new MyCompanyUserWrongPasswordException("Password doesn't match");
		ex.Data.Add("Username", username);
		// potentially if you had an MD5 or something here you could add the hashed password to the data collection too

		throw ex;
	}
	
	switch(user.AccountStatus)
	{
		case AccountStatus.SelfExcluded:
		{
			//TODO: LOGGING
			MyCompanyUserSelfExcludedException ex = new MyCompanyUserSelfExcludedException("User self excluded");
			ex.Data.Add("Username", username);
			throw ex;
 		}	
		case AccountStatus.CoolingOff:
		{
			//TODO: LOGGING
			MyCompanyUserCoolingOffException ex = new MyCompanyUserCoolingOffException("User cooling off");
			ex.Data.Add("Username", username);
			throw ex;
		}	
		case AccountStatus.Disabled:
		{
			//TODO: LOGGING
			MyCompanyUserAccountDisabledException ex = new MyCompanyUserAccountDisabledException("Account disabled");
			ex.Data.Add("Username", username);
			throw ex;
		}	
		case AccountStatus.Closed:
		{
			//TODO: LOGGING
			MyCompanyUserAccountClosedException ex = new MyCompanyUserAccountClosedException("Account closed");
			ex.Data.Add("Username", username);
			throw ex;
		}	
	}
	return user;
}

obviously with this in place I can either Log at this level or log at the UI layer (I don’t have a strong feel architecturally either way).

The process login method call at the UI layer then becomes a little more convoluted:

try
{
	MyCompanyUser user = service.ProcessLogin(model.Username, model.Password);

	return RedirectToAction("LoggedIn", "Home");
}
catch (MyCompanyUserSelfExcludedException)
{
	return RedirectToAction("SelfExcluded", "ErrorPages");
}
catch (MyCompanyUserCoolingOffException)
{
	return RedirectToAction("CoolingOff", "ErrorPages");
}
catch (MyCompanyUserAccountDisabledException)
{
	return RedirectToAction("AccountDisabled", "ErrorPages");
}
catch (MyCompanyUserAccountClosedException)
{
	return RedirectToAction("AccountClosed", "ErrorPages");
}
catch (MyCompanyUserException)
{
	// if we're this far, it's either UserNotFoundException or WrongPasswordException, but we'll catch the base type (UserException)
	// we can log them specifically, handle them specifically, etc. though here we don't care which one it is, we'll handle them the same
	ModelState.AddModelError("General", "We have been unable to match your details with a valid login.  (friendly helpful stuff here).");
}

I don’t know why I find this a more elegant solution though – it certainly doesn’t generate any less code! There is very much a need for good documentation in this one (each method call documenting what types of exceptions can be thrown).

Want to see more?

I’ve put together a test VS2010 project using MVC2 and separate projects for the exception definitions and one for the models/services/dal stuff.

It’s rudimentary, but our core solution as Unity in there as an IoC container, it has interface based Services and Repositories, it has unit tests etc. and it just wasn’t viable (or commercially acceptable) to make any of that available, so I’ve distilled it down to the basics in the solution.

What I’d love now is feedback – how do people feel about this approach (Business Exception led) as opposed to the other?  What other approaches are available?  Is it bad to use exceptions in this way (and I’m fine if the answer is ‘ffs tez, stop this now!’ so long as there’s a good reason behind it!)

The code is available on google code at: http://code.google.com/p/business-exception-example/

and I’ve only created a trunk (subversion) at present at: http://code.google.com/p/business-exception-example/source/browse/#svn/trunk

Feedback pleeeeeez!

Using T4 to generate enums from database lookup tables

I’m sure a fair few people will be working on projects like us where we have a database backend with referential integrity, including a number of lookup tables.  A lot of the time in this situation you also want to mirror the lookup values in your code (as enums for us).  Most of the time, it’s relatively easy to just manually create both sets of entries as they will rarely change once created.  Or so we hope!

I quite fancied learning about T4, and the first example I could think of was this tie up between database lookup tables and code enums. 

I love the idea that the output from your T4 work is available at compile time and available directly in your code once you’ve created the template – the synching of things between a database and your code base is an obvious first play.

So with that in mind, lets crack on.

Initial Setup

I’ve created a simple console app and a simple DB with a couple of lookup tables – simple ‘int / string’ type values.  I installed T4 Toolbox to get extra code generation options within the ‘Add New…’ dialog, though it turns out my final solution didn’t actually require it – that said, the whole T4 Toolbox project looks very interesting, so I’ll keep an eye on that.

image

This will generate a file ‘GenerateCommonEnums.tt’, and the base content of the file is:

image

Add a reference to your DB

At this point, I would have loved to use linq to sql to generate my enums, as it’s a friendly/syntacitcally nice way of getting at data within the database.

That said, this proved far more difficult than I’d have hoped – any number of people had made comments about it, and saying if you ensure System.Core is referenced and you import System.Linq job should be a good un.  It wasn’t in my case.

Thankfully, this wasn’t the end of the investigation.  I managed to find an example online that used a SQLConnection… old skool it was to be!

So what does the code look like…

The code I generated turned into the following, and I’m sure you’ll agree it aint that far away from the sort of code we’d write day in day out.

<#@ template language="C#" hostspecific="True" debug="True" #>
<#@ output extension="cs" #>
<#@ assembly name="System.Data" #> 
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#
    SqlConnection sqlConn = new SqlConnection(@"Data Source=tombola009;Initial Catalog=TeamDev;Integrated Security=True");
    sqlConn.Open();
#>
namespace MyCompany.Models.Enums
{
	public enum TicketType
	{
		<#
		string sql = string.Format("SELECT Id, Name FROM LOOKUP_TABLE_1 ORDER BY Id");
        SqlCommand sqlComm = new SqlCommand(sql, sqlConn);

        IDataReader reader = sqlComm.ExecuteReader();

        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        while (reader.Read())
        {
            sb.Append(TidyName(reader["Name"].ToString()) + " = " + reader["Id"] + "," + Environment.NewLine + "\t\t");
        }
        sb.Remove(sb.Length - 3, 3);

        reader.Close();
        sqlComm.Dispose();
		#>
<#= sb.ToString() #>
	}
	
	public enum TicketCategory
	{
		<#
		sql = string.Format("SELECT Id, Area, Name FROM LOOKUP_TABLE_2 ORDER BY Id");
        sqlComm = new SqlCommand(sql, sqlConn);

        reader = sqlComm.ExecuteReader();

        sb = new System.Text.StringBuilder();

        while (reader.Read())
        {
            sb.Append(TidyName(reader["Area"].ToString()) + "_" + TidyName(reader["Name"].ToString()) + " = " + reader["Id"] + "," + Environment.NewLine + "\t\t");
        }

        sb.Remove(sb.Length - 3, 3);

        reader.Close();

        sqlComm.Dispose();
		#>
<#= sb.ToString() #>
	}
}

<#+
	
    public string TidyName(string name)
    {
        string tidyName = name;

		tidyName = tidyName.Replace("&", "And").Replace("/", "And").Replace("'", "").Replace("-", "").Replace(" ", "");
		
        return tidyName;
    }

#>

The ‘TidyName’ method was in there just to try to tidy up the obvious string issues that could crop up.  I could have regex replaced anything that wasn’t a word character, though I think this gives me a bit more flexibility and allows customisable rules.

This basically generates me the following .cs file:

 
namespace MyCompany.Models.Enums
{
	public enum TicketType
	{
		Problem = 1,
		MAC = 2,

	}
	
	public enum TicketCategory
	{
		Website_Affiliates = 1,
		Website_Blog = 2,
		Website_CentrePanel = 3,
		Website_CSS = 4,
		Website_Deposit = 5,
		Website_Flash = 6,
		Website_GameRules = 7,
		Website_GameChecker = 8,
		Website_HeaderAndFooter = 9,
		Website_HelpContent = 10,
		Website_Images = 11,
		Website_LandingPage = 12,
		Website_MiscPage = 13,
		Website_Module = 14,
		Website_Multiple = 15,
		Website_MyAccount = 16,
		Website_myTombola = 17,
		Website_Newsletters = 18,
		Website_Playmantes = 19,
		Website_Refresh = 20,
		Website_Registrations = 21,
		Website_Reports = 22,
		Website_TermsAndConditions = 23,
		Website_WinnersPage = 24,
		Website_Other = 25,
	}
}

From that point on, if there are extra lookup values added, a simple click of the highlighted button below will re-run the templates and re-generate the CS files.

image

Next Steps

I’m utterly sure there must be an easy way to use linq to sql to generate the code above and I’m just missing it, so that’s the next play area.  I’m going to be playing with the POCO stuff for EF4, so I think the above has given me a taster for it all.

As with all initial plays with this sort of thing, I’ve barely scratched the surface of what T4 is capable of, and I’ve had to rely upon a lot of existing documentation.  I’ll play with this far more over the coming weeks – I can’t believe I’ve not used it before!

A better way to check for validity in emails?

I’ve had a method that I’ve used from time to time to validate email addresses, trying to cater for the common problems that have been seen with addresses.  This weekend I had cause to look at it and thought there must be a better way of representing it all.

Couple of thoughts crossed my mind:

  1. I’m not throwing exceptions anywhere, and although I know the method, so use it as I’d expect, perhaps I should be throwing a FormatException? or some others?
  2. It’d be easy to make this an extension method, but I guess it’d be an extension to System.String, and doesn’t really feel right as it serves such a focussed purpose.
  3. Should I be doing any other checks in the code that I’m not already?

I’ll have a read around and look at refactoring, but thought I’d post it here so that I have a record of the ‘before’ and ‘after’ views.

/// 
/// 
/// 
/// 
/// 
/// 
public static string ValidateEmail(string email, out string error)
{
	try
	{
		error = "";

		// Pre-formatting steps
		email 	= email.Trim().Replace(" ", "");
		email 	= email.Replace(",", ".");												// mostly, commas are full stops gone wrong
		email 	= (email.EndsWith(".")) ? email.Substring(0, email.Length-1) : email;	// kill any full stop at the end of an address
		email	= email.Replace(@"""", "");												// remove " in the email address
		email	= (email.StartsWith("'")) ? email.Substring(1) : email;					// remove ' at the start of the address
		email	= (email.EndsWith("'")) ? email.Substring(0, email.Length-1) : email;	// remove ' at the end of the address

		// STEP 1	- No '@' symbol in Email
		if (!email.Contains("@"))
		{
			error = "Email contains no '@' symbol.";
			return "";
		}

		// STEP 2	- More than 1 '@'symbol in Email
		if (email.Split('@').Length > 2)
		{
			error = "Email contains too many '@' symbols.";
			return "";
		}

		// STEP 3	- No .com, .co.uk at end of addresses
		//			- Invalid characters ()<>,?/\|^!"£$%^&* ??? in address
		Regex _regex = new Regex(@"^[-\w._%+']+@[-\w.]+\.[\w]{2,4}$", RegexOptions.IgnoreCase);
		if (!_regex.IsMatch(email))
		{
			error = "Email address appears invalid.";
			return "";
		}
		
		return email;
	}
	catch
	{
		error = "Unknown error with email address.";
		return "";
	}
}

Dependent objects in SQL Server

another of those ‘bloody hell, why did I not know this before’ moments, but one of the lads circulated this during the week as a means of checking dependencies on either stored procs or tables.

A simple

exec sp_depends [object name]

Will return a set of results that highlight which stored procs, views, tables, user-defined functions or triggers are dependent upon that object.  The full MSDN documentation is available here.

So handy when there are schema changes in legacy code/schema’s.

Orphaned SQL Server Users

Been blogged about all over the place, but I wanted a central place to remember it.

After restore of a database from another server, often the user account can become unassigned from an SQL server login.

The following sorts it:

sp_change_users_login 'update_one', 'orphaned_login', 'sql_username'

jobs a good un.

Now I never need hunt again – huzzah 🙂

NamingContainer, where have you been all my life!

Well, it’s been where it’s always been – in the .net framework, all the way back to version 1.0 apparently!  I’m sure I must have written some awful code in the past to get around the fact that I didn’t know about this, and I really must spend more time getting down to the details of the framework for reasons of framework nuggets like this.

Imagine you have a ListView and want to allow updates on each item, with perhaps a text field etc. in there, and you click a button inside that ListViewDataItem.  NamingContainer for the button will return that ListViewDataItem so that you can find just within that container… ace!

protected void btnWishList_Click(object sender, EventArgs e)
{
    Button _button = ((Button)sender);
    RadTextBox tb = (RadTextBox)_button.NamingContainer.FindControl("txtNotes");
    // do other processing
}

the above is formatting awfully at the moment, looking for a nice wordpress syntax plugin.