Awaiting Tasks Triggered by SQL Notification

Question

Hello everyone and thanks for the help in advance. I am writing an application that monitors a SQL table for new entries, then processes the new records based on a Processed = "Y" or "N" flag. Here is my code:

private async void Form1_Load(object sender, EventArgs e) 
{
	DBNotification notification = new DBNotification();
	notification.StartNotification();
}
//         public class DBNotification 
{
	//  Beging Start Notifications           
	public void StartNotification() 
	{
		//usual SQL notification code
	}
	private async void SqlDependencyOnChange(object sender, SqlNotificationEventArgs eventArgs) 
	{
		ProcessReferrals();
	}
	private async void ProcessReferrals() 
	{
		//Select a list of referrals where Procssed = "N", process referral, then update the Processed flag to "Y"}
	}

I need the application to complete the ProcessReferrals routine before requerying the database for unprocessed referrals. The problem is when I update the processed flag, a new notification fires, thus duplicating the processing of each referral. Any help would be appreciated.

Answer 1

Hi @Kmcnet ，Welcome to Microsoft Q&A,

To ensure that the ProcessReferrals routine is completed before requerying the database for unprocessed referrals, you can use a simple locking mechanism to prevent multiple concurrent executions of the ProcessReferrals method. You can achieve this by introducing a boolean flag to indicate whether the method is currently being executed. Here's how you can modify your code:

In this code, we use the isProcessing boolean flag to prevent re-entry into the ProcessReferrals method while it is already being executed. When a notification event is received, we check if isProcessing is false before executing the processing logic. If it's true, we skip processing to avoid duplicate work. Once processing is complete, we set isProcessing back to false.

private bool isProcessing = false;

private async void SqlDependencyOnChange(object sender, SqlNotificationEventArgs eventArgs) 
{
    if (!isProcessing)
    {
        isProcessing = true;
        await ProcessReferrals();
        isProcessing = false;
    }
}

private async Task ProcessReferrals() 
{
    // Lock the processing to prevent concurrent executions
    if (isProcessing)
    {
        return;
    }
    try
    {
        isProcessing = true;

        // Select a list of referrals where Processed = "N", process referral, then update the Processed flag to "Y"
        // Your database processing logic here
        // After processing, update the Processed flag to "Y"
        // Your update logic here
    }
    finally
    {
        isProcessing = false;
    }
}

This should ensure that your application completes the ProcessReferrals routine before processing another notification event, preventing the duplication of processing for each referral.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.