Enterprise Etc Adventures and Insights of a .Net Developer

As noted in a former post, I implemented a change order system using Windows Workflow.  Recently, the system was deployed for User Acceptance Testing and odd things began to happen.  The system is using both tracking and persistence services and what we observed in the Sql Profiler was that the Sql to insert the persistence record was never making it to the database, but the tracking was working fine.

I implemented a handler for the ServicesExceptionNotHandled event on the WorkflowRuntime object, but nothing was being raised.  After a couple of hours poking and prodding, I finally linked the problem to the windows firewall.  If the firewall was turned off, everything worked as expected.  I spent some time researching the problem and found a couple of posts on the Microsoft Forums here and here that discuss the SharedConnectionWorkflowTransactionService.  Apparently, when the persistence and tracking data are in two different databases, the DTC gets involved any updates to the data and the firewall was blocking the DTC traffic. 

As mentioned in one of these posts, I combined the persistence and tracking databases into one, and added the SharedConnectionWorkflowTransactionService to runtime when I started up.  The combination of these actions removed the dependency on the DTC and all is working fine, now. 

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 recently had the chance to immerse myself in doing CLR development on Sql Server 2005.  The project I'm working on requires some complex pricing algorithms for varying combinations of products and doing the calculations in a "set-based" way just didn't feel right.

I needed to write several closely related stored procedures and did so in a single project.  When you create each procedure, Visual Studio creates a separate file for each procedure, but the files declare the class using the partial keyword which means that each procedure is actually just a static (shared) method on a single class.  As work progressed, I saw a number of opportunities to reuse some of the methods I had written across procedures.  This was easy enough since all the procs were actually in the same class...even private members we accessible from all the procedures. 

At some point, however, I realized that I needed to call one procedure from another.  This presented a problem, though.  I typically started all my procedure like this...

    . . . .

So, at the entry point of the first procedure, I opened a Context Connection and as soon as I called into the second proc, it tried to create another connection.  Since only one context connection is allowed per call, the attempt to open the second one failed.  I could, of course, call into the second procedure by creating a SqlCommand and executing it, but that feels dirty considering that both procs are actually in the same class.

So, after some thought, I determined a pattern that I intend to use from now on in such situations.  For all my CLR procs, the method that is the entry point for the procedure will be structured like this...

Now, I can call the code as a stored procedure (through up_DoWork), which is its primary purpose, and also call into the whole of the proc's functionality (DoWork) while passing in a context connection created in another proc.

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.

OK, I admit it.  When the Xml support first started showing up in Sql Server, I was a skeptic.  I wondered why anyone would want to do all that parsing and string manipulation on the database tier.  It made me wince to think about it.  So now, a few years later, and I'm wondering what I ever did without it.  Especially since the Xml data type has been added to Sql Server 2005.

As an example of its usefulness, consider a recent situation I found myself in.  I had a collection of objects that represented changed that needed to be made to the state in the database.  I could have iterated through the collection and called a stored procedure for each object and applied the change sets one at a time, but I tend to try to make any call across the network as chunky as possible and thought how nice it would be if I could send the entire collection of changes to the database at once.  It's entirely possible to do just that with the Xml data type.

First, I serialized each of the objects into Xml and gathered the individually object's state into a single Xml document.  As an illustration, let's suppose the Xml looked like this.  

I can pass this xml into a stored procedure as a string where the proc takes a single parameter of type Xml.

Inside the stored procedure I can parse through the Xml and place the data it contains into a temporary table that I can use any way I like.

Starting with the FROM clause, I've basically specified an Xpath expression to the level within the XmlDocument which corresponds with the level at which my "rows" are defined and I'm saying that each element returned by the Xpath expression is to be treated as a row and each element below that level should be treated as a column in that row.  In the select statement, I'm specifying each column by telling Sql Server the name of the element within the row I want the value from and I'm specifying the data type to cast the data as.

As I said before, I'm pulling the data out of the Xml into a temporary table so that I can run set-based Sql against the data to perform the updates I needed.