c# - 将 SubSonic SqlQuery 对象转换为纯 SQL 的扩展方法

Question

有时我需要查看 SubSonic 生成的 SQL 语句。

适用于:

SqlQuery qry = DB.Select().From<Product>()
                 .Where(Products.Columns.Name).IsEqualTo("Productname");

Console.WriteLine(qry.BuildSqlStatement());

然而，这会返回带参数的查询 (SELECT ... WHERE productname = ?productname)。因此，我总是必须在我的代码中找到真正的值，并将参数替换为格式正确的值(例如 productname = 'Productname')，这对于复杂的查询来说真的很烦人。

所以，我决定做一个我想分享的扩展方法(看看我对代码的回答)。

Answer 1

它离完美还很远，但它符合我现在的需求。

但是我可以用

 Console.WriteLine(qry.BuildSqlStatementDebug());

然后将结果复制到剪贴板并从查询浏览器再次运行我的数据库。

using System;
using SubSonic;

namespace MyNamespace.ExtensionMethods
{

public static class SubsonicSqlQueryExtensionMethods
{

    public static String BuildSqlStatementDebug(this SqlQuery qry)
    {

        var result = qry.BuildSqlStatement();

        foreach (var c in qry.Constraints)
        {

            if (c.Comparison == Comparison.BetweenAnd)
            {
                result = result.Replace(c.ParameterName + "_start", GetFormattedValue(c.StartValue, c.DbType));
                result = result.Replace(c.ParameterName + "_end", GetFormattedValue(c.EndValue, c.DbType));
            }
            else
            {
                result = result.Replace(c.ParameterName, GetFormattedValue(c.ParameterValue, c.DbType));
            }

        }

        return result;

    }

    // Works for MySQL
    private static readonly String formatter_date = "'{0:yyyy-MM-dd}'";
    private static readonly String formatter_datetime = "'{0:yyyy-MM-dd hh:mm:ss}'";

    private static readonly String formatter_string = "'{0}'";

    private static String GetFormattedValue(Object value, System.Data.DbType type)
    {

        switch (type)
        {
            case System.Data.DbType.AnsiString:
                return String.Format(formatter_string, value);
            case System.Data.DbType.AnsiStringFixedLength:
                return String.Format(formatter_string, value);
            //case System.Data.DbType.Binary:
            //    break;
            case System.Data.DbType.Boolean:
                return (Boolean)value == true ? "true" : "false";
            //case System.Data.DbType.Byte:
            //    break;
            //case System.Data.DbType.Currency:
            //    break;
            case System.Data.DbType.Date:
                return String.Format(formatter_date, value); break;
            case System.Data.DbType.DateTime:
                return String.Format(formatter_datetime, value); break;
            case System.Data.DbType.DateTime2:
                return String.Format(formatter_datetime, value); break;
            //case System.Data.DbType.DateTimeOffset:
            //    break;
            //case System.Data.DbType.Decimal:
            //    break;
            //case System.Data.DbType.Double:
            //    break;
            case System.Data.DbType.Guid:
                return String.Format(formatter_string, value);
            //case System.Data.DbType.Int16:
            //    break;
            //case System.Data.DbType.Int32:
            //    break;
            //case System.Data.DbType.Int64:
            //    break;
            //case System.Data.DbType.Object:
            //    break;
            //case System.Data.DbType.SByte:
            //    break;
            //case System.Data.DbType.Single:
            //    break;
            case System.Data.DbType.String:
                return String.Format(formatter_string, value);
            case System.Data.DbType.StringFixedLength:
                return String.Format(formatter_string, value);
            //case System.Data.DbType.Time:
            //    break;
            //case System.Data.DbType.UInt16:
            //    break;
            //case System.Data.DbType.UInt32:
            //    break;
            //case System.Data.DbType.UInt64:
            //    break;
            //case System.Data.DbType.VarNumeric:
            //    break;
            case System.Data.DbType.Xml:
                return String.Format(formatter_string, value);
            default:
                return value.ToString();
        }

    }

}

}