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