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.
Nick, loved your channel for years! I use dapper for stored procedures. We use reflection on classes and use them for the proc parameters... literally one line of code to call a stored procedure! (once you get a model (C# class) setup for the parameters. Sooo sweet and easy and the easiest way to write fool-proof proc calls with Dapper. (I use EF for table access, but for complex data processing via procs, there is no easier to call procs with "strongly typed" parameters. Keep Coding!
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!
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.
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..
@@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.
@@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.
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.
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.
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.
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).
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.
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?
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.
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.
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.
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?
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 🙂
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 .
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.
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)
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
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?
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.
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.
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.
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.
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 😶🌫
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.
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.
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?
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!
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
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.
@@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.
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.
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
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();
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.
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.
@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.
There is should be no debate about "do I need to learn SQL?"... You HAVE TO!
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.
I use EFCore. But I think a Dapper deep dive would be great. Dometrain rocks btw
Nick, loved your channel for years! I use dapper for stored procedures. We use reflection on classes and use them for the proc parameters... literally one line of code to call a stored procedure! (once you get a model (C# class) setup for the parameters. Sooo sweet and easy and the easiest way to write fool-proof proc calls with Dapper. (I use EF for table access, but for complex data processing via procs, there is no easier to call procs with "strongly typed" parameters.
Keep Coding!
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!
EF Core's RawSql querying made it unnecessary to use micro-ORMs such as Dapper.
try get complex data - for example with embedded address ;-)
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)?
Yes. A deep-dive would be more than welcome, Nick :)
Thank you so much. Would be great if you can do a deep dive into db connection pooling. 🙏
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.
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..
What is the point of abstracting the connection factory behind the interface when you write prostgres specific SQL in the movie service anyway?
True. You'd have to make an IMovieRepository and then PostgresMovieRepo, SqlServerMovieRepo...
To abstract how create connection string
@@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.
@@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.
It's because of unit testing the services - inject a factory mock into the service which produces a mocked Dapper db connection object.
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.
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.
just use DbDataSource class, let the framework to manage the connection pool
Thank you for sharing this Nick
Certainly a hands on! And I wouldn't trust a teammate who doesn't know SQL. Thanks for a great video
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.
Am I the only one who read "Rapper" in the thumbnail, because the D is covered. Rapper Nick, that would be a great video
Thanks for the video, but can I use migration when working with dapper?
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).
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.
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?
it's already been implemented in the framework, use DbDataSource class
@@vborovikov Interesting, didn't know about that. Does it work for SQL Server? Does Dapper implement something like it?
@@charlesaboujawdeh it's implemented in the framework and available for every provider
SqlClientFactory.Instance.CreateDataSource
Also there is AOT extension for Dapper - DapperAOT. In this case Dapper can AOT, but EF Core now don't
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.
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.
I use paging features of sql. Limit and offset. To dapper those are just parameters.
@@ChrisBrandsma Yes, that's exactly what I do!
@@ChrisBrandsma You might want to consider keyset paging instead of offset paging.
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.
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?
What about transactions?
Would be good to see a best practice example of using the same transaction across different methods in the same repository
How to deploy changes on different envs using this dapper approach?
It's crazy how Rider is miles ahead from vs 2022. I need to start using it all asap
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 🙂
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?
The underlying provider handles this concern
Yeah they are already pooled from the provider
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 .
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.
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)
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
Ahhh finally, not using EF!
Great video!
Is using sql in code wise/good?
I'd put it in a repository class, like we used to do, and not worry about it at all. It works great honestly.
> 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.
Future proofing
@@nickchapsas good answer
I prefer Dapper.FastCrud built upon Dapper but not depending on magic strings
Let's goooo dapper
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.
Dapper is just a response mapper over ADO. I don't know what else they could add honestly.
What is missing?
@@jessekela165 I guess batch support
What about DB migrations combined with Dapper?
He made a video on migrations without efcore.
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?
Try FluentMigrations
does this match with the repository pattern ?
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.
Could you also do a video about Dapr please? 😁
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.
Stored procedures? What is this 1997.
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.
i wonder when dapper will support Span
ADO + Dapper :)
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.
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 😶🌫
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.
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.
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?
Ef core vs dapper
Linq2Db wins ^^
General ergonomics vs thin layer over ADO, which saves you some (not all) boilerplate
Go with efcore and add Dapper as a side library if you ever need hand written sql.
@@gileee EFCore supports Raw SQL queries too
@@crush1214 True. And neither supports real batch command without paid extensions.
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!
I think interpolation can bring risk of sql injection. You can take advantage of dapper using parameters
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
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.
@@brickman127 But there is string interning under the hood it will not be creating every call.
Google - String.Intern
SqlKata has a much nicer API in my opinion
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.
@@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.
@@parlor3115 That's true. I've used all 3, but just stick to efcore now.
Is this a reupload or am I having a deja vu?
This is about Dapper the other one was Entity Framework
Reupload from years ago maybe. Nick did go over Dapper a long time ago, and had several videos where he used it.
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.
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
The knowledge is not necessary for the usage of the IDbConnection, it is for the Dapper extension methods. And they are no abstractions
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();
@@Robert-yw5ms yep, I somehow didn't get what you were talking about initially. Of course it's leaky
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.
EF 8 is good enough.
Meh... Still prefer EF Core for 99% of the things.
Tell me 3 things you can do in Dapper that you can not do in EF.
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.
no
Its just a reference at this point, still faster at some places
@@fusedqyou How it should be, people need to stop being so damn tribal with software.
no u
@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.