locked
question about open and close of MySqlConnection RRS feed

  • Question

  • hi

        i'd like to ask a question.   

      

    MySqlConnection connection = newMySqlConnection(stringConnection);                 MySqlCommand cmd = connection.CreateCommand();                 connection.Open();

    and i have a try catch. accoding to your opinion, i should put  connection.Close() in the block of try,or outside of block of try?

    according to you,if i open the connection and do not close,is it bad?or i open 2 times(using different istancse,like connection1 and connecton2),is it ok?

    thank u very much

    best regards

    martin

     
    • Moved by Kristin Xie Wednesday, September 3, 2014 8:03 AM
    Tuesday, September 2, 2014 9:24 AM

Answers

  • You should always dispose the connection object, and any other object that implements the IDisposable interface, when you are done using it. The easiest way of doing this is to wrap it in a using statement:

               
     using (SqlConnection connection = new SqlConnection(stringConnection))
                {
                    connection.Open();
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
    
                    }
                }
    

    If you don't wrap it in a using statement, you should call the Dispose method explicitly in a finally block:

                SqlConnection connection;
                try
                {
                    connection = new SqlConnection(stringConnection);
                    connection.Open();
                    //...
                }
                catch (Exception ex)
                {
                    //handle ex...
                }
                finally
                {
                    if (connection != null)
                        connection.Dispose();
                }

    Connection objects are meant to be shortlived, i.e. you should open the connection, fecth the data and dispose the object. Then you should create a new object if you need to fetch more data later on.

    Please remember to mark helpful posts as answer and/or helpful.

    • Proposed as answer by Mr. Zator Tuesday, September 2, 2014 1:27 PM
    • Marked as answer by martinwang1985 Thursday, September 4, 2014 8:04 AM
    Tuesday, September 2, 2014 1:09 PM
  • You should always dispose the connection object, and any other object that implements the IDisposable interface, when you are done using it. The easiest way of doing this is to wrap it in a using statement:

               
     using (SqlConnection connection = new SqlConnection(stringConnection))
                {
                    connection.Open();
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
    
                    }
                }

    If you don't wrap it in a using statement, you should call the Dispose method explicitly in a finally block:

                SqlConnection connection;
                try
                {
                    connection = new SqlConnection(stringConnection);
                    connection.Open();
                    //...
                }
                catch (Exception ex)
                {
                    //handle ex...
                }
                finally
                {
                    if (connection != null)
                        connection.Dispose();
                }

    Connection objects are meant to be shortlived, i.e. you should open the connection, fecth the data and dispose the object. Then you should create a new object if you need to fetch more data later on.

    Please remember to mark helpful posts as answer and/or helpful.

    Agreed: Create. Use. Dispose. Is the best way to deal with disposeable objects. It can cause significant issues if the connection is kept open past the function that created it (like you having cases where it is not properly disposed/finalised).

    Also note that many SqlConnection subclasses do internal connection caching. So there would be no speed advantage to keep the instance around anyway, but a great chance for resource leaks.


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2 Please mark post as helpfull and answers respectively.


    Tuesday, September 2, 2014 1:20 PM

All replies

  • hi,

    You use final and close the connection in that.

    don't open connection two time. you should close all connections. that is the good practice 


    • Edited by Shaafs Tuesday, September 2, 2014 1:02 PM
    Tuesday, September 2, 2014 1:01 PM
  • You should always dispose the connection object, and any other object that implements the IDisposable interface, when you are done using it. The easiest way of doing this is to wrap it in a using statement:

               
     using (SqlConnection connection = new SqlConnection(stringConnection))
                {
                    connection.Open();
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
    
                    }
                }
    

    If you don't wrap it in a using statement, you should call the Dispose method explicitly in a finally block:

                SqlConnection connection;
                try
                {
                    connection = new SqlConnection(stringConnection);
                    connection.Open();
                    //...
                }
                catch (Exception ex)
                {
                    //handle ex...
                }
                finally
                {
                    if (connection != null)
                        connection.Dispose();
                }

    Connection objects are meant to be shortlived, i.e. you should open the connection, fecth the data and dispose the object. Then you should create a new object if you need to fetch more data later on.

    Please remember to mark helpful posts as answer and/or helpful.

    • Proposed as answer by Mr. Zator Tuesday, September 2, 2014 1:27 PM
    • Marked as answer by martinwang1985 Thursday, September 4, 2014 8:04 AM
    Tuesday, September 2, 2014 1:09 PM
  • You should always dispose the connection object, and any other object that implements the IDisposable interface, when you are done using it. The easiest way of doing this is to wrap it in a using statement:

               
     using (SqlConnection connection = new SqlConnection(stringConnection))
                {
                    connection.Open();
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
    
                    }
                }

    If you don't wrap it in a using statement, you should call the Dispose method explicitly in a finally block:

                SqlConnection connection;
                try
                {
                    connection = new SqlConnection(stringConnection);
                    connection.Open();
                    //...
                }
                catch (Exception ex)
                {
                    //handle ex...
                }
                finally
                {
                    if (connection != null)
                        connection.Dispose();
                }

    Connection objects are meant to be shortlived, i.e. you should open the connection, fecth the data and dispose the object. Then you should create a new object if you need to fetch more data later on.

    Please remember to mark helpful posts as answer and/or helpful.

    Agreed: Create. Use. Dispose. Is the best way to deal with disposeable objects. It can cause significant issues if the connection is kept open past the function that created it (like you having cases where it is not properly disposed/finalised).

    Also note that many SqlConnection subclasses do internal connection caching. So there would be no speed advantage to keep the instance around anyway, but a great chance for resource leaks.


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2 Please mark post as helpfull and answers respectively.


    Tuesday, September 2, 2014 1:20 PM
  • Hi martinwang,

    Like you mentioned, this is a MySql case. I am afraid this is out of our support. Your question is not related to the topic of this forum. I would suggest you ask a question on their official forum.

    Have a nice day!

    Kristin
     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 3, 2014 8:03 AM
  • hi Christopher84:

        thank u very much for ur kindness reply

      i made a test

     

    try             {                 MySqlConnection connectionLev = newMySqlConnection("server=P30LSIMUL;user id=root;database=cncp20l");                 MySqlCommand cmd = connectionLev.CreateCommand();                 connectionLev.Open();                 Console.WriteLine("OK");                             }             catch (Exception ex)             {                 Console.WriteLine(ex.Message);             }             try             {                 MySqlConnection connectionLev = newMySqlConnection("server=P30LSIMUL;user id=root;database=cncp20l");                 MySqlCommand cmd = connectionLev.CreateCommand();                 connectionLev.Open();                 Console.WriteLine("OK");             }             catch (Exception ex)             {                 Console.WriteLine(ex.Message);             }

    and it seems that even i dont close the connection it is ok

    thank u

      

    Thursday, September 4, 2014 8:04 AM
  • hi, Magnus

      

        thank u very much for ur kindness reply

      i made a test

     

    try             {                 MySqlConnection connectionLev = newMySqlConnection("server=P30LSIMUL;user id=root;database=cncp20l");                 MySqlCommand cmd = connectionLev.CreateCommand();                 connectionLev.Open();                 Console.WriteLine("OK");                             }             catch (Exception ex)             {                 Console.WriteLine(ex.Message);             }             try             {                 MySqlConnection connectionLev = newMySqlConnection("server=P30LSIMUL;user id=root;database=cncp20l");                 MySqlCommand cmd = connectionLev.CreateCommand();                 connectionLev.Open();                 Console.WriteLine("OK");             }             catch (Exception ex)             {                 Console.WriteLine(ex.Message);             }

    and it seems that even i dont close the connection it is ok

    thank u

      
    Thursday, September 4, 2014 8:04 AM
  • This code will work in tests. But it will propably fail in a production environment. You don't even close the connection and that is bad enough. This is how you should never, EVER work with disposeable objects!

    try
    {
    	MySqlConnection connectionLev = newMySqlConnection("server=P30LSIMUL;user id=root;database=cncp20l");
    	MySqlCommand cmd = connectionLev.CreateCommand();
    	connectionLev.Open();
    	Console.WriteLine("OK");
    }
    catch (Exception ex)
    {
    	Console.WriteLine(ex.Message);
    }
    
    try
    {
    	MySqlConnection connectionLev = newMySqlConnection("server=P30LSIMUL;user id=root;database=cncp20l");
    	MySqlCommand cmd = connectionLev.CreateCommand();
    	connectionLev.Open();
    	Console.WriteLine("OK");
    }
    catch (Exception ex)
    {
    	Console.WriteLine(ex.Message);
    }

    If code like that runs in any production environment, you are about to get fired.
    From a catapult.

    SqlConnections implement Finalize so when the GC does collect them, they will be properly closed. That can be anywhere between "milisccond the reference vanishes" and "the programm is closed after 100 years of the computer running without a hitch and reboot."
    Put one of those blocks (or both) into a loop that runs 100 times and you will run out of avalible SQL conenctions.

    Since you alraedy have a try...catch block, just use the construct that Magnus showed you:
    Put the variable outside of the try (so finalize can access it). Check for null (in case an exception happened before/during the creation, not during the use of it) then Dispose.
    Note that finalize is even run after the function it is contained in returns (about the only code that can still run then). And that you should always log/expose ex.ToString(), never only the message (that one contains only 5% of the information, the otehr 95% are much more important).

    More tips about exception handling:
    http://www.codeproject.com/Articles/9538/Exception-Handling-Best-Practices-in-NET


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2 Please mark post as helpfull and answers respectively.

    Thursday, September 4, 2014 12:49 PM