question

Aaronsoggi-6095 avatar image
0 Votes"
Aaronsoggi-6095 asked ·

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

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

dotnet-csharpwindows-forms
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DuaneArnold-0443 avatar image
1 Vote"
DuaneArnold-0443 answered ·

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://docs.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.



· 1
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I ended up using dependency injection, thanks dude :) and thank you for the links, I'll 100% check them out.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered ·

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;
         }
    
     }
 }


10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.