MySqlDataReader close connection

Question

This is my code for select data from table in MySql:

            MySqlDataReader msdr;

            MySqlConnection connect = new MySqlConnection(connectionStringMySql);
            MySqlCommand cmd = new MySqlCommand();

            string commandLine = "SELECT id,token FROM Table WHERE id = @id AND token = @token;";

            cmd.CommandText = commandLine;

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@token", token);

            cmd.Connection = connect;
            cmd.Connection.Open();

            msdr = cmd.ExecuteReader();

            //do stuff.....

            msdr.Close();
            cmd.Connection.Close();

As you can see i close this two:

        msdr.Close();
        cmd.Connection.Close();

And i want to ask if i need to close this two? or it will be ok to close only the cmd.Connection.Close();

The reason i asked it it because sometimes i get this error when i try to select data in table: Details: MySql.Data.MySqlClient.MySqlException: Too many connections

And i want to know if it's because i don't close this connections.

Steve · Accepted Answer · 2020-12-14 17:23:37Z

The best coding for this is the following

using(MySqlConnection connect = new MySqlConnection(connectionStringMySql))
using(MySqlCommand cmd = new MySqlCommand())
{
    string commandLine = "SELECT id,token FROM Table WHERE id = @id AND token = @token;";
    cmd.CommandText = commandLine;

    cmd.Parameters.AddWithValue("@id", id);
    cmd.Parameters.AddWithValue("@token", token);

    cmd.Connection = connect;
    cmd.Connection.Open();

    using(msdr = cmd.ExecuteReader())
    {

         //do stuff.....
    } // <- here the DataReader is closed and disposed.

}  // <- here at the closing brace the connection is closed and disposed as well the command

The using statement will keep your connection closed and disposed as well as the command object. There is no need to call explicitily Close on MySqlConnection or MySqlDataReader because the using statement will do this for you ALSO in case of Exceptions triggered in your code between the open and close of the connection

In your original code it is enough to close the connection just one time with the command, datareader or the connection itself, because they work with the same object instance, but if you have an exception then your code fails to close the connection and thus you risk the 'too many connections open' problem

UPDATE for C# 8.0
In this version of the language there is a small but useful enhancement to the using statement. It is called using declaration

With this change the code above could be written removing the braces around the using blocks

using MySqlConnection connect = new MySqlConnection(connectionStringMySql);
using MySqlCommand cmd = new MySqlCommand();
string commandLine = "SELECT id,token FROM Table WHERE id = @id AND token = @token;";

.....
cmd.Connection.Open();
using msdr = cmd.ExecuteReader();
//do stuff.....

When the method exits all the using declaration will act to dispose their repective declared variable

Monir Tarabishi · Accepted Answer · 2013-06-02 21:51:52Z

0

if you are using this code in a method, it's better to close the data reader as well as the data connection

answered Jun 2, 2013 at 21:51

Monir Tarabishi

7141 gold badge17 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySqlDataReader close connection

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related