Retrieve Sql Out parameter in c#

Recently I got an requirement of developing a Stored procedure which searches some data in our database including paging also it should return the total number of records as well. Following is the solution I came up.

1. Create a SP which returns the results set and have an output parameter in it which returns the total records count.

when deciding how to get total records from the SP which has paging as well , came across with following solutions.

• Use COUNT(*) OVER in the select statement which will add a new column to your results set which has the total number of records as the value for each record.
• Create a temp table with the select statement without paging, return the results set applying paging to that results set, and out the Total count taking the total count in the temp table
• Use an out parameter for total ,rather creating a temp table run two separate queries and get the result set and count.

after reading few articles found out 3rd option is best as it has best performance. Check the following link for More Information.

So following is the stored procedure I came up with

1. GO
2.  
3. SET ANSI_NULLS ON
4. GO
5. SET QUOTED_IDENTIFIER ON
6. GO
7.  
8. IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('SP_SearchWorkOrders') AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
9.     DROP PROCEDURE SP_SearchWorkOrders
10. GO
11.  
12. CREATE PROCEDURE [dbo].[SP_SearchWorkOrders]
13. (
14. @FromRecord int = null,
15. @RecordCount int = null,
16. @TotalRecordsCount int out
17. )
18. AS
19. BEGIN
20.  
21. SELECT @TotalRecordsCount= COUNT(*)
22. FROM [WorkOrders]
23.   WHERE[WorkOrders].Archived = 0
24.  
25.  
26.  
27. SELECT [WorkOrders].Id AS [ID]
28. ,[WorkOrders].[DateLastModified] AS [Timestamp]
29. ,[WorkOrders].[WorkOrderNo] as [WorkOrderNo]
30. FROM [WorkOrders]
31.   WHERE[WorkOrders].Archived = 0
32. ORDER BY [WorkOrders].[CreatedOn] DESC
33. OFFSET IsNull(@FromRecord, 0) ROWS
34. FETCH NEXT IsNull(@RecordCount, 100000000) ROWS ONLY
35.  
36. END
37.  
38. GO

2. Create a c# method which returns the results set and the total count.

1. public IList<WorkOrder> Search(SearchQuery searchQuery, out int totalRecordsCount)
2.         {
3.             Database database = GetDatabase();
4.             IList<WorkOrder> valueObjectList = new List<WorkOrder>();
5.             WorkOrderSearchQuery workOrderSearchQuery = searchQuery as WorkOrderSearchQuery;
6.             using (DbCommand command = database.GetStoredProcCommand("SP_SearchWorkOrders"))
7.             {
8.                 database = AddSearchParameters(searchQuery, database, command, workOrderSearchQuery);
9.                 //set the out parameter for the command                
10.                 database.AddOutParameter(command, "TotalRecordsCount", DbType.Int32, 4);
11.                 using (IDataReader reader = database.ExecuteReader(command))
12.                 {
13.                     while (reader.Read())
14.                     {
15.                         valueObjectList.Add(MapWorkOrder(reader));
16.                     }
17.                     // must close the reader to get the out put parameter                     
18.                     reader.Close(); //read the out parameter value                    
19.                     totalRecordsCount = (int) command.Parameters["@TotalRecordsCount"].Value;
20.                 }
21.             }
22.             return valueObjectList;
23.         }

Hope this helps.

Happy Coding !!!!