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
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);
}
}
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())
{
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
isTimeout = true;
}
if (iResetCounter++ == MAX_RESET)
{
ReRegisterSqlNotification();
iResetCounter = 0;
}
else
{
RegisterSqlNotificationRequest();
}
}
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.
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