none
How to establish DB connection using singleton pattern RRS feed

  • Question

  • Hi All, I have done R&D on Single ton design and tried to establish the connection. I am able to  define the class with requirements but couldn't know how to call the method . Please help me on this.

     public class Database
        {
            private static Database _instance=null;
            private static SqlConnection con = null;
           static  object _syncObject = new object();
            private Database()
            {
                string conString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
                try
                {
                    con = new SqlConnection(conString);
                    con.Open();

                }

                catch (Exception e)
                {
                     con.Close();
                   Console.Write(e.Message);
                }
            }
                 
            public static Database Instance
            {
                get
                {
                    if (_instance == null)
                    {
                        lock (_syncObject)
                        {
                            if (_instance == null)
                            {
                                _instance = new Database();
                            }
                        }
                    }
                    return _instance;
                }
            }


        }

    Monday, August 26, 2019 12:30 PM

Answers

  • Hi Karen,

        Please let me know your concerns and please guide me how to do the things.Waiting for valuable replies.

    In regards to concerns, it's bad practice to depend on a single connection and also not good to keep a connection open.

    Even though Entity Framework is not being used the take-away is Entity Framework opens and closes connections as needed.

    Perhaps 20 years ago developers felt the need to have a single connection, not going to open that can of worms but safe to say developers have moved on from a single connection and move to creating a connection when needed as a local variable in a single method, the exception is when two connections from two different data sources are needed at the same time e.g. two servers working in tangent is one exception.

    Yet with all that said, you need to made an educated decision with the information provided by all those who have offered assistance.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Sai Pranav Wednesday, September 4, 2019 7:46 AM
    Saturday, August 31, 2019 11:00 PM
    Moderator
  • Hi Sai Pranav, 

    Thank you for posting here.

    For your question, you need to make a little change to your code.

    I make a test based on your code, and you can refer it.

        public sealed class Database
        {
            private static Database _instance = null;
            private static readonly object _syncObject = new object();
            private string conString = @"Data Source=...";
            private Database()
            {
                DataTable dt = new DataTable();
                using (SqlConnection Conn = new SqlConnection(conString))
                {
                    Conn.Open();
                    if (Conn.State == System.Data.ConnectionState.Open)
                    {
                        Console.WriteLine("successfully connect");
                    }
                    string sql = "select * from TableNames";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = Conn;
                            da.SelectCommand = cmd;
                            da.Fill(dt);
                        }
                    }
                }
            }
            public static Database Instance
            {
                get
                {
                    if (_instance == null)
                    {
                        lock (_syncObject)
                        {
                            if (_instance == null)
                            {
                                _instance = new Database();
                            }
                        }
                    }
                    return _instance;
                }
            }
        }
            static void Main(string[] args)
            {
                Database db = Database.Instance;
                Console.ReadKey();
            }

    Data in table TableNames:

    Data in my DataTable dt:


    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, August 27, 2019 9:39 AM
    Moderator
  • Hi Sai Pranav,

    I'll give an example of turning the Database singleton to a disposable object, and thus you will be able to close connection when disposing the object:

    public class DataAccess<TConnection>: IDisposable
        where TConnection: class, IDbConnection, new()
    {
        private static DataAccess<TConnection> _instance = null;
        private static IDbConnection _innerConnection = null;
        static object _syncObject = new object();
        const string defaultConnectionString = "Data Source=.\\;Initial Catalog=TestDb;Integrated Security=True;Pooling=False";
    
        private DataAccess(string connectionString = defaultConnectionString)
        {
            try
            {
                _innerConnection = new TConnection();
                _innerConnection.ConnectionString = connectionString;
                _innerConnection.Open();
            }
    
            catch (Exception e)
            {
                _innerConnection.Close();
                Console.Write(e.Message);
            }
        }
    
        public static DataAccess<TConnection> Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (_syncObject)
                    {
                        _instance = _instance ?? new DataAccess<TConnection>();
                    }
                }
                return _instance;
            }
        }
    
        public TResult ExecuteCommand<TResult>(Func<IDbCommand, TResult> func)
        {
            using (var cmd = _innerConnection.CreateCommand())
            {
                return func.Invoke(cmd);
            }
        }
    
        public void Dispose()
        {
            if (_innerConnection != null && _innerConnection.State == ConnectionState.Open)
            {
                _innerConnection.Close();
            }
            _innerConnection.Dispose();
        }
    }

    and we can use our DataAccess object this way :

    // you can use this object this way 
    using(var instance = DataAccess<SqlConnection>.Instance){
        instance.ExecuteCommand<int>((cmd) => {
           cmd.CommandText = ".......";
           return cmd.ExecuteNonQuery();
        });
    }
    using (var instance = DataAccess<SqlConnection>.Instance)
    {
        instance.ExecuteCommand<List<dynamic>>(cmd =>
        {
            cmd.CommandText = "select * from TableA";
            var reader = cmd.ExecuteReader();
            var resultToReturn = new List<dynamic>();
            while (reader.Read())
            {
                resultToReturn.Add(new { id = reader[0], dept = reader[1] });
            }
            return resultToReturn;
        });
    }


    Best Regards,

    Mouad.


    • Edited by Cherkaoui.Mouad Sunday, September 1, 2019 2:43 PM
    • Marked as answer by Sai Pranav Wednesday, September 4, 2019 7:46 AM
    Saturday, August 31, 2019 7:46 PM

All replies

  • Hello,

    Although this can be done it's unwise to have a connection that is open longer than it needs to be, best to use a using statement for anytime a connection is needed. 

    About the only thing that makes sense to have available throughout the life of an application is a connection string optionally encrypted. 

    If you were to look at Entity Framework or Entity Framework Core, EF handles connections, open use close.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, August 26, 2019 1:34 PM
    Moderator
  • Can you give an example. I will be thankful to you or guide me.
    Tuesday, August 27, 2019 6:56 AM
  • Hi Sai Pranav, 

    Thank you for posting here.

    For your question, you need to make a little change to your code.

    I make a test based on your code, and you can refer it.

        public sealed class Database
        {
            private static Database _instance = null;
            private static readonly object _syncObject = new object();
            private string conString = @"Data Source=...";
            private Database()
            {
                DataTable dt = new DataTable();
                using (SqlConnection Conn = new SqlConnection(conString))
                {
                    Conn.Open();
                    if (Conn.State == System.Data.ConnectionState.Open)
                    {
                        Console.WriteLine("successfully connect");
                    }
                    string sql = "select * from TableNames";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = Conn;
                            da.SelectCommand = cmd;
                            da.Fill(dt);
                        }
                    }
                }
            }
            public static Database Instance
            {
                get
                {
                    if (_instance == null)
                    {
                        lock (_syncObject)
                        {
                            if (_instance == null)
                            {
                                _instance = new Database();
                            }
                        }
                    }
                    return _instance;
                }
            }
        }
            static void Main(string[] args)
            {
                Database db = Database.Instance;
                Console.ReadKey();
            }

    Data in table TableNames:

    Data in my DataTable dt:


    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, August 27, 2019 9:39 AM
    Moderator
  • Thank you so much for your post. I want to add one more thing to this. I want to red the data by returning data table. Here i have two options. One is by passing Query as the parameter and 2nd one is by passing SP as parameter. Can you/any one help me on this. Waiting for valuable replies.
    Wednesday, August 28, 2019 1:25 PM
  • Hi Sai Pranav,

    Thanks for your feedback.

    I have updating my code, and you can refer it.

    Best Regards,

    Xingyu Zhao 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 30, 2019 7:31 AM
    Moderator
  • Hi Zhao,

        Thanks for your support. Here i have a doubt. You have established the connection while performing the crud operations. Can i use a method to establish the connection to perform the different operations(like delete,update,...). It means re usability of connection. Please guide me on this.

    Saturday, August 31, 2019 12:37 PM
  • Although I'm not a fan for what is being asked this will do this.

    Install the following NuGet package. Create a class project, add the NuGet package as per the class below.

    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace ApplicationDataConnector 
    {
        public class SqlServerConnections
        {
            private static readonly Lazy<SqlServerConnections>
                lazy = new Lazy<SqlServerConnections>(() => new SqlServerConnections());
    
    
            public static SqlServerConnections Instance => lazy.Value;
    
            //private static SqlServerConnections _instance;
            private readonly Hashtable _connections = new Hashtable();
    
    
            /// <summary>
            /// Reset an existing connection string
            /// </summary>
            /// <param name="connectionString">An existing connection string</param>
            public void Reset(string connectionString)
            {
                SqlConnection connection = null;
    
                try
                {
                    connection = (SqlConnection)(_connections[connectionString]);
                    connection.Dispose();
                    connection = null;
                }
                catch (Exception)
                {
                    // ignored
                }
            }
    
            /// <summary>
            /// Used to reset all known connections to null
            /// </summary>
            public void ResetAll()
            {
                foreach (var item in _connections)
                {
                    SqlConnection connection = null;
    
                    try
                    {
                        connection = (SqlConnection)item;
                        connection.Dispose();
                        connection = null;
                    }
                    catch (Exception)
                    {
                        // ignored
                    }
                }
            }
            /// <summary>
            /// Returns an open connection for connection string
            /// </summary>
            /// <param name="connectionString">Valid connection string</param>
            /// <returns>Connection</returns>
            public SqlConnection Connection(string connectionString)
            {
                SqlConnection connection = null;
                bool bNeedAdd = false;
                try
                {
                    connection = (SqlConnection)(_connections[connectionString]);
                }
                catch (Exception)
                {
                    // ignored
                }
    
                if (connection == null)
                {
                    bNeedAdd = true;
                }
    
                if (connection == null || connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed)
                {
                    try
                    {
                        connection.Dispose();
                        connection = null;
                    }
                    catch (Exception)
                    {
                        // ignored
                    }
    
                    connection = new SqlConnection();
                }
    
                if (connection.State == ConnectionState.Closed)
                {
                    connection.ConnectionString = connectionString;
                    connection.Open();
                }
    
                if (bNeedAdd)
                {
                    _connections.Add(connectionString, connection);
                }
    
                return connection;
            }
    
        }
    }
    

    Testing was done against a SQL-Server database table. I created a unit test project, add the following class. Add a reference to the unit test project for the project listed above.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace UnitTestProject1
    {
        public class TestBase : SqlServerConnection
        {
            public TestBase()
            {
                DatabaseServer = "KARENS-PC";
                DefaultCatalog = "NorthWindAzure";
            }
    
            protected void ResetConnection()
            {
                ApplicationDataConnector.SqlServerConnections.Instance.Reset(ConnectionString);
            }
    
            protected DataTable ConnectionWithUsingStatement()
            {
                var dt = new DataTable();
    
                using (var cn = ApplicationDataConnector.SqlServerConnections.Instance.Connection(ConnectionString))
                {
                    mHasException = false;
    
    
                    const string selectStatement =
                        "SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
                        "cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
                        "cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
                        "INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier;";
    
                    using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
                    {
                        try
                        {
                            cn.Open();
                            dt.Load(cmd.ExecuteReader());
                        }
                        catch (Exception e)
                        {
                            mHasException = true;
                            mLastException = e;
                        }
                    }
    
                }
    
                return dt;
            }
    
            public bool CustomersWithError2(out DataTable dtCustomers)
            {
                mHasException = false;
    
                dtCustomers = new DataTable();
    
                // using a invalid fieldname
                const string selectStatement =
                    "SELECT cust.CustomerIdentifer,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
                    "cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
                    "cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
                    "INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier;";
    
                using (var cn = ApplicationDataConnector.SqlServerConnections.Instance.Connection(ConnectionString))
                {
                    using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
                    {
                        try
                        {
                            dtCustomers.Load(cmd.ExecuteReader());
                        }
                        catch (Exception e)
                        {
                            mHasException = true;
                            mLastException = e;
                        }
                    }
                }
    
                return IsSuccessFul;
            }
        }
    }
    

    One unit test

    using System;
    using System.Data;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    
    namespace UnitTestProject1
    {
        [TestClass]
        public class UnitTest1 : TestBase
        {
            [TestMethod]
            public void TestMethod1()
            {
                ConnectionWithUsingStatement();
    
                Assert.IsTrue(ApplicationDataConnector
                                  .SqlServerConnections
                                  .Instance
                                  .Connection(ConnectionString).State == ConnectionState.Open);
    
                var dt = new DataTable();
    
                ConnectionWithUsingStatement();
    
                ApplicationDataConnector.SqlServerConnections.Instance.Connection(ConnectionString).Close();
    
                Assert.IsTrue(ApplicationDataConnector
                                  .SqlServerConnections
                                  .Instance
                                  .Connection(ConnectionString).State == ConnectionState.Open);
    
                ResetConnection();
    
                Assert.IsTrue(ApplicationDataConnector
                                  .SqlServerConnections
                                  .Instance
                                  .Connection(ConnectionString).State == ConnectionState.Open);
    
    
    
                CustomersWithError2(out dt);
                
                Assert.IsTrue(ApplicationDataConnector
                                  .SqlServerConnections
                                  .Instance
                                  .Connection(ConnectionString).State == ConnectionState.Open);
    
                Assert.IsTrue(dt.Rows.Count == 0);
                Assert.IsTrue(LastExceptionMessage == "Invalid column name 'CustomerIdentifer'.");
    
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, August 31, 2019 2:08 PM
    Moderator
  • Hi Karen,

        Please let me know your concerns and please guide me how to do the things.Waiting for valuable replies.

    Saturday, August 31, 2019 3:42 PM
  • Hi Sai Pranav,

    I'll give an example of turning the Database singleton to a disposable object, and thus you will be able to close connection when disposing the object:

    public class DataAccess<TConnection>: IDisposable
        where TConnection: class, IDbConnection, new()
    {
        private static DataAccess<TConnection> _instance = null;
        private static IDbConnection _innerConnection = null;
        static object _syncObject = new object();
        const string defaultConnectionString = "Data Source=.\\;Initial Catalog=TestDb;Integrated Security=True;Pooling=False";
    
        private DataAccess(string connectionString = defaultConnectionString)
        {
            try
            {
                _innerConnection = new TConnection();
                _innerConnection.ConnectionString = connectionString;
                _innerConnection.Open();
            }
    
            catch (Exception e)
            {
                _innerConnection.Close();
                Console.Write(e.Message);
            }
        }
    
        public static DataAccess<TConnection> Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (_syncObject)
                    {
                        _instance = _instance ?? new DataAccess<TConnection>();
                    }
                }
                return _instance;
            }
        }
    
        public TResult ExecuteCommand<TResult>(Func<IDbCommand, TResult> func)
        {
            using (var cmd = _innerConnection.CreateCommand())
            {
                return func.Invoke(cmd);
            }
        }
    
        public void Dispose()
        {
            if (_innerConnection != null && _innerConnection.State == ConnectionState.Open)
            {
                _innerConnection.Close();
            }
            _innerConnection.Dispose();
        }
    }

    and we can use our DataAccess object this way :

    // you can use this object this way 
    using(var instance = DataAccess<SqlConnection>.Instance){
        instance.ExecuteCommand<int>((cmd) => {
           cmd.CommandText = ".......";
           return cmd.ExecuteNonQuery();
        });
    }
    using (var instance = DataAccess<SqlConnection>.Instance)
    {
        instance.ExecuteCommand<List<dynamic>>(cmd =>
        {
            cmd.CommandText = "select * from TableA";
            var reader = cmd.ExecuteReader();
            var resultToReturn = new List<dynamic>();
            while (reader.Read())
            {
                resultToReturn.Add(new { id = reader[0], dept = reader[1] });
            }
            return resultToReturn;
        });
    }


    Best Regards,

    Mouad.


    • Edited by Cherkaoui.Mouad Sunday, September 1, 2019 2:43 PM
    • Marked as answer by Sai Pranav Wednesday, September 4, 2019 7:46 AM
    Saturday, August 31, 2019 7:46 PM
  • Hi Karen,

        Please let me know your concerns and please guide me how to do the things.Waiting for valuable replies.

    In regards to concerns, it's bad practice to depend on a single connection and also not good to keep a connection open.

    Even though Entity Framework is not being used the take-away is Entity Framework opens and closes connections as needed.

    Perhaps 20 years ago developers felt the need to have a single connection, not going to open that can of worms but safe to say developers have moved on from a single connection and move to creating a connection when needed as a local variable in a single method, the exception is when two connections from two different data sources are needed at the same time e.g. two servers working in tangent is one exception.

    Yet with all that said, you need to made an educated decision with the information provided by all those who have offered assistance.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Sai Pranav Wednesday, September 4, 2019 7:46 AM
    Saturday, August 31, 2019 11:00 PM
    Moderator