ASP.NET: Data access Layer - Retriving Output Param Value

 public GetAllPermissionResponse GetAllPermission(GetAllPermissionRequest request)
        {
            GetAllPermissionResponse response = new DataContract.GetAllPermissionResponse();

            try
            {

                Database db = DatabaseFactory.CreateDatabase(ConstantManager.CONNECTION_NAME);
                using (DbCommand dbCommand = db.GetStoredProcCommand(ConstantManager.SP_PERMISSION_GETALL))
                {
                    db.AddInParameter(dbCommand, "PageNumber", DbType.Int32, request.PageNumber);
                    db.AddInParameter(dbCommand, "Pagesize", DbType.Int32, request.Pagesize);
                    db.AddInParameter(dbCommand, "SortColumn", DbType.String, request.SortColumn);
                    db.AddInParameter(dbCommand, "SortOrder", DbType.String, request.SortOrder);
                    db.AddOutParameter(dbCommand, "RowCount", DbType.Int32, 5);
                  //  db.AddOutParameter(dbCommand, "PageCount", DbType.Int32, 5);

                    PermissionCollection collection = new DataContract.PermissionCollection();
                    PermissionList list = new DataContract.PermissionList();
                    using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                    {

                        while (dataReader.Read())
                        {
                            Permission obj = new DataContract.Permission();
                            if (dataReader["PermissionID"] != DBNull.Value) { obj.PermissionID = Convert.ToInt64(dataReader["PermissionID"]); }
                            if (dataReader["PermissionName"] != DBNull.Value) { obj.PermissionName = Convert.ToString(dataReader["PermissionName"]); }
                            if (dataReader["PermissionDescription"] != DBNull.Value) { obj.PermissionDescription = Convert.ToString(dataReader["PermissionDescription"]); }
                            if (dataReader["IsActive"] != DBNull.Value) { obj.IsActive = Convert.ToBoolean(dataReader["IsActive"]); }
                            if (dataReader["CreatedDate"] != DBNull.Value) { obj.CreatedDate = Convert.ToDateTime(dataReader["CreatedDate"]); }
                            if (dataReader["CreatedBy"] != DBNull.Value) { obj.CreatedBy = Convert.ToInt64(dataReader["CreatedBy"]); }
                            if (dataReader["UpdatedDate"] != DBNull.Value) { obj.UpdatedDate = Convert.ToDateTime(dataReader["UpdatedDate"]); }
                            if (dataReader["UpdatedBy"] != DBNull.Value) { obj.UpdatedBy = Convert.ToInt64(dataReader["UpdatedBy"]); }

                            collection.Add(obj);

                        }
                    }

                    var rowcount = Convert.ToInt32(db.GetParameterValue(dbCommand, "RowCount"));
                   // var pagecount = Convert.ToInt32(db.GetParameterValue(dbCommand, "PageCount"));
                    list.Rows = collection;
                    list.TotalRowCount = rowcount;
                   // list.NoOfPages = pagecount;
                    response.Response = list;
                }


            }
            catch (Exception exception)
            {
                ExceptionManager.HandleDataException(exception, ConstantManager.DATA_EXCEPTIONPOLICY);
            }
            return response;
        }

ASP.NET: Data access Layer using Enterprise Lib

        private const string Config = "ConnectionString";
        public bool Insert(Destination destination)
        {
            Database db = DatabaseFactory.CreateDatabase(Config);
            DbCommand dbCommand = db.GetStoredProcCommand("uspSaveDestination");
            db.AddInParameter(dbCommand, "DestinationID", DbType.Int64, destination.DestinationID);
            db.AddInParameter(dbCommand, "DestinationName", DbType.String, destination.DestinationName);
            db.AddInParameter(dbCommand, "Description", DbType.String, destination.Description);
            return (db.ExecuteNonQuery(dbCommand) == 1);
        }
        public bool Update(Destination destination)
        {
            Database db = DatabaseFactory.CreateDatabase(Config);
            DbCommand dbCommand = db.GetStoredProcCommand("uspSaveDestination");
            db.AddInParameter(dbCommand, "DestinationID", DbType.Int64, destination.DestinationID);
            db.AddInParameter(dbCommand, "DestinationName", DbType.String, destination.DestinationName);
            db.AddInParameter(dbCommand, "Description", DbType.String, destination.Description);
            return (db.ExecuteNonQuery(dbCommand) == 1);
        }
        public bool Delete(long DestinationID)
        {
            Database db = DatabaseFactory.CreateDatabase(Config);
            DbCommand dbCommand = db.GetStoredProcCommand("uspDeleteDestination");
            db.AddInParameter(dbCommand, "DestinationID", DbType.Int64, DestinationID);
            return (db.ExecuteNonQuery(dbCommand) == 1);
        }
        public IList<Destination> GetAll()
        {
            Database db = DatabaseFactory.CreateDatabase(Config);
            DbCommand dbCommand = db.GetStoredProcCommand("uspGetAllDestination");
            List<Destination> list = new List<Destination>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Destination obj = new Destination();
                    if (dataReader["DestinationID"] != DBNull.Value) { obj.DestinationID = (Int64)dataReader["DestinationID"]; }
                    if (dataReader["DestinationName"] != DBNull.Value) { obj.DestinationName = (String)dataReader["DestinationName"]; }
                    if (dataReader["Description"] != DBNull.Value) { obj.Description = (String)dataReader["Description"]; }
                    list.Add(obj);
                }
            }
            return list;
        }
        public Destination GetByID(long DestinationID)
        {
            Database db = DatabaseFactory.CreateDatabase(Config);
            DbCommand dbCommand = db.GetStoredProcCommand("uspGetDestinationByID");
            db.AddInParameter(dbCommand, "DestinationID", DbType.Int64, DestinationID);

            Destination obj = new Destination();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    if (dataReader["DestinationID"] != DBNull.Value) { obj.DestinationID = (Int64)dataReader["DestinationID"]; }
                    if (dataReader["DestinationName"] != DBNull.Value) { obj.DestinationName = (String)dataReader["DestinationName"]; }
                    if (dataReader["Description"] != DBNull.Value) { obj.Description = (String)dataReader["Description"]; }
                }
            }
            return obj;
        }

SQL SERVER : Dynamic Paging , Sorting

Example 1
GO
/******
-- Date                Name            Description
-- 04/27/2012        Vijay Mishra    Created



 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[SP_PERMISSION_SEARCH]
@PermissionID bigint=null,
@PermissionName nvarchar(500)=null,
@PermissionDescription nvarchar(max)=null,
@PageNumber int=0,
@Pagesize int=0,
@SortColumn nvarchar(50)=null,
@SortOrder nvarchar(10)=null,
@RowCount int Output

AS
BEGIN         
      
   DECLARE @FirstRecord int, @LastRecord int
   SELECT @FirstRecord = ((@PageNumber - 1) * @Pagesize) + 1
   SELECT @LastRecord = (@PageNumber * @Pagesize )
            
   -- Set the ROWCOUNT to the @FirstRecord from where data read starts
   SET ROWCOUNT @FirstRecord
   -- Get the first row from Table (based on above statement it would retrive only one row)
   DECLARE @FirstRow int
   SELECT @FirstRow = PermissionID FROM [PEGASUS_TBL_PERMISSION] ORDER BY PermissionID asc  
  
   -- Now, set the row count to @Pagesize(maximum rows) and get
    -- all records >= @FirstRow
   SET ROWCOUNT @Pagesize
   -- execute the required condition  
   select * from [PEGASUS_TBL_PERMISSION]
   WHERE (@PermissionName IS NULL OR PermissionName LIKE '%' + @PermissionName + '%')      
      AND (@PermissionDescription IS NULL OR PermissionDescription LIKE '%' + @PermissionDescription + '%')
      AND (@PermissionID IS NULL OR PermissionID = @PermissionID)
    and PermissionID >= @FirstRow
    order by
          CASE WHEN @SortColumn='PermissionID' AND @SortOrder='DESC' THEN PermissionID END DESC,
        CASE WHEN @SortColumn='PermissionID' AND @SortOrder='ASC' THEN PermissionID END ASC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='DESC' THEN PermissionName END DESC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='ASC' THEN PermissionName END ASC,     
        CASE WHEN @SortColumn='PermissionDescription' AND @SortOrder='DESC' THEN PermissionName END DESC,
        CASE WHEN @SortColumn='PermissionDescription' AND @SortOrder='ASC' THEN PermissionName END ASC     
    -- Set total resulted ROWCOUNT
    set @RowCount=@@ROWCOUNT  
    SET ROWCOUNT 0
   
     
END

Example 2


GO
/****** Object:  StoredProcedure [dbo].[SP_PERMISSION_SEARCH_VIJAY]    Script Date: 04/27/2012 15:09:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[PEGASUS_SP_PERMISSION_SEARCH_VIJAY1]
@PermissionID bigint=null,
@PermissionName nvarchar(500)=null,
@PermissionDescription nvarchar(max)=null,
@PageNumber int=0,
@Pagesize int=0,
@SortColumn nvarchar(50)=null,
@SortOrder nvarchar(10)=null,
@RowCount int Output

AS
BEGIN         
          --
           DECLARE @FirstRecord int, @LastRecord int
           SELECT @FirstRecord = ((@PageNumber - 1) * @Pagesize) + 1
           SELECT @LastRecord = (@PageNumber * @Pagesize )
           --Get Total rowcount          
           set @RowCount= (select COUNT(0) from [PEGASUS_TBL_PERMISSION])               
       -- Prepare Common Table               
      ;With PERMISSION AS(SELECT PermissionID,PermissionName,PermissionDescription, ROW_NUMBER() OVER
       (order by
          CASE WHEN @SortColumn='PermissionID' AND @SortOrder='DESC' THEN PermissionID END DESC,
        CASE WHEN @SortColumn='PermissionID' AND @SortOrder='ASC' THEN PermissionID END ASC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='DESC' THEN PermissionName END DESC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='ASC' THEN PermissionName END ASC     
       ) as RowNumber, ROW_NUMBER() OVER(ORDER BY PermissionID ASC) AS Total FROM [PEGASUS_TBL_PERMISSION]
      WHERE (@PermissionName IS NULL OR PermissionName LIKE '%' + @PermissionName + '%')      
      AND (@PermissionDescription IS NULL OR PermissionDescription LIKE '%' + @PermissionDescription + '%')
      AND (@PermissionID IS NULL OR PermissionID = @PermissionID)
      )
      , PermissionCount  as
      (
      SELECT COUNT(0) CNT FROM PERMISSION
     
      )       
   
   --   --Select required colums
      select PermissionID,PermissionName,PermissionDescription,Total+RowNumber -1 as Totalrows
      from PERMISSION
      Where RowNumber >=  case when @FirstRecord = 1 then RowNumber else @FirstRecord end and
            RowNumber <=   case when @LastRecord = 0 then  RowNumber else @LastRecord end
     
  
   
     
END