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

Question

I have the scenario to use 2 different SqlDependencies with 2 different databases connection, but call in one function.

I want to get updates by 1st DB using SqlDependency, then sync the 2nd DB on 1st DB changes, so then on 2nd DB updates I want to load the changes at client end Kendo Grid by Signalr the simple process is working, but when 1st time DB1 changes it sync the DB2 then DB2 notify at client end to show changes, but also the same process when get 2nd time changes in DB1, SqlDependency calls 3 times and notify client side 3 time, for 3rd time changes in DB1 its SqlDepency calls 6 time or more, mean when next changes after 3 to so on its SqlDependency calls infite time:

1. 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();
           }
   
       }
   

2. 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();
           }
   
       }
   

3. 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;
       }
   

4. DB2 SqlDependency

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

5. HMailDB (DB1 SQL function)

   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();
       }
   
   }
   

6. DB1 SqlDependency

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

7. Client End code(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");
       }
   
   });
   

8. API method use in Kendo Grid

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

I have explained my problem above in detail. Please guide me to resolve or suggest me any alternate optimized solution, I appreciate your valuable time and effort. Thanks

Answer 1

i had same kind of problems using SQL Dependency.

so i have created a class to use it effectively.

Note: you should call SqlDependency.Start(_connStringDB1); at 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();      
    }
}

usage

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
});