What is appropriate way for managing MySQL connection through C#

by Sylca   Last Updated September 22, 2017 01:05 AM

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, ...

Tags : c# mysql


Answers 3


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.

DFord
DFord
November 16, 2012 13:59 PM

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".

mike30
mike30
November 16, 2012 17:10 PM

Another answer, insert in usertable

    public void insert_command(string username, string password, string fullname)
    {
        using (var con = new MySqlConnection { ConnectionString = "Your Connection String.." })
        {
            using (var command = new MySqlCommand { Connection = con })
            {
                con.Open();

                command.CommandText = @"INSERT INTO usertable SET [email protected], [email protected], [email protected]";
                command.Parameters.AddWithValue("@usename", username);
                command.Parameters.AddWithValue("@password", password);
                command.ExecuteNonQuery();
                command.Parameters.Clear();

            } // command close

        } // connection close

    }
Ramgy Borja
Ramgy Borja
September 22, 2017 00:47 AM

Related Questions


Is this a race condition?

Updated April 16, 2015 20:02 PM


problem in show threads 1 result mysql.php

Updated May 28, 2015 22:02 PM