Using Dapper with DB2

I know the new hotness is development is always looking for Greenfield projects and building out your new applications using the new technology and building out your database using Code First with Entity Framework.

However, life in the development world isn’t all green fields and puppies and you’re finding yourself having to use legacy systems for one reason or another. I’ve found myself in that position in needing to interact with a legacy DB2 database and create a Web API in order to create some services that can produce and consume that data.

In the past we’ve used a powerful ORM tool like Entity Framework to do our CRUD operations to the DB2 database. We definitely had a lot of pain points in using EF, most importantly speed and performance being the culprit. It was so bad that there were times we ended up just resorting to ADO.NET. Yes, it was messier code and tougher to unit test, but it was also incredibly faster.

Due to this, on a Web API I was creating, I decided to do some research on other ORMs and came across the Micro ORM that Stack Overflow created called Dapper. Dapper promised speed close to ADO.NET while also providing extensions for your IDbConnection interface. Upon using this I was blown away by the speed and also how simple it was to set up.

Let me walk you through a quick and simple repository and in some future blogs I will show how I implemented the Web API portion using other nuget packages like Automapper and Fluentvalidation.

The first thing you will need to do is download and install the ODBC and CLI client DB2 drivers from IBM. More information on that here.

In your newly created .NET project you will then add a reference and browse to the location of IBM.Data.DB2.dll and add that as a reference. This is what will allow you to now use DB2Connection extensions. The DLL is most commonly found in this location if you did a non custom install for 64 bit or (x86) for 32 bit — C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin\netf40

The next thing you will want to do is add the Dapper reference using Nuget Package Manager. This is quite simple to do. Just go to your Package Manager Console and type in the following: Install-Package Dapper

This should quickly install Dapper as a reference to your project and you are ready to begin coding. Now I don’t want to get into dependency injection or unit testing with this example — but you should totally use it and fully unit test your code, those examples will be for another day.

The first thing I want to do is create my model. This is how my database table will look like that I want to do CRUD operations with. For example purposes I made a very simple EmployeeModel that has an id, First Name, Last Name, and City.

The EmployeeModel

The second thing I do is create a very simple Interface for my repository. I want to be able to Find by Id, Get all records, Retrieve, Add, Update, and Delete. I’m going to show Find by Id, Get All, Add, and Delete in this example. For Retrieve and Update I use something called Dapper.SqlBuilder and I’d like to discuss that alone in a future blog.

My Interface I will be using for EmployeeRepository

And finally I will create an EmployeeRepository class that will implement the interface I just created. I will also go ahead and set up my IDbConnection using DB2Connection and reference my connection string that I have in my web.config. This is where normally you would want to start using Dependency Injection so you can create mocks of your code for unit testing purposes — but for ease of setting up I will by pass that.

A blank repo implementing my interface

The first method we’ll do is GetAll, it’s really simple. All we want to do is run a Query using our EmployeeModel, return all records in a list. It’s a very simple SQL statement.

This will return a list of EmployeeModel from DB2

The second method we will implement is FindById. In this case we are passing in an integer and we want to return 1 record from our Employee table. You’ll notice the SQL in this case I am using @ID as it is very important that you parameter your SQL to defend against injection attacks. In this case it knows that id is what is being passed on, I’m using it as a parameter and using an anonymous object to assign my EmployeeModel Id to the variable id being passed in.

This will then return the record in the database where the Id equals what we passed in.

Always use parameters in your SQL!

The third method I want to show is delete. It’s also very simple and works much the same way we saw in the last 2 examples. In real life I hardly ever use delete as a method in my APIs and would likely want to return something if I did, like a message or something to that effect. However, for example sake I went with a very easy void method.

The main difference is we don’t have to do a query here, we can just straight up execute our SQL. Remember to use a parameter!

And for the final method I want to show today is our Add method. This one gets to be a little more complex because what I want to do is add the record, and then return the record I just created back.

Remember that in this case Id is an autonumber that is created by the database when a record is created. It’s a unique identifier that will not be passed in through the method. With SQL Server you can just use @SCOPE_IDENTITY to accomplish this but DB2 is a bit more rusty with how to do it.

Instead we want to select the Id from the FINAL TABLE of the INSERT statement we do. Once we get the id that was just created we can run a query on that table, add it back to the model we passed in, and return it back.

Seriously, good luck finding any other place on the internet that describes this, I tried. One of our DBAs was able to figure out a method for me that worked without making a second trip to the database.

NOTE: There is something in DB2 called “IDENTITY_VAL_LOCAL” but I’m not sure if that solely works with DB2 for Linux/Unix/Windows – I was unable to get it to work with Z/OS.

And that should do it! We still have Retrieve and Update to accomplish but those will be for my next blog as I explain how to use Dapper.SqlBuilder for those. After that I will show you how to wrap this all up in a Web API.

This is my first ever post I have made about programming, so if you liked it please let me know. It won’t be perfect so if you have any suggestions for improvements I would love to hear that as well. You can do so here or hit me up on Twitter.

Good luck and happy coding!