C# – Best way to get push notifications to server from MS SQL Server

Using stored procedures is one way, and has been in widespread use for many years.

A more modern way to interact with SQL Server databases from C# (or any .NET language) is to use Entity Framework. The advantage of Entity Framework is that it provides a higher level of abstraction.

To quote from Microsoft (https://msdn.microsoft.com/en-us/data/jj590134 ):

The ADO.NET Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

• Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.
• Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
• Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
• Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
• Multiple conceptual models can be mapped to a single storage schema.
• Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

The use of an ORM vs Stored Procedures involves tradeoffs, particularly in terms of security and where the logic resides.

The "classic" approach to development with SQL Server is to have the application logic reside in stored procedures and programs only given security rights to execute stored procedures, not update tables directly. The concept here is that stored procedures are the business logic layer for the application(s). While the theory is sound, it has tended to fall out of favor for various reasons, being replaced by implementing the business logic in a programming language like C# or VB. Good applications are still implemented with a tiered approach, including separation of concerns etc. but are more likely to follow a pattern like MVC.

One downside of implementing logic in the ORM rather than the database is ease of debugging and testing data integrity rules by those responsible for the database (DA or DBA). Take the classic example of transferring money from your checking to savings account, it is important that this be done as an atomic unit of work, in other words sandwiched in a transaction. If this sort of transfer is only allowed to be done through a stored procedure it is relatively easy for the DA and auditors to QA the stored procedure.

If on the other hand this is done via an ORM like Entity Framework and in production it is discovered that on rare occasions money is taken from checking but not put into savings debugging may be far more complex, particularly if multiple programs are potentially involved. This would most likely be an edge case, perhaps involving peculiar hardware issues that need to occur in a particular sequence etc. How does one test for this?

Your requirements are pretty mild, so you can afford to write a very simple / easy to maintain solution.

In your case I'd probably write a console app & launch it via the task scheduler. The app would read the "queue", process any existing requests, then exit. You could schedule it to run every 5 - 10 minutes or so. Updating the code is easy - overwrite the file between scheduled runs.

You could also use a Windows Service (using TopShelf for example) - they're pretty easy to write, but then instead of exiting when finished you'd need to sleep. Installing a new version gets to be a little bit more of a hassle than a console app, but not much.

If you start looking at higher volumes, you'd eventually want to eliminate the polling and probably move the queue out of the database & into something else (I have mixed feelings about MSMQ)