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!


sony xbr 65x930ecgvaacer h277husmipuz reviewcheapest efax servicenasdaq aeisimessage read receiptelbit systems stock pricenyse brcroomba 980 black fridaytktdy stockgeekseekinwk stockggal stockdmp ub400dsgtulteoslw premarketdysl stocklogitech c920 frame rateacer h277husmipuz reviewaieq holdingspippin atmarkqmcqfvol index thinkorswimbenro goplus travel fgp18aerddbdcl stockafdah applenovo l412 specificationsipad model md512ll ametro pcs iphone 5selg oledb6pnasdaq axassony xbr75x940dspoosdishonored 2 bloodflieswhlmhow to activate anc on beats studiogddy stock pricefactorio radaruvxy tickerdell latitude e5470 xctocreative sound blaster recon3d thxnyse tefcostco ihome speakerepix healthcarebzun newsnyse lazquidd keyboardepzm stockhbcyfereader with backlightlg sj9 soundbar reviewfizzics amazonnasdaq twouslidenjoy alternativehow to turn off superfetchnewszillacefl stockacer aspire e 15 e5 575g 57d4 reviewdtfmyamaha rx v3900gthxclif high half past humanokta market capptshverastem stockgekko trading botvlkay stock price todayaphria stock price todaynyse auydgdm stocknyse ryblenovo x1 carbon keyboard shortcutsmg7720 reviewrlgt stocksrlp stocksoundfreaq bluetooth reviewfesa4hp m252dw reviewsndx stockvizio smartcast 38 2.1 sound bar systemhpinstantink ca my plancablecard spectrumbhggdtfaucfc stock pricevutalk appsir lancelot's armorasus chromebook flip c100pa db02wgbs stock pricepowerbeats wireless manualmabtc paymentplantronics rig 400hspsdv stock pricehow to turn off gear iconxerddjsd instructurenasdaq qlysdajin resources corpasus vp278svxy after hoursdvdstyler reviewosvr leap motioncbriq stockbeoplay h4 reviewmarshall kilburn bluetooth speaker reviewhcg tsxsony xb950n1 reviewplexurenovogen stockzettabyte hard drivethrustmaster 458 spider xbox one reviewwallstreetbetsopiant pharmaceuticals stock pricetrdramlp stock pricemediadex professionalamfe stockpromethease vs 23andmeweathernation castevgn stocktcl 55s405 reviewbotz etfepgl stockiolo system mechanic 15.5astell & kern kann reviewcrbp stock price todayamlp holdingscrrvf stockhltoy stockrxdx stockjtpypsdv stock pricedell precision m6400 covet reviewtcl 55c807slingbox curryspro ject 8.6 tonearmgdx stock pricemlfb stock pricepftvicpt stock price todaynasdaq pphmbcba average salarysage therapeutics stock pricehrzn stock pricewldn stock priceesdi stockapd stock price todayprph stockiwm tickerwell tempered versalexstaples logitech mx masterlg sh7b reviewhtgm stock priceeve windowed modecyrxwyamaha yas 203 best buyston stock priceexport slideshow from iphotohpc309gunlockedcells com reviewslenovo t440p specsnasdaq rrrwintv hvr 900dyson am08 reviewwinmail dat viewer macwhere was chappie filmedgame wizard retropiegatorade chews reviewlunmf stockb&w cm5 specsnasdaq eglevrme stockjnug vs nugtfuddingalienware mx18 priceavast safezone browser reviewdirec5 tvlmfx loginmsbi stockhow accurate are ancestry com dna testsnyse hdbnyse weccommscope stock pricesanm stock pricemirn stockfireftp ubuntutck stock pricenwgffsamsung sleepsensetmhczx770bt bluetooth headphonesnugt share priceequitas holdings share pricekef t205ndsn stock pricerigdhp envy 5540 setupskan imagingarth stock priceableton live powerpcinfrascale partnersflpc stocktxmd stock price todaynetgear router r6400 firmwareacer et322qk reviewesz7seeso apple tvcujo smart internet security firewallaur5 9714slvadata su800 reviewks8500 reviewpokemon pikablusony zx220bt reviewvcit stocklisk price predictionnas540foxfgbabntip stockdrake's fortune treasuressmart tab st1009xapptimemachine storedsgt stocklpsydell ultrasharp u2715h reviewslygalankit share priceades stock pricerxw 12 subwooferfoxclocksmfrm stocksony xbr x850eiomega storcenter ix2 200 specshow to have a custom gamerpic on xbox onevizio smartcast soundbar reviewbest usbc hubaqxpnasdaq blcmskylanders battlecast mobile gamevorpx vivestaples logitech mx masternyse mtzgoodee projectoraoc u2879vf reviewglpdopgn stockswbf2 release datefsgbthld stock newsjaybird pairing modevnqi stockapti stock priceveeqodigibyte segwitssrm stock pricekidzeaxs tv rokuosbuddy for macasus rt n66nintendo switch restock gamestophysr stock pricerepcfmmlp stock pricepvct otcipod classic 160gb review 7th generationlogitech k360 reviewmric stockambit 2s battery lifebsbr stockarkham origins backwards compatiblelg 55uj6540 tvclbs stockgthp stockcocp stock priceroku stick overheatingdnvbstvvynasdaq umpqnikon 24 megapixel dslr camera d3200 price in indianh l9i tdpabev stockibb stock price todaybharatfin share priceipvanish dd wrtacer cb281hkstreamytvnasdaq splkeurn stockensv stock priceacer chromebook n15q9skyzip vpndashlane opera pluginlinn akurate 242 pricegnmxraghee hornercrackdown 2 backwards compatibilitycredit card autofill safariacer cb5 132t c1lksamsung hw j250 soundbar reviewdgaz stockthld stock newsrotolight aeosameren stock pricewrt1900acs openwrt1and1 mail setupkef t205bte nysegwph stock newsactinium pharmaceuticals stocknvfyoih holdingscpxx stockcelh stock price55c7pbusybox smart installmystoracse2ldos stock priceat lp3bk reviewshimo vpnunder armour healthbox reviewhp pavilion 15 au158nrhceicanon mg2920 scanneralienware alpha gamestoptyrant season 3 huluxbr65x850d reviewwindows cannot setup a homegroup on this computer windows 10colgate tickerzbio stocktigervnc ubuntujbhtbt retentionsturtle beach x01nesbox emulatorlumie bodyclock iriskellogg ticker symbolchiclet mechanical keyboardnasdaq fogomotioneye raspberry pisamsung j3 flashlightehost website builderasus vg248 monitordnkn stock priceqqqqaaxpack hard drivetouchpad t650orthopediatrics corproku express 5ghzmg248tvia stock price