Use NPoco ORM With ASP.NET Core

NPoco is a simple C# micro-ORM that maps the results of a query onto a POCO object. NPoco is a fork of PetaPoco with a handful of extra features. And NPoco ORM can be used with .NET Core. So in this post, let’s find out how to use NPoco ORM with ASP.NET Core for CRUD operations.

Use NPoco ORM With ASP.NET Core

In this post, we will be using SQL Server as a database with NPoco ORM. So let’s first create the database. Open SQL Server Management Studio and create a new database called “NPocoDemo” or you can name it anything of your choice. And create a table named “Products”.

CREATE TABLE [dbo].[Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Quantity] [int] NULL,
	[Price] [float] NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Okay. Let’s create an ASP.NET Core Web API project. The default Web API template comes with a controller named “Value”. We don’t need this for demo, so I deleted it. To use NPoco ORM, we need to install following nuget package.

• “NPoco”: “3.4.7”

To test our WEB API, also add Swagger. If you don’t know about using Swagger then read how to add swagger to the ASP.NET Core WEB API

So your project.json should look like this, (BTW if you are not knowing then, it’s time to say Bye-Bye Project.json and .xproj and welcome back .csproj

"dependencies": {
  "Microsoft.NETCore.App": {
    "version": "1.0.0",
    "type": "platform"
  },
  "Microsoft.ApplicationInsights.AspNetCore": "1.0.0",
  "Microsoft.AspNetCore.Mvc": "1.0.0",
  "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
  "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
  "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
  "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
  "Microsoft.Extensions.Configuration.Json": "1.0.0",
  "Microsoft.Extensions.Logging": "1.0.0",
  "Microsoft.Extensions.Logging.Console": "1.0.0",
  "Microsoft.Extensions.Logging.Debug": "1.0.0",
  "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
  "Swashbuckle": "6.0.0-beta902",
  "NPoco": "3.4.7"
},

Now, we need to add Product entity model. So create a folder named “Model” and create a new class “Product.cs” as follows,

[TableName("Products")]
[PrimaryKey("ProductId")]
public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int Quantity { get; set; }
    public double Price { get; set; }
}

NPoco works by mapping the column names to the property names on the entity object. This is a case-insensitive match. By default, no mapping is required. It will be assumed that the table name will be the class name and the primary key will be ‘Id’ if it’s not specified. But when your table name is different from entity name, then you need to use TableName attribute which indicates the table for which the Poco class will be mapped to. Similarly, if primary key column name is other than ‘Id’ then use PrimaryKey attribute to let NPoco know that this is the primary key column for this entity (As decorated in above code).

You can find more details on list of all supported attributes here.

Setting up NPoco is very easy process and similarly using it is also very simple and straightforward. You need to follow 3 simple steps to use it.

• Create an IDatabase object with a connection string, Database Type and DBProvider factory.
• IDatabase provides methods for insert, update, delete and GetById. You can also execute Raw SQL queries.
• Pass the entity object or query to NPoco IDatabase and it’s done.

So let’s create ProductRepository for all database calls. Create a folder named “Repository” and create a new class “ProductRepository.cs” as follows,

public class ProductRepository
{
    private string connectionString;
    public ProductRepository()
    {
        connectionString = @"Server=localhost;Database=NPocoDemo;Trusted_Connection=true;";
    }

    public IDatabase Connection
    {
        get
        {
            return new Database(connectionString, DatabaseType.SqlServer2008, SqlClientFactory.Instance);
        }
    }

    public void Add(Product prod)
    {

        using (IDatabase db = Connection)
        {
            db.Insert<Product>(prod);
        }
    }

    public IEnumerable<Product> GetAll()
    {
        using (IDatabase db = Connection)
        {
            return db.Fetch<Product>("SELECT * FROM Products");
        }
    }

    public Product GetByID(int id)
    {
        using (IDatabase db = Connection)
        {
            return db.SingleById<Product>(id);
        }
    }

    public void Delete(int id)
    {
        using (IDatabase db = Connection)
        {
            db.Delete<Product>(id);
        }
    }

    public void Update(Product prod)
    {
        using (IDatabase db = Connection)
        {
            db.Update(prod);
        }
    }
}

To create NPoco Database class object, you need to either pass a connection string or DBConnection class object. Here, the connection string is passed to create the object.

return new Database(connectionString, DatabaseType.SqlServer2008, SqlClientFactory.Instance);

You can also create SqlConnection class object which is inherited from DBConnection class and pass the same. Like,

SqlConnection con = new SqlConnection(connectionString);
return new Database(con);

The only thing is to keep in mind when initializing using DBConnection object, that you will need to open the connection before any DB operation. And while initializing using the connection string, Database class will take care of opening the connection. Please take a look at Database.cs class code in Github and look for _connectionPassedIn property use.

There are CRUD methods defined that uses entity object for all DB operations. As mentioned earlier, in every method

• Create IDatabase object.
• Pass the object/Query and call the method.

As you can see, there is also a method SingleById to get single record from a table and Fetch method to get all records using a RAW SQL query.

Now, lets create a new controller “ProductController.cs” as follows,

[Route("api/[controller]")]
public class ProductController : Controller
{
    private readonly ProductRepository productRepository;
    public ProductController()
    {
        productRepository = new ProductRepository();
    }
    // GET: api/values
    [HttpGet]
    public IEnumerable<Product> Get()
    {
        return productRepository.GetAll();
    }

    // GET api/values/5
    [HttpGet("{id}")]
    public Product Get(int id)
    {
        return productRepository.GetByID(id);
    }

    // POST api/values
    [HttpPost]
    public void Post([FromBody]Product prod)
    {
        if (ModelState.IsValid)
            productRepository.Add(prod);
    }

    // PUT api/values/5
    [HttpPut("{id}")]
    public void Put(int id, [FromBody]Product prod)
    {
        prod.ProductId = id;
        if (ModelState.IsValid)
            productRepository.Update(prod);
    }

    // DELETE api/values/5
    [HttpDelete("{id}")]
    public void Delete(int id)
    {
        productRepository.Delete(id);
    }
}

This controller has methods for GET, POST, PUT and DELETE. That’s all to code. Now, let’s just run the application and execute the GET API. Since the table is empty, so you should see following.

Now, let’s add a product via Post API.

And now call the GET Product API again and you should see that product you just added is returned.

Here is the video showing all Product API operations.

That’s it. It’s really very easy to setup and use NPoco. Along with executing SQL queries, you can also use inbuit methods for CRUD operations. And the good thing is that it maps the results to Poco objects. It also supports transaction supports, mapping of nested objects, change tracking, Fluent based mapping and many other features. It’s tiny but really powerful.

If you are interested in Entity Framework Core, then read my series of posts about EF Core and loves Dapper.NET then read Use Dapper.NET With ASP.NET Core.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

PS: If you found this content valuable and want to return the favour, then