.NET 6.0 Web API + Dapper - Getting exception in the Connection After querying

tRuEsAtM 0
Viewed 56

I have . NET 6.0 Web API, and Dapper Set up with DI.

public class DapperContext
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public DapperContext(IConfiguration configuration)
    {
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("DefaultConnection");
    }

    public IDbConnection CreateConnection()
        => new SqlConnection(_connectionString);
}

public class ApiUserRepository : IApiUserRepository
{
    private readonly DapperContext _context;

    public ApiUserRepository(DapperContext context)
    {
        _context = context;
    }
    public async Task<ApiUser> GetUser(string name)
    {
        //using (var context = new PrincipalContext(ContextType.Domain, "elegrity.com"))
        using (var context = new PrincipalContext(ContextType.Domain, _domain.GetDomainNameFromConfiguration()))
        {
            var userIdentity = UserPrincipal.FindByIdentity(context, name);

            if (userIdentity != null)
            {
                var query = "SELECT UserID, FirstName + ', ' + LastName as [Name], RoleID FROM EIFUser WHERE EmailAddress = @EmailAddress";

                var connection = _context.CreateConnection();

                var apiUser = await connection.QuerySingleOrDefaultAsync<ApiUser>(query, new { userIdentity.EmailAddress });
                return apiUser;
}
}
}
}

I am getting the following error message, using Microsoft.Data.SqlClient library

ServerVersion = '((Microsoft.Data.SqlClient.SqlConnection)connection).ServerVersion' threw an exception of type 'System.InvalidOperationException'

ConnectionString in appSettings.json looks like,

  "ConnectionStrings": {
    "DefaultConnection": "Trust Server Certificate=true;packet size=4096;user id=user;password=password;data source=Somedatasource;persist security info=False;initial catalog=MY_DB;Connect Timeout=200;pooling=true;Max Pool Size=750;"
  },

enter image description here What is causing this issue?

dapper .net-6.0 c#
1 Answers

This isn't an application problem. The debugger watch window says that the debugger couldn't read that property. It can be ignored.

The image shows that the connection to the server is closed. It's impossible to read the server's version when there's no connection, hence the InvalidOperationException error when trying to display this.

This won't affect program execution unless the program tries to read the ServerVersion property of a closed connection.

There some real problems in the code though:

  • The connection is never disposed
  • The DapperContext is trying to do what the built-in ADO.NET abstract class DbProviderFactory already does. It shouldn't be used that way because it only increases coupling - the SqlClient provider is now hard-coded into that class.

A better idea would be to register the correct DbProviderFactory and use it in the repository class to create connections. This way any dependency on the actual provider, eg SqlClient or SqliteClient, ends during service registration.

DbProviderFactory can only create connections without a connection string. That's common and boring code, which means a DapperContext-like object would be needed, based on DbProviderFactory this time:

public class ConnectionFactory
{
    public ConnectionFactory(DbConnectionFactory factory,IConfiguration configuration)
    {
        _factory=factory;
        _connectionString = configuration.GetConnectionString("MyConnectionString");
    }
    
    public DbConnection CreateConnection()
    {
        var connection=_factory.CreateConnection();
        connection.ConnectionString=_connectionString;
        return connection;
    }
}

In Program.cs this would register the DbProviderFactory and ConnectionFactory:

builder.Services.AddSingleton<DbProviderFactory>(()=>SqlClientFactory.Instance>();
builder.Services.AddScoped<ConnectionFactory>();

The repository can use that class now to create connections:

public class ApiUserRepository : IApiUserRepository
{
    private readonly ConnectionFactory _factory;

    public ApiUserRepository(ConnectionFactory factory)
    {
        _factory=factory;
    }
    public async Task<ApiUser> GetUser(string name)
    {
        using var connection=_factory.CreateConnection();
        ...
    }
}

In .NET Framework it was possible to register provider factories on a machine or application, allowing code to pick the correct factory by name, or even by specifying the provider name in configuration. This doesn't work in .NET Core though

Panagiotis Kanavos0
Related