Paginate database records using stored procedure

GO
/****** Object:  StoredProcedure [dbo].[GetBlogs]    Script Date: 05-11-2018 01:50:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[GetBlogs]

@count int,
@start int,
@categoryId int,
@blogCount int Output
as
begin
	Declare @LastBlog int
	Set @count = 9
	Set @lastBlog = @count + @start;

	create table #x (id int identity(1,1),blogid int)

	if @categoryId is null
		begin
			insert into #x
			select Id from Blog where IsDeleted=0 and StatusId = 2

		end
	else
		begin
			insert into #x	
			select b.Id from Blog b inner join BlogCategories bc on b.Id = bc.BlogId where bc.CategoryId = @categoryId and b.IsDeleted=0 and b.StatusId = 2;
		end
	
	select @blogCount = COUNT(#x.id) from #x 

	select b.* from #x inner join Blog b on #x.blogid = b.Id where #x.id > @start and #x.id <= @LastBlog

	drop table #x

end

Select allOpen in new window

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Project.Utility
{
   public class DAL
    {
        #region Variables

        public static bool? IsStoredProc = null;
        public static int CommandTimeoutSeconds = 240;
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["BlogDataModel"].ConnectionString;

        #endregion Variables

        #region Port Methods

        protected static DbConnection CmnDbConnection(string connStr)
        {
            return new SqlConnection(connStr);
        }

        protected static DbCommand CmnDbCommand(string query, DbConnection connection)
        {
            
                return new SqlCommand(query, connection as SqlConnection);
        }

        public static DbDataAdapter CmnDbDataAdapter(DbCommand command)
        {
            return new SqlDataAdapter(command as SqlCommand);
        }

        public static DbParameter CmnDbParameter(string name, object value)
        {
            return new SqlParameter(name, value);
        }

        #endregion Port Methods

        #region Methods
        public static DataTable Read(string query, DbParameter[] paramList, bool? isStoredProc, int? timeout)
        {
            DataTable dt = new DataTable();
            using (DbConnection connection = CmnDbConnection(ConnectionString))
            {
                try
                {
                    DbCommand command = CmnDbCommand(query, connection);
                    if (isStoredProc != null)
                    {
                        command.CommandType = isStoredProc.Value ? CommandType.StoredProcedure : CommandType.Text;
                    }
                    if (isStoredProc != null && isStoredProc == true)
                    {
                        if(paramList!=null)
                        {
                            command.Parameters.AddRange(paramList);
                        }
                    }

                    command.CommandTimeout = timeout ?? CommandTimeoutSeconds;

                    DbDataAdapter adapter = CmnDbDataAdapter(command);
                    adapter.SelectCommand.Connection.Open();
                    adapter.Fill(dt);
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
            return dt;
        }

        public static int Insert(string query, int? timeout)
        {
            int returnValue = 0;
            using (DbConnection connection = CmnDbConnection(ConnectionString))
            {
                try
                {
                    DbCommand command = CmnDbCommand(query, connection);
                    command.CommandTimeout = timeout ?? CommandTimeoutSeconds;

                    DbDataAdapter adapter = CmnDbDataAdapter(command);
                    adapter.InsertCommand = command;
                    adapter.InsertCommand.Connection.Open();
                    adapter.InsertCommand.ExecuteNonQuery();
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
            return returnValue;
        }

        #endregion

    }
}

Select allOpen in new window