Wednesday, October 14, 2009

A Master Programmer

You can say that you are just a good programmer when you write a computer program for a year.

You can say that you are an experienced programmer when you first write a computer program for a year, then you release it to your clients and then you fix bugs and develop the program for next five years.

You can say that you are a true master programmer when you first write a computer program for a year, then you release it to your clients, then you fix bugs and develop the program for next five years, then you screw something so badly that the version you release makes some huge, ultimate damage to the clients' important data and then you have to release yet another version which has to somehow magically undo the damage you have made with the previous version.

Are you a master programmer yet?

Thursday, October 8, 2009

Breaking backward compatibility vs SqlDataReader - rematch

Few weeks ago I wrote about an ugly backward compatibility break in Microsoft SQL Server 2008. So how to write a clean code which works correctly on both 2005 and 2008 and deals with the sp_helpuser issue described in my previous post?

Let's assume that the original code is as follows:

 
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_helpuser";
 
conn.Open();
 
SqlDataReader r = cmd.ExecuteReader();
 
while ( r.Read() )
{
    // throws an exception on SQL Server 2008
    // since GroupName has been renamed to RoleName
    if ( (string)r["GroupName"] ) == "anyvalue" )
       ...
}    

The subtle side issue here is that it's not easy to determine whether a column is or is not available in SqlDataReader. You can try to read a value and catch an exception but this is just slow. However, another, not-so-obvious method has been proposed - please take a look here.


Let's just then add two auxiliary methods:



private bool columnExists( SqlDataReader reader, string columnName )
{
    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
    return (reader.GetSchemaTable().DefaultView.Count > 0);
}
 
private string GetGroupRoleName( SqlDataReader reader )
{
    if ( columnExists( reader, "GroupName" ) )
        return (string)reader["GroupName"];
    if ( columnExists( reader, "RoleName" ) )
        return (string)reader["RoleName"];
 
    /* return something unique */
    return Guid.NewGuid().ToString();
}

and change the original code to:



SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_helpuser";
 
conn.Open();
 
SqlDataReader r = cmd.ExecuteReader();
 
while ( r.Read() )
{
   // no exception, works on both 2005 and 2008
   if ( GetGroupRoleName( r ) == "anyvalue" )
      ...
}