Dapper Query With CRUD in Asp.net Core 1.0 - An artist's personal website

First install Visual studio 2015.
Create a New .NET core project
In Option->Nuget Package manager
Add New PackageSource
https://www.myget.org/F/aspnet-contrib/api/v3/index.json
Create a Table

CREATE TABLE [dbo].[Emp](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Emp](

[Id] [bigint] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) NULL,

CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

in project.json

add dependency

    "Dapper": "1.50.2"

1	"Dapper": "1.50.2"

and Here is the CRUD in Dapper

public class Emp
    {
        public long Id { get; set; }
        public String Name { get; set; }
    }


            String connectionString = @"Server=.\SQLEXPRESS;Database=Test;Persist Security Info=True;User ID=sa;Password=abc";
            var conn = new SqlConnection(connectionString);

            using (IDbConnection dbConnection = conn)
            {
                dbConnection.Open();

                ////Create
                Emp emp = new Emp
                {
                    Name = "akash"
                };

                
                string insertQuery = @"INSERT INTO Emp (Name) VALUES(@Name)";
                int i= dbConnection.Execute(insertQuery, emp);

                ////Read
                var epmList = dbConnection.Query<Emp>("SELECT * FROM Emp").ToList();

                ////Update
                //#Custom Data
                string updateQuery = @"UPDATE Emp SET Name = @Name WHERE Id = @Id";
                dbConnection.Query(updateQuery, new { Name = "UpdatedName", Id = 1 });

                //#Sepecific Object
                Emp updatedEmp = new Emp
                {
                    Id = 4,
                    Name = "xyz"
                };
                dbConnection.Query(updateQuery, updatedEmp);
                var firstemp = dbConnection.Query<Emp>("SELECT * FROM Emp ").ToList();

                ////Delete
                string deleteQuery = @"DELETE FROM Emp WHERE Id = @Id";
                dbConnection.Execute(deleteQuery, new { Id = 1 });

                dbConnection.Execute(deleteQuery, new { Id = updatedEmp.Id });

                var newEpmList = dbConnection.Query<Emp>("SELECT * FROM Emp").ToList();

            }

public class Emp

{

public long Id { get; set; }

public String Name { get; set; }

}

String connectionString = @"Server=.\SQLEXPRESS;Database=Test;Persist Security Info=True;User ID=sa;Password=abc";

var conn = new SqlConnection(connectionString);

using (IDbConnection dbConnection = conn)

{

dbConnection.Open();

////Create

Emp emp = new Emp

{

Name = "akash"

};

string insertQuery = @"INSERT INTO Emp (Name) VALUES(@Name)";

int i= dbConnection.Execute(insertQuery, emp);

////Read

var epmList = dbConnection.Query<Emp>("SELECT * FROM Emp").ToList();

////Update

//#Custom Data

string updateQuery = @"UPDATE Emp SET Name = @Name WHERE Id = @Id";

dbConnection.Query(updateQuery, new { Name = "UpdatedName", Id = 1 });

//#Sepecific Object

Emp updatedEmp = new Emp

{

Id = 4,

Name = "xyz"

};

dbConnection.Query(updateQuery, updatedEmp);

var firstemp = dbConnection.Query<Emp>("SELECT * FROM Emp ").ToList();

////Delete

string deleteQuery = @"DELETE FROM Emp WHERE Id = @Id";

dbConnection.Execute(deleteQuery, new { Id = 1 });

dbConnection.Execute(deleteQuery, new { Id = updatedEmp.Id });

var newEpmList = dbConnection.Query<Emp>("SELECT * FROM Emp").ToList();

}

#references

Use Dapper ORM With ASP.NET Core

Leave a Reply Cancel reply