Ok, after more research I found out that there is something called SqlDependency
that was implemented since SQL server 2005.
Very interesting function that enables the SQL Server to "push notifications" to the subscriber. (where here the subscriber is my C# program)
So now whenever the table in SQL change or is modified then it triggers a function in my C# program.
Thanks Abhinav for the hint. It helped me finding that.