Posts Tagged ‘’

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

Based on Sam Holder’s answer at, just contributed an extension method for fetching Int32 values via’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 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

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:

The Regex idea was based on @stambikk’s and EvZ’s comments at that StackOverflow thread and the "Update:" section of 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 (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.

%d bloggers like this: