Click here to Skip to main content
15,887,683 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i don't know how to start this question but.
i am working on order notification module where i want to notify window service when new order arrived in database,for this i am using sqlnotificationrequest class and registered query (e.g. "select orderid from ordertable"),also created listener which will wait for query "WAITFOR (RECEIVE * FROM ConditionalOrderChangedMessages)"to be execute.
but this query running infinite time.i don't know from where this queue get message that it execute "WAITFOR (RECEIVE * FROM ConditionalOrderChangedMessages)"
command.

below is code to register notification

C#
private void RegisterSqlNotificationRequest()
      {
          if (request == null)
          {
              Guid guid = new Guid();
              request = new SqlNotificationRequest();
              request.UserData = "FWMarketDataEngine-" +serviceName + "-" + guid;
              request.Options = String.Format("Service={0};", serviceName);
              request.Timeout = notificationTimeout;
          }
          if (OnChanged != null)
          {
              OnChanged(this, null);
          }
      }


C#
/// <summary>
     /// Monitoring the Service Broker queue.
     /// </summary>
     private void Listen()
     {
         try
         {
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 using (cmd = new SqlCommand(listenSql, conn))
                 {
                     if (conn.State != ConnectionState.Open)
                     {
                         conn.Open();
                     }
                     cmd.CommandTimeout = notificationTimeout + 150;
                     using (SqlDataReader reader = cmd.ExecuteReader())
                     {
                         while (reader.Read())
                         {
                             //for (int i = 0; i <= reader.FieldCount - 1; i++)
                             //    Debug.WriteLine(reader[i].ToString());
                         }
                     }
                 }
             }
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.Message);
             isTimeout = true;
         }
         //fnCleanUp_Queue_Messages();
         if (iResetCounter++ == MAX_RESET)
         {
             ReRegisterSqlNotification();
             iResetCounter = 0;
         }
         else
         {
             RegisterSqlNotificationRequest();
         }
         //Logger.Log("Monitoring the Service Broker queue.");
     }


C#
private void fnGetSyncData()
      {
          if (conn == null)
          {
              conn = new SqlConnection(connectionString);
          }

          if (command == null)
          {
              command = new SqlCommand(GetSelectSQL(), conn);
          }

          if (dataToWatch == null)
          {
              dataToWatch = new DataSet();
          }
          notification = null;
          notification = new SqlNotificationRequestRegister(GetListenerSQL(), serviceName, timeOut, connectionString);
          notification.OnChanged += NotificationOnChanged;
          FetchDataFromDatabase();
          notification.StartSqlNotification();

      }


since "WAITFOR (RECEIVE" executing multiple time below line also executing multiple time and sending notification to window service.
C#
using (SqlDataReader reader = cmd.ExecuteReader())


i am new to sql service broker framework, don't know what to do
Thanks,

What I have tried:

sql notification
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900