Guidance on replacing ConnectionManager

[mtavares628]

I'm still in process of converting my existing code to CSLA 6, and my DAL is using ADO.Net and the Connection Manager. I came across issue #2633 that now marks ConnectionManager as obsolete, so I'm working on the recommended approach of using DI. I'm looking for guidance on whether I'm on the right track before I go about converting all of my DAL objects. Here is a sample of what my Csla 5 DAL looks like using CslaGenFork:

    public partial class AdvGenderLookupCollectionDal : IAdvGenderLookupCollectionDal
    {

        private readonly string _connectionString;
        
        #region Constructor

        public AdvGenderLookupCollectionDal(IDalConfigDad dalConfig)
        {
            _connectionString = dalConfig.ConnectionString;
        }
        
        #endregion
        
        #region DAL methods

        /// <summary>
        /// Loads a AdvGenderLookupCollection collection from the database.
        /// </summary>
        /// <param name="checkDate">The checkDate parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <param name="advGenderID">The advGenderID parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <param name="advocacyGroupID">The advocacyGroupID parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <param name="timeZone">The timeZone parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <returns>A list of <see cref="AdvGenderLookupDto"/>.</returns>
        public List<AdvGenderLookupDto> Fetch(SmartDate checkDate, int advGenderID, int advocacyGroupID, short timeZone)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager(_connectionString, false))
            {
                using (var cmd = new SqlCommand("procAdmin_AdvGendersByActiveDate_Select", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@CheckDate", checkDate.DBValue).DbType = DbType.DateTime;
                    cmd.Parameters.AddWithValue("@AdvGenderID", advGenderID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@AdvocacyGroupID", advocacyGroupID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@TimeZone", timeZone).DbType = DbType.Int16;
                    var dr = cmd.ExecuteReader();
                    return LoadCollection(dr);
                }
            }
        }

        private List<AdvGenderLookupDto> LoadCollection(IDataReader data)
        {
            var advGenderLookupCollection = new List<AdvGenderLookupDto>();
            using (var dr = new SafeDataReader(data))
            {
                while (dr.Read())
                {
                    advGenderLookupCollection.Add(Fetch(dr));
                }
            }
            return advGenderLookupCollection;
        }

        private AdvGenderLookupDto Fetch(SafeDataReader dr)
        {
            var advGenderLookup = new AdvGenderLookupDto();
            // Value properties
            advGenderLookup.AdvGenderID = dr.GetInt32("AdvGenderID");
            advGenderLookup.Name = dr.GetString("Name");
            advGenderLookup.IsDefault = dr.GetBoolean("IsDefault");

            return advGenderLookup;
        }

#endregion

    }

And the startup code for that object and the dependant IDalConfigDad object is defined as:

services.AddSingleton<IDalConfigDad, DalConfigDad>(options => new DalConfigDad(connectionString));
services.AddTransient<IAdvGenderDal, AdvGenderDal>();

Switching to Csla 6 I'm using the following guidance:

I created an interfaced based off of IDbConnection:

    public interface IDalConnection : IDbConnection
    {
    }

and defining the service in the startup as:

services.AddScoped<IDalConnection>((provider) => (IDalConnection)(IDbConnection)(new Microsoft.Data.SqlClient.SqlConnection(connectionString)));

Note that I needed to explicity cast first to an IDbConnection and then to IDallConnection in order to get it to compile. This is different than Rocky's sample in #2633, in which it looks with his code, the implicit cast works. However, when I run my code, I end up with an exception that it is unable to explicitly cast a SqlConnection type to IDalConnection. If I just use a scoped IDbConnection in my startup like this:

services.AddScoped<IDbConnection>((provider) => new Microsoft.Data.SqlClient.SqlConnection(connectionString));

It works without issue.

Here is what the DAL Object was changed to:

    public partial class AdvGenderLookupCollectionDal : IAdvGenderLookupCollectionDal
    {

        private readonly SqlConnection _connection;
        


        #region Constructor

        public AdvGenderLookupCollectionDal(IDbConnection dalConnection)
        {
            _connection = (SqlConnection)dalConnection;
        }
        
        #endregion
        
        #region DAL methods

        /// <summary>
        /// Loads a AdvGenderLookupCollection collection from the database.
        /// </summary>
        /// <param name="checkDate">The checkDate parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <param name="advGenderID">The advGenderID parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <param name="advocacyGroupID">The advocacyGroupID parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <param name="timeZone">The timeZone parameter of the AdvGenderLookupCollection to fetch.</param>
        /// <returns>A list of <see cref="AdvGenderLookupDto"/>.</returns>
        public List<AdvGenderLookupDto> Fetch(SmartDate checkDate, int advGenderID, int advocacyGroupID, short timeZone)
        {
            using (_connection)
            {
                _connection.Open();
                using (var cmd = new SqlCommand("procAdmin_AdvGendersByActiveDate_Select", _connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@CheckDate", checkDate.DBValue).DbType = DbType.DateTime;
                    cmd.Parameters.AddWithValue("@AdvGenderID", advGenderID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@AdvocacyGroupID", advocacyGroupID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@TimeZone", timeZone).DbType = DbType.Int16;
                    var dr = cmd.ExecuteReader();
                    return LoadCollection(dr);
                }
            }
        }

        private List<AdvGenderLookupDto> LoadCollection(IDataReader data)
        {
            var advGenderLookupCollection = new List<AdvGenderLookupDto>();
            using (var dr = new SafeDataReader(data))
            {
                while (dr.Read())
                {
                    advGenderLookupCollection.Add(Fetch(dr));
                }
            }
            return advGenderLookupCollection;
        }

        private AdvGenderLookupDto Fetch(SafeDataReader dr)
        {
            var advGenderLookup = new AdvGenderLookupDto();
            // Value properties
            advGenderLookup.AdvGenderID = dr.GetInt32("AdvGenderID");
            advGenderLookup.Name = dr.GetString("Name");
            advGenderLookup.IsDefault = dr.GetBoolean("IsDefault");

            return advGenderLookup;
        }

#endregion

    }

So here are my questions:

1. How do I set up my interface that inherits IDbConnection, so that it can be implicitly converted properly to a SQLConnection?
2. Should I still be using a Using statement when utilizing the connection like I used to for the ConnectionManager?
3. Do I need to now explicitly close the connection with the DI connection, or is that still handled automatically with GC with the using similar to the ConnectionManager?
4. Is there a better approach to this?

I'm intending to update the CslaGenFork templates to output this DAL object, so I'm looking for a best practice here.

[rockfordlhotka]

I answered a stackoverflow question similar to this: https://stackoverflow.com/questions/72295421/upgrade-to-csla-6-connectionmanager-problem

Maybe that'll help?

[rockfordlhotka]

1. That bug should be fixed in v6.1, and should allow normal use of data access patterns via DI
2. My understanding is that you can still use DI, you just might need to create your own sub-scopes by injecting an IScopeFactory into your DAL and then manually calling GetRequiredService to create sub-scoped services (but I haven't tried this myself)

[mtavares628]

@TheCakeMonster - Where can I take a look at the code for this DotNetStandard.DataAccess package? And do you have a code snippet of how you are using it in your DAL?

@rockfordlhotka - So the DI method seems like a lot more hoops need to be jumped through. I understand that the ConnectionManager can no longer be a static class, but what would be the downside of injecting an instance of that into the DAL and using it, if it is already capable of handling the transactional stuff and not having to re-invent the wheel with DI? Or am I misinterpreting and ConnectionManager just won't work at all going forward?

[rockfordlhotka]

I think it could work fine. My understanding is that it just shouldn't be necessary, because DI should do this work for you.

If you create the db connection and/or transaction as a scoped service, then when the data portal ends the scope (scope is per-call), the transaction/db are closed/disposed as needed - exactly what the connection managers were doing.

Even if we did restore the connection manager concept, I would expect they'd rely on DI scopes to create/dispose the managers, and so, again, what value would they provide?

[TheCakeMonster]

@mtavares628 Sure, here you go.

The source of the package is at https://github.com/DotNotStandard/DataAccess and the package available for consumption from Nuget

I've just pushed a very early version of the result of a ProjectTracker tutorial video I am recording for DesiGen - hopefully tomorrow - to https://github.com/TheCakeMonster/Examples/tree/master/CSLA6/ProjectTracker/BlazorServer/V1

You basically start a transaction in a root object, and request the 'current' connection in any repository/DAL consumed by that root or any children. Hopefully the sample is simple enough.

Note that this sample is NOT a complete replication of ProjectTracker's equivalent object model. In ProjectTracker, the ProjectResources collection - and to some extent the ProjectResourceEdit object - are quite specialised. I haven't attempted any of that specialisation yet; instead, this shows pretty much what DesiGen generates by default for a graph of this type. The root object is about right, the child collection and object are, at best, vaguely indicative!

[TheCakeMonster]

It's quite possible that this package isn't the best solution to this problem. I am, of course, very interested in Rocky's solution; if I can do away with a custom package then I will. However, I wanted to get the templates out to people since we're now a month into CSLA 6 being available, and I needed something that I felt was reasonably likely to work without causing them a headache.

It does appear to do the trick, for the time being, but YMMV.

[rockfordlhotka]

I think there are several ways to use DI to manage database connections. The key thing to understand is that each server-side data portal request runs in its own DI scope, so all scoped services are created for the lifetime of the request and are disposed/closed at the end of the request.

You can't subclass SqlConnection, so it needs to be wrapped in another class. For example, you could have a base class like this:

using Microsoft.Data.SqlClient;

namespace CslaDataAccess
{
  /// <summary>
  /// Base class for scoped db connection provider
  /// </summary>
  public class DbProvider
  {
    public virtual SqlConnection? DbConnection { get; protected set; }
    public virtual SqlTransaction? DbTransaction { get; protected set; }
  }
}

And then a subclass for each specific database connection string you need to inject into your code.

using Microsoft.Data.SqlClient;

namespace CslaDataAccess
{
  public class MainDb : DbProvider
  {
    public MainDb()
    { 
      DbConnection = new SqlConnection("main db connection string"); 
      DbTransaction = DbConnection.BeginTransaction();
    }
  }

  public class LoggerDb : DbProvider
  {
    public LoggerDb() => DbConnection = new SqlConnection("logger db connection string");
  }
}

In your Program.cs register these services.

services.AddScoped<MainDb>();
services.AddScoped<LoggerDb>();

Then inject MainDb and LoggerDb into your DAL when you need access to these specific services.

Or maybe you say that each time you use LoggerDb it needs to be per-use, not for the whole scope. That's fine, make it a transient service in Program.cs:

services.AddScoped<MainDb>();
services.AddTransient<LoggerDb>();

That way, each time you inject a LoggerDb, you'll get a new instance, while MainDb will be one instance for the entire server-side data portal request.

Conceptually, your code that uses these services does this:

using (var scope = provider.CreateScope())
{
  var mainDb = scope.ServiceProvider.GetRequiredService<MainDb>();
  // do stuff here
  mainDb.DbTransaction?.Commit();
}

Though in reality you rely on the data portal to create/dispose the scope, so your DAL code can just inject the MainDb service and use it, so a DAL would be more like:

namespace CslaDataAccess
{
  /// <summary>
  /// This is a transient service
  /// </summary>
  public class PersonDal
  {
    public PersonDal(MainDb mainDb)
    {
      if (mainDb is null) throw new NullReferenceException(nameof(mainDb));
      MainDb = mainDb;
    }

    private MainDb MainDb { get; set; }

    public async Task<int> Insert(PersonData data)
    {
      int newId = -1;
      using (var command = MainDb.DbTransaction.Connection.CreateCommand())
      {
        // set up command
        using (var reader = await command.ExecuteReaderAsync())
        {
          // get newid from reader
        }
      }
      await MainDb.DbTransaction?.CommitAsync();
      return newId;
    }
  }
}

[Chicagoan2016]

@mtavares628 Did you get a chance to try Rocky's solution. We are in the same boat; all of our applications use ConnectionManager and we would like to take the DI route in the future.

[mtavares628]

@Chicagoan2016 Yes, I ended up getting things working with Rocky's suggestion. I used his same DBProvider class, but am not currently doing anything with the DbTransaction, just the DbConnection, since I'm still relying on the TransactionScope on the BO to handle the transactional access. I'm also passing in the connection string, so mine looks like this:

    public class MainDb : DbProvider
    {
        public MainDb(string connectionString)
        {
            DbConnection = new SqlConnection(connectionString);
            DbConnection.Open();
        }
    }

So then my program.cs has:

builder.Services.AddScoped<MainDb>(options => new MainDb(builder.Configuration.GetConnectionString("MainConnectionString")));

And here is a sample of one of my DAL Objects:

    public partial class AdvRaceDal : IAdvRaceDal
    {
        private MainDb DalDb { get; set; }
        
        public AdvRaceDal(MainDb dalDb)
        {
            DalDb = dalDb;
        }

        public AdvRaceDto Fetch(int advRaceID)
        {
            using (var cmd = new SqlCommand("procAdmin_AdvRaceByAdvRaceID_Select", DalDb.DbConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@AdvRaceID", advRaceID).DbType = DbType.Int32;
		var dr = cmd.ExecuteReader();
                var data = Fetch(dr);
		return data;
            }
        }

        private AdvRaceDto Fetch(IDataReader data)
        {
            var advRace = new AdvRaceDto();
            using (var dr = new SafeDataReader(data))
            {
                if (dr.Read())
                {
                    advRace.AdvRaceID = dr.GetInt32("AdvRaceID");
                    advRace.Name = dr.GetString("Name");
                    advRace.Description = dr.IsDBNull("Description") ? null : dr.GetString("Description");
                    advRace.NDRNTimestamp = dr.GetValue("NDRNTimestamp") as byte[];
                }
            }
            return advRace;
        }
    }

This seems to work fine in my dev testing, but hasn't really been tested with multiple users and connections at this point.