1

So what i'm looking to do is open a MysqlConnection and then never close it for the entire application (until the end).

I have this:

static void Main(string[] args)
{
    OpenCon();
}

public static MySqlConnection OpenCon()
{
    MySqlConnection masterOpenCON = new MySqlConnection(SQLStringClass.masterConString);
    masterOpenCON.Open();
    return masterOpenCON;
}

However, i believe this will continuously open the connect, how do i open the connection once and reference the connection throughout the app. This is how I am calling it right now.

try
{
    MySqlCommand mysqlprocessCmdInsertItem = new MySqlCommand(SQLStringClass.mySQLCOMMAND, OpenCon());
    mysqlprocessCmdInsertItem.ExecuteNonQuery();
}
Improve this question

3 Answers 3

Reset to default
9

Don't bother with this. The MySQL ADO.NET Connector uses a pool of connections meaning that when you call .Open on a connection you are not actually opening it, you are drawing it from the existing connection pool and when you call .Close you are not closing it, you are returning it to the connection pool so that it can be reused.

IIRC connection pooling is enabled by default but you could control it with the following parameters on your connection string:

  • Pooling = true
  • Max Pool Size = 100
  • Min Pool Size = 0

So when you want to send a SQL query all you need to do is the following and let the ADO.NET framework worry about connections:

using (var conn = new MySqlConnection(SQLStringClass.masterConString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT Foo FROM Bar";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            ...
        }
    }
}

I would recommend you avoiding any static members and manual connection handling.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I don't know what MySql does.
This is not always the case. I learned the hard way, that e.g. MDB files are never pooled (the provider doesn't support it). Same can be true for other providers, too.
@SLaks, @Uwe Keim, here's the documentation about MySQL: dev.mysql.com/doc/refman/5.1/en/…
4

You need to store the connection in a static field or property.

For example:

public static MySqlConnection Connection { get; private set; }

Connection = OpenCon();
Improve this answer

3 Comments

is that not what I am doing? If not do you have an example?
No, that's not what you're doing.
In case anybody was wondering do the Connection = OpenCon(); inside of Man(string[] args)
0

You need to also specify the connection object as static.

private static MySqlConnection masterOpenCON;

static void Main(string[] args)
{
    OpenCon();
}

public static MySqlConnection OpenCon()
{
    masterOpenCON = new MySqlConnection(SQLStringClass.masterConString);
    masterOpenCON.Open();
    return masterOpenCON;
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.