ConnectionManager Replacement

[Brad-IMS]

I am updating a Csla4/.Net Framework/Ado.Net library of 40+ classes to Csla6.2.2/.Net6. I implemented EFCore7 in the rewrite, but quickly found out EF cannot duplicate some of the things that we were doing in Ado.Net (multiple joins, etc.), at least until EFCore8, or possibly later. So I am switching back to implementing Ado,Net in the new environment. I found guidance in this post, with a reply by Rocky: https://stackoverflow.com/questions/72295421/upgrade-to-csla-6-connectionmanager-problem
I am fine up to the section on establishing the connection to the database in Program.cs. Rocky's example is:

  services.AddScoped(typeof(OracleConnection), () =>
    {
      // initialize the connection here
      return connection;
    });
  services.AddScoped<MyDal>();

My problem is with "// initialize the connection here". In Csla4, ConnectionManager established the connection. You just fed it the config file key. I've spent a full day searching the net for an answer, but nothing seems to work. Here's what I currently have (Blazor app):

builder.Services.AddScoped(typeof(SqlConnection), () =>
{
    SqlConnection connection = builder.Configuration.GetConnectionString("DefaultConnection");
    return connection;
});
builder.Services.AddScoped<TicketStatusDal>();

On the GetConnectionString line I am getting the error: "Error CS1660 Cannot convert lambda expression to type 'Type' because it is not a delegate type". Can anyone steer me to what should be there instead?

[Chicagoan2016]

I am in the same boat, we have several applications in Csla 4.xx , would love to update them to 6.xx.

[rockfordlhotka]

It looks like this line is trying to put a string into a SqlConnection variable?

SqlConnection connection = builder.Configuration.GetConnectionString("DefaultConnection");

Shouldn't you get the connection string and then use it to create a new SqlConnection instance? (and possibly open it?)

[mtavares628]

I've upgraded to CSLA 6 and took a slightly different approach than Rocky's example that I think is a little closer in use to using connection manager. First I created a class that will take the connection string and return a new opened SQLConnection.

public class DbConnect
    {
        private string ConnectionString { get; set; }

        public DbConnect(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            return conn;
        }
    }

Then in my program.cs I'm adding the class to the services collection:

builder.Services.AddScoped<DbConnect>(options => new DbConnect(builder.Configuration.GetConnectionString("ConnectionStringName")));

Then I inject that class into my DAL object and use it similarly to the connection manager:

public partial class ClientNoteDal : IClientNoteDal
    {
        private DbConnect DalDb { get; set; }
        
        public ClientNoteDal(DbConnect dalDb)
        {
            DalDb = dalDb;
        }
       
        public ClientNoteDto Fetch(int noteID)
        {
            using (var ctx = DalDb.GetConnection())
            {
                using (var cmd = new SqlCommand("procNotesByNoteID_Select", ctx))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@NoteID", noteID).DbType = DbType.Int32;
                    var dr = cmd.ExecuteReader();
                    var data = Fetch(dr);
		    return data;
                }
            }
        }

        private ClientNoteDto Fetch(IDataReader data)
        {
            var clientNote = new ClientNoteDto();
            using (var dr = new SafeDataReader(data))
            {
                if (dr.Read())
                {
                    clientNote.NoteID = dr.GetInt32("NoteID");
                    clientNote.Subject = dr.GetString("Subject");
                    clientNote.Note = dr.GetString("Note");
                    clientNote.DateOfNote = dr.GetSmartDate("DateOfNote", true);
                    clientNote.ClientID = (int?)dr.GetValue("ClientID");
                }
            }
            return clientNote;
        }      
    }

This way I can put the connection in a using statement at the point that it needs to run the command and it will be immediately closed and disposed of when the Fetch method is finished.

[Brad-IMS]

Thanks. That works.

[rockfordlhotka]

I added a link to this thread from the upgrading to CSLA 6 doc.

[JacoJordaan]

I have ported my Csla 5 to the latest Csla 8 code and are running into an issue with transaction handling in my DAL.
In Csla 5 the connection manager was used it it worked fine. In Csla 8 I am using the suggested option as per this thread.

I am receiving 2 types of errors

1. Trying to enlist in ditributed transaction related errors
2. Connection pool errors: The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I am using ADO.NET code in my DAL. I am also using the transaction scope attributes in the data portal call in the business object e.g. [Transactional(TransactionalTypes.TransactionScope)]
I am using only one connection string

This is my connection class:

public class QSDbConnection : IQSDbConnection
{   
    private readonly ILogger<QSDbConnection> _logger;
    public QSDbConnection(ILogger<QSDbConnection> logger)
    {
        _logger = logger;
        _logger.LogInformation("Init QSDbConnection");            
    }

    public SqlConnection GetConnection(string connectionString)
    {        
        var conn = new SqlConnection(connectionString);   
        conn.Open();
        return conn;
    }

    public IDbConnection GetDapperConnection(string connectionString)
    {        
        return new SqlConnection(connectionString);
    }
}

Calls to the connection is wrapped in using statements e.g.:

using (var ctx = conn.GetConnection("QSDb"))
{
    using (var cm = ctx.CreateCommand())
    {        
        cm.CommandText = RequiredPartBase_FetchSql;
        cm.CommandType = System.Data.CommandType.Text;
        cm.Parameters.AddWithValue("@ID", id);
		....

A few questions comes to mind:

• Has anyone experience the same issue and was able to resolve this?
• Is the using of the transaction scope attributes in the business object still a valid way of handling transactions?