Category Insight Database

Insert into SQL database with return inserted id through Insight Database ORM

First of all you should add reference of insight datatabse from nuget package manager. command bellow :

Install-Package Insight.Database -Version 6.2.8

After that just include namespace of insight database .

using Insight.Database;

Query :

these are the paramaeters which we will pass in insert query values

               var parameters = new
                            Name= model.Name,
                            StreetAddress = model.StreetAddress,
                            City = model.City,
                            State = model.State,
                            Zip = model.Zip,
                            PhoneNo = model.PhoneNo,
decimal InsertedId = DB.ExecuteScalarSql<decimal>(@"INSERT INTO [dbo]. 
                                 ) SELECT @@IDENTITY", parameters);

it will return the inserted id in InsertedId variable.

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]

	(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]


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