Reveal Data provider parameter values at run time

When allowing users to enter to enter dynamic values for querying a database table there is always the possibility the user will enter a value that returns nothing.

One step that may be considered is to write the SQL statement to a log file yet when using parameters values will not be available given the following code.

public static List<DataContainer> Example1(string countryName)
{
    List<DataContainer> list = new();

    using SqlConnection cn = new(ConnectionString());
    using SqlCommand cmd = new() { Connection = cn, CommandText = Statement1 };

    cmd.Parameters.Add("@CountryName", SqlDbType.NVarChar).Value = countryName;

    Console.WriteLine(cmd.CommandText);

We get the following.

What we need is to see the value for @CountryName so a developer can diagnose if the problem is SQL related or a value entered that causes no record(s) to be returned.

No matter if the user is in the same location or remote consider the following path.

Install the following NuGet package DbPeekQueryLibrary which provides a simple method to peek at parameter values.

By using this package a developer can make a determination perhaps without even running the query, example, the user entered Mexico with a leading space.

Using the package/library we can see the problem.

Example implementation

First step, we don't always want to view the SQL so let's setup a value in an appsettings.json file to toggle logging.

{
  "Debug": {
    "LogSqlCommand":  "True"
  }
}

Add a Logger package, in this case SeriLog.

For the sample project there are several packages:

<ItemGroup>
   <PackageReference Include="Serilog" Version="2.12.0" />
   <PackageReference Include="Serilog.Sinks.Console" Version="4.1.0" />
   <PackageReference Include="Serilog.Extensions.Logging.File" Version="3.0.0" />
   <PackageReference Include="Serilog.Sinks.File" Version="5.0.0" />
</ItemGroup>

Configure SeriLog

public class SetupLogging
{
    public static void Production()
    {
        Log.Logger = new LoggerConfiguration()
            .WriteTo.File(Path.Combine(AppDomain
                    .CurrentDomain.BaseDirectory, "LogFiles", "Log.txt"),
                rollingInterval: RollingInterval.Day)
            .CreateLogger();
    }
}

Create a class to read settings from appsettings.json, adjust as needed for ASP.NET Core in regards to environments.

public class Configurations
{
    public static IConfigurationRoot GetConfigurationRoot()
    {
        return new ConfigurationBuilder()
            .SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
            .AddJsonFile("appsettings.json")
            .Build();
    }
}

Create a Singleton class to read our setting for logging SQL statements.

internal sealed class ApplicationSettings
{
    private static readonly Lazy<ApplicationSettings> Lazy = 
        new(() => new ApplicationSettings());
    public static ApplicationSettings Instance => Lazy.Value;

    public bool LogSqlCommands { get; set; }
    private ApplicationSettings()
    {
        IConfigurationRoot configuration = 
            Configurations.GetConfigurationRoot();
        LogSqlCommands = Convert.ToBoolean(
            configuration.GetSection("Debug")["LogSqlCommand"]) ;
    }
}

Install the following NuGet package DbPeekQueryLibrary

Next write an assertion into a method which request data from a database, this code has been taken from the source code for this article e.g.

if (ApplicationSettings.Instance.LogSqlCommands)
{
    Log.Information($"{nameof(SqlServerOperations)}.{nameof(Example1)}\n{cmd.ActualCommandText()}");
}

• ActualCommandText is what provides access to parameter values.

With "LogSqlCommand": "True" SQL statements are logged.

In the following screenshots I've read the log and enhanced parts for clarification.

Working with different databases.

The default database is SQL-Server which expects @ for the first parameter as is the default so nothing needs to be done. To use with Oracle, set the first parameter to Oracle

These are the various providers

public enum CommandProvider
{
    SqlServer,
    Access,
    SqLite,
    Oracle
}

Does this really work with Microsoft Access? Not when ? character is used, for those who work a lot with access knows that we can setup named parameters, just make sure they are positional for this to work.

Source code

Clone the following GitHub repository

.NET Framework class version

NuGet package covers Framework 4.6 to 4.8