Category

Insight Database

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

By | Insight Database | No Comments

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]. 
                                                            [tbl_USER]
                                                           (Name
                                                         ,StreetAddress
                                                           ,City
                                                           ,State
                                                           ,Zip
                                                           ,PhoneNo)
                                                         VALUES
                                                           (@Name 
                                                        ,@StreetAddress
                                                           ,@City
                                                           ,@State
                                                           ,@Zip 
                                                           ,@PhoneNo 
                                 ) SELECT @@IDENTITY", parameters);

it will return the inserted id in InsertedId variable.

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

By | C#, Insight Database, ORM, SQL | No Comments

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