Convert SqlCommand to T-SQL command

Question

I have an SqlCommand with parameters. Due to some external requirement (see PS), I need one T-SQL string instead, i.e., I need ... @parameter ... replaced by ... the-value-of-@parameter-encoded-correctly ... (e.g. O'Brien --> 'O''Brien', 02 Mar 2010 --> '20100302', 3.5 --> 3.5).

I know that I could home-brew such a solution quite easily, but correct escaping is tricky (make sure you get the right date and number formats, watch for quote signs in strings, etc.), and I guess that I'm not the only one needing this, so my question is:

Is there an existing solution for converting an SqlCommand with parameters into a single T-SQL statements with the parameters embedded and correctly escaped?

PS: We need to do that because we need to use the SqlCommand as a data source for an MS Access report... and "pass-though querydefs" in Access don't support parameterized SQLs.

PPS: I know that there are similar questions, but they all assume that SQL Server somehow does this conversion (@parameter --> some escaped string representing the value), so the answers ("SQL Server doesn't do that") do not apply here. I understand that there is no such conversion happening in SQL Server.

Answer 1

I think it is far clearer to write out the SQL query with the parameters and have a few DECLARE @p1 type; and SET @p1 = 'value'; lines before your primary SELECT query and leave the original query text in the SqlCommand instance unchanged. I have some example code which does this if you'd like to see that.

If you're really adamant about simply doing parameter value substitution within the query text, then find yourself a T-SQL grammar specific to the server you're talking to, parse the query until you find a parameter reference, and replace it with text according to the T-SQL grammar's escaping rules for strings.

EDIT:

Since I'm such a fantastic guy, I'll get you started on your way to formatting SQL parameters for output with proper escaping. This is woefully incomplete since it does not handle every single database type known to man and SQL Server, but it gets the job done for me in that it handles my most commonly-used types. As I said in my comment, simply escaping the single-quote character as two single-quote characters in succession seems to suffice for proper string-value escaping. You'll have to double and triple check the T-SQL grammars used by the various SQL Server versions to make sure this is good enough.

private static string FormatSqlValue(System.Data.Common.DbParameter prm)
{
    if (prm.Value == DBNull.Value) return "NULL";
    switch (prm.DbType)
    {
        case System.Data.DbType.Int32: return (prm.Value.ToString());
        case System.Data.DbType.String: return String.Format("'{0}'", ScrubSqlString((string)prm.Value));
        case System.Data.DbType.AnsiString: return String.Format("'{0}'", ScrubSqlString((string)prm.Value));
        case System.Data.DbType.Boolean: return ((bool)prm.Value ? "1" : "0");
        case System.Data.DbType.DateTime: return String.Format("'{0}'", prm.Value.ToString());
        case System.Data.DbType.DateTime2: return String.Format("'{0}'", prm.Value.ToString());
        case System.Data.DbType.Decimal: return (prm.Value.ToString());
        case System.Data.DbType.Guid: return String.Format("'{0}'", prm.Value.ToString());
        case System.Data.DbType.Double: return (prm.Value.ToString());
        case System.Data.DbType.Byte: return (prm.Value.ToString());
        // TODO: more conversions.
        default: return (prm.DbType.ToString());
    }
}

private static string FormatSqlValue(Type type, object value)
{
    if (value == null)
        return "NULL";
    // Handle Nullable<T> types:
    if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
    {
        // If the Nullabe<T> value has been found to have !HasValue, return "NULL":
        if (!(bool)type.GetProperty("HasValue").GetValue(value, null))
            return "NULL";
        // Try our best to format the underlying non-nullable value now:
        return FormatSqlValue(type.GetGenericArguments()[0], type.GetProperty("Value").GetValue(value, null));
    }
    if (type == typeof(Int32)) return value.ToString();
    if (type == typeof(String)) return String.Format("'{0}'", ScrubSqlString((string)value));
    if (type == typeof(Boolean)) return ((bool)value ? "1" : "0");
    if (type == typeof(DateTime)) return String.Format("'{0}'", value.ToString());
    if (type == typeof(Decimal)) return (value.ToString());
    if (type == typeof(Guid)) return String.Format("'{0}'", value.ToString());
    if (type == typeof(Double)) return (value.ToString());
    if (type == typeof(Byte)) return (value.ToString());
    // TODO: complete the mapping...
    return value.ToString();
}

private static string ScrubSqlString(string value)
{
    StringBuilder sb = new StringBuilder();
    int i = 0;
    while (i < value.Length)
    {
        if (value[i] == '\'')
        {
            sb.Append("\'\'");
            ++i;
        }
        else
        {
            sb.Append(value[i]);
            ++i;
        }
    }
    return sb.ToString();
}

private static string FormatSqlParameter(System.Data.Common.DbParameter prm)
{
    StringBuilder sbDecl = new StringBuilder();
    sbDecl.Append(prm.ParameterName);
    sbDecl.Append(' ');
    switch (prm.DbType)
    {
        case System.Data.DbType.Int32: sbDecl.Append("int"); break;
        // SQL does not like defining nvarchar(0).
        case System.Data.DbType.String: sbDecl.AppendFormat("nvarchar({0})", prm.Size == -1 ? "max" : prm.Size == 0 ? "1" : prm.Size.ToString()); break;
        // SQL does not like defining varchar(0).
        case System.Data.DbType.AnsiString: sbDecl.AppendFormat("varchar({0})", prm.Size == -1 ? "max" : prm.Size == 0 ? "1" : prm.Size.ToString()); break;
        case System.Data.DbType.Boolean: sbDecl.Append("bit"); break;
        case System.Data.DbType.DateTime: sbDecl.Append("datetime"); break;
        case System.Data.DbType.DateTime2: sbDecl.Append("datetime2"); break;
        case System.Data.DbType.Decimal: sbDecl.Append("decimal"); break;  // FIXME: no precision info in DbParameter!
        case System.Data.DbType.Guid: sbDecl.Append("uniqueidentifier"); break;
        case System.Data.DbType.Double: sbDecl.Append("double"); break;
        case System.Data.DbType.Byte: sbDecl.Append("tinyint"); break;
        // TODO: more conversions.
        default: sbDecl.Append(prm.DbType.ToString()); break;
    }
    return sbDecl.ToString();
}