从SqlCommand对象获取生成的SQL语句?

我有以下代码:

Using cmd As SqlCommand = Connection.CreateCommand
    cmd.CommandText = "UPDATE someTable SET Value = @Value"
    cmd.CommandText &= " WHERE Id = @Id"
    cmd.Parameters.AddWithValue("@Id", 1234)
    cmd.Parameters.AddWithValue("@Value", "myValue")
    cmd.ExecuteNonQuery
End Using

我想知道是否有任何方法来获得最终的SQL语句作为字符串，它应该看起来像这样:

UPDATE someTable SET Value = "myValue" WHERE Id = 1234

如果有人想知道我为什么要这么做

用于记录(失败的)语句可以将其复制粘贴到企业管理器中进行测试

当前回答

也需要覆盖非存储过程，所以我用以下逻辑扩充了CommandAsSql库(见上面@Flapper的回答下的评论):

    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()); //the first one is \B, the 2nd one is \b, since ParameterName starts with @ which is a non-word character in RegEx (see https://stackoverflow.com/a/2544661)

        sql.AppendLine(query);
    }

pull请求在: https://github.com/jphellemons/CommandAsSql/pull/3/commits/527d696dc6055c5bcf858b9700b83dc863f04896

Regex的想法是基于@stambikk和EvZ上面的评论，以及https://stackoverflow.com/a/2544661/903783的“更新:”部分提到了“消极的事后判断”。在正则表达式的开头使用\B而不是\B来进行单词边界检测是因为p.parameterName总是以“@”开头，而“@”不是单词字符。

注意ParameterValueForSQL()是CommandAsSql库中定义的一个扩展方法，用于处理单引号字符串参数值等问题。

2018-09-19 15:58:06

其他回答

为了记录日志，恐怕没有更好的方法了，只能自己构造字符串:

string query = cmd.CommandText;

foreach (SqlParameter p in cmd.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}

2008-11-05 14:24:32

sql命令查询将通过exec sp_executesql执行，所以这里有另一种方法来获取语句作为字符串(SqlCommand扩展方法):

public static string ToSqlStatement(this SqlCommand cmd)
{
    return $@"EXECUTE sp_executesql N'{cmd.CommandText.Replace("'", "''")}'{cmd.Parameters.ToSqlParameters()}";
}

private static string ToSqlParameters(this SqlParameterCollection col)
{
    if (col.Count == 0)
        return string.Empty;
    var parameters = new List<string>();
    var parameterValues = new List<string>();
    foreach (SqlParameter param in col)
    {
        parameters.Add($"{param.ParameterName}{param.ToSqlParameterType()}");
        parameterValues.Add($"{param.ParameterName} = {param.ToSqlParameterValue()}");
    }
    return $",N\'{string.Join(",", parameters)}\',{string.Join(",", parameterValues)}";
}

private static object ToSqlParameterType(this SqlParameter param)
{
    var paramDbType = param.SqlDbType.ToString().ToLower();
    if (param.Precision != 0 && param.Scale != 0)
        return $"{paramDbType}({param.Precision},{param.Scale})";
    if (param.Precision != 0)
        return $"{paramDbType}({param.Precision})";
    switch (param.SqlDbType)
    {
        case SqlDbType.VarChar:
        case SqlDbType.NVarChar:
            string s = param.SqlValue?.ToString() ?? string.Empty;
            return paramDbType + (s.Length > 0 ? $"({s.Length})" : string.Empty);
        default:
            return paramDbType;
    }
}

private static string ToSqlParameterValue(this SqlParameter param)
{
    switch (param.SqlDbType)
    {
        case SqlDbType.Char:
        case SqlDbType.Date:
        case SqlDbType.DateTime:
        case SqlDbType.DateTime2:
        case SqlDbType.DateTimeOffset:
        case SqlDbType.NChar:
        case SqlDbType.NText:
        case SqlDbType.NVarChar:
        case SqlDbType.Text:
        case SqlDbType.Time:
        case SqlDbType.VarChar:
        case SqlDbType.Xml:
            return $"\'{param.SqlValue.ToString().Replace("'", "''")}\'";
        case SqlDbType.Bit:
            return param.SqlValue.ToBooleanOrDefault() ? "1" : "0";
        default:
            return param.SqlValue.ToString().Replace("'", "''");
    }
}

public static bool ToBooleanOrDefault(this object o, bool defaultValue = false)
{
    if (o == null)
        return defaultValue;
    string value = o.ToString().ToLower();
    switch (value)
    {
        case "yes":
        case "true":
        case "ok":
        case "y":
            return true;
        case "no":
        case "false":
        case "n":
            return false;
        default:
            bool b;
            if (bool.TryParse(o.ToString(), out b))
                return b;
            break;
    }
    return defaultValue;
}

2016-12-06 10:43:40

我的解决方案:

public static class DbHelper
{
    public static string ToString(this DbParameterCollection parameters, string sqlQuery)
    {
        return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
    }
}

2016-06-15 18:12:45

正如@pkExec和@Alok提到的，使用Replace在100%的情况下都不能工作。这是我在我们的DAL中使用的解决方案，它使用RegExp只“匹配整个单词”并正确格式化数据类型。因此，生成的SQL可以直接在MySQL Workbench(或SQLSMS等…)中测试:)

(根据使用的DBMS替换MySQLHelper.EscapeString()函数)

Dim query As String = cmd.CommandText
query = query.Replace("SET", "SET" & vbNewLine)
query = query.Replace("WHERE", vbNewLine & "WHERE")
query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
If query.Contains("UNION ALL") Then
    query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
ElseIf query.Contains("UNION DISTINCT") Then
    query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
Else
    query = query.Replace("UNION", vbNewLine & "UNION" & vbNewLine)
End If

For Each par In cmd.Parameters
    If par.Value Is Nothing OrElse IsDBNull(par.Value) Then
        query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "NULL")
    ElseIf TypeOf par.Value Is Date Then
        query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
    ElseIf TypeOf par.Value Is TimeSpan Then
        query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & par.Value.ToString & "'")
    ElseIf TypeOf par.Value Is Double Or TypeOf par.Value Is Decimal Or TypeOf par.Value Is Single Then
        query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", Replace(par.Value.ToString, ",", "."))
    ElseIf TypeOf par.Value Is Integer Or TypeOf par.Value Is UInteger Or TypeOf par.Value Is Long Or TypeOf par.Value Is ULong Then
        query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", par.Value.ToString)
    Else
        query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
    End If
Next

例子:

SELECT * FROM order WHERE order_status = @order_status AND order_date = @order_date

将生成:

SELECT * FROM order WHERE order_status = 'C' AND order_date = '2015-01-01 00:00:00'

2015-07-15 13:02:35

一个衬套:

string.Join(",", from SqlParameter p in cmd.Parameters select p.ToString())

2017-02-22 17:34:22

从SqlCommand对象获取生成的SQL语句?

推荐文章

最新文章

标签