UserDequeueColumns

Relational Database additional dequeue columns

The transports that use relational databases support adding and using additional columns as part of the de-queue statement.

To enable - when creating the queue

 createQueue.Options.AdditionalColumnsOnMetaData = true;

The above will cause any user created columns to be placed on the metadata data used for de-queueing items

Any columns added as part of the creating will be created on the metadata table, instead of the status table.

 createQueue.Options.AdditionalColumns.Add(new Column("DayOfWeek", ColumnTypes.Int, true, null));

When adding items to the queue via the producer, pass in a value for your column(s); these values will be added the queue record.

private static IAdditionalMessageData CreateData()
{
	var data = new AdditionalMessageData();
    data.AdditionalMetaData.Add(new AdditionalMetaData<int>("DayOfWeek", Convert.ToInt32(DateTime.Today.DayOfWeek)));
    return data;
}

When creating the consumer, you can specify static where/params or use a factory to set dynamic where/params as part of the de-queue

var dayofWeek = int.Parse(ConfigurationManager.AppSettings.ReadSetting("UserDayOfWeek"));
log.Information($"Only processing items created on {((DayOfWeek)dayofWeek).ToString()}");
queue.Configuration.SetUserParametersAndClause(() => Parameters(dayofWeek), WhereClause);

private static string WhereClause()
{
	return "(DayOfWeek = @DayOfWeek)";
}

private static List<SqlParameter> Parameters(int dayOfWeek)
{
	var list = new List<SqlParameter>();
	var userParam = new SqlParameter("@DayOfWeek", SqlDbType.Int)
	{
		Value = dayOfWeek
	};
	list.Add(userParam);
	return list;
}

This allows your consumers to only de-queue items based on criteria that the queue does not directly support. You could also disable the consumer by simply returning (1=0) in the where clause; for instance, if you wanted to control processing to be between particular hours, but wanted to keep your service always running.