DEV Community

Cover image for Reveal Data provider parameter values at run time
Karen Payne
Karen Payne

Posted on • Updated on

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



Enter fullscreen mode Exit fullscreen mode

We get the following.

screen shot

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.

screen shot

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


Enter fullscreen mode Exit fullscreen mode

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>


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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"]) ;
    }
}


Enter fullscreen mode Exit fullscreen mode

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()}");
}


Enter fullscreen mode Exit fullscreen mode

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

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

screen shot

SqlDataAdapter

Simple example.



public static void DataAdapterPeekParameters(int customerId)
{
    var statement =
        """
        SELECT     CU.CompanyName,
                   CO.FirstName,
                   CO.LastName
         FROM      dbo.Customers AS CU
        INNER JOIN dbo.Contacts AS CO
           ON CU.ContactId = CO.ContactId
        WHERE      (CU.CustomerIdentifier = @CustomerIdentifier);
        """;
    using SqlConnection cn = new(ConnectionString);
    using SqlDataAdapter da = new(statement, cn);
    da.SelectCommand.Parameters.AddWithValue(
        "@CustomerIdentifier", customerId);

    var actualCommandText = da.SelectCommand.ActualCommandText();
    DataSet ds = new();
    da.Fill(ds, "Customers");
    DataTable dt = ds.Tables["Customers"];

}


Enter fullscreen mode Exit fullscreen mode

Here 6 is passed for customer id and this is the result.



SELECT     CU.CompanyName,
           CO.FirstName,
           CO.LastName
 FROM      dbo.Customers AS CU
INNER JOIN dbo.Contacts AS CO
   ON CU.ContactId = CO.ContactId
WHERE      (CU.CustomerIdentifier = 6);


Enter fullscreen mode Exit fullscreen mode

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
}


Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)