Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

State of Db.Connection #1347

Answered by acentrella13
acentrella13 asked this question in Q&A
State of Db.Connection #1347
@acentrella13 acentrella13
Jul 19, 2023 · 2 comments · 3 replies
Answered by acentrella13 Return to top
Discussion options

acentrella13
Jul 19, 2023

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?
You must be logged in to vote
Answered by acentrella13 Jul 20, 2023

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;
            }
View full answer

Replies: 2 comments · 3 replies

Comment options

tcsaddul
Jul 20, 2023

Just connect with a try.. catch block. Yes the connection closed (pooled) inside a using block.
You must be logged in to vote
0 replies
Comment options

bgrainger
Jul 20, 2023
Maintainer

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
}
You must be logged in to vote
3 replies
@bgrainger
Comment options

bgrainger Jul 20, 2023
Maintainer

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

using var connection = dataSource.OpenConnectionAsync();
// use it ...
@acentrella13
Comment options

acentrella13 Jul 20, 2023
Author

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
Comment options

acentrella13 Jul 20, 2023
Author

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;
            }
Answer selected by bgrainger
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
🙏
Q&A
Labels
None yet
3 participants
@acentrella13 @bgrainger @tcsaddul
Morty Proxy This is a proxified and sanitized view of the page, visit original site.