using System.Data;
using Microsoft.Data.SqlClient;

namespace [myapp].Database
{
public interface DataConnectionInterface
{
public string DefaultConnectionStringKey { get; }
public void OpenConnection();
public void OpenConnection(string ConnectionStringKey);
public void CloseConnection();
public Boolean TransactionPending { get; }
public ConnectionState ConnectionState { get; }
public void BeginTransaction(IsolationLevel IsolationLevel = IsolationLevel.Unspecified);
public void CommitTransaction();
public void RollbackTransaction();
public DataTable GetDataTable(string sql);
public SqlDataReader GetSqlDataReader(string sql);
public SqlCommand GetsqlCommand(string sql);
public void Execute(string sql);
}
}

using Microsoft.Data.SqlClient;
using System.Data;

namespace [myapp].Database
{
    public class DataConnection : DataConnectionInterface
    {
        private SqlConnection _SqlConnection = new SqlConnection();
        private SqlTransaction? _sqlTransaction = null;
        public string DefaultConnectionStringKey { get { return "default"; } }
        public void OpenConnection()
        {
            OpenConnection(DefaultConnectionStringKey);
        }
        public void OpenConnection(string ConnectionStringKey)
        {
            try
            {
                var builder = WebApplication.CreateBuilder();
                string? connectionString = builder.Configuration.GetConnectionString(ConnectionStringKey);
                if (connectionString == null) throw new Exception("The connection string is empty");
                _SqlConnection.ConnectionString = connectionString;
                _SqlConnection.Open();
            }
            catch (Exception ex) { throw new Exception("DataConnection:OpenConnection - " + ex.Message, ex); }
        }
        public void CloseConnection()
        {
            if (ConnectionState != ConnectionState.Closed)  
            try
            {  
                _SqlConnection.Close();
            }
            catch (Exception ex) { throw new Exception("DataConnection:CloseConnection - " + ex.Message, ex); }
        }
        public Boolean TransactionPending { get { return _sqlTransaction != null; } }
        public ConnectionState ConnectionState { get { return _SqlConnection.State; } }
        public void BeginTransaction(IsolationLevel IsolationLevel = IsolationLevel.Unspecified)
        {
            if (ConnectionState == ConnectionState.Open)
            try
            {
                _sqlTransaction = _SqlConnection.BeginTransaction(IsolationLevel);
            }
            catch (Exception ex) { throw new Exception("DataConnection:BeginTransaction - " + ex.Message, ex); }
        }
        public void CommitTransaction()
        {
            if (_sqlTransaction != null)
            try
            {
                _sqlTransaction.Commit();
                _sqlTransaction = null;
            }
            catch (Exception ex) { throw new Exception("DataConnection:CommitTransaction - " + ex.Message, ex); }
        }
        public void RollbackTransaction()
        {
            if (_sqlTransaction != null)
            try
            {
                _sqlTransaction.Rollback();
                _sqlTransaction = null;
            }
            catch (Exception ex) { throw new Exception("DataConnection:RollbackTransaction - " + ex.Message, ex); }
        }
        public SqlCommand GetsqlCommand(string sql)
        {
            SqlCommand sqlCommand;
            try
            {
                if (_sqlTransaction == null) sqlCommand = new SqlCommand(sql, _SqlConnection);
                else
                sqlCommand = new SqlCommand(sql, _SqlConnection, _sqlTransaction);
                return sqlCommand;
            }
            catch (Exception ex) { throw new Exception("DataConnection:GetsqlCommand - " + ex.Message, ex); }
        }
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, _SqlConnection);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex) { throw new Exception("DataConnection:GetDataTable - " + ex.Message, ex); }
        }
        public SqlDataReader GetSqlDataReader(string sql){
            try
            {
                SqlCommand cmd = GetsqlCommand(sql);
                return cmd.ExecuteReader();
            }
            catch (Exception ex) { throw new Exception("DataConnection:GetSqlDataReader - " + ex.Message, ex); }
        }
        public void Execute(string sql)
        {
            try
            {
                GetsqlCommand(sql).ExecuteNonQuery();
            }
            catch (Exception ex) { throw new Exception("DataConnection:GetSqlDataReader - " + ex.Message, ex); }
        }
        public string TextFormatted(string text)
        {
            return "'" + text.Replace("'","''") + "'";
        }

    }
}

appsettings.json
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
      "ConnectionStrings": {
        "default": "User ID=*****;Password=*****;Data Source=*****\\******;initial catalog=mydatabase;Encrypt=false;",
    }
}