Getting Started with Dapper in .NET

[@I-PixALbI4-I]

There is should be no debate about "do I need to learn SQL?"... You HAVE TO!

[@josephizang6187]

I use EFCore. But I think a Dapper deep dive would be great. Dometrain rocks btw

[@maacpiash]

When I first heard of Dapper and wanted to try it in a web API project, I found out to my surprise that there is no documentation on how to set up a project with Dapper for data access - not even in the official documentation. Thank you, Nick Chapsas. This video was really necessary for the .NET community.

[@waltavista]

Thank you very much for this video. I always wanted to use Dapper. By now I am using DevExpress XPO. It uses UnitOfWork, Collections and Objects and no need to write SQL. Maybe little bit slower than Dapper..

[@BehroozTahanzadeh]

Thank you so much. Would be great if you can do a deep dive into db connection pooling. 🙏

[@ml_serenity]

EF Core's RawSql querying made it unnecessary to use micro-ORMs such as Dapper.

[@sulmarpl]

try get complex data - for example with embedded address ;-)

[@fabiovalt2795]

I personally love the philosophy to have Dapper for queries and EF Core for commands (mostly because i hate writing additional DTOs to save aggregates when EF does it for me), but i still think that if high performance is a requirement Dapper + Stored Procedures offers a great deal between SQL maintainability and .Net... Anyways a deep dive on how to handle aggregates / complex relationships would still be awesome!

[@vamvdotnet]

Yes. A deep-dive would be more than welcome, Nick :)

[@a_solonovich]

Certainly a hands on! And I wouldn't trust a teammate who doesn't know SQL. Thanks for a great video

[@DavidChavez-k9v]

How do you usually maintain your queries as your models evolve? And how do you test your queries (so you're sure they aren't broken)?

[@kevinlloyd9507]

Interesting. When using Dapper, I'd typically use a closed connection. Since it's clever about opening and closing connections (that it has opened). In rare instances (multi command transactions stuff) I'd open the connection before hand. Curious as to your thoughts Nick.

[@saymehname]

Thank you for sharing this Nick

[@keletsoa.botsalano4531]

Hi Nick, excellent content as usual. I have one suggestion - instead of getting all records, why don't you show your subscribers how to do pagination using Dapper? For example, the api can take in pageNumber, pageSize and order (ASC, DESC) as the input.

[@DevelTime]

Many thank for sharing. Despite I basically hate EF Core 🙂I use it anyway. I guess benefits outweight the problems. Deep dive into EF Core and/or Dapper would be great, SQL does not go anywhere so it is always good to know more 🙂

[@Thomas-dp8eb]

Am I the only one who read "Rapper" in the thumbnail, because the D is covered. Rapper Nick, that would be a great video

[@stevepercival4663]

I prefer dapper just because I know what the SQL is doing. It would be good in you follow ups if you go over the various ways of dealing with a parent child relationships(order/order items).

[@gileee]

I mean efcore is pretty clear too considering it just takes your lambda functions as expressions and just does a transcription into sql. Not perfect control of sql (like it likes to add an automatic "order by Id" which breaks uuid ids somewhat), but good enough for the vast majority of cases. It also supports RawQuery.

[@pere5854]

Nice! Would like a deep dive or have access to some dapper best practice in your course material. One thing that was mentioned in the video - "remember to dispose your connections" - please show this. An additional video where CancellationToken is passed onto the Dapper methods would be nice. An video showing join, splitOn etc. would be helpful for people, I believe.

[@Robert-yw5ms]

He does show the disposal but it's easy to miss if you don't know the syntax. He always does using var connection = await connectionFactory.CreateConnectionAsync(); That using keyword causes the connection to be disposed whenever it goes out of scope.

[@vborovikov]

just use DbDataSource class, let the framework to manage the connection pool

[@guilhawarz7291]

It's crazy how Rider is miles ahead from vs 2022. I need to start using it all asap

[@ИльяДзюбин-й1л]

Also there is AOT extension for Dapper - DapperAOT. In this case Dapper can AOT, but EF Core now don't

[@urfriendstefan]

I love dapper . But usually i pass the class objects in stored procedure and make it work . Inline queries require redeployment to modify some logic once deployed .

[@AlexTushinsky]

I love Dapper, but struggle to implement third-party grids (Telerik or DevExpress) because they favor EF Code. Would love a video on how to get a grid working with Dapper efficiently! In other words, I don't want to return 100k rows to a grid, just so it can show 20. I want the grid to only pick up the 20 rows it needs, and so far the only way I've been able to do that is with a custom grid implementation with Dapper.

[@ChrisBrandsma]

I use paging features of sql. Limit and offset. To dapper those are just parameters.

[@AlexTushinsky]

@@ChrisBrandsma Yes, that's exactly what I do!

[@pilotboba]

@@ChrisBrandsma You might want to consider keyset paging instead of offset paging.

[@tamaslencse3468]

Great as always, but I have sooo many more questions like how about stored procedures, what is case with complex queries containing aggregated columns, is there a way to generate model classes from db, or what if the model class does not exacty match the query result?

[@elraito]

Hope you will make a follow up for queries with multiple joins where column names are duplicates cause tgats where ef's linq shines an dapper becomes annoying.

[@MilYanXo]

I prefer Dapper.FastCrud built upon Dapper but not depending on magic strings

[@MarcDuiker]

Could you also do a video about Dapr please? 😁

[@markovcd]

What is the point of abstracting the connection factory behind the interface when you write prostgres specific SQL in the movie service anyway?

[@gileee]

True. You'd have to make an IMovieRepository and then PostgresMovieRepo, SqlServerMovieRepo...

[@zoiobnu]

To abstract how create connection string

[@shadowsir]

@@gileee Oh but then we definitely also need a RepositoryFactory, RepositoryFactoryFactory and then 50 more layers of abstraction just to handle EVERY POSSIBLE DATABASE IN EXISTENCE ☠ --- Or you could just assume that your primary database will never change and keep it at one layer maximum the way sane programmers do it.

[@gileee]

@@shadowsir I have 2 databases right now in one of my systems, Sqlite and SqlServer. And you never need Repository factories so that's just a nonsensical argument. Although nowadays I just use efcore which removes the need for repo's, but I need a SqliteDbContext and SqlServerDbContext which ironically also require an SqliteDbContextFactory and SqlServerDbContextFactory for scaffolding to function. Repositories worked fine, and efcore also works fine.

[@DustyBg]

It's because of unit testing the services - inject a factory mock into the service which produces a mocked Dapper db connection object.

[@saymehname]

Let's goooo dapper

[@mgltuba]

dapper with T-SQL are most minimal good c# coding

I would like to see your approach for mapping forth and back with object graphs and joined queries (JOIN and/or LEFT JOIN). Find it to be a little complicated and maybe it's my approach is not the best one

[@GilbertoMadeira83]

Great video!

[@dovh49]

I love Dapper and how close to real SQL it makes it. I just wish it was still under development. There are some features it is missing still.

[@gileee]

Dapper is just a response mapper over ADO. I don't know what else they could add honestly.

[@jessekela165]

What is missing?

[@gileee]

@@jessekela165 I guess batch support

[@joost00719]

I love dapper. I used it all the time at my previous job. I was still working on my degree back then, and I implemented a Dapper-like system myself from scratch, and I included that in every single school project, because the school did not allow me to use packages for the data access layer. Probably not as optimized as Dapper itself, but easy inserts, easy reads etc, was really nice. Only thing I wish for is that inserting a list of objects was quicker in Dapper. At my old job we sometimes had to inserts which ranged between 1,000 and 1,000,000 records. Dapper would take ages, while the SqlBulkCopy did it in a fraction of the time. Not sure if this has changed in recent years with Dapper, but it was one of the pain points I had with it.

[@Crumbledore]

For this people tend to use Dapper Plus which is paid (so not great outside a corporate setting) but it has bulk operations that provide near SqlBulkCopy performance(without being restricted to Sql Server databases as well)

[@NotACat20]

Dapper not fastest mapper in the world. Linq2db is. And linq2db has lot of extensions about what you want do. Not always is easy, but it is clearly better than EF. Dapper is good, but at same time it is not perfect/best mapper in the world.

[@NotACat20]

Good abstractions known about bulk insert and try do as they can. And they can.

[@IanWilkinson-SgtWilko]

I would like to know if you would use / recommend for/against using DapperContrib or any of the other lightweight packages that remove the need for boilerplate sql in dapper.

[@AmirItzhak]

Thanks for the video, but can I use migration when working with dapper?

[@jiM3op]

Hallo! Would have been nice to tell that you can run procedures aswell... So you dont have to mix SQL and C#... I prefer that approach whenever possible.

[@gileee]

Stored procedures? What is this 1997.

[@ДмитрийКондратенко-б5ь]

This approach leads to the fact that business logic begins to be located both in the application code and in the code of stored procedures. It’s terrible when it’s not enough just to look at the code and understand what’s going on, you also need to open some database management studio and look at the code of the stored procedure. It seems to me that stored procedures can be used for places that are particularly critical from a performance point of view, but if there are hundreds of them in a project, this is sad.

[@BenvdStouwe]

> I will show you how you can get started with Dapper in 2025 I guess we'll have to wait a few months to be able to use this knowledge.

[@nickchapsas]

Future proofing

[@mattbristo6933]

@@nickchapsas good answer

[@charlesaboujawdeh]

Is it a good idea to make a "Wrapper" / helper class around dapper (I've seen people calling it a Generic Repository) instead of having to create a connection in every method we'd just inject that wrapper class and use it? Something that would for example take care of the connection and transactions and disposing them and maybe having default values set for the dapper methods for example the connection timeout or if you use Stored Procedures the majority of the time, set the command type to stored procedure by default?

[@vborovikov]

it's already been implemented in the framework, use DbDataSource class

[@charlesaboujawdeh]

@@vborovikov Interesting, didn't know about that. Does it work for SQL Server? Does Dapper implement something like it?

[@vborovikov]

@@charlesaboujawdeh it's implemented in the framework and available for every provider SqlClientFactory.Instance.CreateDataSource

[@duszekmestre]

Can you do some video about database scaffolding tools? You mentioned EF Core has one but Dapper no. And there are other libraries for that. Maybe you have experience with any to share knowledge with us?

[@pilotboba]

Try FluentMigrations

[@levmatta]

What about transactions?

[@danp602]

Would be good to see a best practice example of using the same transaction across different methods in the same repository

[@srokoszuio1]

Ahhh finally, not using EF!

[@WhisperII]

How to deploy changes on different envs using this dapper approach?

[@andreyz3133]

i wonder when dapper will support Span

[@randyriegel8553]

Our main application at work uses EFCore. It was already built. But when I have to do automation applications and such and need database access I use Dapper. Dapper is so easy. EFCore is easy to but take away a lot of control.

[@WantMore-mc8dx]

ADO + Dapper :)

[@YoosufRiffath-c5z]

Any way we could pool the connection and re-use it instead of creating a new connection each time? Is it even a good idea to do that?

[@danp602]

The underlying provider handles this concern

[@nickchapsas]

Yeah they are already pooled from the provider

[@svengijsen]

What about DB migrations combined with Dapper?

[@gileee]

He made a video on migrations without efcore.

[@shadowsir]

1) Learn SQL................ 2) Start with EF Core, then, if you really really need to optimize the queries (e.g. you're constantly fetching and pushing thousands of rows) OR if you need to use some DB-specific concept that EF Core doesn't support (yet), use Dapper. Reason I usually go with EF Core first: 1) EF Core makes renaming things so, so much easier. E.g., in the example Nick gave, if he had just refactor renamed the property on the class directly from "YearOfRelease" to "Year". The compiler wouldn't have yelled at him to also take a look at the queries. (this is why I always use anonymous objects and never just give a Dapper query an object with properties that COULD change at any time). 2) You can be 90% sure that, if the EF Core query builds, everything will just work. It might not be optimal, but it will work. This opposed to Dapper where, if you forget a comma or you didn't use brackets in a way the DB can understand, everything will compile, but you will crash AT RUNTIME 😶‍🌫

[@gileee]

Dapper methods accept a list but will just run the query n times for n elements in that list. Neither of them support bulk inserts without a paid third party library. Which means you'll be writing code like: movies.Chunk(200).ForEach(c => conn.Execute("INSERT INTO Movies VALUES {string.Join(',', c.Select(m => $"({m.Id, m.Name...})"))};")) It works but it's a hassle, especially since this is unsafe code and instead of m.Id and m.Name you'd actually have to generate unique query parameter names and then generate a dictionary with the values of those parameters. Again it works but you'll notice a Chunk call at the top, because SqlServer for example only supports some 1000 parameters in one query. So if your object has 5 fields you'll only be able to bulk insert 200 objects at a time. Or you convert the data into json and pass that json as a single parameter to the database which I've seen efcore do. Neither Dapper nor efcore help with bulk inserts unfortunately.

[@gileee]

Dapper methods accept a list but will just run the query n times for n elements in that list. Neither of them support bulk inserts without a paid third party library. Which means you'll be writing code like: movies.Chunk(200).ForEach(c => conn.Execute("INSERT INTO Movies VALUES {string.Join(',', c.Select(m => $"({m. Id, m. Name...})"))};")) It works but it's a hassle, especially since this is unsafe code and instead of m. Id and m. Name you'd actually have to generate unique query parameter names and then generate a dictionary with the values of those parameters. Again it works but you'll notice a Chunk call at the top, because SqlServer for example only supports some 1000 parameters in one query. So if your object has 5 fields you'll only be able to bulk insert 200 objects at a time. Or you convert the data into json and pass that json as a single parameter to the database which I've seen efcore do. Neither Dapper nor efcore help with bulk inserts unfortunately.

[@rainair402]

I wanted to point out the same: it's like telling people: why you want to drive a automatic car? Drive first a manual car. Ok but after so many years in traffic you will appreciate the automatic over the manual. I would never write a SQL statement myself. Either it's dead easy. Or the ORM will do the optimization for you. Why to waste hours on something, it can get automated?

[@parlor3115]

SqlKata has a much nicer API in my opinion

[@gileee]

Dapper uses ADO for sql execution and just maps the result set to an object. SqlKata is a query builder that you would use to make an SqlServer sql query string for example and then execute it with something like Dapper. In fact the SqlKata library with execution uses Dapper.

[@parlor3115]

​@@gileeeI know, but I've only seen people either go full raw strings with dapper or full code with ef. SqlKata is a nice middle ground, but it's nowhere near as popular.

[@gileee]

@@parlor3115 That's true. I've used all 3, but just stick to efcore now.

[@_MoshikoAz_]

does this match with the repository pattern ?

[@gileee]

The repo pattern historically helped remove hard dependencies to a specific database in code by having an interface like IMovieRepo that is then extended by concrete classes like PostgresMovieRepo or SqliteMovieRepo that contain sql for their specific database. So Dapper is the perfect fit for using repo's like the old times. Although changing DBs is something most people never do on a project.

[@yousafwazir286]

Ef core vs dapper

[@tanostro]

Linq2Db wins ^^

[@MrSchmell]

General ergonomics vs thin layer over ADO, which saves you some (not all) boilerplate

[@gileee]

Go with efcore and add Dapper as a side library if you ever need hand written sql.

[@crush1214]

​@@gileee EFCore supports Raw SQL queries too

[@gileee]

@@crush1214 True. And neither supports real batch command without paid extensions.

[@troncek]

Is using sql in code wise/good?

[@gileee]

I'd put it in a repository class, like we used to do, and not worry about it at all. It works great honestly.

[@cocoscacao6102]

Meh... Still prefer EF Core for 99% of the things.

[@I-PixALbI4-I]

Show also DynamicParametrs and pls say in video what about interpolation like: $"SELECT {m.Id} {m.Title} {m.YearOfRelease} FROM {nameof(Movie)}" Is it worse and explain pls why. THX waiting for a new video!

[@SebastiaoJoseph]

I think interpolation can bring risk of sql injection. You can take advantage of dapper using parameters

[@brickman127]

you shouldnt use string interpolation for this, if you do you will be creating a new string on every call rather than having one be effectively constant

[@fko079]

I'm using SQLKata with Dapper and I'm kind of independent of DB Provider. No need to think about SQL syntax specifics of each provider.

[@I-PixALbI4-I]

@@brickman127 But there is string interning under the hood it will not be creating every call. Google - String.Intern

[@galaridor8]

Is this a reupload or am I having a deja vu?

[@nickchapsas]

This is about Dapper the other one was Entity Framework

[@gileee]

Reupload from years ago maybe. Nick did go over Dapper a long time ago, and had several videos where he used it.

[@Robert-yw5ms]

The IDbConnection seems like a bit of a leaky abstraction here. In theory you shouldn't know or care what the concrete implementation is you're using, but in practice you need to know it's the postgres implementation because if you write t-sql (for example) it's eventually gonna not work.

[@MaiconLLoti]

In my company, we have an IQueryDBFunctions interface that stores specific database functions(limit in postgres, rows in firebird). Additionally, you can create an IQueries interface that can be implemented by classes for specific databases and inject in service

[@MrWuffels]

The knowledge is not necessary for the usage of the IDbConnection, it is for the Dapper extension methods. And they are no abstractions

[@Robert-yw5ms]

The extension methods ultimately rely on methods and properties on the interface. Even if you don't use Dapper, you still need to know the db provider. The documentation for IDbConnection even mentions this in the examples when they write // Create a command to get the server version // NOTE: The query's syntax is SQL Server specific IDbCommand command = connection.CreateCommand(); command.CommandText = "SELECT @@version"; return (string)command.ExecuteScalar();

[@MrWuffels]

@@Robert-yw5ms yep, I somehow didn't get what you were talking about initially. Of course it's leaky

[@gileee]

IDbConnection is a built-in interface for every database connection. So I don't see the leak there. Obviously you have to use Dapper with a specific database's sql syntax, but you put those methods into a SqlServerMovieRepository and use an IMovieRepository interface from code. Or alternatively use an sql builder and just configure that instead of repositories, like a more modern factory pattern.

[@q7314568]

EF 8 is good enough.

[@petropzqi]

Tell me 3 things you can do in Dapper that you can not do in EF.

[@fusedqyou]

Who cares? Pick what you like. It's been proven that they both exceed in certain things every one in a while, before the other one adjusts and catches up.

[@CodeAbstract]

no

Its just a reference at this point, still faster at some places

[@local9]

@@fusedqyou How it should be, people need to stop being so damn tribal with software.

[@nickchapsas]

no u

[@NotACat20]

I dpn't see any reason in this video - dislike. It shows some stupid things, but did not add anything new. And in the end - is highly opitionated. Video did not expose nor strong things of Dapper or other solutions, nor did not show EF deficiencies.

[@All-in-on-GME]

My guy, the video is titled 'get started with...' You aren't the target audience and that's okay. Edit: I did not realize you too are not a cat. My apologies sir. I'd like to recant my outrage of your outrage. All in on GME, remember to DRS.

[@IanGratton]

@nickchapsas - Dapper is really quite nice. I think one of the good things about it is it forces developers to be a bit more hands-on with the DB and that is a really good thing. I come across a great many people who are missing that slightly deeper knowledge of RDBMS systems and I think that stems from hiding behind do-it-all (until they dont) ORMs (same in the Java world with Hibernate). Code first is powerful at times - but not always optimal - learn the technologies you use. I also love that you've created tables that use names that adhere to the Postgres standards. The number of times I've seen postgres DBs with tables/views/fields tha don't adhere to these standards makes me sad - I really don't want to have to be using ANSI quoted column and table/view names when I'm building queries.