How to Keep a Sleep function to a C# program So that it executes an SQL Command specified in the Program for every certain time Period

Question

1.00/5 (2 votes)

See more:

I have developed a c# Console Application which executes an SQL Command and display the output of the query in the console. Now i need to Execute the same Query which is present in the Program for every 2 Minutes. So that the previous output should be cleared and display the fresh output of that query. I have tried with some Threading and sleep functions, but i was unable to do it. I have kept time interval as 30 Seconds, But after 30 Seconds the window is getting closed.

The code Which i have was:-

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;

namespace LeakLocationFinder
{
 class Program
  {
 static void Main(string[] args)
    {
  using (SqlConnection conn = new SqlConnection())
      {        
  conn.ConnectionString = "server=(local);database=modelDb;user id=sa;pwd=123456";
  conn.Open();
             
SqlCommand command = new SqlCommand("DECLARE @var varchar(1000) = (SELECT TOP 1 Text FROM Alarms WHERE AlarmDefinitionId=139 ORDER BY EventTime DESC) DECLARE @start_position int, @end_position int SELECT @start_position = PATINDEX('% at%', @var) SELECT @end_position = PATINDEX('%kilometers%', @var) DECLARE @VALUE VARCHAR(10) = (Select SUBSTRING(@var, @start_position+5,5)) Select Top 1 @VALUE,RouteTable.Latitude,Routetable.Longitude,Alarms.EventTime FROM Alarms INNER JOIN Routetable ON Routetable.Location BETWEEN FLOOR(@VALUE)-1 AND CEILING(@VALUE)+1 WHERE AlarmDefinitionId=139 ORDER BY EventTime DESC", conn);

using (SqlDataReader reader = command.ExecuteReader())
          {
 Console.WriteLine("Column1\t\tColumn2\t\tColumn3\t\t");
                    while (reader.Read())
             {
      Console.WriteLine(String.Format("\t{0} \t\t | {1} \t\t | {2} \t",
                            reader[0], reader[1], reader[2]));
              }
           }

         }
      }
    }
}

Posted 9-Apr-15 20:23pm

Krishna Chaitanya Bezawada

Add a Solution

Comments

Sinisa Hajnal 10-Apr-15 3:04am

Use Timer component and on each timer tick trigger your function. That's what it is for.

4 solutions

Solution 2

You could also try something like this, does about the same as Florian's answer but just a bit more basic.

C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;

namespace LeakLocationFinder
{
 class Program
  {
 static void Main(string[] args)
    {
  Console.Clear();
  using (SqlConnection conn = new SqlConnection())
      {
  conn.ConnectionString = "server=(local);database=modelDb;user id=sa;pwd=123456";
  conn.Open();

SqlCommand command = new SqlCommand("DECLARE @var varchar(1000) = (SELECT TOP 1 Text FROM Alarms WHERE AlarmDefinitionId=139 ORDER BY EventTime DESC) DECLARE @start_position int, @end_position int SELECT @start_position = PATINDEX('% at%', @var) SELECT @end_position = PATINDEX('%kilometers%', @var) DECLARE @VALUE VARCHAR(10) = (Select SUBSTRING(@var, @start_position+5,5)) Select Top 1 @VALUE,RouteTable.Latitude,Routetable.Longitude,Alarms.EventTime FROM Alarms INNER JOIN Routetable ON Routetable.Location BETWEEN FLOOR(@VALUE)-1 AND CEILING(@VALUE)+1 WHERE AlarmDefinitionId=139 ORDER BY EventTime DESC", conn);

using (SqlDataReader reader = command.ExecuteReader())
          {
 Console.WriteLine("Column1\t\tColumn2\t\tColumn3\t\t");
                    while (reader.Read())
             {
      Console.WriteLine(String.Format("\t{0} \t\t | {1} \t\t | {2} \t",
                            reader[0], reader[1], reader[2]));
              }
           }

         }
         Thread.Spleep(time);
         Refresh(args);
         Console.ReadKey();
      }
      public static void Refresh(string[] reff)
        {
            Main(reff);

        }
    }
}

Posted 9-Apr-15 21:04pm

BulletVictim

Solution 3

Use Timer...it works without you needing to pause the whole thread.

C#

using System;
using System.Timers;

public class Example
{
   private static Timer aTimer;

   public static void Main()
   {
        // Create a timer 
        aTimer = new System.Timers.Timer(120000);
        // Hook up the Elapsed event for the timer.
        aTimer.Elapsed += OnTimerElapsed;
        aTimer.Start();
// do whatever
   }

    private static void OnTimerElapsed(Object source, ElapsedEventArgs e)
    {

// do some work here (get the database data etc...)
    }
}

Posted 10-Apr-15 0:16am

Sinisa Hajnal

Comments

Florian Braun 10-Apr-15 6:38am

of course timers are cool for repeating tasks.. but if you have an application for console you still need some loop to stay within the program AND if your program doesnt do anything else timers are as useless as waiting without doing anything

Sinisa Hajnal 10-Apr-15 9:03am

Not true. You could show whatever you want on the screen ending with do you want to quit question (or simple press any key...). As long as the timer works it will refresh...and at some point it will exit when do user wants to stop it. If the thread is blocked, there is no way to end it except Ctrl+C

Florian Braun 10-Apr-15 9:07am

not really true. i totally agree that timers are great but i just wanted to show they are maybe not the best solution here as simply not necessary. I presented a solution where the user can quit at ANYTIME by pressing a key but it was not what the guy who asked the question wanted so i removed that feature.

Solution 4

without knowing more about your database i'd add:

C#

List<string> newList=new List<string>();
List<string> oldList=new List<string>();
bool firstrun=true;

do{
 
    using (SqlDataReader reader = command.ExecuteReader())
    {
         newList.clear();
         Console.clear(); //delets the Console
         Console.WriteLine("Column1\t\tColumn2\t\tColumn3\t\t");
              
         while (reader.Read())
         {
              string line=String.Format("\t{0} \t\t | {1} \t\t | {2} \t",
                            reader[0], reader[1], reader[2]);

              Console.WriteLine(line);
              newList.Add(line);
          }
     }
 
     if (!firstrun){
          if (newList.Count!=oldList.Count) {
              Messagebox.Show("Count of Lines has changed!");
          }
          else {
              for (int i=0;i<newlist.count;i++){
                  if (newList[i]!=oldList[i]) {
                       Messagebox.Show("At least one line has changed");
                       break;
                   }
              }
       }
     else firstrun=false; 

     oldList.Clear();
     for (int i=0;i<newlist.count;i++)oldlist.add(newlist[i]);
             
     Thread.Sleep(120000);
} while (true);

also you will need to add using System.Windows.Forms; and if you get an error (namespace not found, missing assembly) the reference to System.Windows.Forms

Posted 10-Apr-15 1:43am

Florian Braun

Updated 10-Apr-15 1:47am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Florian Braun · Accepted Answer · 2015-04-09T20:46:00

I don't think there is any need for threading if waiting and SQL-Output is all what your programm is doing.

So my suggestion is

C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
 
namespace LeakLocationFinder
{
 class Program
  {
 static void Main(string[] args)
    {
  using (SqlConnection conn = new SqlConnection())
      {        
  conn.ConnectionString = "server=(local);database=modelDb;user id=sa;pwd=123456";
  conn.Open();
             
SqlCommand command = new SqlCommand("DECLARE @var varchar(1000) = (SELECT TOP 1 Text FROM Alarms WHERE AlarmDefinitionId=139 ORDER BY EventTime DESC) DECLARE @start_position int, @end_position int SELECT @start_position = PATINDEX('% at%', @var) SELECT @end_position = PATINDEX('%kilometers%', @var) DECLARE @VALUE VARCHAR(10) = (Select SUBSTRING(@var, @start_position+5,5)) Select Top 1 @VALUE,RouteTable.Latitude,Routetable.Longitude,Alarms.EventTime FROM Alarms INNER JOIN Routetable ON Routetable.Location BETWEEN FLOOR(@VALUE)-1 AND CEILING(@VALUE)+1 WHERE AlarmDefinitionId=139 ORDER BY EventTime DESC", conn);

do{
 
    using (SqlDataReader reader = command.ExecuteReader())
    {
         Console.clear(); //delets the Console
         Console.WriteLine("Column1\t\tColumn2\t\tColumn3\t\t");
         
         while (reader.Read())
         {
              Console.WriteLine(String.Format("\t{0} \t\t | {1} \t\t | {2} \t",
                            reader[0], reader[1], reader[2]));
          }
     }
 

     Thread.Sleep(120000);
} while (true);
}
}
}
}

(I hope I closed all curly brakets)