0

could someone please help me, I'm trying to set up alerts with the total amount of records for some tables that I want. In this example, I'm just trying to return COUNT as a result of one of the tables to say how many records don't have schedules for the customer, however with all these exceptions Creating a Query for Notification

,I couldn't think of a solution for my case. 

SELECT COUNT(A.CODREF)QTDEAGENDSEMAGENDA FROM REGISTROS A INNER JOIN ATENDENTES U ON U.CODUSUARIO = A.CODUSUARIO WHERE A.CODUSUARIO = 11 AND A.STATUS IS NULL AND A.CODREF NOT IN ( SELECT CODREF FROM RETORNOS WHERE CODDIALOGO IS NULL AND AGEND_INTERNO IS NULL ) AND DATEDIFF(DAY, A.INICIO, GETDATE())> 11

All the queries I'm going to assemble will look like this in the example. I had thought of creating a view.

Calling my view:

SELECT QTDEAGENDSEMAGENDA FROM ALERTS

then the query would be simple and it would work, but I saw that it is also on the list not to be used.

This is my code and does not work with this query that I set up or with the View

 public class NotificationHub : Hub
{
    string qtdeAgendSemAgenda = string.Empty;

    [HubMethodName("sendNotifications")]
    public string SendNotifications()
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {               

            string query = @"SELECT COUNT(A.CODREF)QTDEAGENDSEMAGENDA FROM REGISTROS A INNER JOIN ATENDENTES U ON U.CODUSUARIO = A.CODUSUARIO WHERE A.CODUSUARIO = 11 AND A.STATUS IS NULL AND A.CODREF NOT IN ( SELECT CODREF FROM RETORNOS WHERE CODDIALOGO IS NULL AND AGEND_INTERNO IS NULL ) AND DATEDIFF(DAY, A.INICIO, GETDATE())> 11";

            connection.Open();

            using (SqlCommand command = new SqlCommand(query, connection))
            {

                command.Notification = null;

                DataTable dt = new DataTable();

                SqlDependency dependency = new SqlDependency(command);

                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)

                    connection.Open();

                var reader = command.ExecuteReader();

                dt.Load(reader);

                if (dt.Rows.Count > 0)
                {
                    qtdeAgendSemAgenda = (dt.Rows[0]["QTDEAGENDSEMAGENDA"].ToString());                        
                }

            }

        }

        IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();

        return Tratar.String(context.Clients.All.RecieveNotification(qtdeAgendSemAgenda));


    }
sheetara
  • 1
  • `This content has been retired and may not be updated in the future. The product, service, or technology mentioned in this content is no longer supported` Hmm! – Richard Hansell Apr 08 '20 at 15:54
  • Hi Richard, Would you have any other suggestions for me to get this same result I want? I need to notify the user in real time all the appointments he has that do not have an appointment with the client, read in the SQL Server database and bring it to Asp.Net Web Application. Thank you – sheetara Apr 08 '20 at 18:17
  • You can run a normal SQL query when the user connects and send them the count. If you want to check periodically invoke the query again and update them. You don't need to use sql dependency at all for this. All of that can be done from the client side by use of "invoke" to call the server method. – Frank M Apr 08 '20 at 18:48
  • @Frank M, thanks for answer. I am new trying to use signalR, would you have an example to give me or some link that can help me to implement – sheetara Apr 08 '20 at 19:11
  • @sheetara, Here is a link for calling methods on the server from the client. I would recommend reading through the docs and examples overall. - https://learn.microsoft.com/en-us/aspnet/signalr/overview/guide-to-the-api/hubs-api-guide-javascript-client#how-to-call-server-methods-from-the-client – Frank M Apr 08 '20 at 19:53

0 Answers0