Hello, I have a simple table like this:
CREATE TABLE [dbo].[Notifications](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Fk_institutionId] [int] NOT NULL,
[Fk_userId] [int] NOT NULL,
[Read] [bit] NOT NULL,
[CategoryId] [int] NOT NULL,
[Title] [nvarchar](150) NULL,
[CreateDate] [datetime] NOT NULL,
[ReadDate] [datetime] NULL,
[DisplayDate] [datetime] NULL,
[ReadBy] [nvarchar](100) NULL,
[NotificationText] [nvarchar](2000) NULL,
CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
On the master page of my application (which is loaded on each page off the web app when you are logged in), I get the notifications that were showed to the user yet by doing the following code.
CollectionClasses.NotificationsCollection notifications = GetNotifications(userId, null, null, null, null, null, true, "", NotificationRptType.Unread);
#region update undisplayed notification as displayed
EntityClasses.NotificationsEntity newNotification = new EntityClasses.NotificationsEntity();
newNotification.DisplayDate = DateTime.Now;
//filter
IPredicateExpression filter = new PredicateExpression();
filter.Add(CdrlShared.HelperClasses.NotificationsFields.FkUserId == userId);
filter.Add(CdrlShared.HelperClasses.NotificationsFields.DisplayDate == DBNull.Value);
notifications.UpdateMulti(newNotification, filter);
#endregion
return notifications;
Here is the method for GetNoticatification:
public static CollectionClasses.NotificationsCollection GetNotifications(int userId, int? category, DateTime? dateFrom, DateTime? dateTo,
bool? unreadOnly, bool? readOnly, bool? undisplayedOnly, string containsWords, NotificationRptType ContainerType)
{
CollectionClasses.NotificationsCollection notifications = new CollectionClasses.NotificationsCollection();
#region predicate expression
IPredicateExpression filter = new PredicateExpression();
//User ID
filter.Add(HelperClasses.NotificationsFields.FkUserId == userId);
//Notification category
if (category.HasValue)
filter.Add(HelperClasses.NotificationsFields.CategoryId == category.Value);
//Notification From Date
if (dateFrom.HasValue)
filter.Add(HelperClasses.NotificationsFields.CreateDate >= dateFrom.Value);
//Notification To Date
if (dateTo.HasValue)
filter.Add(HelperClasses.NotificationsFields.CreateDate <= dateTo.Value);
//Notification Unread only
if (unreadOnly.HasValue && unreadOnly.Value)
filter.Add(HelperClasses.NotificationsFields.Read == false);
//Notification read only
else if (readOnly.HasValue && readOnly.Value)
filter.Add(HelperClasses.NotificationsFields.Read == true);
//Notification undisplayed only
else if (undisplayedOnly.HasValue && undisplayedOnly.Value)
filter.Add(HelperClasses.NotificationsFields.DisplayDate == DBNull.Value);
//Notification contains words
if (!string.IsNullOrEmpty(containsWords))
filter.Add(HelperClasses.NotificationsFields.NotificationText % ("%" + containsWords.Trim() + "%"));
#endregion
//sort
ISortExpression sort = new SortExpression();
sort.Add(HelperClasses.NotificationsFields.CreateDate | SortOperator.Descending);
int resultNumber = 0;
if (ContainerType == NotificationRptType.Read)
resultNumber = 50;
notifications.GetMulti(filter, resultNumber, sort);
return notifications;
}
So because I do a SELECT then an UPDATE right away and we have many users navigating the app at the same time, could this raise deadlock? if so how can i avoid it?
Thank you