Multiple SQL Server connection to my ASP.NET web application

Question

I have a class for establishing connection to sql server. now i want to connect my application with 3 connections respective of user accessing my application. how i can implement this with this class.

namespace SafetyNet.Logic
{
    public class DBManager : IDisposable
    {
        #region Privates
        public const string Database_Index = "";

        private String _innerTransName = "innerTransaction";
        private SqlConnection _conn = null;
        private SqlTransaction _trans = null;
        private bool _disposed = false;
        public static readonly string DBConnectionString = ConfigurationManager.ConnectionStrings["Connection String"].ToString();

        public DBManager()
            : this(DBConnectionString)
        {
            string connection = "";   

            this._innerTransName = "Guide" + DateTime.UtcNow.Ticks.ToString();
        }

        public DBManager(string connectionString)
        {
            string connection = "";
            _conn = new SqlConnection(connectionString);
            this._innerTransName = "Guide" + DateTime.UtcNow.Ticks.ToString();
        }

        public String ConnectionString
        {
            get 
            {
                return ConnectionString;
            }
        }

        private void Open()
        {
            if (_conn != null && _conn.State != ConnectionState.Open)
            {
                _conn.Open();
            }
        }

        public void Close()
        {
            if (_conn != null)
            {
                if (_conn.State == ConnectionState.Open)
                    _conn.Close();
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected void Finalize()
        {
            Dispose(true);
        }

        private void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (_trans != null)
                {
                    _trans.Dispose();
                    _trans = null;
                }
                if (_conn != null)
                {
                    _conn.Close();
                    _conn.Dispose();
                    _conn = null;
                }
                _disposed = true;
            }
        }

        #endregion

        #region Transaction

        public SqlTransaction BeginTransaction()
        {
            Open();
            if (_trans == null)
                _trans = _conn.BeginTransaction(IsolationLevel.ReadUncommitted, _innerTransName);
            return _trans;
        }

        public void CommitTransaction()
        {
            if ((_conn.State == ConnectionState.Open) && (_trans != null))
            {
                _trans.Commit();
            }
        }

        public void RollbackTransaction()
        {
            if ((_conn.State == ConnectionState.Open) && (_trans != null))
            {
                _trans.Rollback(_innerTransName);
            }
        }

        public SqlConnection GetConnection()
        {
            return _conn;
        }

        #endregion
    }
}

Thanks in advance.

Is there some reason you think you can do better than the connection pool? — Andrew Barber
– Andrew Barber, Commented Jan 21, 2014 at 6:19

TGH · Accepted Answer · 2014-01-21 06:19:55Z

1

If you want the three connections to use different connections/permissions you have to add three different connection strings to your config and load them based on some discriminator (user type etc)

Change the code to work with the different connection strings.

However, if this is a question about concurrent connections (the same connection string) you can rely on default connection pooling supported by .Net. It supports many concurrent connections by default.

answered Jan 21, 2014 at 6:19

TGH

39.4k13 gold badges106 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3205585 Over a year ago

Connections we are declared in global part. Am not able to check conditions on these parts and this connection statement should be static. I have problem to meet all these conditions together. Once my DBManager class is called its connection by default goes to the connection as mentioned in the example.

TGH Over a year ago

I don't see how you can solve this without being able to load connection strings dynamically based on user type. Right now your application seems hard coded to only work with one connection string.

Collectives™ on Stack Overflow

Multiple SQL Server connection to my ASP.NET web application

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related