How can I prevent myself from creating a SQL connection in every class

Question

I currently have a SQLDataFunctions class where i have specified the SQl connection. I would like to access this connection (just the connection string) from other classes, as i don't want to have to keep creating a new one.

I was thinking of making the SQL connection static? is that bad

so then i can just do SQLDataFunctions.connection whenever i want to use it, or would it be best to create a new connection in each class? My code works fine at the moment but i just feel like I'm repeating myself too much.

For example below is some code in one of my classes. In this class I'm creating a new sql connection and a sql command.

 SqlCommand cmd;             
        SqlConnection connection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TGCSBookingSystem;Integrated Security=True");  
  
  
  {  
                string query = " SELECT cs.coachScheduleId, cs.stationDeparture, cs.stationArrival, cs.timeOfDeparture, cs.timeOfArrival, cs.dateOfDeparture, c.numberOfSeats FROM coachSchedule cs JOIN Coach c ON cs.coachId = c.CoachId WHERE cs.dateOfDeparture BETWEEN @dateFrom AND @dateTo AND cs.stationDeparture= @travelFrom AND cs.stationArrival= @travelTo";  
                                             
                cmd = new SqlCommand(query,connection);  
                cmd.Parameters.AddWithValue("@dateFrom", dateFromPicker.Value);  
                cmd.Parameters.AddWithValue("@dateTo", dateTooPicker.Value);  
                cmd.Parameters.AddWithValue("@travelFrom", comboBoxTravelFrom.SelectedValue);  
                cmd.Parameters.AddWithValue("@travelTo", comboBoxTravelTo.SelectedValue);  
  
                _IsqlDataFunctions.displayDataInGrid(cmd, availableBookings);      
            }       

This is in my sqlDataFunctions class

        SqlConnection connection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TGCSBookingSystem;Integrated Security=True");  
  
  
 public void displayDataInGrid(SqlCommand cmd, DataGridView datagrid)  
        {  
            try  
            {                  
                connection.Open();  
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);  
                DataTable dt = new DataTable();  
                adapter.Fill(dt);  
                datagrid.DataSource = dt;  
                connection.Close();  
            }  
            catch (Exception message)  
            {  
                MessageBox.Show(message.Message);  
            }  

In both classes I'm having to create two separate SQL connections when i only want to have to do this once. I was thinking of putting the line " cmd = new SqlCommand(query,connection);" within the displayDataInGrid method but i got an error

Answer 1

You should understand the new is glue principle.

https://ardalis.com/new-is-glue/

You should learn how to use dependency injection.

https://www.tutorialsteacher.com/ioc/dependency-injection

https://objcsharp.wordpress.com/2013/07/08/why-static-code-is-bad/

You should learn how to write clean code and it don't matter that its talking about ASP.NET Core, becuase the principles can be applied to any type of .NET solution Web or Windows desktop Core or non Core.

https://learn.microsoft.com/en-us/archive/msdn-magazine/2016/may/asp-net-writing-clean-code-in-asp-net-core-with-dependency-injection

You should maybe learn how to use the ADO.NET Entity Framework too.

Answer 2

Another way is to use a singleton class something like below, test method here.

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace ApplicationDataConnector
{
    public sealed class SqlServerConnections
    {
        private static readonly Lazy<SqlServerConnections> 
            Lazy = new Lazy<SqlServerConnections>(() => new SqlServerConnections());

        public static SqlServerConnections Instance => Lazy.Value;
        private readonly Hashtable _connections = new Hashtable();

        /// <summary>
        /// Reset an existing connection string
        /// </summary>
        /// <param name="connectionString">An existing connection string</param>
        public void Reset(string connectionString)
        {
            SqlConnection connection = null;

            try
            {
                connection = (SqlConnection)_connections[connectionString];
                connection.Dispose();
                connection = null;
            }
            catch (Exception)
            {
                // ignored
            }
        }

        /// <summary>
        /// Used to reset all known connections to null
        /// </summary>
        public void ResetAll()
        {
            foreach (var cn in _connections)
            {
                SqlConnection connection = null;

                try
                {
                    connection = (SqlConnection)cn;
                    connection.Dispose();
                    connection = null;
                }
                catch (Exception)
                {
                    // ignored
                }
            }
        }
        /// <summary>
        /// Returns an open connection for connection string
        /// </summary>
        /// <param name="connectionString">Valid connection string</param>
        /// <returns>Connection</returns>
        public SqlConnection Connection(string connectionString)
        {
            SqlConnection connection = null;
            var bNeedAdd = false;

            try
            {
                connection = (SqlConnection)_connections[connectionString];
            }
            catch (Exception)
            {
                // ignored
            }

            if (connection == null)
            {
                bNeedAdd = true;
            }

            if (connection == null || connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed)
            {
                try
                {
                    connection?.Dispose();
                    connection = null;
                }
                catch (Exception)
                {
                    // ignored
                }

                connection = new SqlConnection();
            }

            if (connection.State == ConnectionState.Closed)
            {
                connection.ConnectionString = connectionString;
                connection.Open();
            }

            if (bNeedAdd)
            {
                _connections.Add(connectionString, connection);
            }

            return connection;
        }

    }
}