locked
SqlDependency is not working if table is updated RRS feed

  • Question

  • User-1353617951 posted

    I am trying to show online friends of a user. For this I have created a column 

    OnlineStatus

     in 

    UserInfo

     table.I have created a hub which ,on start,calls a function and that function sends a 

    Ajax

    request to server to get the list of online friends. If a friend of that user logs in, the 

    OnlineStatus

     will be set to 

    1

     otherwise 

    0

    .For a user to see his online friends in real time I am using 

    SqlDependency

     if the 

    OnlineStatus

     column is updated and 

    SiganlR

     to call a function of 

    hub

     which in turn calls a function of Client and that function in turn sends a 

    Ajax

     request to server requesting the updated list of online users. 

    Hub

     ,on start, working fine but if a user logs out and SqlDependency is not getting called. Please help me to fix it. here is javascript:

    $(document).ready(function () {
            var onlineFriends = $.connection.socialnetworkHub;
    
    
            onlineFriends.clients.updateOnlineFriends = function () {
                getOnlineFriends()
            };
    
            $.connection.hub.start().done(function () {
                alert("Hub started");
                getOnlineFriends();
            });
        });
        function getOnlineFriends() {
            var resdiv = $("#online-friends");
            var onlineusers = "";
            $.ajax({
                type: 'Post',
                url: '/SearchUser/FindOnlineFriends?UserId=@Convert.ToInt64(User.Identity.Name)',
                success: function (data) {
                    $.each(data, function (id, result) {
                        resdiv.html('');
                        var searchresult = "";
                        $.each(data, function (id, result) {
    
                            searchresult += '<div style=";height:30px;width:230px;overflow:auto;border:1px solid green;background-color:ghostwhite;padding:2px 2px 2px 2px;margin-left:5px;">' +
                                                '<img src="~/Images/UserImages/' + result.DisplayPhoto + '" width="25" height="25" />'
                                                + '<span>&nbsp;</span>'
                                                + '<a href="http://localhost:1391/Post/Showposts?UserId=' + result.UserId + '&self=True" style="text-decoration:none;;top:0px;left:0px;width:100%;height:100%">' + result.Name + '</a>'
                                                + '<br />'
                                                    + '</div>';
                        });
                        resdiv.html(searchresult);
                    });
                },
                error: function () {
                    alert("Couldn't fetch online users");
                }
            });
        }


    public class SocialNetworkHub : Hub
        {
            private static string conString = ConfigurationManager.ConnectionStrings["SQL"].ToString();
            public void Hello()
            {
                Clients.All.hello();
            }
            public static void ShowOnlineFriends()
            {
                IHubContext context = GlobalHost.ConnectionManager.GetHubContext<SocialNetworkHub>();
                context.Clients.All.updateOnlineFriends();
            }
        }


    Public JsonResult FindOnlineFriends(long UserId)
            {
                List<UserDetails> onlinefriends = new List<UserDetails>();
                using(SqlConnection con=new SqlConnection(connectionString))
                {
                    using(SqlCommand cmd=new SqlCommand())
                    {
                        StringBuilder builder = new StringBuilder();
                        builder.Append("select [UserId],[DisplayPhoto],[Name] from [dbo].[UserInfo] as userself,(");
                        builder.Append("(select [PersonId2] from [dbo].[FriendsRelation] where  [PersonId1] like @UserId) union");
                        builder.Append("(select [PersonId1] from [FriendsRelation] where  [PersonId2] like @UserId)) as friends ");
                        builder.Append("where [userself].[UserId]=[PersonId2] and [OnlineStatus]=1");
                        cmd.CommandText = builder.ToString();
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@UserId",UserId);
                        cmd.Notification = null;
                        SqlDependency.Stop(connectionString);
                        SqlDependency.Start(connectionString);
                        SqlDependency dependency = new SqlDependency(cmd);
                        dependency.OnChange += new OnChangeEventHandler(onlineFriends_OnChange);
                        con.Open();
                        using(SqlDataReader rdr=cmd.ExecuteReader())
                        {
                            if(rdr.HasRows)
                            {
                                while(rdr.Read())
                                {
                                    onlinefriends.Add(new UserDetails { UserId = Convert.ToInt64(rdr["UserId"]), Name = rdr["Name"].ToString(), DisplayPhoto = rdr["DisplayPhoto"].ToString() });
                                }
                            }
                        }
                    }
                }
                return  Json(onlinefriends,JsonRequestBehavior.AllowGet);
            }
            private void onlineFriends_OnChange(object sender, SqlNotificationEventArgs e)
            {
                if (e.Type == SqlNotificationType.Change)
                {
                    SocialNetworkHub.SocialNetworkHub.ShowOnlineFriends();
                }
            }
    Thursday, April 23, 2015 2:34 PM

Answers

  • User71929859 posted

    Is it possible for sqldependency to work if command have 'WHERE' and 'UNION' clause?

    Below two are quoted from the MSDN reference

    • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

    • The statement must not include the UNION, INTERSECT, or EXCEPT operators.

    https://msdn.microsoft.com/library/ms181122.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 26, 2015 5:40 AM

All replies

  • User1711366110 posted

    am trying to show online friends of a user

       As per your case, I suggest you to replace the following code in part of the given code :

    function getOnlineFriends() {
    …..
    …
    searchresult += "<div style=';height:30px;width:230px;overflow:auto;border:1px solid green;background-color:ghostwhite;padding:2px 2px 2px 2px;margin-left:5px;'>" 
    +"<img src='~/Images/UserImages/" + result.DisplayPhoto + "' width='25' height='25' />"
    + "<span>&nbsp;</span>"
    + "<a href='http://localhost:1391/Post/Showposts?UserId=" + result.UserId + "&self=True' style='text-decoration:none;;top:0px;left:0px;width:100%;height:100%'>" + result.Name + "</a>"
    + "<br />"
    + "</div>";
    ..
    ..
    }
    
    Public JsonResult FindOnlineFriends(long UserId)
            {
                List<UserDetails> onlinefriends = new List<UserDetails>();
                using(SqlConnection con=new SqlConnection(connectionString))
                {
                    using(SqlCommand cmd=new SqlCommand())
                    {
    String builder;
    builder="select [UserId],[DisplayPhoto],[Name] from [dbo].[UserInfo] as userself,(";
    builder+=string.Format("(select [PersonId2] from [dbo].[FriendsRelation] where  [PersonId1] like '%{0}%') union all ",@UserId);
    builder+=string.Format("(select [PersonId1] from [FriendsRelation] where  [PersonId2] like'%{0}%')) as friends ",@UserId);
    builder+="where [userself].[UserId]=[PersonId2] and [OnlineStatus]=1";
    …
    ..
    }
    

    --
    with regards,
    Edwin

    Friday, April 24, 2015 2:39 AM
  • User-1353617951 posted

    Is it possible for sqldependency to work if command have 'WHERE' and 'UNION' clause?

    Saturday, April 25, 2015 6:38 PM
  • User71929859 posted

    Is it possible for sqldependency to work if command have 'WHERE' and 'UNION' clause?

    Below two are quoted from the MSDN reference

    • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

    • The statement must not include the UNION, INTERSECT, or EXCEPT operators.

    https://msdn.microsoft.com/library/ms181122.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 26, 2015 5:40 AM