How-to: get int value via ADO.net SqlDataReader using column name

Based on Sam Holder’s answer at https://stackoverflow.com/questions/7388475/reading-int-values-from-sqldatareader/54296026, just contributed an extension method for fetching Int32 values via ADO.net’s SqlDataReader, without jumping through hoops (aka first fetch column ordinal [number] by name, then fetching the int value passing the column ordinal).

Would be nice if Microsoft was providing such things out of the box.

namespace adonet.extensions
{
  public static class AdonetExt
  {
    public static int GetInt32(this SqlDataReader reader, string columnName)
    {
      return reader.GetInt32(reader.GetOrdinal(columnName));
    }
  }
}

and use it like this

using adonet.extensions;

//…

int farmsize = reader.GetInt32("farmsize");

assuming there is no GetInt32(string) already in SqlDataReader – if there is any, just use some other method name instead

Getting resulting SQL string from parametric ADO.net SqlCommand

Microsoft is suggesting to always use Named parameters with SqlCommands for security reasons (to avoid SQL injection exploits), but they haven’t thought of providing a property at SqlCommand to give you back the result SQL so that you can have your app log it without resorting to SQL Server for that logging.

Found a solution at CommandAsSQL project on GitHub and the discussion that brought it to life at https://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object/

That solution was only for the case where the type of the SqlCommand was StoredProcedure, but needed to cover non-Stored procedures too (aka type Text of SqlCommand), so I augmented that library with this logic:

    private static void CommandAsSql_Text(this SqlCommand command, 
                                          System.Text.StringBuilder sql)
    {
        string query = command.CommandText;

        foreach (SqlParameter p in command.Parameters)
            query = Regex.Replace(query, 
               "\\B" + p.ParameterName + "\\b", p.ParameterValueForSQL());

        sql.AppendLine(query);
    }

The pull request is at: https://github.com/jphellemons/CommandAsSql/pull/3/commits/527d696dc6055c5bcf858b9700b83dc863f04896

The Regex idea was based on @stambikk’s and EvZ’s comments at that StackOverflow thread and the "Update:" section of https://stackoverflow.com/a/2544661/903783 that mentions "negative look-behind assertion".

The use of \B instead of \b for word boundary detection at the start of the regular expression is because the p.parameterName will always start with a "@" which is not a word character.

Note that ParameterValueForSQL() is an extension method defined at the CommandAsSql library to handle issues like single-quoting string parameter values etc. It may have a bug with its date handling currently though, will submit an issue on that

Btw, other promising piece of code is at https://github.com/jeroenpot/SqlHelper/blob/master/Source/Mirabeau.MsSql.Library/SqlGenerator.cs (mentioned at an answer in this thread). Probably could merge code from CommandAsSql and SqlGenerator if one finds something not working at one or the other.