locked
The way I use connection pooling..., any ideas if it can be improved? RRS feed

  • Question

  • User1116310904 posted

    The following code shows my main database connection class, I inherit this class in all my other model classes to get connections and stuff like that.

    I am not sure when I call mConnection.Dispose() does it remove the original connection from pool as well? Cause I dont want that to happen.

    I hope to get some feedbacks from you experienced people :)


    Thanks in advance for your help :)


    [Serializable]
    public class clsMyDB : IDisposable
    {
        protected SqlConnection mConnection;

        public clsMyDB()
        {
            InitDB();
        }

        public void Dispose()
        {
            try
            {
                mConnection.Dispose();
            }
            catch
            {
                //Do Nothing
            }
        }

        ~clsMyDB()
        {
            try
            {
                this.Dispose();
            }
            catch
            {
                //Do Nothing
            }
        }

        protected bool InitDB()
        {
            try
            {
                mConnection = new SqlConnection("Data Source = MyDataSource;" +
                                                "Initial Catalog = MyDatabase;" +
                                                "User Id = MyUserID;Password=MyPassword;" +
                                                "Min Pool Size=20; Max Pool Size=200;" +
                                                "Connection Timeout=30;");
            }
            catch
            {
                return false;
            }
            return true;
        }

        public SqlConnection GetDBConnection()
        {
            return mConnection;
        }

        protected bool SetDBConnection(SqlConnection pConnection)
        {
            try
            {
                mConnection = pConnection;
            }
            catch
            {
                return false;
            }
            return true;
        }

        public void CloseConnection()
        {
            try
            {
                mConnection.Close();
            }
            catch
            {
                //Do Nothing
            }
        }
    }

    In other classes I use MyDB like this:


    [Serializable]
    public class clsUserManager : clsMyDB, IDisposable
    {
        public clsUserManager()
        {
        }
        ~clsUserManager()
        {
            try
            {
                this.Dispose();
            }
            catch
            {
                //Nothing
            }
        }
        public void Dispose()
        {
            try
            {
                base.Dispose();
            }
            catch
            {
                //Nothing
            }
        }
        public clsUserRec GetUser(int pUserID)
        {
            clsUserRec lUserRec = new clsUserRec();
            try
            {
                mConnection.Open();
                string lSQL = "SELECT * FROM Users WHERE ID = " + pUserID.ToString();
                SqlCommand lCommand = new SqlCommand(lSQL, mConnection);
                SqlDataReader lReader = lCommand.ExecuteReader();

                if (lReader.HasRows)
                {
                    lReader.Read();

                    lUserRec.mID = int.Parse(lReader["ID"].ToString());
                    lUserRec.mUserName = lReader["UserName"].ToString();
                }
                lReader.Close();
                mConnection.Close();

                lCommand.Dispose();
                lReader.Dispose();
            }
            catch(Exception ex1)
            {
                return null;
            }
            return lUserRec;
        }

    ....

    Friday, June 11, 2010 11:20 AM

Answers

  • User197322208 posted

    1-Why dont I need to use idisposable in the derived classes? Isnt it that every class should take care of its own disposing issues?

    Yes, that's the cause. If A have some unmanaged resource, and

    class  B:  A

    and B has NOT unmanaged resource, WHY B have to implement IDisposable ? A should take care ...

    Please read

    http://www.bluebytesoftware.com/blog/PermaLink.aspx?guid=88e62cdf-5919-4ac7-bc33-20c06ae539ae

    2-Sorry I dont know what is a finalizer and where I have used them.

    Please read

    http://www.bluebytesoftware.com/blog/PermaLink.aspx?guid=88e62cdf-5919-4ac7-bc33-20c06ae539ae

    3-Would you please give me an example on how to use "using" for sqldatareader or sqlcommand?

    using(SqlCommand lCommand = new SqlCommand(lSQL, mConnection))


    {

    using(            SqlDataReader lReader = lCommand.ExecuteReader())
    {

    //code
    }


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 12, 2010 4:15 PM

All replies

  • User197322208 posted

    Dispose does not remove connection from the pool  - it just marked as "released" adn waiting for a new request

    but some problems :

    1. you do not have to use idisposable  on derived classes

    2. you do not have to use finalizer- just use GC.SupressFinalize for the class  - see http://msdn.microsoft.com/en-us/library/b1yfkh5e.aspx

    3. for sqlcommand and sqldatareader please use "using"


    Saturday, June 12, 2010 3:44 AM
  • User1116310904 posted

    Thanks a lot for your review and reply, but it raises some questions for me:


    1-Why dont I need to use idisposable in the derived classes? Isnt it that every class should take care of its own disposing issues?

    2-Sorry I dont know what is a finalizer and where I have used them.

    3-Would you please give me an example on how to use "using" for sqldatareader or sqlcommand?


    Sorry for my basic questions, I am a newbie on C# coding and I appreciate your help :)

    Mike

    Saturday, June 12, 2010 12:35 PM
  • User197322208 posted

    1-Why dont I need to use idisposable in the derived classes? Isnt it that every class should take care of its own disposing issues?

    Yes, that's the cause. If A have some unmanaged resource, and

    class  B:  A

    and B has NOT unmanaged resource, WHY B have to implement IDisposable ? A should take care ...

    Please read

    http://www.bluebytesoftware.com/blog/PermaLink.aspx?guid=88e62cdf-5919-4ac7-bc33-20c06ae539ae

    2-Sorry I dont know what is a finalizer and where I have used them.

    Please read

    http://www.bluebytesoftware.com/blog/PermaLink.aspx?guid=88e62cdf-5919-4ac7-bc33-20c06ae539ae

    3-Would you please give me an example on how to use "using" for sqldatareader or sqlcommand?

    using(SqlCommand lCommand = new SqlCommand(lSQL, mConnection))


    {

    using(            SqlDataReader lReader = lCommand.ExecuteReader())
    {

    //code
    }


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 12, 2010 4:15 PM
  • User1116310904 posted

    Thanks a lot for your replies sir :)

    Saturday, June 12, 2010 5:00 PM