I have an application which currently reads data from a table using the following stored procedure:
CREATE PROCEDURE [dbo].[GetBatchOfEmails]
@BatchSize INT
AS
BEGIN
SET NOCOUNT ON;
WITH ResultSet AS
(
SELECT TOP(@BatchSize) [To], [Subject], [MessageBodyText], [MessageBodyHtml],
[From], [ReplyTo], [DateCreated], [EmailId]
FROM SendMailQueue WITH (ROWLOCK, READPAST)
ORDER BY TableId
)
DELETE FROM ResultSet
OUTPUT deleted.[To], deleted.[Subject], deleted.MessageBodyHtml, deleted.MessageBodyText,
deleted.ReplyTo, deleted.[From], deleted.EmailId, deleted.DateCreated
END
As you can see, the stored procedure uses ROWLOCK and READPAST so that a number of rows (controlled by @BatchSize) are read safely by one connection only. After reading, the rows are deleted.
Can anyone point me towards how I might accomplish the same with NHibernate?