Mistr Mowgli Mistr Mowgli - 19 days ago 5
C# Question

Working With Multiple Timers in C# Web Service

I am trying to perform 3 Database Related operations in One Hour.

1: Checking for Specific Time On Every One Minute

2: Updating Specific Records After Every 15 Minutes

3: Updating Specific Records After Every 60 Minutes

Till 15 Minute everything is Ok... But on 15 Minutes 2 timers has to access database at the same time. So that's why it's showing me error.

Connection is already Open
. Now After 60 Minutes all three Timer are accessing database at the same time so that's why it will show message again
Connection is Already Open
. After 60 Minutes everything is normal till 15 Minutes.But when next 15 Minutes will come. Message will be visible again and so on. Here is Timer

Timer1:

_Timer = new Timer();
this._Timer.Interval = 1000 * 60 * 1;
this._Timer.Elapsed += new System.Timers.ElapsedEventHandler(this._Timer_Tick);
_Timer.Enabled = true;


Here is _Timer_Tick Method

CheckConnnectionStatus();
string cGroupQuery = "if(DATEADD(minute,-(select value from settings where name='SYS_STATE_MIN'),(select top 1 TIME from prayertimes where Time>sysdatetime()))=dateadd(mi, datediff(mi, 0, sysdatetime()), 0))select 'True' else select 'False'";

try
{
sqlConnection.Open();
sqlCommand = new SqlCommand(cGroupQuery, sqlConnection);
sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
string Prayer_Time = sqlDataReader[0].ToString();

if (Prayer_Time == "True")
{
Library.WriteErrorLog("System State Done Successfully");
TakeSystemState();
UpdateSystemState();
}
}
}
catch (Exception exp)
{
Library.WriteErrorLog(exp.Message.ToString() + " | Exception in CheckPrayerTime");
}
finally
{
CheckConnnectionStatus();
}


Timer2:

_Timer02 = new Timer();
this._Timer02.Interval = 1000 * 60 * 15;
this._Timer02.Elapsed += new System.Timers.ElapsedEventHandler(this._Timer02_Tick);
_Timer02.Enabled = true;


Timer3:

_Timer03 = new Timer();
this._Timer03.Interval = 1000 * 60 * 60;
this._Timer03.Elapsed += new System.Timers.ElapsedEventHandler(this._Timer03_Tick);
_Timer03.Enabled = true;


Can anyone guide me best approach to perform these three operations in Timer in Web Service

I am using Sql Server 2008R2 Express Edition

Thanks

Answer

According to the documentation, the SqlConnection.Open() method throws an InvalidOperationException if the connection is already open. For more details, see: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.open(v=vs.110).aspx

As a solution, you could create a new SqlConnection object in each of your Timer event handler methods and Dispose() it once you are done with the processing.

It is a good practice, to place the newly created SqlConnection object in a using statement, so that the connection will be released even if an exception was thrown:

using (var conn = new SqlConnection())
{
    conn.Open();
    // ...
}
Comments