views:

98

answers:

0

I can't get the SqlCacheDependency to remain valid after the ExecuteReader() instruction. Please see comment in code.

using (SqlConnection connection = new SqlConnection(AppConfiguration.GetInstance.DefaultConnectionString()))
{
    using (SqlCommand command = new SqlCommand("ListLanguages", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@Page", SqlDbType.Int).Value = page;
        command.Parameters.Add("@ItemsPerPage", SqlDbType.Int).Value = itemsPerPage;
        command.Parameters.Add("@OrderBy", SqlDbType.NVarChar, 100).Value = orderBy;
        command.Parameters.Add("@OrderDirection", SqlDbType.NVarChar, 4).Value = orderDirection;
        command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

        SqlCacheDependency cacheDependency = new SqlCacheDependency(command);
        connection.Open();

        /* When debugging, I can see that right after executing the
           instruction command.ExecuteReader() the base property HasChanged
           of the SqlCacheDependencyObject becomes true although I did not
           change anything
        */
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    list.Add(new Language(reader));
                }
            }
        }

The following the stored proc code:

CREATE PROCEDURE [dbo].[ListLanguages]
@Page INT = 1,
@ItemsPerPage INT = 10,
@OrderBy NVARCHAR (100) = 'ID',
@OrderDirection NVARCHAR(4) = 'DESC',
@TotalRecords INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT @TotalRecords = COUNT(*) FROM dbo.Languages

EXEC('SELECT ID, Name, Flag, IsDefault FROM (
 SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' ' + @OrderDirection + ') as Row, ID, Name, Flag, IsDefault
 FROM dbo.Languages) results
 WHERE Row BETWEEN ((' + @Page + '-1)*' + @ItemsPerPage + '+1) AND (' + @Page + '*' + @ItemsPerPage + ')')
END

Can anyone please find what's with this code?

Thank you