Category

ORM

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

Insert data in database through Dapper ORM

By | Dapper, ORM | No Comments

You can insert tha data in database through Dapper ORM. Just use Execute Method, but before this you
have to importĀ Dapper namespace.

To use Dapper name space first you have to add reference of dapper from nuget if you are using visual studio.

to add reference of dapper in visual studio ->Tools->Nuget Package Manager->Package Manager Console

and than type below line in console to add dapper reference in visual studio.

Install-Package Dapper -Version 1.50.5

you can check versions of Dapper on Nuget site.

https://www.nuget.org/packages/Dapper/


using Dapper;

string sql = "INSERT INTO testTable (name,password) VALUES(@name,@password)";
var affectedrows = con.Execute(sql, new { name = "Demo", password = "123456" });
if (affectedrows > 0)
{
    return "Done";
}