Implementing a Windows Service - how do I meet these requirements?

I'm looking for some general advice on implementing a windows service. My requirements are as follows:

• The service processes rows from a database - so if a row exists with a certain status, it needs to do some work, write to some other tables, then mark the row as being processed.
• There may be many thousands of rows awaiting processing when the service starts - it should process them all.
• The service should then continue to run, processing any rows as they are written into my ForProcessing table.
• I need to be able to shut it down gracefully - I don't want any half processed rows
• It needs to perform efficiently - I don't want to be polling the database if possible

So my specific questions are:

• How do I keep it alive and responding to new rows as they're written? Can I use a SQLDependency object of some kind to efficiently process new rows?
• How do I handle graceful startup and shut down?
• When picking up rows from the db, should I fetch all that are awaiting processing, or just one at a time?
• Presumably, given that it is already a background servic开发者_如何学Goe, I would process rows synchronously? In otherwords, there is no need to process rows on background threads?
• What do I need to think about in terms of accounts and under what permissions it runs? Is this a deployment issue?
• Any other gotchas or things to be aware of?

Thanks for any advice.

Ps. Are there any other reasonable alternatives to using a Windows Service to achieve this?

Writing a Windows Service is not really that difficult, but can seem daunting at first admittedly.

As far as flushing out the business logic, I will typically write a command prompt program first because it's a whole lot easier to debug.

Now, on to the service. Of course, use Visual Studio to create a Windows Service Project.

How do I keep it alive and responding to new rows as they're written? Can I use a SQLDependency object of some kind to efficiently process new rows?

Well, I'm not sure if you can, but I can tell you what you will need. You will need a thread. If you don't create a new thread in your OnStart event your service will stop immediately. This thread can be created either directly using one of .NET's many thread mechinisims or it can be done indirectly via a Timer of some sort, FileSystemWatcher (which will probably not apply to you), or probably others.

How do I handle graceful startup and shut down?

You do that in your OnStop. You do that via signaling your thread, perhaps via a global variable, that it is time to shut down. Then, it's up to your thread notice the signal in a timely manner and clean up and exit the thread. If you are in the middle of something that may take more than say 20 seconds you need to tell the SCM (Service Control Manager) that you need some more time. If you don't the user stopping the service gets messages saying that service is not responding and eventually will just get killed.

When picking up rows from the db, should I fetch all that are awaiting processing, or just one at a time?

That's a business decision. Probably should depend on how long each takes to process so you can be timely in case a Stop happens.

Presumably, given that it is already a background service, I would process rows synchronously? In otherwords, there is no need to process rows on background threads?

Again, this is a business decision, but yes, I would probably keep is synchronous.

What do I need to think about in terms of accounts and under what permissions it runs? Is this a deployment issue?

Well, that all depends. Is the SQL Server on the same box? Are you using Windows Authentication? If SQL is not on the same box you will have to use a User Account or perhaps Network Service. When you create your service project you will need to add an installer. When your project is created you get a Component Class--on the gray screen, right click and choose Add Installer. That will create a new Component Class with two modules already installed. serviceIntaller1 has things like the service name, display name, and description as well as the start type (Manual, Auto, etc). The other is serviceProcessInstaller1 and that is where you decide what logon account to use.

Any other gotchas or things to be aware of?

Uh, probably. But I haven't had my coffee yet this morning.

Really, the biggest thing is planning on how to keep your thread alive and responsive to an eventual OnStop request. For example if you are going to try and use the SqlDependency class you may need to create a Manual or AutoResetEvent in your OnStart, start your thread and have your thread setup the SqlDependency then block on the reset event. Then, in your OnStop release your reset event so your thread can clean up and leave. After that, it's just a skate down hill!

Have Fun!

My first question is why isn't a trigger or a SQL job good enough? It seems like either would be sufficient. Run a script to catch up the processing then turn on the trigger.

If you are set on a service then Jim has good advice about running as a console program first. Or do something like this http://tech.einaregilsson.com/2007/08/15/run-windows-service-as-a-console-program/

Also http://topshelf-project.com/ looks like a very good starting point too.

You might want to handle the pause and restart events for the service as well. And again like Jim said spawn your processing off in another thread. I just had to fix a big on my service where I didn't do this and the service will just sit in a starting state and you will not be able to kill it easily. The onStart method HAS to return.