Reveal Data provider parameter values at run time
Tags: database, csharp, dotnetcore
Published: 2023-02-03 23:28:20

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.

```csharp
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.

![screen shot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i7e3b7e2n4iag91oakj7.png)

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](https://www.nuget.org/packages/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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xrfhc9su5hvhtck4rqx7.png)

## 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.

```json
{
  "Debug": {
    "LogSqlCommand":  "True"
  }
}
```
Add a Logger package, in this case [SeriLog](https://serilog.net/).

For the sample project there are several packages:

```xml
<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

```csharp
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.

```csharp
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.

```csharp
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](https://www.nuget.org/packages/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.

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

- [ActualCommandText](https://github.com/karenpayneoregon/dbpeek-query-library/blob/master/DbLibrary/LanguageExtensions/IDbExtensions.cs#L29) 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. 


![screen shot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mun0lh4k9r67afkknsxe.png)

## SqlDataAdapter

Simple example.

```csharp
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"];

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

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

## 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

```csharp
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](https://github.com/karenpayneoregon/dbpeek-query-library)

## .NET Framework class version

[NuGet package](https://www.nuget.org/packages/DataProviderCommandHelpers/#supportedframeworks-body-tab) covers Framework 4.6 to 4.8