[C#] communicate between a winforms application users

See more:

C#

entity-framework

I am trying to develop a WinForms application, the application is a procurement system where employees at different locations (SITE) send purchase requests containing transactions that must be carried out by the procurement employee(HO).

I am using c# and Entity Framework 6 (Database-First Approach) to connect to a SQL Server Database but to my knowledge Database Triggers and SqlDependency don't work for EF so that when a user (SITE) send a request the (HO) gets a notification.

Since this is winForms application, instances (users) are unable to communicate with each other, I searched all over the web and found that if my application is a web application this can be easy but I have a basic knowledge for ASP.NET.

I also searched for Web service/API (don't know if they are same or diffecrent) but will such service/API allow my application users to communicate with each other.

What I have tried:

I tried SqlDependency but its not working for Entity Framework.

using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System;
using System.Data.Entity.Core.Objects;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq.Expressions;
using System.Threading;
using System.Collections.Concurrent;

namespace SQL_Dependency_Test
{
    public static class DbQueryExtension
    {
        public static ObjectQuery<T> ToObjectQuery<T>(this DbQuery<T> query)
        {
            var internalQuery = query.GetType()
                .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
                .Where(field => field.Name == "_internalQuery")
                .Select(field => field.GetValue(query))
                .First();

            var objectQuery = internalQuery.GetType()
                .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
                .Where(field => field.Name == "_objectQuery")
                .Select(field => field.GetValue(internalQuery))
                .Cast<ObjectQuery<T>>()
                .First();

            return objectQuery;
        }

        public static SqlCommand ToSqlCommand<T>(this DbQuery<T> query)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = query.ToString();

            var objectQuery = query.ToObjectQuery();

            foreach (var param in objectQuery.Parameters)
            {
                command.Parameters.AddWithValue(param.Name, param.Value);
            }

            return command;
        }

        public static string ToTraceString<T>(this DbQuery<T> query)
        {
            var objectQuery = query.ToObjectQuery();

            return objectQuery.ToTraceStringWithParameters();
        }

        public static string ToTraceStringWithParameters<T>(this ObjectQuery<T> query)
        {
            string traceString = query.ToTraceString() + "\n";

            foreach (var parameter in query.Parameters)
            {
                traceString += parameter.Name + " [" + parameter.ParameterType.FullName + "] = " + parameter.Value + "\n";
            }

            return traceString;
        }
    }

    public class EntityChangeEventArgs<T> : EventArgs
    {
        public IEnumerable<T> Results { get; set; }
        public bool ContinueListening { get; set; }
    }

    public class EntityChangeNotifier<TEntity, TDbContext>
        : IDisposable
        where TDbContext : DbContext, new()
        where TEntity : class
    {
        private DbContext _context;
        private Expression<Func<TEntity, bool>> _query;
        private string _connectionString;

        public event EventHandler<EntityChangeEventArgs<TEntity>> Changed;
        public event EventHandler<NotifierErrorEventArgs> Error;

        public EntityChangeNotifier(Expression<Func<TEntity, bool>> query)
        {
            _context = new TDbContext();
            _query = query;
            _connectionString = _context.Database.Connection.ConnectionString;

            SafeCountDictionary.Increment(_connectionString, x => { SqlDependency.Start(x); });

            RegisterNotification();
        }

        private void RegisterNotification()
        {
            _context = new TDbContext();

            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                using (SqlCommand command = GetCommand())
                {
                    command.Connection = connection;
                    connection.Open();

                    var sqlDependency = new SqlDependency(command);
                    sqlDependency.OnChange += new OnChangeEventHandler(_sqlDependency_OnChange);

                    // NOTE: You have to execute the command, or the notification will never fire.
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                    }
                }
            }
        }

        private string GetSql()
        {
            var q = GetCurrent();

            return q.ToTraceString();
        }

        private SqlCommand GetCommand()
        {
            var q = GetCurrent();

            return q.ToSqlCommand();
        }

        private DbQuery<TEntity> GetCurrent()
        {
            var query = _context.Set<TEntity>().Where(_query) as DbQuery<TEntity>;

            return query;
        }

        private void _sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (_context == null)
                return;

            if (e.Type == SqlNotificationType.Subscribe || e.Info == SqlNotificationInfo.Error)
            {
                var args = new NotifierErrorEventArgs
                {
                    Reason = e,
                    Sql = GetCurrent().ToString()
                };

                OnError(args);
            }
            else
            {
                var args = new EntityChangeEventArgs<TEntity>
                {
                    Results = GetCurrent(),
                    ContinueListening = true
                };

                OnChanged(args);

                if (args.ContinueListening)
                {
                    RegisterNotification();
                }
            }
        }

        protected virtual void OnChanged(EntityChangeEventArgs<TEntity> e)
        {
            if (Changed != null)
            {
                Changed(this, e);
            }
        }

        protected virtual void OnError(NotifierErrorEventArgs e)
        {
            if (Error != null)
            {
                Error(this, e);
            }
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                SafeCountDictionary.Decrement(_connectionString, x => { SqlDependency.Stop(x); });

                if (_context != null)
                {
                    _context.Dispose();
                    _context = null;
                }
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }

    public class NotifierErrorEventArgs : EventArgs
    {
        public string Sql { get; set; }
        public SqlNotificationEventArgs Reason { get; set; }
    }

    public static class SafeCountDictionary
    {
        private class SafeCount : MarshalByRefObject
        {
            private int _counter;
            public int Counter { get { return _counter; } }

            public void Increment()
            {
                Interlocked.Increment(ref _counter);
            }

            public void Decrement()
            {
                Interlocked.Decrement(ref _counter);
            }
        }

        private static ConcurrentDictionary<string, SafeCount> _registeredConnectionStrings = new ConcurrentDictionary<string, SafeCount>();

        /// <summary>
        /// Increments the count for the given key by one. If the key does not exist, it creates it and sets the count to 1.
        /// </summary>
        /// <param name="key">The key to increment.</param>
        /// <param name="onAdd">Executes when the key is first created. Does not run on updates.</param>
        public static void Increment(string key, Action<string> onAdd)
        {
            var safeCount = _registeredConnectionStrings.GetOrAdd(key, x =>
            {
                onAdd(x);
                return new SafeCount();
            });

            safeCount.Increment();
        }

        /// <summary>
        /// Decrements the count for the given key by one. If the count reaches 0, it runs <paramref name="onZero"/>.
        /// </summary>
        /// <param name="key">The key to decrement.</param>
        /// <param name="onZero">Executes when the count equals zero.</param>
        public static void Decrement(string key, Action<string> onZero)
        {
            SafeCount val;

            if (_registeredConnectionStrings.TryGetValue(key, out val))
            {
                val.Decrement();

                if (val.Counter == 0)
                {
                    onZero(key);
                }
            }
        }
    }

}

In the Test form class this is the code I wrote

private void Form1_Load(object sender, EventArgs e)
        {
            textBox2.Text = ConfigurationManager.ConnectionStrings["TestEntities"].ConnectionString;

            using (var notifer = new EntityChangeNotifier<Employee, TestEntities>(p=>p.employeeName == "John Doe"/*id != null*/))
            {
                notifer.Error += (sender2, e2) =>
                {
                    Console.WriteLine("[{0}, {1}, {2}]:\n{3}", e2.Reason.Info, e2.Reason.Source, e2.Reason.Type, e2.Sql);
                };

                notifer.Changed += (sender2, e2) =>
                {
                    Console.WriteLine(e2.Results.Count());
                    foreach (var p in e2.Results)
                    {
                        textBox2.AppendText(p.id + ":" + p.employeeName);
                    }
                };
            }
        }