Sometimes we need to communicate with a NAV Database, and generally the legacy approach I have seen is to call Stored Procedures, or in very rare cases, utilize ADO.NET within your application.
While the above technologies work fine, there is a lot of manual work between mapping different entities and fields. Recently, I attempted to utilize an Object Relational Mapper to help with this.
You will quickly find that there are lots of libraries to help you with this, the most popular being the Entity Framework. However, For my purposes, I did not require such a power library. I was looking for something lightweight, that specifically did the job of Mapping.
Introducing Dapper - A super fast library for mapping between databases and .NET objects. In this post, I am going to show how we can utilize the benefits of this library should you ever need a good mapping utility. In my examples, I am connecting to a Dynamics NAV Database, with CRONUS data.
Lets start with the Datalayer. Open Visual Studio, and create a Service Library. I am using .NET Core 2.0 but you can use whatever flavor of .NET you prefer.
Data Layer
- Add Dapper, and Dapper.Contrib as a Dependency via Nuget
2. Lets create a Model. In this case, Lets say we want Customer details.
3. We will be creating a Repository Pattern for our Data Access Layer. Lets create a Repository for Customer.
What about the DB Connection? We will add the following code to use dependency injection via constructor -
Now to write the query that will return Customers using Dapper -
As you can see, when using a micro ORM such as Dapper, we have control over the SQL, without the tedious task of mapping.
And that is it. All you need now is to call your data layer from your application, and pass in the connection string.
Also, I will be posting great shots of Fiji Islands in this blog as a way to showcase Fiji to our international readers :)
Post Picture: Maui Bay, Fiji Islands. Shot by: VijeshDattPhotography