loop every minute using c#
Good Day!
How to loop every minute using C#.
In my case I'm using while but I set only in bool because I don't know how to loop every minute.
This is the example code
bool status = true;
while (status)
{
// Query to check the status of the Job
string check_Jobs = "CREATE TABLE #list_running_SQL_jobs ( job_id UNIQUEIDENTIFIER NOT NULL , last_run_date INT NOT NULL , last_run_time INT NOT NULL , next_run_date INT NOT NULL , next_run_time INT NOT NULL , next_run_schedule_id INT NOT NULL , requested_to_run INT NOT NULL , request_source INT NOT NULL , request_source_id sysname NULL , running INT NOT NULL , current_step INT NOT NULL , current_retry_attempt INT NOT NULL , job_state INT NOT NULL ); DECLARE @sqluser NVARCHAR(128) , @is_sysadmin INT; SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0); DECLARE read_sysjobs_for_running CURSOR FOR SELECT DISTINCT SUSER_SNAME(owner_sid)FROM msdb.dbo.sysjobs; OPEN read_sysjobs_for_running; FETCH NEXT FROM read_sysjobs_for_running INTO @sqluser; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #list_running_SQL_jobs EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @sqluser; FETCH NEXT FROM read_sysjobs_for_running INTO @sqluser; END; CLOSE read_sysjobs_for_running; DEALLOCATE read_sysjobs_for_running; SELECT j.name , 'Enbld' = CASE j.enabled WHEN 0 THEN 'no' ELSE 'YES' END , '#Min' = DATEDIFF(MINUTE, a.start_execution_date, ISNULL(a.stop_execution_date, GETDATE())) , 'Status' = CASE WHEN a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL THEN 'Executing' WHEN h.run_status = 0 THEN 'FAILED' WHEN h.run_status = 2 THEN 'Retry' WHEN h.run_status = 3 THEN 'Canceled' WHEN h.run_status = 4 THEN 'InProg' WHEN h.run_status = 1 THEN 'Success' ELSE 'Idle' END , r.current_step , spid = p.session_id , owner = ISNULL(SUSER_SNAME(j.owner_sid), 'S-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1))) - CONVERT(BIGINT, 256) * CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + '-' + CONVERT(NVARCHAR(12), UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 4), 1)) / 256 + CONVERT(BIGINT, NULLIF(UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256, 0)) - CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 5), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 6), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 6), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 7), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 8), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 8), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 9), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 10), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 10), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 11), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 12), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 12), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 13), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 14), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 14), -1)) * 0), '')) , a.start_execution_date , a.stop_execution_date , t.subsystem , t.step_name FROM msdb.dbo.sysjobs j LEFT OUTER JOIN (SELECT DISTINCT * FROM #list_running_SQL_jobs) r ON j.job_id = r.job_id LEFT OUTER JOIN msdb.dbo.sysjobactivity a ON j.job_id = a.job_id AND a.start_execution_date IS NOT NULL AND NOT EXISTS ( SELECT * FROM msdb.dbo.sysjobactivity at WHERE at.job_id = a.job_id AND at.start_execution_date > a.start_execution_date ) LEFT OUTER JOIN sys.dm_exec_sessions p ON p.program_name LIKE 'SQLAgent%0x%' AND j.job_id = SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 7, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 5, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 3, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 1, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 11, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 9, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 15, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 13, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 17, 4) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 21, 12) LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.instance_id = a.job_history_id LEFT OUTER JOIN msdb.dbo.sysjobsteps t ON t.job_id = j.job_id AND t.step_id = r.current_step where name='INVM_Export_Job' ORDER BY 1; DROP TABLE #list_running_SQL_jobs";
using (DataTable getStatus_Job = Utilities.SQL_Query(Globals.sSQLCS_FNS, check_Jobs, true, false)) // Connection to the database
{
if (getStatus_Job.Rows[0]["Status"].ToString() == "Success")
{
Globals.sSSIS_TDMAT_STATUS = "Success";
status = false;
}
else if (getStatus_Job.Rows[0]["Status"].ToString() == "FAILED")
{
Globals.sSSIS_TDMAT_STATUS = "Failure";
status = false;
}
else
{
Globals.sSSIS_TDMAT_STATUS = "Ongoing Processing";
status = true;
}
}
}
This is the example image to check the status of the SSIS Job
This is the Utilities.cs that connects to the database
public static DataTable SQL_Query(string sSQLCon, string sQry, Boolean isReturn, Boolean isRedirect = true, string sDataTableName = "DATA")
{
Globals.sError = "";
DataSet dsData = new DataSet();
try
{
using (SqlConnection sqlCon = new SqlConnection(sSQLCon))
{
if (isReturn)
{
using (SqlDataAdapter sqlAdptr = new SqlDataAdapter(sQry, sqlCon))
{
sqlAdptr.SelectCommand.CommandTimeout = 0;
sqlAdptr.Fill(dsData, sDataTableName);
}
}
else
{
using (SqlCommand sqlCmd = new SqlCommand(sQry, sqlCon))
{
sqlCmd.Connection.Open();
sqlCmd.CommandTimeout = 0;
sqlCmd.ExecuteNonQuery();
dsData.Tables.Add("DATA");
}
}
return dsData.Tables[0];
}
}
catch (Exception ex)
{
Globals.sError = ex.Message;
if (isRedirect)
HttpContext.Current.Response.RedirectToRoute("Error02");
return null;
}
}
Perhaps using System.Threading.Timer along the lines of the code below, may need some modifications to work in your project.
A basic example which sets the time to wait to one minute with an optional constructor overload to change the wait time. An event for passing a status to the caller. For testing messages are sent to listeners on start and stopping the timer. Calling your operations and report status in StatusCheck.
using System.Threading;
using Timer = System.Threading.Timer;
namespace WorkingWithTimer.Classes
{
public class Utilities
{
private static int _dueTime = 6000 * 10;
private static Timer _workTimer;
public delegate void MessageHandler(string message);
public static event MessageHandler Message;
private static void Initialize()
{
_workTimer = new Timer(Dispatcher);
_workTimer.Change(_dueTime, Timeout.Infinite);
}
private static void Initialize(int dueTime)
{
_dueTime = dueTime;
_workTimer = new Timer(Dispatcher);
_workTimer.Change(_dueTime, Timeout.Infinite);
}
private static void Dispatcher(object e)
{
StatusCheck();
_workTimer.Dispose();
Initialize();
}
public static void Start()
{
Initialize();
Message?.Invoke("Started");
}
public static void Stop()
{
_workTimer.Dispose();
Message?.Invoke("Stopped");
}
/// <summary>
/// Report to caller status e.g.
/// Globals.sSSIS_TDMAT_STATUS = "Success";
/// </summary>
private static void StatusCheck()
{
Message?.Invoke("Performing work");
}
}
}