SqlCommand 到 T-SQL 打印机
4.55/5 (8投票s)
一个 Entity Framework 日志打印机，
引言
在本文中，我们将看到一个与 Entity Framework 协同工作的日志打印机，它将 SqlCommand 转换为一个完全可执行的 T-SQL 语句，包括所有的参数声明，并写入日志文件。一旦命令执行，您将在日志文件中看到如下消息。
-- SQL executed in 1 ms:
DECLARE @p__linq__0 VarChar(100) = 'Cloudie';
SELECT
[Extent1].[StudentID] AS [StudentID],
[Extent1].[StudentName] AS [StudentName],
FROM [dbo].[Student] AS [Extent1]
WHERE [Extent1].[StudentName] = @p__linq__0
-- Result: SqlDataReader
日志消息还会显示命令所花费的时间。这可以帮助我们调试和测试由 DbContext 执行的每个 SQL 命令。
本文中的示例基于 .NET Framework 4.7 和 Entity Framework 6.2.0。
创建一个拦截器
首先要做的是创建一个实现 IDbCommandInterceptor 接口的类。在本文中，我们将其命名为 SqlLogInterceptor。
public class SqlLogInterceptor : IDbCommandInterceptor
{
#region Fields
private static readonly TraceSource _traceSource =
new TraceSource(nameof(SqlLogInterceptor));
#endregion
#region Constructor
public SqlLogInterceptor() { }
#endregion
#region Implementations
public void NonQueryExecuting(DbCommand cmd,
DbCommandInterceptionContext<int> interceptionContext)
{
interceptionContext.SetUserState(nameof(Stopwatch), Stopwatch.StartNew());
}
public void NonQueryExecuted(DbCommand cmd,
DbCommandInterceptionContext<int> interceptionContext)
{
var stopwatch = interceptionContext.FindUserState(nameof(Stopwatch)) as Stopwatch;
// TODO: print T-SQL statement.
}
public void ReaderExecuting(DbCommand cmd,
DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
interceptionContext.SetUserState(nameof(Stopwatch), Stopwatch.StartNew());
}
public void ReaderExecuted(DbCommand cmd,
DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
var stopwatch = interceptionContext.FindUserState(nameof(Stopwatch)) as Stopwatch;
// TODO: print T-SQL statement.
}
public void ScalarExecuting(DbCommand cmd,
DbCommandInterceptionContext<object> interceptionContext)
{
interceptionContext.SetUserState(nameof(Stopwatch), Stopwatch.StartNew());
}
public void ScalarExecuted(DbCommand cmd,
DbCommandInterceptionContext<object> interceptionContext)
{
var stopwatch = interceptionContext.FindUserState(nameof(Stopwatch)) as Stopwatch;
// TODO: print T-SQL statement.
}
#endregion
}
从这些方法的命名中，您已经可以知道它们的用途和执行顺序。例如，NonQueryExecuting() 将在非查询命令即将运行时被调用。而 NonQueryExecuted() 方法将在命令完成后被调用。在每个 -ing 方法中，我们初始化一个 Stopwatch 实例并将其分配给 interceptionContext，然后在相应的 -ed 方法中检索它。这使我们能够测量命令所花费的时间，并将其包含到日志消息中。
要启用拦截器，我们需要在 Web.config 中的 <entityFramework> 节中添加一段配置，如下面的代码片段所示
<entityFramework>
<interceptors>
<interceptor type="LogExample.Logging.SqlLogInterceptor, LogExample"></interceptor>
</interceptors>
</entityFramework>
请注意，我们有一个 TraceSource 字段，用于接收日志消息。您可以将其替换为您想要使用的任何东西。
格式化基本类型参数
接下来要做的是处理 SqlCommand 实例使用的每个参数，并将其格式化为 T-SQL 语句。为了实现这一点，我们需要弄清楚它的 SqlDbType，并相应地选择正确的 string 格式。
private static string FormatParameters(DbParameterCollection parameters)
{
return string.Join(Environment.NewLine, parameters.OfType<SqlParameter>().Select(FormatParameter));
}
private static string FormatParameter(SqlParameter param)
{
switch (param.SqlDbType)
{
case SqlDbType.BigInt:
case SqlDbType.Binary:
case SqlDbType.Float:
case SqlDbType.Int:
case SqlDbType.TinyInt:
case SqlDbType.SmallInt:
case SqlDbType.DateTimeOffset:
case SqlDbType.Timestamp:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatNumericParameter(param)};";
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatNumericParameterSize(param)} {FormatNumericParameter(param)};";
case SqlDbType.Bit:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatBooleanParameter(param)};";
case SqlDbType.NText:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.Xml:
case SqlDbType.UniqueIdentifier:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatStringParameter(param)};";
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.VarChar:
case SqlDbType.NVarChar:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatStringParameterSize(param)} {FormatStringParameter(param)};";
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatDateTimeParameter(param)};";
case SqlDbType.Structured:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.TypeName} {FormatDataTableParameter(param)};";
case SqlDbType.Udt:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.UdtTypeName} -- {param.Value};";
default:
return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatNumericParameter(param)};";
}
}
private static string FormatNumericParameter(SqlParameter param)
{
if (param.Direction == ParameterDirection.Input)
return " = " + (Convert.IsDBNull(param.Value) ? "NULL" : Convert.ToString(param.Value));
else
return string.Empty;
}
private static string FormatNumericParameterSize(SqlParameter param)
{
if (param.Direction == ParameterDirection.Input || !Convert.IsDBNull(param.Value))
return string.Empty;
return $"({param.Precision}, {param.Scale})";
}
private static string FormatBooleanParameter(SqlParameter param)
{
if (param.Direction == ParameterDirection.Input)
return " = " + (Convert.IsDBNull(param.Value) ?
"NULL" : Convert.ToString(Convert.ToByte(param.Value)));
else
return string.Empty;
}
private static string FormatStringParameter(SqlParameter param)
{
if (param.Direction == ParameterDirection.Input)
return " = " + (Convert.IsDBNull(param.Value) ? "NULL" : $"'{param.Value}'");
else
return string.Empty;
}
private static string FormatStringParameterSize(SqlParameter param)
{
if (Convert.IsDBNull(param.SqlValue)) return string.Empty;
if (param.Size < 0) return "(MAX)";
if (param.Size > 0) return $"({param.Size})";
return $"({Convert.ToString(param.SqlValue).Length})";
}
private static string FormatDateTimeParameter(SqlParameter param)
{
if (param.Direction == ParameterDirection.Input)
return " = " + (Convert.IsDBNull(param.Value) ?
"NULL" : $"'{param.Value:yyyy-MM-ddTHH:mm:ss}'");
else
return string.Empty;
}
这将为日志消息中的每个 SqlCommand 实例生成一个 DECLARE 语句。下面显示了一些示例
DECLARE @QueryString VarChar(200) = 'string';
DECLARE @BeginDate DateTime = '1970-01-01T08:00:00';
DECLARE @PageNumber Int = 0;
格式化结构化类型参数
结构化类型参数处理起来有点复杂。结构化类型在 SQL Server 中定义，通常用于将数组传递给存储过程。在应用程序端，我们需要创建一个 DataTable 实例，并向其添加一个或多个 DataRow 实例。在 T-SQL 中，该语句由一个 DECLARE 语句和一个或多个 INSERT 语句组成。为了打印结构化类型参数，我们需要获取所有列和行，然后根据每个单元格的数据类型选择正确的格式。在本文中，我们使用 GetTypeCode 方法。
private static string FormatDataTableParameter(SqlParameter param)
{
if (param.Direction == ParameterDirection.Input)
{
var table = param.Value as DataTable;
if (table.Rows.Count == 0) return string.Empty;
var columns = table.Columns.OfType<DataColumn>().ToArray();
var rows = table.Rows.OfType<DataRow>().Select
(row => $"({string.Join(", ", FormatDataRowColumns(columns, row))})");
var sb = new StringBuilder(Environment.NewLine);
sb.AppendLine($"INSERT INTO @{param.ParameterName.TrimStart('@')}
({string.Join(", ", columns.Select(col => col.ColumnName))}) VALUES");
sb.Append(string.Join("," + Environment.NewLine, rows));
return sb.ToString();
}
return string.Empty;
}
private static IEnumerable<string> FormatDataRowColumns(DataColumn[] columns, DataRow row)
{
foreach (var col in columns)
{
var value = row[col];
switch (Type.GetTypeCode(col.DataType))
{
case TypeCode.Empty:
yield return string.Empty;
break;
case TypeCode.Object:
yield return $"{row[col]}";
break;
case TypeCode.DBNull:
yield return $"NULL";
break;
case TypeCode.Boolean:
yield return $"{(Convert.ToBoolean(row[col]) ? 1 : 0)}";
break;
case TypeCode.Char:
case TypeCode.String:
yield return $"N'{row[col]}'"; // TODO: Add your logic to remove prefix N
break;
case TypeCode.SByte:
case TypeCode.Byte:
case TypeCode.Int16:
case TypeCode.UInt16:
case TypeCode.Int32:
case TypeCode.UInt32:
case TypeCode.Int64:
case TypeCode.UInt64:
case TypeCode.Single:
case TypeCode.Double:
case TypeCode.Decimal:
yield return $"{row[col]}";
break;
case TypeCode.DateTime:
yield return $"'{row[col]:yyyy-MM-ddTHH:mm:ss}'";
break;
default:
yield return string.Empty;
break;
}
}
}
上面的代码片段将产生以下语句
DECLARE @YourParamter dbo.YourStructuredType
INSERT INTO @YourParamter (Column1Name, Column2Name) VALUES
(10001001, N'Robert'),
(20002001, N'Cloudie'),
(30003001, N'Rachel');
生成完整的 SQL 语句
在 SqlLogInterceptor 中，现在我们可以在每个 -ed 方法中生成完整的 SQL 语句以及执行时间、异常和结果，并写入日志消息。除了日志消息，我们还会检查 Exception 属性来确定消息级别。
public void ScalarExecuted(DbCommand cmd,
DbCommandInterceptionContext<object> interceptionContext)
{
var stopwatch = interceptionContext.FindUserState(nameof(Stopwatch)) as Stopwatch;
var type = interceptionContext.Exception == null ?
TraceEventType.Information : TraceEventType.Error;
_traceSource.TraceEvent(type, 1,
string.Join(Environment.NewLine,
$"-- SQL executed in {stopwatch.ElapsedMilliseconds} ms:",
$"USE {cmd.Connection.Database};",
FormatParameters(cmd.Parameters),
$"{cmd.CommandText};",
interceptionContext.Exception,
$"-- Result: {interceptionContext.Result}"));
}
// Same stuff for other -ed methods
上面的代码片段将生成以下日志消息。
-- SQL executed in 1 ms:
DECLARE @p__linq__0 VarChar(200) = 'Robert';
SELECT
[Extent1].[StudentID] AS [StudentID],
[Extent1].[StudentName] AS [StudentName],
[Extent1].[StandardId] AS [StandardId]
FROM [dbo].[Student] AS [Extent1]
WHERE [Extent1].[StudentName] = @p__linq__0
-- Result: SqlDataReader
如果执行目标是存储过程，则日志消息将如下所示
-- SQL executed in 20 ms:
DECLARE @QueryString VarChar(200) = 'string';
DECLARE @BeginDate DateTime = '1970-01-01T08:00:00';
DECLARE @PageNumber Int = 0;
EXEC [dbo].[YourStoredProcedure] @QueryString, @BeginDate, @PageNumber;
-- Result: 1
结论
Entity Framework 提供了一种可访问的方式来监视每个命令。本文中的代码利用了它，并在日志文件中生成可执行的 T-SQL 语句。我们可以直接复制它们，并在 SQL Server Management Studio 上调试它们，从而节省我们的调试时间。
历史
- 2018-03-16 首次发布
- 2018-03-22 修复了一个错误，即
CHAR,VARCHAR,NCHAR,VARCHAR数据大小未出现在DECLARE语句中。 - 2018-03-23 修复了一个错误，即
CHAR,VARCHAR,NCHAR,VARCHAR数据大小未正确显示。向DECIMAL,MONEY和SMALLMONEY声明添加精度和小数位数。