Auditing ExtendedProperties

So yesterday I was coding a User Story about reporting audit information for the project I am working on.  This project basically extends the ERP application called Macola (don’t get me started). What we are building is an extended properties application and we are auditing each change to an extended property for SOX compliance.

So we are using a class called (funny enough) ExtendedProperty. We have a collection of these objects to fill our grid using a List(of ExtendedProperty).  We were auditing each save in the Save method of each individual ExtendProperty.  This created a ton of audit entries in the database. We decided to combine these into a batch audit at the collection level to mitigate the number of entries and use the xml data type in SQL Server.

So our Audit objects began to take shape and now look like this:

An AuditEntry has a one to many relationship to an AuditChange. And an AuditChange has a many to many relationship to AuditColumn as well as AuditKey. Each of the objects overrides the .ToString function to return xml to be stored in the database.

The metadata in AuditEntry is in separate columns in the database and AuditChange, AuditColumn and AuditKey are all stored in a single xml column.  This gives us great flexibility in what we can audit and how much space it takes up.

How this relates to an Extended Property is that each property is responsible for its own audit unless it is in a collection of changes.  So we had to change how we accessed the List(of ExtendedProperty) in the containing class. We could not have a List anymore because it became difficult to act on that list as a whole. This caused us to create an ExtendedProperties collection class that inherits from List(of ExtendedProperty). This class gives us the ability to act on the collection as a whole but still allows us the functionality of a List(of T).

The results of this audit can be returned flattened using xquery in SQL Server.  It can be a little complicated when trying to compare values (sql:parameter(“parametername”) is your friend in xquery). But it makes life easier when batching audit entries.

Cool Visual Studio 2010 feature

As an aside, while I was coding the audit classes above I discovered a GREAT feature in VS2010. First I created a file for the class, then I started coding the public properties of the class. I did not create the constructor of the class yet.  I went to a calling class and instantiated this new class and put the arguments in the call.  There appeared a blue squiggly line below the code with the little red block in the bottom right.

When I click on the little red block it tells me it can create the constructor in the class.

So I tell it to go ahead and do that by clicking on the link. Now keep in mind I had already created the public properties for this class that will expose the arguments in the constructor.  So what did Visual Studio do?  It knew which arguments to assign to the private class variables automatically and wrote the constructor for me and assigned the arguments to the variables.

Sub New(ByVal userName As String, ByVal propertyName As String, ByVal operation As String, ByVal originalValue As String, ByVal newValue As String, ByVal timeStamp As Date)

   ' TODO: Complete member initialization 
   _userName = userName
   _propertyName = propertyName
   _operation = operation
   _originalValue = originalValue
   _newValue = newValue
   _timeStamp = timeStamp

End Sub

Cool!

till next time…

Technorati Tags: Visual Studio,User Stories,Audit

T-SQL Conditional WHERE Clause

I started the day today with great ambition. I am tasked with removing hard coded SQL and change to use stored procedures. I made it through several stored procedures and was fat, dumb, and happy. Until… I was stumped with a problem I was unprepared to solve. The problem occurred in converting a bunch of case statements in the hard coded SQL.  The code looked something like this (changed for brevity)

Dim strSQL As New StringBuilder
strSQL.Append("SELECT TOP 300 cus_no ,cus_name, search_name, slspsn_no")
strSQL.Append("FROM MyTable")
If myValue1 IsNot Nothing Then
     strSQL.AppendFormat("WHERE value1 = '{0}' ", myValue1)
ElseIf myValue2 Nothing Then
      strSQL.AppendFormat("WHERE value2 = '{0}' ", myValue2)
ElseIf myValue3 IsNot Nothing Then
      strSQL.AppendFormat("WHERE value3 LIKE '{0}%' ", myValue2)
End If
If accountType > 0 Then
      strSQL.AppendFormat("AND value1 {0} IN (SELECT thisValue FROM MySecondTable", IIf(accountType = 1, "", "NOT"))
End If

So I decided to tackle the first if block first. I googled the hell out of conditional where clauses in T-SQL. After a little bit of reading and experimenting I finally decided to go with the COALESCE solution.  this was tricky because if a field is NULL and the parameter is NULL then COALESCE will not equal each other so for the three conditions in the first if block I came up with the following:

WHERE COALESCE(value1,' ') = COALESCE(@myValue1, COALESCE(value1,' '))
AND COALESCE(value2,' ') = COALESCE(@myValue2, COALESCE(value2,' '))
AND COALESCE(value3,' ') LIKE COALESCE(@myValue3, COALESCE(value3,' '))

This had the desired affect. Notice the first and third COALESCE in each line has the space.  This is how you account for the NULL = NULL situation.

Now on to what I consider the harder of the two types of conditional where clauses represented here. If accountType is supplied and it is equal to one then check to see if it is IN the list returned from the SELECT. If accountType is supplied and is greater than one then it should not be IN the SELECT statement. if accountType is not supplied then don’t bother adding it to the where clause. I found some great examples of bit comparisons in the google search but they all used equals instead of IN and I couldn’t get IN to work with the case statements but I combined a couple of good items into this solution:

AND ((@AccountType=1 AND value1 IN (SELECT thisValue FROM mySecondTable))
OR (@AccountType>1 AND value1 NOT IN(SELECT thisValue FROM mySecondTable))
OR (@AccountType<1 AND value1 = value1))

This covers all possible scenarios. Let me know if you have another way to accomplish this.

Till next time…

Technorati Tags: .NET,T-SQL,Conditional WHERE Clause,stored procedures,COALESCE

Back In Time: NUnit Again

So I have been tasked with implementing unit testing in some legacy code that uses Visual Studio 2005.  For various reasons this code base is not ready to be converted to the newest version of .Net.  That being said, it is a sizable chunk of code and really needs to be refactored and tested.

There are no unit tests for this codebase. So now is the time to add them.  Since we are past the Test Driven Development option we are now going to try Defect Driven Testing.  Every time a defect is found or reported, we will write a test that proves it is a defect and then green the test to fix the defect. We will also try to refactor areas around the code in which the defect was found. But before we refactor we will write tests so we are assured not to have changed what the code was doing.

Visual Studio 2005 does not have the MS Testing framework unless you have purchased the "Test" version of Visual Studio.  VS 2008 and forward included the testing framework by default.  So I downloaded Nunit.

Now I haven't used Nunit since 2008 so I wanted to make sure I had everything working.  I tried running the sample tests that were downloaded with the installer. I opened the samples solution and keyed ctrl-shft-b to build the solution. Fail.

I thought, what? This should work out of the box.  Well, it didn't.  The nunit.framework reference in the project had the little yellow warning triangle  because it couldn't find the assembly.  So I removed the reverence and added it again. Voila! It compiled.  Great, moving on.

Then I added Nunit as an external tool like I remembered I had to do to get a quick link in Visual Studio.  So I clicked Tools/External Tools menu and it brought up the External Tools form.  I clicked Add to add a new tool and added the path and the name of the tool.  Then I forgot what to put in the Arguments and Initial Directory fields so a quick Google search gave me the answer. Or so I thought. 

Several links on Google said to set Arguments to $(TargetPath) and Initial Directory to $(TargetDir).  This did not work.  So what does a guy do who can’t get a software package to work? Look at the documentation, of course!

Well right there in from of my eyes, the Nunit documentation says:

Running From Within Visual Studio

The most convenient way to do this is to set up a custom tool entry specifying the path to NUnit as the command. For a VS2003 C# project, you can use $(TargetPath) for the arguments and $(TargetDir) for the initial directory.

With Visual Studio VS2005 this becomes a bit harder, because that release changed the meaning of the 'Target' macros so they now point to the intermediate 'obj' directories rather than the final output in one of the 'bin' directories. Here are some alternatives that work in both versions:

• $(ProjectDir)$(ProjectFileName) to open the VS Project rather than the assembly. If you use this approach, be sure to rename your config file accordingly and put it in the same directory as the VS project file.

So I did changed the Argument and Initial Directory settings to be $(ProjectDir)$(ProjectFileName) and it all worked!

Interesting how things get remembered.  Someone else must have figured this out at my place of employment back in 2008.

Till next time...

Technorati Tags: Unit Testing,Visual Studio,NUnit,Tools

Strong Management in Agile Development Deflect and Prevent Distractions

I remember in High School listening to Bill Cosby when he did the bit on parenting.  How he compared parenting to being a hockey goalie, I think.  The parent needed to deflect one tantrum after another or something like that.  It has been a long time since I listened to a Bill Cosby tape.  That was funny stuff back in the 70’s and 80’s.

Little did I know then that what Cosby described would be applicable to leading and managing teams. A good leader can be like a hockey goalie with his team as the net and all the distractions as the puck. He does his best to prevent the distractions from causing in-efficiencies on his team.

What do I mean when I say distractions? This can be a very broad range of items but in the software development world it can generally include things like unnecessary meetings, office politics, feature and scope creep, pet projects that aren’t a priority to the business.  There are many more but these are a few I have observed over the last few years.

In an agile development shop teams are supposed to be communicating and interacting directly with each other and with stakeholders on a daily basis. This means someone from the business needs to actively participate in the process of developing the software and have the authority to make decisions regarding that software. In some instances the stakeholders are represented by a single person. This person should have the authority to make decisions and communicate with the team when ever they feel the need. It is management’s responsibility to give this person the tools and resources to be as efficient as possible in this communication.

Some may think this contradicts the deflection theory because “why on earth would a business person talk directly to a developer?” (Yeah – I heard that), but I disagree. The key to deflection is the unnecessary bit:  things that are not required to move the project forward.  In my view this communication is required. Not only is it not a distraction but it is, if done right, one of the single most important aspects of software development.

This is where management plays a key role in the agile world. Sure the teams should be self-directing and be made up of all that are required to push the product to the customer. But management is important here because they hold the bigger picture in hand. A good manager will work with the development team and the stakeholders to be sure there is proper direction and focus. He will prevent any distractions that may result in loss of velocity. He will give guidance when there is a conflict in the priority of stories. He will prevent the office politics from interfering with the movement of the team.  He does all these things so the team doesn’t have to; so the team can do what it does best: produce software that meets the customers prioritized requirements.

Thanks to Bill Cosby I enjoyed some summer nights with friends around the tape deck in my youth. I didn’t know, at the time, that my career would have some basis in his comedy.

Till next time…

Technorati Tags: Management,Agile Development

I am addicted to Woot!

last week I discovered something that I am not proud to say has me addicted. www.woot.com! It is amazing! Woot offers one item a day for sale at some great % off the price you would pay at some other online retailer. The sale ends when they run out of merchandise or they reach the end of the day. (Midnight)

I haven’t bought anything yet because this is the reason Dave Ramsey stays in business. But one day Woot will offer something that I am actually in need of and I will buy it! I check it everyday. I have signed up for the daily email and I have the RSS feed setup on my reader.

Today was something special at woot that happens every once in a while; a Woot Off! This basically means they offer one item at a time until it runs out then offer another and another and another.  So far my count is 12 items today. Only one tempted me, but I remembered Dave Ramsey. I also could hear my wife’s voice in the back of my head saying “That is not on the Ramsey Plan!”

So my addiction can only go so far. I can look but cannot touch. But… I want one!

Till next time…

Technorati Tags: woot.com,dave ramsey,addicted

New Buzzword in Software Development–Craftsmanship

9PAUXZYJ7299

There is a new buzzword in the Software development industry.  This buzzword is “Craftsmanship”  There is a Manifesto. There is a conference SCNA. There is a website.  There is a new Academy. And there are many followers, one of whom I had the pleasure to be trained by back in 2006, Uncle Bob Martin, and one I have lunch with every now and then, Jared Richardson.

This is a new buzzword but it is not a new word to the industry. From everything I can gather it originated in Steve McConnell’s Book Code Complete: A Practical Handbook of Software Construction.  In this book, (which every software developer in the world should read) Steve talks about the construction metaphor as it relates to software development. This linking of craftsman in construction to craftsman in software development is not a new thing.

What is new? Well, what is new is the pervasiveness of the word as it applies to software development.  Software engineers are beginning to call themselves craftsman. The connotation of good craftsmanship is something that we all can strive for.  It is a belief that we need to take some pride in our work and always do it to the best of our ability and if we discover that we made a mistake then we own it and fix it.  This notion is spreading amongst our community at a rapid rate. 

The community also champions many extreme programming practices like pair programming and test driven development. These are practices that enhance our craftsmanship while developing solutions to problems. I am very pleased that this word is becoming a part of our nomenclature. I have not always considered my work as full of craftsmanship, but you better believe I strive to fill it to the brim now.

Till next time…

Technorati Tags: Software Craftsmanship,Agile,SCNA