hi
i am working with Signal r and sql dependency . i have created one demo for such requirement that if i update in database . it will auto change ui content. its working fine but when i tried to use group by clause or top or Rownum . Sql dependency Change event is not firing and it does not reflect any change on UI.
readonly string _connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
public IEnumerable<productinfo> GetAllMessages()
{
var ProductInfo = new List<productinfo>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"[dbo].[GetAllRecordsWithParams]", connection))
{
command.Notification = null;
command.CommandType = CommandType.StoredProcedure;
var dependency = new SqlDependency(command);
SqlDependency.Start(_connString);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
ProductInfo.Add(item: new ProductInfo
{
CustomerID = (string)reader["CustomerID"],
ContactName = (string)reader["ContactName"],
Phone = (string)reader["Phone"],
ProductName = (string)reader["ProductName"],
UnitPrice = (decimal)reader["UnitPrice"],
IsDeleted = (bool)reader["IsDeleted"]
});
}
}
connection.Close();
connection.Dispose();
}
return ProductInfo.ToList();
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
MessagesHub.SendMessages();
}
}
Sql changes ::
ALTER PROCEDURE [dbo].[GetMixItems]
AS
BEGIN
select dm.MessageID, dm.Message, dm.EmptyMessage, dm.Date, dt.MessageExtendedText,dt.IsRight
from [dbo].[Messages] as dm inner join [dbo].[TestTable] as
dt on dm.MessageID = dt.MessageTestId where IsRight = 0
group by dm.MessageID, dm.Message, dm.EmptyMessage, dm.Date, dt.MessageExtendedText,dt.IsRight
order by dm.MessageID desc
END
What I have tried:
i tried all i have mentioned .. please tell me d solution of this. how to do such all clause work with sql dependency...