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" )
      ...
}

No comments: