State of Db.Connection

[acentrella13]

Should we be using something like if (Db.Connection.State != System.Data.ConnectionState.Open) await Db.Connection.OpenAsync();

Or do we ever Close the connection? How do we Open it on every use but not close, if we issue the OpenAsync everytime there will be an error that its already open?

[tcsaddul]

Just connect with a try.. catch block. Yes the connection closed (pooled) inside a using block.

[bgrainger]

Should we be using something like if (Db.Connection.State != System.Data.ConnectionState.Open) await Db.Connection.OpenAsync();

No.

MySqlConnection objects should be short-lived. When you need an open connection, create a new one and open it:

using (var connection = new MySqlConnection(myConnectionString))
{
    await connection.OpenAsync();

    // ...

    // it will be closed at the end of this block
}

[bgrainger]

MySqlDataSource (from #1208) will make this even easier:

using var connection = dataSource.OpenConnectionAsync();
// use it ...

[acentrella13]

I am using it in .net 6 as per https://mysqlconnector.net/tutorials/net-core-mvc/

Like this:

        public async Task<List<mmCustomer_Rec>> GetCustomers()
        {
            await Db.Connection.OpenAsync();
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = "SELECT * from customers where recdate = curdate()";

            var asyncreply = await GetCustomersAsync(await cmd.ExecuteReaderAsync());
            return asyncreply;
        }

Works the first call, on subsequent calls there is an error that the Db.Connection is already open... this seems different than using a MySqlConnection...should i change the way i'm doing it or use .Net 7? Is there example code you can point me to please? I very much appreciate your time

[acentrella13]

I went with this..

            using (var connection = new MySqlConnection(sConnectionString))
            {
                await connection.OpenAsync();
                using var cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * from customers where recdate = curdate()";

                var asyncreply = await GetCustomersAsync(await cmd.ExecuteReaderAsync());
                return asyncreply;
            }