How to use SqlDependency and SignalR with two different databases using one function in C #?

Question

I have a script for using two different SqlDependencies with 2 different database connections, but it is called by a single function.

I want to receive updates on the 1st database using SqlDependency, and then synchronize the second database with the changes of the 1st database, so when updating the 2nd database I want to download the changes on the client side of Kendo Grid by Signalr a simple process works, but when DB1 changes the first time, it synchronizes DB2, and then DB2 notifies the client side to show the changes, but also the same process, when the second time changes to DB1, SqlDependency calls 3 times and notifies the client side 3 times, for 3 temporary changes to DB1 its SqlDepency causes 6 or more h, means that with the following changes after 3 to so on SqlDependency comes infinite time:

• EmailHub (DB2 Hub)

  public class EmailHub : Hub { private static string _connStringDB2 = ConfigurationManager.ConnectionStrings["MyDB2"].ToString(); [HubMethodName("updateRecords")] public static void UpdateRecords() { IHubContext context = GlobalHost.ConnectionManager.GetHubContext<EmailHub>(); context.Clients.All.getUpdates(); } } 

• HMailHub (DB1 hub)

    public class HMailHub : Hub { private static string _connStringDB1 = ConfigurationManager.ConnectionStrings["MyDB1"].ToString(); [HubMethodName("updateRecords")] public static void UpdateRecords() { IHubContext context = GlobalHost.ConnectionManager.GetHubContext<EmailHub>(); context.Clients.All.getUpdates(); } } 

• GetEmailMessagesSQL (DB2 function)

    public IEnumerable<EmailAflAwmMessageDM> GetEmailMessagesByAccountSQL(string emailid) { var messages = new List<EmailAflAwmMessageDM>(); // sync hmailDb to LocalDb by EmailAccountId HMailServerSync objEmailSync = new HMailServerSync(); objEmailSync.GetEmailMessagesByAccount(Guid.Parse(emailid)); // stop all Sql dependencies before start new one SqlDependency.Stop(_connStringDB1); SqlDependency.Stop(_connStringDB2); //hmailDB service(DB1 sql function call) hmailsyncService(emailid); using (var connection = new SqlConnection(_connString)) { connection.Open(); using (var command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), connection)) { command.Parameters.Add(new SqlParameter("@emailaccountid", emailid)); command.Notification = null; var dependency = (dynamic)null; SqlDependency.Start(_connStringDB2); dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependencyemailmessage_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); using (var reader = command.ExecuteReader()) messages = reader.Cast<IDataRecord>() .Select(x => new EmailAflAwmMessageDM() { to_msg = x.GetString(0), from_msg = x.GetString(1), subject = x.GetString(2), msg_date = x.GetDateTime(3) }).ToList(); } connection.Close(); } return messages; } 

• DB2 SqlDependency

    private void dependencyemailmessage_OnChange(object sender, SqlNotificationEventArgs e) { if (e.Type == SqlNotificationType.Change) { EmailHub.UpdateRecords(); } } 

• HMailDB Function (DB1 SQL)

   public void GetHmailMessagesByAccountSQL(int hmailid) { using (var connection = new SqlConnection(_connStringDB1)) { connection.Open(); using (var command = new SqlCommand(SQL.hmailmessages_sql(), connection)) { command.Parameters.Add(new SqlParameter("@messageaccountid", hmailid)); command.Notification = null; var dependency = (dynamic)null; SqlDependency.Start(_connStringDB1); dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependencyhmailmessage_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); var reader = command.ExecuteReader(); } connection.Close(); } } 

• DB1 SqlDependency

    private void dependencyhmailmessage_OnChange(object sender, SqlNotificationEventArgs e) { if (e.Type == SqlNotificationType.Change) { EmailHub.UpdateRecords(); } } 

• Client End (Kendo Grid)

    <div id="grid"> </div> @Scripts.Render("~/bundles/signalr") <script src="~/signalr/hubs"></script> <script type="text/javascript"> var emailid = '@TempData["DefaultEmailId"]' $(function () { // Declare a proxy to reference the hub. var notifications = $.connection.emailHub; // Create a function that the hub can call to broadcast messages. notifications.client.getUpdates = function () { alert("notification called"); updateGridData(); }; // Start the connection. $.connection.hub.start().done(function () { alert("connection started") updateGridData(); }).fail(function (e) { alert(e); }); function updateGridData() { datasource = new kendo.data.DataSource({ transport: { read: { url: crudServiceBaseUrl + "EmailAflAwmMessage/getMessages/?emailid=" + emailid, dataType: "json", }, update: { url: crudServiceBaseUrl + "EmailAflAwmMessage/Put/", type: "PUT", parameterMap: function (options, operation) { if (operation !== "read" && options.models) { return { models: kendo.stringify(options.models) }; } } }, }, schema: { model: { id: "EmailMessageId", fields: { EmailMessageId: { editable: true, nullable: false, type: "guid" }, subject: { editable: true, nullable: true, type: "string" }, to_msg: { editable: true, nullable: false, type: "string" }, } } } }); $("#grid").kendoGrid({ dataSource: datasource, editable: "popup", toolbar: ["create"], columns: [ { field: "to_msg", title: "to_msg", }, { field: "from_msg", title: "from_msg", }, { field: "subject", title: "subject", }, { field: "msg_date", title: "msg_date", } ], height: "400px", pageable: { refresh: true, pageSizes: true, buttonCount: 5 }, }).data("kendoGrid"); } }); 

• Using the Kendo Grid API Method

   public IEnumerable<EmailAflAwmMessageDM> GetMessages(string emailid) { return objEmailSQLFunction.GetEmailMessagesByAccountSQL(emailid); } 

I have explained my problem in detail above. Please help me solve or offer me an alternative optimized solution, I appreciate your valuable time and effort. Thanks

Answer 1

I had problems using SQL Dependency.

so I created a class for efficient use.

Note: you must call SqlDependency.Start(_connStringDB1); in Application_Start (only once)

 public class LiveData { public string SprocOrQuery { get; set; } private Dictionary<string, object> par = new Dictionary<string, object>(); public Dictionary<string, object> Parameters { get { return par; } set { par = value; } } public string SqlConn { get; set; } public Action<DataTable> ActionOnData { get; private set; } public bool EffectedOnly { get; set; } public DateTime EffectDate = DateTime.Now; public int EffectedCyles { get; private set; } public DataTable Data { get; private set; } public List<SqlNotificationInfo> Events { get; set; } public SqlNotificationInfo CurrentEvent { get; private set; } public LiveData() { } public LiveData(string sprocOrQuery, Dictionary<string, object> parameters = null, string connection = null) { SprocOrQuery = sprocOrQuery; Parameters = parameters; SqlConn = connection; } public Task Start(Action<DataTable> actionOnData = null) { return Task.Factory.StartNew(() => { try { if (ActionOnData == null) ActionOnData = actionOnData; SqlConnection sqlConn = new SqlConnection(SqlConn); using (SqlCommand cmd = new SqlCommand(SprocOrQuery, sqlConn) { CommandType = SprocOrQuery.Contains(" ") ? CommandType.Text : CommandType.StoredProcedure, CommandTimeout = 60 }) { if (Parameters != null && Parameters.Count > 0) foreach (var key in Parameters.Keys) cmd.Parameters.Add(new SqlParameter(key, Parameters[key])); if (EffectedOnly) /* Sproc or Query must accept @UpdateDate parameter as DateTime */ { if (cmd.Parameters.Contains("EffectDate")) cmd.Parameters["EffectDate"].Value = EffectDate; else cmd.Parameters.Add(new SqlParameter("EffectDate", EffectDate)); } cmd.Notification = null; Data = new DataTable(); new SqlDependency(cmd).OnChange += OnChange; if (sqlConn.State == ConnectionState.Closed) sqlConn.Open(); Data.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection)); } if ((Events == null || Events.Contains(CurrentEvent))) { if (EffectedCyles > 0) EffectDate = DateTime.Now; EffectedCyles++; if (ActionOnData != null) ActionOnData.Invoke(Data); } } catch (Exception ex) { Logger.LogException(ex); } }); } private void OnChange(object sender, SqlNotificationEventArgs e) { CurrentEvent = e.Info; SqlDependency dependency = sender as SqlDependency; dependency.OnChange -= OnChange; Start(); } } 

Using

 new LiveData() { SprocOrQuery = @"SELECT t.[ID], t.[CreateDate], t.[UpdateDate] FROM dbo.Table t INNER JOIN dbo.Group g ON g.[ID] = t.[GroupID] WHERE t.[UpdateDate] >= @EffectDate", SqlConn = "SqlConnectionString", EffectedOnly = true, Events = new List<SqlNotificationInfo>() { SqlNotificationInfo.Update } }.Start(dt => { /* dt is the dataTable you get for every update */ // you can run your dependencyemailmessage_OnChange logic here });