Cockroach connection best practice?-CodePudding

I am using npgsql to connect to a CockroachDb cluster, in code with each command I want to execute I create a new connection and open it! I wonder if creating a private field variable holding the connection and opening it then closing it is much better than my current approach?

static async Task Task1()
{
    using (var conn = new NpgsqlConnection(connString))
    {
        conn.Open();

        new NpgsqlCommand("SQL Query here", conn).ExecuteNonQuery();

        conn.Close();
    }
}

static async Task Task2()
{
    using (var conn = new NpgsqlConnection(connString))
    {
        conn.Open();

        new NpgsqlCommand("SQL Query here", conn).ExecuteNonQuery();

        conn.Close();
    }
}and so on ....

CodePudding user response：

Not really: although possible, it's a bit of an anti pattern.

NpgsqlConnection inherits from DbConnection. And DbConnection is a general Microsoft abstract class that represents a database connection, which is IDisposable.

So when putting the conn in a using block, you implicitly call Dispose as soon as it goes out of scope. This will close the connection and clean up other stuff. You don't have to separately call Close.

What you could do, is make a general wrapper like

public static async Task NonQueryCommand(string command)
{
    using (var conn = new NpgsqlConnection(connString))
    {
        conn.Open();
        using (var cmd = new NpgsqlCommand(command, conn) // Also IDisposable
        {
            cmd.ExecuteNonQuery();
        }
    } 
}

But you could lose all flexibility that way (the repository pattern can be bad if not used correctly).

Also check the official resources