C#•14mo ago

❔ Avoiding "MySql connection is already in use"

so i have a bunch of C# scripts that all use the same api (mysql wrapper) but i came to notice that sometimes i get "connection in use" errors. by script i mean functions loaded through cs-script, but i guess you can think of it like a thread to avoid this, i thought of using separate connections for every script. i thought this idea is what's called pooling, but apparently i was wrong. so how can i get by this issue?

5 Replies

JakenVeina•14mo ago

you should be newing up an SqlConnection for every command, or serialized set of commands, you want to execute. Connection pooling happens underneath that hood, if you hahaqve it configured. I.E. for every "script"

ch3rryOP•14mo ago

but isnt that opening and closing a connection every time? unless im wrong, can you show me an example pls?

JakenVeina•14mo ago

using var connection = new SqlConnection(connectionString);

await connection.OpenAsync();

var command = new SqlCommand(connection)
{
    .CommandText = "SELECT MY_COLUMN FROM MY_TABLE"
};

using var reader = await command.ExecuteReaderAsync();

while(await reader.ReadAsync())
    Console.WriteLine(reader.GetString(0);

using var connection = new SqlConnection(connectionString);

await connection.OpenAsync();

var command = new SqlCommand(connection)
{
    .CommandText = "SELECT MY_COLUMN FROM MY_TABLE"
};

using var reader = await command.ExecuteReaderAsync();

while(await reader.ReadAsync())
    Console.WriteLine(reader.GetString(0);

new SqlConnection() does not necessarily create a new TCP connection to the database that's what I mean when I say "Connection pooling happens underneath that hood" if you have it configured if a previously-used connection is still open, new SqlConnection() will use that if the maximum number of connections to the database are already open, and none of them is available, .OpenAsync() will "block" until one of them becomes available, and use that when previously-used connections become available, they will be cached and kept open for a certain amount of time, to allow for future reuse if there is already a maximum number of connections being kept open for future reuse, connections will start being closed immediately etc. all of these behaviors are set via configuration, I.E. the connection string

JakenVeina•14mo ago

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-7.0

SqlConnection.ConnectionString Property (System.Data.SqlClient)

Gets or sets the string used to open a SQL Server database.

Accord•14mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Avoiding "MySql connection is already in use"