Returning an Id from SQL Server

I found myself in a familiar situation recently:

• Create an object instance that represents a row in a database.
  
• The table must maintain an identity column that the users use to query the table.
  
• The instance will be created on the client side, but the id must come from the database.
  
• You may not use a GUID, because the customer refuses to use them.
  

I'm not sure exactly how I came upon this idea, but I thought it was interesting. It's either cool, absolutely horrid, or both.

Assume this table:

CREATE TABLE [dbo].[bars] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [bar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

This C# can be used to insert and return the identity:

SqlConnection connection = new SqlConnection("initial catalog=foo; data source=blah; uid=me; pwd=right");
SqlCommand command = new SqlCommand("insert into bars values ('some value') select @@identity",connection);
connection.Open();
int identity = int.Parse(command.ExecuteScalar().ToString());
connection.Close();

Drop me a line if you've successfully (or unsuccessfully) done something similar.

Flexibility Cost

One of the toughest challenges in teaching agile development is the YAGNI concept. Traditional development and BDUF teach you "the design is created before coding and testing takes place". In this environment the decision to implement data(base) driven rules must be made early. Unfortunately, this decision is often based on assumptions that turn out to be incorrect.

Currently, I'm working on an application where exactly this has happened. We have a business rule that will vary by state. The analysis done shows that the business rule will be the same for 35 states (thus leaving 15 exceptions). The obvious solution (to us) was to implement a Strategy Pattern.

The only states that currently needed to be implemented were the base case. We put the strategy structure in place in anticipation of the other states so that adding the other states after we were gone would be a very straight forward task for the client developers.

Upon completion, the client architect asked how we handled the task. He was clearly unhappy with our choice. He required that we provide reasons to code the business rule instead of placing it in a database where a business analyst could add new state logic. I'll list our reasons in order of importance.

1. Testability. Putting the business rule in code allows to easily write tests proving that for each state the proper calculation does occur.
2. Maintainability. All logic is contained in a small (strategy) class.
   
3. Performance. Less trips to the database increases performance.
   
4. Process. Currently, the BAs are not restricted to making changes in QA; therefore, they often make changes directly to production database tables. This is clearly a risk.

Not surprisingly, he did not agree, stating:

1. Testability: "You can test the rule coming back from the database as easily as you can test a rule in code." This is clearly untrue because you aren't testing the logic, but instead testing that the expected result of a database is correct. Since the database can be altered in production the tests become far less valuable.
2. Maintainability: "An application that allows BAs to simply make changes to a table is far more maintainable than forcing a developer to make the change." Also wrong. The application is more flexible, but less maintainable. Additionally, when the application breaks in production it's going to be harder for the developers to track down the source of the bug.
   
3. Process: "The current process does not allow production access." This was also untrue because in an earlier conversation it was stated that they needed to have it in a database to "allow for quick changes in production if a mistake had been made." The point that the code should be tested and the mistake should not make it to production had clearly been lost.

Unfortunately, we were forced into the inferior implementation because of the fear of implementing a new state. Will they ever know they were wrong? No, because the new implementation will allow for the (unnecessary) flexibility and the benefits of the correct implementation will never be noticed. Ignorance is bliss, but it comes at a cost.

NAnt exec: arg || commandline

NAnt contains an exec task that is used for executing a system command. The exec task is commonly used to execute osql, ruby scripts, simian, etc. Generally, when using exec you will need to pass arguments to the program you are executing. Looking at the documentation for NAnt it appears you have two options: arg or commandline.

Commandline is documented as: The command-line arguments for the program. The syntax is:

<exec program="ruby" commandline="script.rb arg1 arg2 arg3"/>

Using exec and commandline should be the same as executing the following statement at a command line.
$ ruby script.rb arg1 arg2 arg3

Arg is documented as: The command-line arguments for the external program. The syntax is:

<exec program="ruby">
 <arg value="script.rb"/>
 <arg value="arg1"/>
 <arg value="arg2"/>
 <arg value="arg3"/>
</exec>

Using exec and arg should be the same as executing the following statement at a command line.
$ ruby "script.rb" "arg1" "arg2" "arg3"

Obviously, the difference is that arg adds quotes around the arguments. This may seem like a small issue, but I've seen time wasted on this minor difference. The most common problematic scenario occurs when someone tries to combine multiple args into one arg tag.

<exec program="ruby">
 <arg value="script.rb arg1 arg2 arg3"/>
</exec>

The problem with the above example is that it is the same as executing the following statement at a command line.
$ ruby "script.rb arg1 arg2 arg3"

This won't work in a build and at the command line it would produce:
ruby: No such file or directory -- script.rb arg1 arg2 arg3 (LoadError)

Using commandline or arg is likely a matter of personal preference as long as you are educated on the differences.

Functional Testing using a DSL and NUnit

Selecting a functional test framework very much depends on the aspects of your project. Currently, I'm staffed on a project with the following characteristics.

• All the development to date has been back-end. (No UI)
• The functional tests need to be maintained by Business Analysts
• The application is written in C#
• The business analysts need to be able to run the tests locally
• The tests need to run as part of the build
• The wiki we are using cannot be used as the input for the tests
• We already had developer maintained functional tests written for NUnit
  

After identifying these and a few other points we narrowed our choices to NFit or rolling our own. Before we made the decision we spiked the level of effort it would take to roll our own.

Our spike consisted of defining a testing DSL and creating a parser to parse the DSL files and convert them into NUnit tests. The entire process looked like this:

1. A business analyst creates a test file that contains multiple tests written in our DSL.
2. The parser parses the DSL into NUnit tests written in C#.
3. The C# is compiled to a testing DLL.
4. The NUnit GUI is used to execute the tests contained in the testing DLL.

The DSL was defined based on the existing NUnit tests. For example the NUnit test:

[Test]

public void TestSomething()
{
 Transformer transformer = new Transformer();
 transformer.LegacyValue = "ASDF";
 Assert.AreEqual("42",transformer.ConvertedValue);
}

Could be expressed in our DSL as:

test
legacy_value = "ASDF"
expect "42"

The parser was written in Ruby because we wanted a lightweight solution that allowed for easy string parsing.

The ruby parser was run against a directory and created one large TestFixture class that contained each test specified in the DSL files. This step was added to the build before the compilation step. The the BAFunctionalTest class was compiled into a DLL and NUnit executed the tests in this DLL as part of the build.

Obviously, this DLL could be used with the NUnit GUI to allow the BAs to run their tests locally.

I'm sure we could have done things better, such as creating an internal Ruby DSL instead of an external one. We didn't actively make this decision, and had we chosen this solution I'm sure we would have moved in that direction. It was a fun spike and hopefully I'll actually get to try it out on my next project.

In the end, the decision was made to go with NFit because the team wasn't very familiar with Ruby and they had used Fit frameworks of a different flavor in the past. Looking back, I think that was the biggest mistake we made on the project.

Simian IgnoreBlocks

Simian contains the feature ignoreBlocks. The documentation on the Simian website states that ignoreBlocks:

Ignores all lines between specified START/END markers

IgnoreBlocks is perfect for ignoring things such as the entire AssemblyInfo class or the Dispose method that is generated for each Form that Visual Studio creates.

To use ignoreBlocks add start and end markers to your code.

#region ignoreBlock - Dispose(bool disposing)
public void Dispose(bool disposing)
{
  ...
}
#endregion

Then add ignoreBlocks to your Simian configuration file.

-ignoreBlocks=#region ignoreBlock:#endregion

Simian should now be correctly configured to ignore all blocks of code in regions named ignoreBlock.

Documentation for ignoreBlocks is very limited. Here are a few things I learned along the way.

• In version 2.2.7 and below ignoreBlocks does not work correctly with #regions in C#.
  
• Simian ignores all comment lines; therefore, comments can not be used as start and or end markers.
  
• If you use a configuration file do not add quotes around the start and end markers.
  
• If you specify your options at the command line put quotes around the start and end markers. (i.e. -ignoreBlocks="#region ignoreBlock:#endregion")
  
• Simon Harris is very responsive and kind. If you have any questions about Simian do not hesitate to contact him.

Improving Brittle Functional Tests

Functional tests often rely on an external resource existing in a well known state. A test could simply validate that a class returns valid data from the database. For end-to-end testing these tests are very valuable. Unfortunately, these end-to-end tests are also brittle if done incorrectly. Brittle tests often lead to builds breaking unexpectedly. These are the worst types of broken builds. The developer who checked in knows that their change did not cause the build to break. Because their change was not the cause of the broken build they feel unmotivated to fix the build. Of course, they have to fix the build, but are unhappy with the level of effort needed to track down the cause of the error. This pain is not quickly forgotten and often leads to one or both of two outcomes.

• Broken builds become common and are ignored. As long as the build occasionally passes everyone writes off the broken builds as a data problem. Much of the value of the build box is lost at this point.
  
• Developers check in less. Often a pair will work an entire day without checking in any changes. The pair understands that fixing the build will be a painful and time-consuming process and for efficiency they choose to endure this task only once a day. This leads to a larger number of code conflicts and general integration issues. Productivity will be lost.

Fortunately, there are alternatives to assuming a resource is in a well known state.

Create a database that is only used during the build. Ensure this database is in a well known state by dropping objects, adding objects, and loading data with each build. If you are using SQL Server, osql can be used to execute SQL scripts that perform these tasks.

If the database is a shared resource you may not have the ability to drop, add, and delete any time you like. In this scenario, everyone requiring access must agree on a time where the data can be reset to a well known state. The reset can be performed by an additional build that is created to reset the database and immediately run all functional tests that depend on the well known state. This option is less favorable because it requires that the tests be run on a timed interval instead of after each change. However, this negative can be mitigated by creating an In Memory Database that mimics the behavior of the real database. The test suite that runs following each change can include tests that act as end-to-end tests but use the In Memory Database.

Durable functional tests are absolutely necessary in a well running agile environment.