Creating a system service for background SQL commands

  • Thread starter Thread starter Dennis Patterson
  • Start date Start date
D

Dennis Patterson

I need to use the .NET framework (using C#) to create a system service that
makes a connection to a SQL Server database during start up, and then
performs a SELECT statement to poll a few tables once every x minutes. Upon
finding some work to do (if the SELECT clause returns rows), it will then
executing some other SQL update statements to update a few tables.
Performance is low priority since it is a very small task and I want to keep
the code as simple as possible (no fancy threading or queuing is necessary).

Does anyone have some sample code they can share or point me to that
provides a general overview of the framework (code skelton etc) necessary to
accomplish this?

In summary I need a system service that will run under the SYSTEM account to
start up at boot and just do this:
INITIALIZATION: Connection to the database and keep a connection open it
can use to perform a SQL select every x minutes. If the connection goes bad
it needs to be able to make a new connection - otherwise I'd like to keep
reusing the existing connection rather than opening/closing a new
connection, unless that doesn't make sense and is not a good idea.
POLLING: Every x minutes perform an SQL SELECT
UPDATES: For each row return in the SELECT, execute an UPDATE on another
table.
SLEEP: Until its time to poll again (x minutes have passed), then repeat
loop.
Shutdown: Upon request from the system to stop the service, clean up and
exit nicely.

A few questions:

1- Is there a code wizard or something like that within VS.NET that can
generate the C# skelton code to create such a system service (not a web
service, but a regular system service)? How do I generate this?

2- Are there any examples or does anyone have an example they can share of a
service that makes a db connection upon init and then reuses the connection
at a set interval to perform SQL operations?

I am a total newbie in this area so I appreciate any tips or pointers, and I
learn best from code samples if you know of any.

Thank you for your time

---Dennis
 
Hi Dennis,

There is a vb.resource kit with a lot of samples, but since you are using C#
you can have a look to this resource kit. It is for ASP.net but contains a
lot of window forms as far as I have looked to it and also for C#

http://msdn.microsoft.com/asp.net/asprk/

I hope this helps a little bit?

Cor
 
Thanks for the tip. There's certainly a lot of info in there about ADO
which is very helpful.

However I don't see any examples on how to create a system service (only Web
services). In other words I am looking to make a system service (that runs
under Control Panel - Services). In that service I want to make a
connection to SQL Server and then perform SQL statements at a set interval.

Can anyone point me in the right direction on how to create a service like
this (basically looking for guideance on what the skelton framework for such
a service looks like or whether there is a code wizard that creates it).

---Dennis
 
Back
Top