Is it okay to open separate database connections in each method or is there a better way to do it?

Question

I have a static class in my code that has two methods that store different data in a SQL database when they are called. In each of the methods I open a connection to the database. Is this the proper way to do it or is there a better way to do it?

My Code

using System.Data.SqlClient;

namespace Asm
{
    internal static class StoreCheckBoxResult
    {
        internal static void StoreCheckBoxInDB(int lineNumber, bool result, string imagePath) 
        {
            string connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION"); ;
            SqlConnection Connection = new SqlConnection(connectionString);
            SqlCommand Command = Connection.CreateCommand();
            Command.CommandText = string.Format("UPDATE [dbo].[ASM_JSON] SET CheckBox{0} = '{1}' WHERE ImagePath = '{2}'", lineNumber, result, imagePath);
            try
            {
                Connection.Open();
                Command.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Connection.Close();
        }

        internal static void StoreCheckBoxCoords(int lineNumber, int[] coords, string imagePath)
        {
            string connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION"); ;
            SqlConnection Connection = new SqlConnection(connectionString);
            SqlCommand Command = Connection.CreateCommand();
            string coordString = string.Format("{0},{1} | {2}, {3} | {4}, {5} | {6}, {7}", coords[0], coords[1], coords[2], coords[3], coords[4], coords[5], coords[6], coords[7]);
            Command.CommandText = string.Format("UPDATE [dbo].[ASM_JSON] SET CheckBoxCoords{0} = '{1}' WHERE ImagePath = '{2}'", lineNumber, coordString, imagePath);
            try
            {
                Connection.Open();
                Command.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Connection.Close();
        }
    }
}

Answer 1

In dot net the connection class uses a connection pool under the hood to reuse a single network connection to the db. You should structure your code as per the example:

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

https://learn.microsoft.com/nl-nl/dotnet/api/system.data.sqlclient.sqlconnection?view=dotnet-plat-ext-6.0

Also, here is an example of parameterised queries

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

Answer 2

This will often be unnecessarily expensive. Many database frameworks offer connection pooling, where you get an already-open connection when you need it, and return it to the pool when you're done with it. Much faster in most cases.

An unrelated issue with your code: you use string interpolation to build SQL statements - bad bad bad, this is how your code becomes a wide open door for SQL injection. Whenever possible, use parameter binding, and perform thorough sanity checks when not.