在C#中规范化SQL查询文本 [英] Normalize SQL Query Text in c#

问题描述

我在SQL Server数据库中执行了以下查询.

I executed the following query in the SQL Server database.

SELECT * FROM Production.Product WHERE Name = 'Bearing Ball'

然后，我尝试使用以下SQL命令获取查询文本以及统计信息:

Then I tried to get the query text along with the statistics using the following SQL command :

    SELECT
    qs.sql_handle,
    qs.execution_count AS EXECUTION_COUNT,
    AVG_TIME = --Converted from microseconds
    (qs.total_elapsed_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    TOTAL_TIME = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text AS TEXT
FROM
    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

我得到这样的东西

I get something like this

(@1 varchar(8000))SELECT * FROM [Production].[Product] WHERE [Name]=@1

但是，我想从我的C#程序中执行字符串匹配，并获取查询文本的统计信息. C#程序仅知道原始查询(SELECT * FROM Production.Product WHERE Name ='Bearing Ball').被替换的参数不是 唯一的问题，如您所见，数据库服务器在表名称上添加了方括号.

But, I want to perform a string match from my c# program and get the statistics of the query text. The c# program only knows about the original query (SELECT * FROM Production.Product WHERE Name = 'Bearing Ball'). The parameters getting replaced is not the only problem, as you can see the database server added square brackets to the table names.

有没有办法解决这个问题.如何将原始查询(SELECT * FROM Production.Product WHERE Name ='Bearing Ball')转换为规范化的查询((@ 1 varchar(8000))SELECT * FROM [Production].[Product] WHERE [Name] = @ 1)?我可以使用Microsoft.Data.Schema.ScriptDom吗? 请注意，我想规范化查询而不在数据库服务器中执行查询.

Is there a way to overcome this issue. How can I convert my original query(SELECT * FROM Production.Product WHERE Name = 'Bearing Ball') to normalized one((@1 varchar(8000))SELECT * FROM [Production].[Product] WHERE [Name]=@1)? Can I do this using Microsoft.Data.Schema.ScriptDom? Please note that I want to normalize my query without executing it in the database server.

推荐答案

尝试类似以下操作:

Try something like this:

public void ForumQuestion()
{
    using (SqlConnection cn = new SqlConnection { ConnectionString = "Your connection" })
    {
        DataTable resultTable = new DataTable();

        using (SqlCommand cmd = new SqlCommand { Connection = cn})
        {
            cmd.CommandText = @"
            SELECT
                qs.sql_handle,
                qs.execution_count AS EXECUTION_COUNT,
                AVG_TIME = --Converted from microseconds
                (qs.total_elapsed_time/1000000) / qs.execution_count,
                qs.total_elapsed_time,
                TOTAL_TIME = --Converted from microseconds
                qs.total_elapsed_time/1000000,
                st.text AS TEXT
            FROM
            sys.dm_exec_query_stats AS qs
                CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
                CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
            ORDER BY qs.total_worker_time DESC";

            cn.Open();
            resultTable.Load(cmd.ExecuteReader());
        }
    }
}

这篇关于在C#中规范化SQL查询文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！