Some time ago, I was working on a project that needed to work with users as a hierarchy.  The hierarchy was similar to what you would see in typical organizational chart and the users were organized by who they worked for and who worked for them.  As part of one of the requirements for the system, I needed to be able to retrieve from the database any particular user and all users who were placed beneath them in the chart, from their position in it all the way to the bottom.

To store the relationship between all the users, I created a self referencing table that contained user_id and managing_user_id columns.  The idea was that the record would contain the id of a user and also the id of the user over them.  If the user was at the top of the hierarchy, the managing_user_id column would be null.

Obviously, getting the data out of the table in the way I needed it could best be done with recursion.  I needed to get the user, all records that pointed to that user as the managing user...all the records that pointed to each of those records and so on.  I was wondering how to do this without making multiple calls to the database, however, because I could see the number of calls being astronomical for users near the top of the hierarchy.

Then I ran across a technique whereby I could use a CTE in a  recursive way.  The code looked something like this.

The key to this technique is in the fact that the second query in the CTE references the entire CTE in its FROM clause.  The net effect is that the CTE is executed and for each row in the result, the CTE is executed again and so on until there are no rows in the result set.

I've known about it and handled it well for years.  You know...when you have a string with a single quote embedded in it and you need to insert the string into a database column.  For the longest time, I simply replaced any quotes with two quotes and all was well.  ADO.Net was smarter than I gave it credit for, however, and it apparently hung me up.

 I've been working on a brand new client-server application for the past several months.  I created the dal that was to be used by the entire application and it had been working well.  We just started allowing some user acceptance testing and it happened.  A user keyed in a string with an apostrophe in it and the application thre an exception.  "No problem", I thought, "I'll just replace any quotes with two quotes and I'm on my way".

One of the developers on this project approached me,  shortly after,  and pointed out that quotes I was doubling in the dal were, in fact, being saved in the database as double quotes.  Huh?  After two hours of debugging and tweaking I figured out the problem.  I found a post on Scott Gutherie's blog that pointed out that the SqlParameter class automatically takes care of escaping single quotes.   So why was it seemingly ignoring my quotes?

Turns out that the problem laid with the creation of the actual parameters.  I had created an interface and my Dal class implemented it.  Besides the other benefits to ding this, I wanted to be able to write specific implementations for different databases and be able to switch them out, if needed, without changing any of the application code.  So, necessarily, all the application code was written against the interface and not the specific dal class.  A side effect of this was that the code that called into the dal could not create specific parameter clases such as SqlParameter, but rather it asked the dal to create a parameter specific to the database the class was written for, and the parameter was stored in a variable of type  IDataParameter.

Long story short, the dal contained a method called CreateParameter that looked like this

Note that this method does not specify a DBType for the parameter.  Apparently, the lack of a type confused the SqlParameter class.  I modified the dal to check each parameter being passed in to see if it was a string type.  If it was, I manually set it to a DBType of Varchar.  This worked, in my case, because our standard was to use varchars for all string data types unless a char made more sense.  At any rate, this modification allowed me to remove the code that manually escaped the single quote.  The SqlParameter class now had all it needed to handle the situation on its own.