Implementation question

[MARKUSER]

@pimbrouwers Please help me on below points.

1. May i know why we have to commit (dbContext.Commit();) the transaction for SQL statements which only get the information from database (Like Read operation). Can we ignore Begin Transaction for operations like Read from SQL.

2. I am using Dapper as middle layer. I have registered the context exactly like how you provided the instructions in startup.cs file. The problem that i am seeing here is with in same controller method call if i am making two database calls (both are read), First call is working fine but the second one is throwing below exception as i believe context was already closed due to
   commit was called after completing the fist DB call. Please let me know if i am missing anything.

System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()

3. Any async call being made with this code resulted in below exception. Please guide me how to invoke async calls.

   DB call: await Connection.QueryAsync(sQuery,transaction:Transaction);

System.InvalidOperationException: Invalid operation. The connection is closed.
at System.Data.SqlClient.SqlCommand.<>c.b__122_0(Task`1 result)

4. I am new to .net core world. As per below IOC registration, according to my understanding every time application gets a new request then connection will be opened. It is possible some requests don't need a database connection in that case application is opening a SQL connection but it will not be closed. Please let me know if my understanding is correct.

services.AddTransient(options =>
{
var builder = new SqlConnectionStringBuilder(Configuration.GetConnectionString("DefaultConnectionString"));

            return new DbConnFactory(() =>
            {
                var conn = new SqlConnection(builder.ConnectionString);

                conn.Open();
                return conn;
            });
        });

Please help.

[johngrant]

After doing a bit of testing with a mild stress test, 200 sequential requets 10ms apart which take 10-500 ms to complete, I'm seeing a lot of errors. Some of these are read connections. I think you're correct that for some queries that are read the connection is, but the connection may never get closed leading to connection pool leaks.

After a min or so I get these messages: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached.

Should the code not call the Dispose method for the connection in DbContext.Dispose() ?

[johngrant]

Created a PR w/ fix #2

Alternatively you can subclass DbContext and implement the IDisposable interface. Inside the Dispose() method call Connection?.Dispose()

[johngrant]

@MARKUSER Can you add comments to the PR I created for or against?