CODEWITHSUNDEEP
c#mysql
user222427
user222427

Reputation:

C# MySql MySqlConnection leave open entire program

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();
}

Upvotes: 1

Views: 1285

Answers (3)

Darin Dimitrov
Darin Dimitrov

Reputation: 1038710

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.

Upvotes: 9

SLaks
SLaks

Reputation: 887215

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

For example:

public static MySqlConnection Connection { get; private set; }

Connection = OpenCon();

Upvotes: 4

PedroC88
PedroC88

Reputation: 3829

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;
}

Upvotes: 0

Related Questions