Solved

C# polling program to monitor db entries

Posted on 2013-10-23
22
550 Views
Last Modified: 2013-10-24
Hi

I'm trying to come up with a way to use a C# program(either console or windows service) that gets called from a sql job running every 10 mins that checks a DB table for an entry and if so it triggers of a notiification window on user machine with a message.

anyone any ideas what is best way to do this?
0
Comment
Question by:razza_b
  • 9
  • 8
  • 4
  • +1
22 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39593821
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39593825
Why not put the program into the 'scheduled tasks' on the user PC and let it be started from their instead of from the SQL Server job.  I'd think that could be rather easier.
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39593826
If you're willing to run this as a service, why does the SQL job need to be involved?  Can't you just write a C# application and connect directly to the DB and look for the table/entry?  That kind of application would be fairly trivial to implement.

Set up a Timer in a windows service, every 10 minutes open a connection to the DB and check for the entry.  If it exists, fire off the user notification.  And of course, it would be even more trivial to not use a service, but just a windows forms application instead.

If you'd like examples, let me know I am sure there are tons out there.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39593846
i think a windows service would be simplest way to do this and let it poll every 10 mins checking the DB and fire off the notification,

any samples on this would be good thanks.
0
 
LVL 12

Assisted Solution

by:jmcmunn
jmcmunn earned 500 total points
ID: 39593940
Basically, you're going to want to create a Windows Service project and then simply initialize the timer in the OnStart method.  Make sure to set your interval as desired and then just do whatever work you want in the Timer tick event.

A note:  some people would argue that a standard windows form/console appication run using the Windows task scheduler would be a better approach, but I think it is 50/50 depending on how often the thing is getting fired off.  I'd rather have a timer for something as often as every 10 minutes.  My 2 cents.

Here is one example.

http://tutorials.csharp-online.net/Creating_a_.NET_Windows_Service%E2%80%94The_Timer_Approach

A simple service example:
http://www.codeproject.com/Articles/3990/Simple-Windows-Service-Sample

A useful writeup on debugging services:
http://www.codeproject.com/Tips/261190/How-to-Debug-or-Test-your-Windows-Service-Without
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39594018
>>>A note:  some people would argue that a standard windows form/console appication run using the Windows task scheduler would be a better approach, but...

Ermmm - does a service actually support displaying a message to the user ? (requirement specified by the asker).

ps.  Displaying a message to the user from a windows form app is trivial.
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39594133
The service should not directly show a message (because a service should not technically use anything with UI), however it can easily call out to a windows form application with Process.Start()

You could also notify the user with any other form of communication such as email, a sound, or updating a web page etc...
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39594163
OK - so the service way involves coding a service and an app.
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39594298
Well, there is no technical reason it can't be one project in visual studio.  You can certainly add a windows form to a windows service project and launch an interface however you see fit.  You would just have to add the right references to the service project.

A "proper" design would probably include two projects:  a windows service, and a windows form/console app.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39594338
to show a message to the user would need to be taskbar notification as the users dont check thier email to see any messages and the user is not to interact with any GUI.

Another point i should add is - can a windows service detect the machine user name?

if not then i would need a console app(as i can get username) being fired by a sql job every 10 mins.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39594349
"A "proper" design would probably include two projects:  a windows service, and a windows form/console app. "

so would the windows service be firing the console?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:jmcmunn
ID: 39594393
yes, in this case the service would do something like:

Process.Start(pathToConsoleApplication);

Or, a windows form app if you desired.

To be more clear:  it does not have to launch the app to do the DB check, just when you are notifying the user if there is something they need to see.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39594420
ok, so if i have visual studio project with a windows service that can get (with path to GUI) and GUI(get the enironment.username) and if username and message in DB exists, then i can use GUI to appear as the message?
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39594700
I'm not sure I understand what you are asking...the service can do everything involved with the database, and then call out to the windows form if it finds what it needs.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39594731
yes i thin get it now, how would i call the form?

just by doing this in the service code...

                        if (!msg.Value.Equals(DBNull.Value))
                        {
                            Form1 frm = new Form1(msg.ToString());
                        }
0
 
LVL 1

Author Comment

by:razza_b
ID: 39594741
how do i actually get the service installed?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39594861
>>yes i thin get it now, how would i call the form? ...

No.  A service does not support any user interface (as I mentioned earlier).  You need to follow the advice of jmcmunn and use are more roundabout route.
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39594864
"yes i thin get it now, how would i call the form?"

If it is in the same project that would work...otherwise, you would need to do something like....

Process.Start("<path to program>"); //where the path to your exe is here...maybe in a config file or else in the same local path


"how do i actually get the service installed?"
http://msdn.microsoft.com/en-us/library/ddhy0byf.aspx

A Google search will provide tons of info on creating a service installer.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39595005
i have the windows service and form in same project and in the onstart method i will call my SP and return my message value and if its not null i call the form...

Form1 frm = new Form1(msg.ToString());

but im having a problem trying trying to install exe its says windows cannot find 'InstallUtil.exe'
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39595217
The onstart method is not where you will want to call your sp...you will want a timer to call it every 10 minutes.

It sounds like due to your unfamiliarity with services, you may want to use a simple Windows Form all and run it with windows task scheduler.  Unless you are willing to follow tutorials online for getting the service up and running, it is going to be more overhead than it is worth.
0
 
LVL 1

Author Comment

by:razza_b
ID: 39595344
i have this..

private Timer timer = new Timer();
        private double servicePollInterval;

        public ScheduledEquipService()
        {
            InitializeComponent();
            servicePollInterval = 120000;
        }

        protected override void OnStart(string[] args)
        {
            timer.Elapsed += new ElapsedEventHandler(timer_Elapsed);
            timer.Interval = servicePollInterval;
            timer.AutoReset = true;
            timer.Enabled = true;
            timer.Start();
        }

and in my timer event method im calling my sp.

i have managed to install my exe into services in admin tools but nothing is happening at all. and i have started the service.
0
 
LVL 12

Accepted Solution

by:
jmcmunn earned 500 total points
ID: 39595478
Have you read the article I posted earlier on debugging a service?  You can also put debug versions of your service out there and attach to it and debug like any windows application to see what happens.

Since you set the interval to 2 minutes, nothing would be expected for at least that long.  Depending on the version of Windows you are running on, you might have to set the "interact with desktop" option on your service.

If the service part of this is giving you too many issues, you could opt for another solution.  Many have been suggested.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now