Archive

Posts Tagged ‘SQL’

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.

Advertisements
%d bloggers like this: