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);
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>();
public static void Increment(string key, Action<string> onAdd)
{
var safeCount = _registeredConnectionStrings.GetOrAdd(key, x =>
{
onAdd(x);
return new SafeCount();
});
safeCount.Increment();
}
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"))
{
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);
}
};
}
}