在SQL Server Management Studio中执行NHibernate生成的准备好的语句 [英] Execute NHibernate-generated prepared statements in SQL Server Management Studio

问题描述

配置NHibernate以显示已执行的SQL可以完成预期的工作，但是每当需要将SQL字符串复制粘贴到SQL Server Management Studio中时，我们都必须对其进行重新排列以使其兼容.

Configuring NHibernate to display executed SQL does what it's supposed to, but whenever a SQL string needs to be copy-pasted into SQL Server Management Studio, we have to rearrange it dramatically in order to be compatible.

在我开始开发自己的应用程序并将其重新分析并重新安排为对ManagementStudio更友好的SQL之前，我想重申一下以前从未做过的事情-我不愿意花时间在上面并找到以后出来.

Before I dive into developing my own application that parses and rearranges this into a more ManagementStudio-friendly SQL, I'd like to reassert that this hasn't been done before - I'd hate to spend time on this and find out later.

是否存在一种便宜且可行的方式将NH生成的准备好的语句转换为可以立即执行的内容?

Is there a cheap and practicable way of converting the NH-generated prepared statement into something that's executable straight away?

预先感谢

推荐答案

我知道您可以使用nhibernate profiler来做到这一点，但这不是免费的工具.我还将对实现此目的的免费替代方法感兴趣.

I know you can do this with nhibernate profiler but this is not a free tool. I would also be interested in a free alternative to doing this.

http://nhprof.com/

好像有一个log4net的自定义附加程序，它将对其格式化，以便您可以实际运行sql NHibernate吐出.我在下面列出的博客中看到了它:

Looks like there is a custom appender out there for log4net that will format it such that you can actually run the sql NHibernate spits out. I saw it in the blog listed below:

http://gedgei.wordpress. com/2011/09/03/logging-nhibernate-queries-with-parameters/

以下是我从上述博客中获取并经过修改以与Guids配合使用的代码:

Below is the code I have taken from the above blog and modified to work with Guids:

/// <summary>
/// This log4net appender is used for outputting NHibernate sql statements in a sql management studio friendly format.
/// This means you should be able to copy the sql output from this appender and run it directly.  Normally in the NHibernate
/// output there is parameterized sql that must be manually edited to run it.
/// </summary>
public class NHibernateSqlAppender : ForwardingAppender
{
    private const string GuidRegex = @"\b[A-F0-9]{8}(?:-[A-F0-9]{4}){3}-[A-F0-9]{12}\b";

    protected override void Append(LoggingEvent loggingEvent)
    {
        var loggingEventData = loggingEvent.GetLoggingEventData();

        if (loggingEventData.Message.Contains("@p"))
        {
            StringBuilder messageBuilder = new StringBuilder();

            string message = loggingEventData.Message;
            var queries = Regex.Split(message, @"command\s\d+:");

            foreach (var query in queries)
                messageBuilder.Append(ReplaceQueryParametersWithValues(query));

            loggingEventData.Message = messageBuilder.ToString();
        }

        base.Append(new LoggingEvent(loggingEventData));
    }

    public static string ReplaceQueryParametersWithValues(string query)
    {
        string returnQuery = Regex.Replace(query, @"@p\d+(?=[,);\s])(?!\s*=)", match =>
        {
            Regex parameterValueRegex = new Regex(string.Format(@".*{0}\s*=\s*(.*?)\s*[\[].*", match));
            return parameterValueRegex.Match(query).Groups[1].ToString();
        });

        //Place single quotes around all Guids in the sql string
        returnQuery = Regex.Replace(returnQuery, GuidRegex, "'$0'", RegexOptions.IgnoreCase);

        int parameterListIndex = returnQuery.LastIndexOf("@p0");

        if (parameterListIndex != -1)
        {
            //Truncate the paramter list off the end since we are substituting the actual values in the regular expression above
            //The -1 also cuts off the semicolon at the end
            return returnQuery.Substring(0, parameterListIndex).Trim();
        }

        return returnQuery.Trim();
    }
}

这是将输出发送到控制台的方式:

Here is how you would send this output to the console:

<appender name="NHibernateSqlAppender" type="NHibernatePlayground.Custom.NHibernateSqlAppender, NHibernatePlayground">
    <appender-ref ref="console" />
</appender>

<root>
    <appender-ref ref="NHibernateSqlAppender" />
</root>

注意:

这似乎在生产系统中引起一些相当严重的性能问题.我还没有找到更好的方法来执行此操作，但是对于使用此方法的任何人，请注意这些性能问题

NOTE:

It appears this causes some fairly significant performance issues in a production system. I haven't found a better way to do this yet but for anyone using this beware of these performance issues

这篇关于在SQL Server Management Studio中执行NHibernate生成的准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！