Visual Studio 2012 Update 1 Sucks

Don’t install it unless you want VS to shut itself down multiple times a day and run slow as hell.

Paleo Nut Bars

I went paleo/gluten-free almost a year ago.  I was eating quite a few Kind Bars but they get expensive.  So I searched the internet for a “homemade kind bars” and ran across this recipe.  I’ve played around with it quite a bit and feel that mine is perfected (at least for my tastes).  Enjoy.

  • 1.25 cup whole almonds
  • 1.25 cup whole pecans
  • 1 cup unsweetened coconut
  • 1/2 cup flax seed flour/meal
  • 1/2 cup chopped dried fruit (cherries or cranberries are my faves)
  • 1/8 cup maple syrup
  • 1/4 cup honey

Optional ingredients

  • 1/2 tsp cinnamon
  • 1/2 tsp nutmeg
  • 1/2 tsp ginger powder
  • 1/4 tsp salt

Preheat oven to 350F.

Roughly chop the almonds and place in large mixing bowl.

Add roughly chopped pecans.

Add coconut, flax seed flour,  dried fruit, and any spices to bowl and mix with fork.

Place honey and maple syrup in the microwave for about 20 seconds.

Evenly distribute about 1/2 of the liquid by lightly drizzling and stir with fork.

Repeat with the remainder of the liquid.

Line a cookie sheet with parchment paper.

Dump the mixture onto the cookie sheet and shape into a rough square using a fork.

Using another sheet of parchment paper, firmly press the mixture into a uniform height cupping and pressing the edges of the mixture as needed.  Discard the parchment paper used to press.

Place the cookie sheet in the oven for 20 minutes.

Remove and let cool for 20 minutes.

Cut into desired shape and store in a covered container.


SQL Server Table Design Rules of Thumb

These are not meant to be comprehensive (and don’t get into advanced normalization or intentional denormalization) but will result in a decent table design without too much thought in most situations. A case can be made for natural primary keys but going the surrogate route is easier and works well too.

  1. Always have a primary key. Typically a surrogate key using an identity column and not necessarily clustered.
  2. Almost always have at least one unique constraint if you used a surrogate primary key
    • consider making this/one of these the clustered index as they may be the most common way rows are selected
    • these will frequently save you from bugs/errors that would result in duplicate data
    • name and abbreviation in a state table are a good example — only one state should have the abbreviation TX and only one should have the name Texas
  3. Always include create date and created by columns. If rows can be updated, include updated date, and updated by columns as well.
  4. Consider effective and expiration date columns in place of “active” flags.
  5. Consider a timestamp (not datetime, but timestamp) field if multiple simultaneous edits are possible
  6. Avoid nullable columns – they result in ISNULL/COALESCES — and use defaults instead – EG ‘’ (empty string) for varchar fields, 0 or -1 for ints, etc. A large number of necessarily nullable columns suggest the table may need to be split.
  7. Avoid nullable foreign keys — they result in outer joins
  8. Consider indexes on any foreign keys
  9. Do not store compound values in one column.
  10. If you know in advance how your table will be queried and none of the indexes created from rules 1, 2, or 7 meet requirements, consider creating indexes on those columns if they are selective (have a wide distribution of values).
  11. You can break any rule except number 1 with good reason after very careful consideration

Stored Procedures Versus LINQ to SQL for Dynamic WHERE Clauses

I was recently discussing the best way to solve the “bad plan gets cached for super-complex WHERE clause with a large number of @column IS NULL OR T.Column = @column conjuncts” problem with colleagues at work. This is another variant of the age-old “stored procedures versus code-generated SQL” (including ORMs here) debate.

I consider myself a T-SQL expert but I am also a firm believer in the “right tool for the right job.” LINQ to SQL (or even old-fashioned, manually-generated SQL) is clearly superior to stored procedures for UI-driven, single SELECT queries with large numbers of WHERE and/or ORDER BY options. In fact, I favor code-based solutions over stored procedures unless performance dictates otherwise (which, in practice, is not very often). But for the specific case I’m discussing here, I don’t think it’s even arguable that the stored procedure is the right way to go.

To make this point, I’m going to provide examples of all 4 scenarios and compare them. The base query is simplified to make the examples easier to follow. Obviously, this problem only becomes intractable using T-SQL when you have more than 3 search criteria, two tables, and two sort options. If your requirements were very stable (yeah, right) and that’s all the variability you had to deal with, you could write (3 x 2 =) 6 different “standard” stored procedures and call the appropriate one from code based on the mix of non-null parameters passed (or embed them all in one procedure within a bunch of IF statements). I’ve been the unfortunate victim (read “maintainer”) of this type of code and I still curse the person that left me that procedure to this day. Requirements aren’t usually that stable or by the time they are, you should have solved the problem another way already.

But I digress. Here’s the “standard” stored procedure in all it’s glory:

	@LastName varchar(50) = null, 
	@FirstName varchar(50) = null,
	@PostingDt datetime = null,
	@OrderBy varchar(50) = null
	SELECT T.AccountNoId, A.LastName, A.FirstName, T.PostingDt, T.BillingAmount
	FROM AccountTransaction T (nolock)
	JOIN Account A (nolock) ON A.AccountNoId = T.AccountNoId
	WHERE (@LastName IS NULL OR A.LastName LIKE @LastName + '%')
	AND (@FirstName IS NULL OR A.FirstName LIKE @FirstName + '%')
	AND (@PostingDt IS NULL OR T.PostingDt = @PostingDt)
		WHEN @OrderBy = "LastName" THEN A.LastName
		WHEN @OrderBy = "FirstName" THEN A.FirstName
		ELSE T.PostingDt

The beauty of the standard stored procedure is that you get compile-time syntax checking and the stored procedure is only parsed once. But…you also get *very* bad query plans if the first call to the procedure includes actual parameters that aren’t representative of the way the procedure is usually called.

“Standard” stored procedures potentially have four advantages: minimizing round trips, reducing network traffic (even for a single call), compile-time syntax checking, and single parse. Only the first two advantages are maintained when you introduce dynamic SQL (or use WITH RECOMPILE which often fails to solve the problem anyway). If you only have one statement, round trips are no longer an issue and, with modern networks, the the fact that the entire SQL statement has to sent over the wire versus just the stored procedure name and the actual parameters is probably undetectable in all but the highest volume situations.

Here’s the dynamic SQL version of the stored procedure (note that I’m not going to even bother discussing the use of EXECUTE which completely sucks and should be avoided at all costs):

	@LastName varchar(50) = null,
	@FirstName varchar(50) = null,
	@PostingDt datetime = null,
	@Orderby varchar(50) = null
	DECLARE @where nvarchar(max)
	SELECT @where = '1 = 1'

		SELECT @Where = @Where + " AND A.LastName LIKE @LastName + '%'"

	IF @FirstName IS NOT NULL
		SELECT @Where = @Where + " AND A.FirstName LIKE @FirstName + '%'"

	IF @PostingDt IS NOT NULL
		SELECT @Where = @Where + " AND T.PostingDt = @PostingDt"

	DECLARE @orderBySql nvarchar(max)
	SELECT @orderBySql = CASE
		WHEN @OrderBy = "LastName" THEN "A.LastName"
		WHEN @OrderBy = "FirstName" THEN "A.FirstName"
		ELSE "T.PostingDt"

	DECLARE @sql nvarchar(max)
	SELECT @sql = "
	SELECT T.AccountNoId, A.LastName, A.FirstName, T.PostingDt, T.BillingAmount
	FROM AccountTransaction T (nolock)
	JOIN Account A (nolock) ON A.AccountNoId = T.AccountNoId
	WHERE " + @where + " 
	ORDER BY " + @orderBySql

	exec sp_executesql @sql,  N'@LastName varchar(50), @FirstName varchar(50), @PostingDt datetime', 
		@LastName, @FirstName, @PostingDt

Even though I’ve formatted this too look nice, if you’ve ever worked with procedures like this you realize they really suck to maintain and debug. All of the important SQL is actually in string variables and doesn’t get parsed and compiled until run-time. So when you compile this stored procedure, the only thing you know is that your T-SQL to generate the SQL that’s actually going to run at run-time is valid. This is exactly the same problem you have with SQL dynamically generated on the client. If I’m doing standard programming work like fancy string manipulation, I’d much rather do it in C# or Java than T-SQL. Particularly since I really need to unit test the hell out of this to make sure valid SQL is generated for all the different combinations of parameters. Oh, and now I get Intellisense and Resharper, at least for the SQL generating part of code. If I’m using LINQ, I get them for everything, including the query itself! But I’m getting ahead of myself.

Before we move on to manually generated dyamic SQL on the client, I want to address plan-caching and sql-injection because stored procedure zealots often bring these up. It turns out that neither of these are an issue with properly parameterized dynamic SQL, whether it’s generated in a stored procedure or off-server in C#, Java, or any other modern language. So don’t let these two canards deter you.

Now let’s see the manually-generated in C# version which is just a translation of the T-SQL code above into C#. For demo purposes, the function is self-contained but you could (and probably should) have a base class with common functions (like handling the ugly DbNull.Value).

public DataTable GetTransactions(string lastName, string firstName, DateTime? postingDt, string orderBy)
	string sql = "SELECT T.AccountNoId, A.LastName, A.FirstName, T.PostingDt, T.BillingAmount " 
		+ "FROM AccountTransaction T (nolock) " 
		+ "JOIN Account A (nolock) ON A.AccountNoId = T.AccountNoId " 
		+ "WHERE 1 = 1 ";
	if (lastName != null) sql += " AND A.LastName LIKE @LastName + '%' ";
	if (firstName != null) sql += " AND A.FirstName LIKE @FirstName + '%' ";
	if (postingDt != null) sql += " AND T.PostingDt = @PostingDt ";
	sql += "ORDER BY ";
	sql += orderBy == "LastName"
		? "A.LastName "
		: orderBy == "FirstName"
			? "A.FirstName "
			: "T.PostingDt ";
	DataTable ret;
	using ( var cn = new SqlConnection(_connStr) )
		var cmd = new SqlCommand(sql, cn);
		cmd.Parameters.AddWithValue("@LastName", (object) lastName ?? DBNull.Value);
		cmd.Parameters.AddWithValue("@FirstName", (object) firstName ?? DBNull.Value);
		cmd.Parameters.AddWithValue("@PostingDt", (object) postingDt ?? DBNull.Value);
		var adapter = new SqlDataAdapter(cmd);
		ret = new DataTable();
	return ret;

Like I said above, if I’m not getting any advantages from working in T-SQL, I’d rather work in C#. But, from a language-neutral perspective, the code above is not clearly easier to maintain and debug than the corresponding T-SQL.

Here’s essentially the same code using LINQ to SQL and a couple of extension methods:

using (new TransactionScope(TransactionScopeOption.Required,
	new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
	var query =
		from t in Context.AccountTransaction
		join a in Context.Account on t.AccountNoId equals a.AccountNoId
		select new { t.AccountNoId, a.LastName, a.FirstName, t.Postingdt, t.Billingamount };
	query = query.AddWhere(lastName, t => t.LastName.StartsWith(lastName));
	query = query.AddWhere(firstName, t => t.FirstName.StartsWith(firstName));
	query = query.AddWhere(postingDt, t => t.Postingdt == postingDt);
	query = query.OrderBy(orderBy);

I mentioned the benefits of Intellisense above. Since this is all “code”, I get to access the table and field names using Intellisense (and likewise, they are checked by the compiler/Resharper). This is a huge advantage in my book.

I prefer to use known (as opposed to anonymous) types but I wanted to keep the sample code as self-contained as possible. I end up converting anything in a DataTable to a generic List of T anyway so the fact the LINQ returns objects is a big bonus for me. If you like/want DataTables (you’re a massochist?), you could write an effecient reflection-based conversion routine.

The SQL generated by LINQ to SQL is quite good and you can see what it’s generating very easily. Once you understand how LINQ implements OUTER JOINS, it’s really quite easy to start thinking in LINQ instead of SQL. If you are persuaded by this article to give LINQ a try, I highly recommend LINQPad which provides an iterative and interactive way to develop LINQ queries similar to how I develop SQL queries in SSMS.

Posted in c#, LINQ, SQL. 3 Comments »

Data Execution Prevention Error After Internet Explorer (IE) 8 Upgrade

This was really driving me nuts and almost caused me to completely switch to Chrome which I use from time to time. Here’s how I resolved the problem. As soon as I would launch IE 8, the Data Execution Prevention (DEP) dialogue box would pop up. I would hit OK and then it would shut down IE 8. I tried disabling DEP for Internet Explorer but this still happened at first about 50% and then almost 100% of the time. I even tried uninstalling the MSJVM. None of this helped.

I noticed that if I did not clear the DEP dialogue, I could move it out of the way and open another tab in IE8. I could also select Tools, Manage Add-Ons. I then disabled most of the add-ons (ones I didn’t really need or didn’t even know were there). That did the trick.

Hopefully this will help any other unfortunate souls out there with this very annoying problem.

Check for Temp Table Existence in SQL Server

IF OBJECT_ID(‘tempdb..#sometable’) IS NOT NULL
DROP TABLE #sometable

Change Your Windows Password from the Command Line

NET USER (username) * /domain

You’ll be prompted for the new password twice.

A Short, Friendly GUID/UUID in .NET

I recently committed to returning a unique ID that was 18 characters or less as part of the response from a new version of an existing web method I support. This value would be stored and used for subsequent calls to other web methods and would potentially be manually entered into a website query page.

Easy, you say. Just use an IDENTITY (or SEQUENCE in Oracle) column (which is already in place as a surrogate key, BTW). The problem with IDENTITY columns is that you have to wait for the call to the database to complete before you know what the value will be. In this scenario, we are using MSMQ to enhance the performance and reliability of the operation — this allows us to return from the web method call without waiting on the DB (which *usually* is quite fast but slows down for short periods of time infrequently). This web method gets called very frequently and the calls can be bursty, so the queuing maintains a very quick response time under all circumstances.

I had planned all along to use a GUID/UNIQUEIDENTIFIER column in the underlying table with the GUID generated during the web method call and written to the write queue with the rest of the data. Unfortunately, I confused two numbers in my head when I made this commitment. GUIDs are 16 *bytes* long but their default display format is actually 32 hex digits with 4 dashes thrown in for good measure.

I didn’t want to go back to the client and ask for them to increase the size of this field. Especially since this was going to be a human queriable field on a web site. I considered base 36 encoding the GUID, but that would still take 25 digits. Next I considered Base64 Encoding a GUID which is actually quite clever but it still came up “short” (or should I say long) by 4 digits compressing the GUID down to 22 digits. But still a nice improvement over 36.

Rick Strahl (a very prolific, well-written .net blogger) contemplated using GetHashCode().ToString(“x”) on a variety of targets, but I couldn’t get comfortable with statistical validity of this approach.

I considered using a combination of a time-based value (such as DateTime.Ticks) but I could not get past the bursty usage problem with those. I also felt that I must be re-inventing the wheel. The GUID was the “best practice” and I couldn’t help but feel I should utilize that approach or at least a similar one.

The “G” in GUID stands for global. I really didn’t need “global” uniqueness. In fact, I just needed to guarantee statistical uniqueness for all these specific types of calls made by a particular client. I could use the “client id” as part of the lookup to further reduce the “uniqueness” required. I then did some research on UUIDs (GUID is just MS’s brand name for UUID) and saw that later versions of the algorithm use random number generators. I decided that if 128 bits gets me “global” uniqueness, I could get by with a few less for my scenario. So now the question was how to encode those bits

I actually used base 36 over a decade ago in an Xbase application to avoid having to increase the width of a field but there are a couple of problems with it. First, because 36 is not a power of 2, you cannot easily (as in bit shifting) convert bits to digits. Second, 0/O (zero, and oh) and 1/I (one and eye) can easily be confused, particularly in some fonts.

Removing those four digits leads to my choice, base 32 with 2-9 and the capital letters minus O and I as the digits. You can easily convert to base 32 because it’s a power of 2 and you get a very friendly set of digits with good bit density.

Now the final piece of the puzzle was how to generate “good” random numbers. Good for me meant that there was a very, very low probability of collisions, even if I was generating an ID on the same machine every 10 milliseconds or so. As I mentioned above, usage of the service can be bursty. Some clients are psuedo-batching their work and hit the service hard for a relatively short period of time.

While doing some research on .net random number generation, I learned that it is much better to instantiate the generator once than it is to repeatedly instantiate it for each number. This sounds counter-intuitive at first, but makes sense when you think about the time-base default seeding these generators use. So a singleton was in order.

Finally, I had to choose between venerable RAND and RNGCryptoServiceProvider. Although I think using the singleton approach with a single instance of RAND would have worked, I felt more comfortable with the more robust seeding used by RNGCryptoServiceProvider. Particularly since we are running on clusters and our web service calls are sessionless (no affinity).

There’s one final wrinkle. In my scenario, I have a unique (most of the time) 32 bit integer value available. I wanted to incorporate that into my scheme to further reduce the odds of a collision. I do this by taking a byte array as an input that is “mixed” with the random bytes to produce the ID.

Without further adieu, here’s the code for the class itself and some unit tests:

using System;
using System.Security.Cryptography;

public class UniqueIdGenerator
	private static readonly UniqueIdGenerator _instance = new UniqueIdGenerator();
	private static char[] _charMap = { // 0, 1, O, and I omitted intentionally giving 32 (2^5) symbols
		'2', '3', '4', '5', '6', '7', '8', '9', 
		'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'

	public static UniqueIdGenerator GetInstance()
		return _instance;

	private RNGCryptoServiceProvider _provider = new RNGCryptoServiceProvider();

	private UniqueIdGenerator()

	public void GetNext(byte[] bytes)

	public string GetBase32UniqueId(int numDigits)
		return GetBase32UniqueId(new byte[0], numDigits);
	public string GetBase32UniqueId(byte[] basis, int numDigits)
		int byteCount = 16;
		var randBytes = new byte[byteCount - basis.Length];
		var bytes = new byte[byteCount];
		Array.Copy(basis, 0, bytes, byteCount - basis.Length, basis.Length);
		Array.Copy(randBytes, 0, bytes, 0, randBytes.Length);

		ulong lo = (((ulong)BitConverter.ToUInt32(bytes, 8)) << 32) | BitConverter.ToUInt32(bytes, 12); // BitConverter.ToUInt64(bytes, 8);
		ulong hi = (((ulong)BitConverter.ToUInt32(bytes, 0)) << 32) | BitConverter.ToUInt32(bytes, 4);  // BitConverter.ToUInt64(bytes, 0);
		ulong mask = 0x1F;

		var chars = new char&#91;26&#93;;
		int charIdx = 25;

		ulong work = lo;
		for (int i = 0; i < 26; i++)
			if (i == 12)
				work = ((hi & 0x01) << 4) & lo;
			else if (i == 13)
				work = hi >> 1;
			byte digit = (byte)(work & mask);
			chars[charIdx] = _charMap[digit];
			work = work >> 5;

		var ret = new string(chars, 26 - numDigits, numDigits);
		return ret;

using System;
using System.Collections.Generic;
using NUnit.Framework;

public class UniqueIdGeneratorTest
public void GetInstanceTest()
var instance = UniqueIdGenerator.GetInstance();
Assert.AreSame(instance, UniqueIdGenerator.GetInstance());

public void GetNextTest()
var b1 = new byte[16];
for (int i = 0; i < 16; i++ ) b1[i] = 0; UniqueIdGenerator.GetInstance().GetNext(b1); Assert.That(Array.Exists(b1, b => b != 0)); // This could be false every billion years or so

public void GetBase32UniqueIdTest()
var b1 = new byte[16];
for (int i = 0; i < 16; i++) b1[i] = 0; string id = UniqueIdGenerator.GetInstance().GetBase32UniqueId(b1, 26); Assert.AreEqual(26, id.Length); Assert.AreEqual(new string('2', 26), id); b1 = new byte[] { 0xFF, 0xFF, 0xFF, 0xFF }; id = UniqueIdGenerator.GetInstance().GetBase32UniqueId(b1, 26); System.Diagnostics.Trace.WriteLine(id); Assert.AreEqual(26, id.Length); Assert.AreEqual("ZZZZZZ", id.Substring(20, 6)); id = UniqueIdGenerator.GetInstance().GetBase32UniqueId(b1, 6); System.Diagnostics.Trace.WriteLine(id); Assert.AreEqual(6, id.Length); Assert.AreEqual("ZZZZZZ", id); id = UniqueIdGenerator.GetInstance().GetBase32UniqueId(18); System.Diagnostics.Trace.WriteLine(id); Assert.AreEqual(18, id.Length); var id2 = UniqueIdGenerator.GetInstance().GetBase32UniqueId(18); System.Diagnostics.Trace.WriteLine(id2); Assert.AreEqual(18, id2.Length); Assert.AreNotEqual(id, id2); } [Test, Ignore] public void GetBase32UniqueIdDupeTest() { var alreadySeen = new Dictionary(1000000);
for (int i = 0; i < 100000000; i++) { var id = UniqueIdGenerator.GetInstance().GetBase32UniqueId(12); Assert.That(!alreadySeen.ContainsKey(id)); alreadySeen.Add(id, id); } } } [/sourcecode]

Expressions versus Delegates in LINQ to SQL Performance

Recently, I was working on a class that stored a delegate and later used that delegate to retreive a LINQ to SQL object from either the database or an EntitySet, depending on the situation. Unfortunately, the performance for the first case was terrible — I believe it was returning the entire table and then applying the delegate to each row in memory.

Here’s the relevant code (original version):

public abstract Func<D, bool> GetDbKeyQueryFunction(E entity);
D dbEntity;
if ( isRoot ) {
	dbEntity = context.GetTable<D>().SingleOrDefault(GetDbKeyQueryFunction(entity));
} else {
	dbEntity = DbParentSet.Invoke(dbParent).SingleOrDefault(GetDbKeyQueryFunction(entity));

Here’s a typical implementation of GetDbKeyQueryFunction:

public override Func<SomeTable, bool> GetDbKeyQueryFunction(SomeEntity entity) {
	return dbEntity => dbEntity.Id == entity.Id;

Now before I created this abstract class, I had specific code that used a (seemingly) identical lambda expression to retrieve a row from the database quite quickly:

int id = 11;
dataContext.SomeTable.SingleOrDefault(dbEntity => dbEntity.Id == id));

When I hovered over SingleOrDefault in the IDE, I noticed that there were two overloads, one that took Expression<Func> and another that took Func. On the surface, these don’t seem much different. In fact, the reason I ended up using Func in my abstract class was because Expression is abstract and I couldn’t just put new Expression() around my lambda expession.

In practice, the difference is huge, as I mentioned above regarding the performance. It appears that LINQ to SQL can optimize expressions but cannot optimize delegates. The tricky part is all I had to do was change:

public abstract Func<D, bool> GetDbKeyQueryFunction(E entity);


public abstract Expression<Func<D, bool>> GetDbKeyQueryFunction(E entity);

Surprisingly (to me, anyway), you don’t have to do anything in the implementing code other than modify the method signature to match. The body of the method remains unchanged, but like magic, the compiler is now creating an Expression instead of a Delegate for us.

Performance for the first case (querying the database) was back up to par. However, there was another minor hurdle to overcome.

public abstract Func<D, bool> GetDbKeyQueryFunction(E entity);
D dbEntity;
if ( isRoot ) {
	dbEntity = context.GetTable<D>().SingleOrDefault(GetDbKeyQueryFunction(entity));
} else {
	dbEntity = DbParentSet.Invoke(dbParent).SingleOrDefault(GetDbKeyQueryFunction(entity));

In the code above, DbParentSet.Invoke(dbParent) is returning an EntitySet instance. Interestingly enough, you cannot directly apply an Expression to an EntitySet, as I found documented here. However, you can compile an Expression, and in my scenario, the type of the value returned by Compile just happened to be Func (it all seems so obvious now, hehe). So here’s the final version of the code that works well for both scenarioes:

public abstract Expression<Func<D, bool>> GetDbKeyQueryFunction(E entity);
D dbEntity;
if ( isRoot ) {
	dbEntity = context.GetTable().SingleOrDefault(GetDbKeyQueryFunction(entity));
} else {
	dbEntity = DbParentSet.Invoke(dbParent).SingleOrDefault(GetDbKeyQueryFunction(entity).Compile());

So remember, always favor Expressions over Delegates when using LINQ. If you have to have a Delegate, you can always use Compile to turn your Expression into a Delegate as needed. But you can’t turn a Delegate into an Expression (that I’m aware of).

Posted in c#, LINQ, SQL. Tags: , , . 3 Comments »

Easily Create DataTables For Unit Tests

Ideally, you have a data access layer that only returns domain objects or data transfer objects. The key is real, strongly-typed objects. One of the many reasons for this is to make unit testing easier. Unfortunately, sometimes you are stuck working with code that uses datatables. I’ve recently written a function that takes most of the pain out of creating datatables for unit testing. This function and the code that calls it takes advantage of several new c# features: lamda expressions, object initializers, list initializers, and auto-implemented properties. It also uses generics and reflection.

The first thing that stinks about creating datatables from scratch is that you have to manually define the columns which is tedious. The second thing that stinks is that you can only add rows to the datatable using an object array which means you have to count commas to keep track of which column you are populating. Or you have to do something awkward like obj[datatable.Columns[“FieldName”].Ordinal] = “somevalue”. To get around this, the first step is to define the structure of our rows by creating a simple class, EG:

public class Person
	public string LastName { get; set; }
	public string FirstName { get; set; }
	public DateTime DateOfBirth { get; set; }
	public decimal Salary { get; set; }

Now we can call the method with a very convenient syntax that capitalizes on object and list initializers:

public DataTable GetPeople()
	return ListToTable(new List<Person> {
		new Person {
			LastName = "Opincar",
			FirstName = "John",
			DateOfBirth = new DateTime(1901, 1, 1),
			Salary = 250000.00M
		new Person {
			LastName = "Lincoln",
			FirstName = "Abe",
			DateOfBirth = new DateTime(1801, 1, 15),
			Salary = 1000.00M

If you’ve ever manually populated datatables you can really appreciate what a huge improvement this is.

Finally, we discuss the method itself. Using reflection, we can leverage the type information stored in Person to create our datatable columns in a generic method that takes a List as input and returns a datatable:

public DataTable ListToTable(List rows)
var dt = new DataTable();
var props = typeof(T).GetProperties();
Array.ForEach(props, p => dt.Columns.Add(p.Name, p.PropertyType));
foreach ( var r in rows )
object[] vals = new object[props.Length];
for (int idx = 0; idx < vals.Length; idx++) { vals[idx] = props[idx].GetValue(r, null); } dt.Rows.Add(vals); } return dt; } [/sourcecode] I hope you find this method as useful as I have.