Which method to use for this task?

Question

I have a simple requirement by my client but I am confused on how to do that task:

TASK DETAILS
My client has an SQL Database and he wants a back-end service/operation which will do the following tasks:

1. Take credentials from user to connect to that database.
2. If any exception occurs then write that to a notepad file in that system.
3. After the connection is OK then that program will check if any new data has arrived in 1 specific table.
4. If the data has just inserted in that table from any source then my service will call an External API and post that latest row data to that API.

Thats It! Now I am stuck on where and how to proceed.

Any guidance/hint will be appreciated.

I am stuck between SQL and Windows Service decision, I myself think that I can go with 2 ways:

Approach 1 - Create a windows service which will run a timer in background and check if any row has inserted, if so then call external API.

Approach 2 - Create a trigger in SQL table which will then run a script/windows service which will then post that latest row data to that API.

I need to do the task in C# or SQL.

Please guide me.

Answer 1

Create a trigger in SQL table which will then run a script/windows service which will then post that latest row data to that API.

What for a API? ActiveX, SOAP, REST Service?
Anyway, achieving that with plain T-SQL is more then difficult, better go the way with a Windows Service.

Answer 2

Besides the triggers, especially if external API is difficult to invoke from SQL, consider “Query Notifications”, and write the code in C#:

• https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/query-notifications-in-sql-server
• https://social.msdn.microsoft.com/Forums/vstudio/en-US/3e77e604-c5bb-43f4-b966-b221894ee007

Although, a simple polling using timers or delays is probably an acceptable simpler solution in certain circumstances.

Answer 3

EDIT Seems the forum editor is acting up with formatting with links so realize this and the links are all at the bottom of the post.

Take credentials from user to connect to that database.

Seems if this is a desktop solution using SqlDependency that there would be a user setup in the database or in active directory with proper permissions so no login is needed.

If any exception occurs then write that to a notepad file in that system.

There are many ways to achieve this from a log library to writing to application logs on the local machine or on a server, goes with proper permissions to perform writing to test or application logs

After the connection is OK then that program will check if any new data has arrived in 1 specific table.

This is the easy part yet considerations go to if a windows service what account is the service running under? local, system what are their permissions. A service does not run under the current user login.

If the data has just inserted in that table from any source then my service will call an External API and post that latest row data to that API.

Same as above.

What is better, both writing a service (if never done this before) is easy to write yet things like

Debugging locally or on a server can be challenging especially when testing on Windows version higher than Windows 7 as Microsoft changed security in Windows 8 on up. Controlling the service can get tedious so I use a utility (I created). Setting up for remote debugging, goes back to Microsoft's security and attaching to the service process in Visual Studio.

Working with SqlDependency other than properly setting up the database is much easier to deal with, could be executed from Windows scheduler or run from a system try application. If needed a login can be done or go with setting up the user(s) permissions in SQL-Server.

My recommendation is to use SqlDependency for your requirements and ease of coding.

References

For creating an Windows service installer Windows Service developer GUI for install while in development mode and source for article with a sample service, in this case writes records to SQL-Server but can also read etc.

Debugging a windows service under Windows 10