Select multiple count in one result set in sql and C#.

Select multiple count in one result set in sql and C#.

Here, you can select multiple counts from different – 2 tables in one result set. with multiple coulumn names.

i used insight databse ORM in C#.

First i created a Model in c# where result set will bind with Columns name and model property name.

public class AnalyticsModel
    {
        public int totalAppointments { get; set; }
        public int totalPrescriptionRenewal { get; set; }
        public int totalNumberOfDropInsPerDay { get; set; }
        public int totalRequest { get; set; }
    }

And Now My Store Procedure in SQL

CREATE PROC [dbo].[AnalyticsTotal]
AS
BEGIN 
	SET NOCOUNT ON;

	SELECT
	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,3) AND Status=1) AS [totalAppointments],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType=2 AND Status=1) AS [totalPrescriptionRenewal],

	(SELECT COUNT(*) FROM Appointment WHERE IsDropIn=1 AND Status=1) AS [totalNumberOfDropInsPerDay],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,2,3) AND Status=1) AS [totalRequest]

END

And in last i got my result set in my DataClass.

var Result = DB.QuerySql<AnalyticsModel>(@"Exec [dbo].[AnalyticsTotal]").FirstOrDefault();

Here QuerySql is the method of Insight Database ORM.

You can Install in your visual studio from nuget package manager.

Install-Package Insight.Database -Version 6.2.8