SqlCommand to T-SQL Printer

View: 311    Dowload: 0   Comment: 0   Post by: admin   Category: C / C++ / MFC   Fields: Other

Download  sqlcommand-to-t-sql-printer.zip (380.09 KB)

You need to Sign In to download the file sqlcommand-to-t-sql-printer.zip
If you do not have an account then Sign up for free here

An Entity Framework log printer that converts SQL commands to executable T-SQL statement

Introduction

In this article, we are going to see a log printer working with Entity Framework that converts SqlCommand to a completely executable T-SQL statement, including all parameter declarations and write to log file. Once a command is executed, You will see following message like this in your log file.

-- SQL executed in 1 ms:
DECLARE @p__linq__0  VarChar  = 'Cloudie';
SELECT 
[Extent1].[StudentID] AS [StudentID], 
[Extent1].[StudentName] AS [StudentName], 
FROM [dbo].[Student] AS [Extent1]
WHERE [Extent1].[StudentName] = @p__linq__0
-- Result: SqlDataReader

The log message will also show the time taken by the command. This can help us debug and test every SQL command executed by DbContext.

The example in this article is based on .Net Framework 4.7 and Entity Framework 6.2.0.

Create an Interceptor

The first thing to do is to create a class that implements IDbCommandInterceptor interface. In this article, we name it 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
} 

From the naming of these methods, you can already tell their purpose and execution order. For example, NonQueryExecuting() will be invoked when a non-query command is about to run. And NonQueryExecuted() method will be invoked once the command is done. In each -ing method, we initialize a Stopwatch instance and assign it to interceptionContext, and retrieve it later in corresponding -ed method. This allows us to measure the time taken by the command and include it to log message.

To enable the interceptor, we need to add a piece of configuration to <entityFramework> section in our Web.config, as following snippet shows.

<entityFramework> 
    <interceptors>
      <interceptor type="LogExample.Logging.SqlLogInterceptor, LogExample"></interceptor>
    </interceptors>
  </entityFramework>

Note that we have a TraceSource field which is to be used to accept log messages. You can replace it with whatever you desire to use.

Format Basic Type Parameters

Next thing to do is to handle every parameter used by the SqlCommand instance and format it in a T-SQL Statement. In order to achieve that, we need to figure out its SqlDbType and choose correct string format accordingly.

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.Decimal:
        case SqlDbType.Float:
        case SqlDbType.Int:
        case SqlDbType.SmallMoney:
        case SqlDbType.TinyInt:
        case SqlDbType.SmallInt:
        case SqlDbType.Money:
        case SqlDbType.DateTimeOffset:
        case SqlDbType.Timestamp:
            return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatNumericParameter(param)};";
        case SqlDbType.Bit:
            return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {FormatBooleanParameter(param)};";
        case SqlDbType.Char:
        case SqlDbType.VarChar:
        case SqlDbType.NChar:
        case SqlDbType.NText:
        case SqlDbType.NVarChar:
        case SqlDbType.Text:
        case SqlDbType.Time:
        case SqlDbType.Xml:
        case SqlDbType.UniqueIdentifier:
            return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.SqlDbType} {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: // We will discuss this later
            return $"DECLARE @{param.ParameterName.TrimStart('@')} {param.TypeName} {FormatDataTableParameter(param)};";
        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 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 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;
}

This will produce a DECLARE statement for each SqlCommand instance in the log message. A couple of examples is shown below.

DECLARE @QueryString VarChar  = 'string';
DECLARE @BeginDate DateTime  = '1970-01-01T08:00:00';
DECLARE @PageNumber Int  = 0;

Format Structured Type Parameters

Structured type parameters are a bit complicated to handle. Structured type is defined in SQL Server, and is usually used to pass array to stored procedure. On application side, we need to create a DataTable instance and add one or more DataRow instances to it. In T-SQL, the statement consists of a DECLARE statement and one or more INSERT statements. In order to print structured type parameters, we need to obtain all columns and row, then choose correct format for each cell by its data type. In this article, we use GetTypeCode method.

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;
        }
    }
}

The code snippet above will produce following statement.

DECLARE @YourParamter dbo.YourStructuredType 
INSERT INTO @YourParamter (Column1Name, Column2Name)  VALUES
(10001001, N'Robert'),
(20002001, N'Cloudie'),
(30003001, N'Rachel');

Produce Complete SQL Statement

In SqlLogInterceptor, now we can produce complete SQL statement as well as execution time, exception and result in every -ed method, and write the log message. Apart from log message, we also check Exception property to decide message level.

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

The code snippet above will produce following log message.

-- SQL executed in 1 ms:
DECLARE @p__linq__0  VarChar  = '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

If the execution target is stored procedure, the log message will be something like this.

-- SQL executed in 20 ms:
DECLARE @QueryString VarChar  = 'string';
DECLARE @BeginDate DateTime  = '1970-01-01T08:00:00';
DECLARE @PageNumber Int  = 0;
EXEC [dbo].[YourStoredProcedure] @QueryString, @BeginDate, @PageNumber;
-- Result: 1

Conclusion

Entity Framework provides an accessible way to monitor each command. The code in this article takes advantage of it and produces executable T-SQL statements in log file. We can directly copy them, and debug them on SQL Server Management Studio, which saves our time of debugging.

SqlCommand to T-SQL Printer

In this article, we are going to see a log printer working with Entity Framework that converts SqlCommand to a completely executable T-SQL statement, including all parameter declarations and write to log file. Once a command is executed, You will see following message like this in your log file.

Posted on 20-03-2018 

Comment:

To comment you must be logged in members.

Files with category

  • Departmental store management system

    Departmental store management system

    View: 2    Download: 0   Comment: 0

    Category: C / C++ / MFC     Fields: none

    This is a simple project on departmental store management system developed in c++ programming language . This project helps to keep all the information details of the items available in departmental store and maintains the stock and prints the bill....

  • Volume of a Cone in C

    Volume of a Cone in C

    View: 2    Download: 0   Comment: 0

    Category: C / C++ / MFC     Fields: none

    Code Features: 1. Find the Volume of a cone 2. Find the value of other properties of the cone 3. Teach user how to compute the volume of the cone manually Thanks to following for the code: John Carlo C. Nañoz, Dondon Bagasbas, Gabby Peñones

  • C# - Student & Lecture Test Application (Using Database)

    C# - Student & Lecture Test Application (Using Database)

    View: 5    Download: 0   Comment: 0

    Category: C / C++ / MFC     Fields: none

    This application was developed by Gopolang Kopano Mathole using visual studio 2013.. Features: - Databases (files as fallback) - Flat UI designs & material designs. - Easy to navigate app. - Login/Register pages (both read and writes to a file). -...

  • Point of Sale and Inventory System C#.Net Version 2.0

    Point of Sale and Inventory System C#.Net Version 2.0

    View: 14    Download: 0   Comment: 0

    Category: C / C++ / MFC     Fields: none

    This is a complete point of sale and inventory system written in C#.NET. You can use this system to your future project.. System Features Staff/Employee Categories Products Point of Sale Inventory Daily Sales Report By User Daily Sales Report By...

  • Calculator Application Using WPF

    Calculator Application Using WPF

    View: 5    Download: 0   Comment: 0

    Category: C / C++ / MFC     Fields: none

    Calculator application written in C# using Windows Presentation Foundation. Code is behind the view. Application is using some Static and Explicit resources.

  • Flappy Bird Clone

    Flappy Bird Clone

    View: 20    Download: 0   Comment: 0

    Category: C / C++ / MFC     Fields: none

    Flappy Bird Clone - this was one of the most popular game in the world. The game is very simple, you have to navigate the bird to past the obstacles. You need to tap the screen to make the bird flies. Don’t fly to high because it will die. To get the...

  • Download Manager

    Download Manager

    View: 30    Download: 1   Comment: 0

    Category: C / C++ / MFC     Fields: none

    This program with source code allows you to download any files from the internet. It display the download speed, file size, downloaded bytes, remaining bytes, and the total file size of the file. It also display the progress of the downloaded file...

  • Email Directory System C# and Sql

    Email Directory System C# and Sql

    View: 19    Download: 0   Comment: 0

    Category: C / C++ / MFC     Fields: none

    Basic Email Directory System using C#.Net and Sql Server Features: Send Email Add Contacts EditContacts Delete Contacts Search Contacts Add Image Note! Please Add System.Web.Helpers in your referenced you can found it on Assemblies Extensions. and...

 
File suggestion for you
File top downloads
Codetitle - library source code to share, download the file to the community
Copyright © 2018. All rights reserved. codetitle Develope by Vinagon .Ltd