Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

My question, at the bottom line, is what is the appropriate(best) way to manage our connection towards MySQL db with C#.

Well, currently I'm working on some C# (winforms type) <-> MySQL application and I've been looking at Server Connections in MySQL Administrator, been witness of execution of my mysql_queries, connection opens an closes, ... an so on! In my C# code I'm working like this and this is an example:

public void InsertInto(string qs_insert)
        {
            try
            {
                conn = new MySqlConnection(cs);
                conn.Open();

                cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = qs_insert;
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

Meaning, every time I want to insert something in db table I call this table and pass insert query string to this method. Connection is established, opened, query executed, connection closed. So, we could conclude that this is the way I manage MySQL connection.

For me and my point of view, currently, this works and its enough for my requirements.

Well, you have Java & Hibernate, C# & Entity Framework and I'm doing this :-/ and it's confusing me. Should I use MySQL with Entity Framework?

What is the best way for collaboration between C# and MySQL? I don't want to worry about is connection that I've opened closed, can that same connection be faster, ...

share|improve this question
2  
Not really sure what you're asking, but I think you'll find the answer in one or both of these existing questions: programmers.stackexchange.com/questions/152535/… programmers.stackexchange.com/questions/89378/… If not then try to focus on the part that isn't answered already. –  pdr Nov 16 '12 at 13:59
    
This first link "Pattern for Accessing MySQL connection" helps me a little bit. What I'm looking for is more concrete way of "Pattern for Accessing MySQL connection" considering C# work with MySQL, in form of suggestions, reasons, ways, ... –  Sylca Nov 16 '12 at 14:19
add comment

2 Answers

I would suggest using

using(MySqlConnection conn = new MySqlConnetion(cs)
{
    conn.open();
    //setup and execute query

} //conn gets closed here

Here, once you exit the using block, the connection is closed.

share|improve this answer
    
@Haris I'm confused. "+1 for the answer"? What else would you be +1-ing for? ;P –  Yannis Rizos Nov 16 '12 at 14:09
    
The MySqlCommand is likely IDisposable as well and should be wrapped in its own using. –  Jesse C. Slicer Nov 16 '12 at 14:45
    
Thanks for sharing this with me, indeed you showed me another way of doing it! –  Sylca Nov 16 '12 at 15:06
add comment

The rule is: open as late as possible. Close as soon as possible. (actually you are getting and putting to the connection pool, but the method names are open/close).

Your code snippet is OK, but if you want to follow the rule literally, then Open() could be moved right before ExecuteNonQuery().

As DFord suggested, the using blocks are preferred in C#. This is for stylistic reasons. This is a scope-based style. You can treat your resources (connections) somewhat like you would ordinary local primitives like int, double, etc. When they are out of scope, then everything is automatically cleaned up. The "using" block is converted to a try/finally by the compiler.

The using block is a simulation of RAII. However it is a simulation, not the real thing. Using blocks are a syntactic sugar for writing try/catch. In real RAII you don't have to write any special syntax to get the stack pop effect as it's controlled by the literal stack. Real RAII is slightly safer as you don't need to remember to write any special constructs like "using" or "try".

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.