Advanced Dapper in C# - SQL Transactions, Mulitple DataSets, UDTs, and more

[@alanyoungjr]

Thank you again! Your tutorials are phenomenal. I only learn concepts well when I understand the practical uses and the logic behind the concept. You are clearly a master educator and I can’t thank you enough.

[@IAmTimCorey]

You're very welcome!

[@adityarane2880]

Genuinely you have overpowered me with your communication skills and the way you explain each and every point.

[@IAmTimCorey]

Happy to hear that!

[@RalfsBalodis]

0:00 - Intro 1:34 - Demo app: Database overview 5:20 - Demo app: Class Library overview 7:17 - Multiple object mapping 15:56 - Multiple object mapping with parameters 19:00 - Multiple Data sets 21:44 - Multiple Data sets with parameters 23:43 - Parameter output form database 28:43 - Safe Transactions 37:25 - Insert Data Set 42:37 - Concluding remarks

[@IAmTimCorey]

Thank you. Much appreciated by MANY.

[@RalfsBalodis]

@@IAmTimCorey As are your tutorials!

[@mrf7283]

Very informative and worth while. very productive 44min. Watching in 2021 questioning myself why didn't watched it before, could had saved me from lots of grief in some of my projects. Keep up the good work. Thank you!

[@IAmTimCorey]

You are welcome.

[@ob5005.]

"A junior developer writes simple code to do simple things. A mid-level developer writes complex code to do complex things. A senior developer writes simple code to do complex things." by Tim Corey

[@IAmTimCorey]

I agree. :-)

[@theguire]

Brilliant!

[@belongstomyself]

thanks tim, you saved me a ton of time looking up all the cool tips from every where .. I find it really easy following your tips and you actually make it look so simple, yet you still show a glimpse of its full potential enough to help everyone watching your videos to pick the best scenario for his needs ! I appreciate your hard work !

[@IAmTimCorey]

You are welcome.

[@muhammadwaqasaziz4054]

You are a teacher of teachers!!!! MAN YOU KNOW HOW TO TEACH!!

[@IAmTimCorey]

Thank you. I strive to educate rather then just dump knowledge. I really appreciate your comment!.

[@ob5005.]

Man thank you all this information you gave at each answer. You're taking care of your channel as your house. This is what is called effort! I hope you get millions of subscribers in short time.

[@IAmTimCorey]

I appreciate that.

[@richardshi1896]

As always, Tim Corey gave a top level instruction on using Dapper.

[@IAmTimCorey]

I am glad it was helpful.

[@nickpollard4203]

Thanks Tim, your videos have really helped me professionally :)

[@IAmTimCorey]

Great to hear!

[@DaniLearnsIT]

I am so glad you mentioned that advanced dapper covers nested objects in your other video. This is exactly what I needed! :)

[@IAmTimCorey]

Great!

[@DaniLearnsIT]

@@IAmTimCorey Hey Tim, one little question. If I had something like a One-To-Many relationship, do you think it is a good idea to serialize the "Many" as a JSON string? The only way I see a One-To-Many working in Dapper is doing a join of the "One" with the "Many" in the query, but this would result in tons of redundant data from the "One"! AND you would need to do a ton of extra work in the app to store the "Many" as some iterable! Serializing the "Many" as a Json in the database would make it so there is only one row per record being returned to your app! And this would decrease the redundant data by a ton! - Although, you would still need to de-serialize the Json in your app. BUT there are serializers out there that are quite fast!

[@jopsuey]

The evolution of Microsoft Applications Blocks.. Excellent video Tim, greetings from Chile

[@IAmTimCorey]

Thank you!

[@summerbreeze25syria]

As a software engineer, when I was a student I didn't get many topics in database, and I thought that it is impossible to learn how to do it. Thanks to you I am more confident in doing database connections now. I really appreciate it. Thank you.

[@IAmTimCorey]

Glad it was helpful!

[@bardus_hobus]

Just want to mention you've been so much help as I go through college for this. Right now, I simply don't have enough money to help in return, but once I land my first job, I want to contribute back!

[@IAmTimCorey]

I am glad my content has been so helpful.

[@winchin1360]

thank you for all the great contents. I don't comment often, but I have watched many of your videos and liked all of the videos that I have watched.

[@tomthelestaff-iamtimcorey7597]

Thank you for engaging with the community and sharing your thoughts!

[@mdrahbarahmedkhan]

I have just 2 years of experience working on WPF technology, I have learned so much from you. Thank you very much from my heart ♥. ☺ Still waiting for WCF tutorial

[@IAmTimCorey]

You are welcome. I’m still trying to fit WCF in.

[@TheBausahab]

Now I understand 'Dapper' . thank you sir.

[@IAmTimCorey]

Great!

[@JohnMarsing]

Great video Tim, I've been using dapper for a couple of years and appreciated this video.

[@IAmTimCorey]

Thank you!

[@ameral-mahdawi2742]

Thank you Tim for this simple and clear explanation. I was lucky enough to see this video today and you added the link to the source code just one hour ago. Means I was watching the video while you added it :)

[@IAmTimCorey]

You're very welcome! I work hard to keep everything current

[@torrvic1156]

Amazing examples as usual, Mr. Corey! I really need to wrap my head around this DataSets to understand how to insert the whole prepared table onto another empty table. But this is fine.

[@IAmTimCorey]

Microsoft is working to make this easier, but it probably won't happen until .NET 9.

[@prorakesh]

This Video saved me a lot of time, I wanted to chose between EF and a better alternative for a project. Thank you Tim Corey.

[@IAmTimCorey]

You are welcome.

[@abhishekjadhav9289]

Hi Tim, Can you please make a video or may be guide on how to pass comma-separated string and using it "IN" clause in stored procedure. I am really struggling with this one, because the c# passes this to sql parameter like this '1,2,3,4'. However, the "IN" clause needs it in '1', '2', '3', '4'. I don't understand how should I pass a comma separated string in this format ?

[@IAmTimCorey]

Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/

[@abhishekjadhav9289]

@@IAmTimCorey Hello Tim, Thank you for the quick response. I will add the suggestion. Also, I fixed the problem, after cheking msdn, where I found that in the new SQL server version there is a built-in function "split_string('1,2,3,4' , ',')". The first parameter is comma separated string and second param is the separator based on which the split will be done. We can use a query say " select value from split_string('1,2,3,4', ',') " to feed its output to "IN" clause. This formats the values in the way "IN" clause expects.

[@hakimm.9399]

Thanks Tim for the video and tutorial on Dapper.

[@IAmTimCorey]

You bet!

[@shelleycurrie764]

Thank You Tim Corey for your really amazing tutorials, you explain the technical aspects really well and have helped me out of challenging coding situations a number of times now.

[@IAmTimCorey]

You're very welcome!

[@vijayalakshmimunji1511]

Hi Tim, Can you please show an example for "Update list of Objects with Oracle Database" using Dapper.

[@IAmTimCorey]

I will add it to the list. Thanks for the suggestion.

[@Marko-the-Beast-Master]

Hello Tim, I want to ask if someone has a generic internal class SqlDataAccess like you used it in your some projects. Is it possible to do multi mapping with generic classes, right? Dapper needs to know where the child object is in the generic parent, am I right? Or how to do this? In our projects we always have at least one 1 - n tables. Thank you.

[@girornsveinsson7970]

I would love to see a video from you covering Dapper implementations of repository-, unitofwork- and specification patterns with possible eager loading of navigation properties. Everything I find on this revolves around Entity Framework, but I think you have made good arguments against EF and I prefer avoiding using it for anything else than authentication and user management. I am really hoping that Dapper can be a good alternative. Maybe some extensions that have been made to Dapper and available as nuget packages can make it even a better choice.

[@IAmTimCorey]

I will add it to the list. Thanks for the suggestion.

[@marredcheese]

@@IAmTimCorey +1 for this idea. From browsing the net, I get the impression that the repository pattern allows you to use any data persistence mechanism you'd like...as long as it's Entity Framework :)

[@javiereduardo4245]

+2 for this idea, EF and I prefer avoiding too.

[@MauricioCaterino]

Excellent video Tim. This was very much needed. In fact, helped me a lot with some issues that I was having recently.

[@IAmTimCorey]

Awesome!

[@MauricioCaterino]

@@IAmTimCorey Happy Thanksgiving

It's soooo cool! Thanks for this tutorial Tim! Is totally worth his weight in gold!

[@IAmTimCorey]

Thanks!

[@TheNimbleCoder]

Excellent video Tim!! I'm new to your channel. Thanks for everything you do for the community. It's awesome!! The bit about having to read the results of the QueryMultiple call in a particular order brought me to a screeching halt. That's tight coupling at its finest. If anyone ever inserts a select between the two existing selects, our system will have a bad day - at least in dev and pre-prod that is. 😀 Thanks again! -Mark

[@IAmTimCorey]

I'm not sure I'd call it tight coupling. Order is almost always important. That's why we have FIFO objects.

[@praadeeprao2073]

Hi Tim, How can we execute the dapper query if the Person have more than one phone number. e.g. A person object has List

[@abhikdey4028]

Hi Tim, At 37:53, you mentioned data table is not much used anymore, with dapper doing mapping for us, could you please guide how to use object mapping for bulk insert data ? Thank you

[@rodrigo6459]

Tim... this was a GREAT stuff to watch.. but it´s missing one KEY part... Multi-Mapping (One to Many), it will be the yewel in the crown!, at least i find it very hard to understand and in my case i will be needing to bring a query that comes from 3 diferent tables, any chance for a video like this?, also.. implementing the Repository Pattern using Dapper including the "Unit of Work"

[@tomthelestaff-iamtimcorey7597]

I noted your recommendation by adding it to Tim's list of possible future topics, thanks.

[@nelsonrivers8546]

Videos on Dapper OR/M were great.Thank you for those.

[@IAmTimCorey]

You are welcome.

[@zoltan.halasz]

Thank you Tim for this tutorial. I really liked the insert set way with UDT types, I already used it in my project!

[@IAmTimCorey]

Awesome!

[@rameshcse2005]

Thanks Tim, Your way of explanation is crystal clear. Got a very good idea about dapper, thinking to implement in my new project.

[@IAmTimCorey]

Glad it was helpful!

[@rameshcse2005]

@@IAmTimCorey Hi Tim, I have general query. Is it really make a differences (In term of performances) to write async task methods in repository/controller classes. Please let me know your point of view.

[@tchpowdog]

I mentioned this in your Entity Framework video - you should definitely check out FOR JSON PATH. Especially since you like stored procedures so much. Your MapMultipleObjects method would be one stored procedure call that returns a string and then you deserialize that string into your FullPersonModel object. Quite simple. And the brilliance of it is that all the "nesting" happens on the database instead of your C# code.

[@IAmTimCorey]

Thanks for the suggestion.

[@tchpowdog]

@@IAmTimCorey No prob. I've learned a lot from you over the past few days, I really appreciate what you're doing. I haven't researched FOR JSON PATH a lot, but I know that I haven't run into any performance issues with it. It works identical to using the old FOR XML if you've ever used that. I think it would make for a nice video in your collection - whether that be a positive or negative review. As always, thanks Tim!

[@tchpowdog]

@@IAmTimCorey Just a couple of pointers if you decide to play with this. 1. Make sure you declare an nvarchar(max) variable and then select your query into that variable. Then return the variable. If you just run the query, the stored procedure will cut off the string that it returns at like 400 characters maybe, I don't remember the number. 2. This is where this gets controversial to me - you can do this same thing inside a function and return the json string from the function. Then use the function inside of a select query. Point of this is to methodize/consolidate your code, and you can nest these functions are far as you want. But Functions inside of subselects are a no-no. Like this: create function GetAddresses ( @UserId int ) returns nvarchar(max) as begin declare @result nvarchar(max) set @result = ( select * from Addresses where UserId = @UserId for json path ) return @result; end create procedure GetPeople as begin declare @result nvarchar(max) set @result = ( select * ,json_query(dbo.GetAddresses(u.Id)) as Addresses from Users u for json path ) return @result; end

[@sidieudelphin1685]

Hi Tim, is it possible to have a generic Query Multi-Mapping method?

[@dominikblazic8667]

Great question, was just wondering the same thing! Hope Tim can answer this one :)

[@bohuang3122]

It's a so useful video which truly make learning C# much easier for me!

[@IAmTimCorey]

Glad to hear that!

[@n9434178]

Great video! Thank you! I have 3 questions 1) Do these advanced scenarios work when using SQLite or other database types? 2) How would the MapMultipleObjects method change when you need data from a third table (e.g. addresses). 3) How would the MapMultipleObjects method change if the FullPersonModel contains a property which itself needs data from multiple tables? Or does it get too complex then to load the FullPersonModel in one go?

[@chezchezchezchez]

Niels van Strien did u find out?

[@GarethDoherty1985]

I am also curious about points 2 & 3 asked here. I also wonder how you would be able to map multiple objects if one of your properties is a list of another object. I have a complex 'Order' object Which has a header line, line items (list) and each line item has variations (another list). Because of this complexity, I had to use EF to retrieve my Order object from the database :( but I use Dapper for all other database operations.

[@IAmTimCorey]

Yes, most of the advanced scenarios work with other databases. The issue with support is more on the database side vs. the Dapper side. Adding more mapping isn't too hard but nesting nested objects does get tricky. Here is my advice: rework your queries. For example, do you really need all of that data right now? If not, don't return it until you need it (if you do). If you do need it, what if you returned a second data set that you then mapped in using LINQ?

[@n9434178]

Unfortunately I did not have time to play with Tim's examples in this video, so no. Tim did respond with a suggestion for a solution that is workable. Returning multiple datasets along the lines that this video showed will work for me. I just wanted to make sure that there was not a simple solution for Q3 that I was overlooking (but when TC writes 'nested objects does get tricky', I believe I should take another path towards my goal ).

[@mohammeddabbour949]

hi Tim , that is an awesome tutorial , where can i find the video tutorial to the basic methods at 6:56 ? i need them too , thanks.

[@IAmTimCorey]

Here you go: kzbin.info/www/bejne/e6WVnJt9o9d8p8U

[@bryandpalmer]

Tim, I'm still having a hard time marrying these advanced Dapper techniques inside of a DataAccess class library set up you demonstrated with the LoadData and SaveData async methods. Neither of those methods will work for these advanced techniques. Do you end up putting these types of calls into the individual TableData methods and just bypass the SqlDataAccess object altogether? I know this is an older video, the but concepts are still accurate for the advanced Dapper calls. I just can't seem to make the jump from this into the dedicated dataaccess class library approach you've suggested and demonstrated in your minimal API videos and in other courses/content.

[@jannickbreunis]

Is there any good reason to create a IDBConnection (the cnn variable) instead of just a SqlConnection? Saw you do it in the Web Api Course on your website too.

[@IAmTimCorey]

It is an interface, which allows us more flexibility to change the implementation later on without causing issues. Also, we can reuse that code other places and just change the implementation to match the database type we are using.

[@blutkraehe_official]

hi, i have on error with ur source code by cnn.Query 7:01 at line 105 IDbConnection does not contain a definition of Query, and no accessible query extension method could be found that accepts a first IDbConnection argument (a using directive or assembly reference may be missing) ( "IDbConnection" enthält keine definition von "Query", und es konnte keine zugängliche Query-Erweiterungsmethode gefunden werden, die ein erstes Argument vom Typ "IDbConnection" akzeptiert (möglicherweuse fehlt eine using-Direktive oder Assemblyverweis) ) any idea to solution? kind regards

[@blutkraehe_official]

lol, the error disappeared after a reboot, no idea what that was

[@IAmTimCorey]

It sounds like you were missing a "using Dapper;" statement at the top.

[@blutkraehe_official]

@@IAmTimCorey yeah it sounds like of course, but i must only reboot VS :-) thanks

[@Gps2290]

@@blutkraehe_official I had the same error and disappeared after rebooting VS, thanks!

[@brunofrechette2063]

Great vidéo! You've converted me to Dapper, I love this tool!

[@IAmTimCorey]

Awesome!

[@muhammadwaqasaziz4054]

Just one question when you pull data from multiple table, does dapper do it parallel or in sequence, if it does parallel then its lots of fun.

[@henrygene5899]

Strategic question: Dapper vs EF, knowing that for 'small- to mid-sized developers (like me), once you one as your tool, you are most likely to re-use that tool over and over again for most (all) of your remaining projects. I write (mostly, currently) Windows desktop apps, but can foresee doing iOS /iPad and web apps. I used to use ADO Record Sets in VB6 and was quite comfortable with it. Even wrote methods that would cycle through all the Controls on a Form and build a RS of fields that needed to be updated, so that such would never have to be manually enumerated, which made coding much faster and less error prone. And used the msBind so that the Controls always matched the underlying recordset record automatically. Loved it. As I move forward, what would be the pros and cons of "Choose Dapper, and never look back at EF" ?

[@IAmTimCorey]

There's a lot to that situation. I've got a blog post on it but it is outdated and needs to be updated to reflect EF Core. The basics, though, are that EF can be easy until it isn't. It can be easy to get data in and out of your application until you find a performance issue (common with EF) or until something breaks in EF (not as common but when it happens it can get messy). EF writes a lot of code for you. Generated code can be great but you are still responsible for it. If it breaks, you are the one to fix it. With Dapper, there is no generated code. Most of the work is done in the tooling and, if it breaks, it is something that the Dapper team fixes. Your code is simple. However, you are responsible for knowing how to get data in and out of your database. I would argue that you should anyway, since you are supporting EF doing exactly that (and by supporting, I mean you are responsible for knowing how it works, not just saying "the magic does stuff"). Dapper is incredibly fast (almost as fast as raw ADO and a lot more flexible). EF6 is slow, and while EF Core can be a lot faster, it does depend on the queries it writes. You are responsible for checking those queries to make sure they are efficient and, if not, for replacing them with efficient ones (if you care about efficiency).

[@MB-nw5sz]

Thank you for another great video Tim, I'm a long term fan of your videos and instruction, you have helped me an awful lot. I spent a year learning and using EF (love the code first approach) but became very (very!!!) frustrated when it went wrong. I've always manually created Databases and am very comfortable with SQL/SSMS/SSDT so using Dapper feels like taking back some control for me (especially alongside SSDT). I am really struggling however with Dapper "breaking" when trying to use simple non-mapped properties, such as FullName => $"{FirstName} {LastName}" and having to strip out relational properties e.g List in order to use Dapper. It seems to be a lot of extra work to create subsets of classes/DTOs, just to be able to use Dapper. I appreciate it's a micro ORM, and I want to use it, but it feels too limited in practice. Would you recommend alternative ORMs that cope better? I am looking around, but as always appreciate your recommendations, which I know come from many years of experience - so thank you for any pointers.

[@IAmTimCorey]

You can write a custom type map (inherit from DefaultTypeMap) and create an attribute to ignore properties.

[@hazlotumismo1419]

Is it a best practice to use dirty reading inside SQL statements? Thank you in advance!

[@Raziaar]

Another great vid Tim. Have you done any about SQL Database Migration techniques used alongside Dapper?

[@IAmTimCorey]

Here you go: kzbin.info/www/bejne/n5unlHt9rd6ke5Y We use this system in the TimCo Retail Manager series.

[@Raziaar]

@@IAmTimCorey Still replying to comments on years old videos, nice! Thanks!

[@AthelstanEngland]

Thanks TIm. I've just come across "Unit of Work" concepts, although nothing related to minimal APIs, which I'm using. Even so, I can't see any point in UoW when Dapper has this transactional capability. Am I missing something?

[@nathanjames1089]

Hey Tim, I am using the transaction portion for an accounting program I am working on (as well as following the C# application from start to finish course). The code I'm using requires multiple executes with rollbacks in the middle of the using transaction statement. At the end I have the transaction.commit function. If a rollback is called it goes back to the begin transaction statement, correct? Then I would expect it to skip past the curly braces like a break statement. Please let me know if I have this correct.

[@IAmTimCorey]

Inside your SQL statement? Yes, the rollback will undo all of the actions that occurred inside the transaction if you roll back.

[@nathanjames1089]

@@IAmTimCorey I guess what I’m trying to clarify is that if I have Using Trans { For loop {SQL execute Fail condition/rollback} Second For loop {SQL execute Fail condition/rollback} Trans.commit; } And one of the rollbacks is called it will break out of the using statement correct?

[@IAmTimCorey]

If you are doing the transaction from the C# side and doing this loop then when you roll back the transaction, you need to exit the loop as well. The rollback only affects the SQL transaction, not your C# code.

[@nathanjames1089]

@@IAmTimCorey is there an issue with calling rollback multiple times? The only thing the for loop does is find information for the sql statement and add data to a string of things go wrong. What happens if rollback is called and then the for loops complete and commit is called? Will it then commit any executes that happened in between? Thank you for your help on this.

[@IAmTimCorey]

Once you roll back, the transaction is dead. You want to stop doing work in it at that point.

[@wheelsam2659]

Great Video! Do you have a dapper video explaining how to map property names to column names if they aren't exactly the same? I.e. column first_name to property FirstName? EDIT: I'm trying to follow the naming standards for PostgresQL

[@troymitchel4790]

For an SQL with inner join wouldn't just be easier to return a datable from a reader using dapper? Less messy? Or is there a generic cleaner way to do this?

[@younessesoft]

Excellent video thank you for what you do for us always shine in your videos and continue in this way you help us a lot and make things easier for us and short the time to learn

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@muzk2710]

Great video Tim I am currently using dapper in one of my projects. I would like to know if it’s possible to use SqlDependency to get the dbchanges ? I need to use signalr notifications when a record changes in db. Kindly let me know if it’s possible Thank you

[@vindigo52]

Well done, Tim. I really like your tutorial video's. I learned SQL and C# all by myself by watching this kind of tutorials. But what I am missing is UPDATING a single record in a database using dapper, C# and models. Most tutorials are handling select and insert, which according to me are not the most difficult commands. Thank so far for the wonderful video's.

[@IAmTimCorey]

I do that in the C# Application from Start to Finish course. Basically, update is just like insert, only it is an update command.

[@vindigo52]

@@IAmTimCorey Thanks Tim. Yes, it shouldn't be that difficult. My application doesn't update because it doesn't find the ID. Back to debugging. Keep up the good work.

[@konstantinoskinnas789]

Hello Tim great video. I was wondering could we not do most of the things with stored procedures. Would this be better or worse or is it a matter of preference because I lot of people are not nececarrily very proficient with sql. For example the transaction if I recall correctly could happen from the sql side throught a procedurs(if I am wrong it is because I am only using sql for basic stuff).Or for example the sql querried from the c sharp side could be used as a stored procedure.

[@IAmTimCorey]

Whenever possible, I recommend doing it on the SQL side. However, there are times when you need to do transactions on the C# side. I do so in the TimCo Retail Manager, because we need to save the header for a sale and then each detail record. There isn't a good way to transmit all of that data to SQL at once and do the transaction in SQL. So we do the transaction in C#.

[@sidieudelphin1685]

Hi Tim, is it possible to have a generic Query Multi-Mapping method? Just like you craete the generic methods in the TimCORetail project.

[@KunalMukherjee3701]

Hi Tim, how do you perform a json HTTP patch operation using dapper where you got dynamic columns to update, I mean how do you build the query / stored procedure without it being vulnerable to SQL injection

[@Pancho763]

Hi Tim, thanks a lot for this video, i have a question, how can use Query if i had a query with inner join to 3 or more tables with different id for relation?, in my class i have a Class Property for each one

[@IAmTimCorey]

Not sure what you mean. Dapper just cares about the output. The output will be rows and columns. For each column, you need a property. Dapper will then create an object per row. It doesn't care how many joins happen in the query.

[@cyrildouglas9262]

Thanks Tim, I have a question about the first method, in person table you have CellPhoneId column which you use it in sql statement, how do you insert CellPhoneId to person table ? cause there is no such a field in models but in person table.

[@IAmTimCorey]

On an insert, you would need to pass in the CellPhoneId. So, you would probably need to add it to the model.

[@asafb5907]

Hello Tim, thank you so much for this video. Do you have a video which shows how to set up pagination with Dapper using MS SQL's FETCH/OFFSET features for pagination? Or if not if you could guide me to a reference to study? Thanks so much.

[@IAmTimCorey]

I don't have an example to show you but I can add it to the suggestion list.

[@LuigiZambetti]

Hi Tim, how about making an introductory video explaining what it Microservices Architecture and why is so in demand in these days?

[@IAmTimCorey]

I can add that to the list. That definitely is a challenging topic that should be covered.

[@nathanunderbsd5972]

2:34 can I use this? LIKE concat('%',@searchTerm,'%'), I just started my coding style November 2018.

[@IAmTimCorey]

Yes, I believe you can.

[@nathanunderbsd5972]

@@IAmTimCorey My theory is correct, work like a charm. :)

[@MrMarkbad]

I have been looking for a way to capture errors using Dapper. I can get the parameters back from a stored proc but what happens if there is a SQL error? I have been looking for different methods but it seems Dapper, when encountering an error, just fails silently. For example, if I am missing a parameter for a stored procedure I just get nothing back. No throw of any error. Any recommendations? I found some information on connection.InfoMessage but that is not available (anymore?)

[@graggster]

loved the video as I do all of yours but one question, if you have a person table with physicaladdress(INT) and mailingaddress(INT) and an addresses table with addressid(INT) ... how can you do sql statement to get join these tables to give me a result set that has each person in the person table and their respective mailing and physical address?

[@IAmTimCorey]

That would just be an inner join statement for each address type (select p.*, phyAdd.*, mailAddr.* from person p inner join address phyAdd on p.physicaladdress = phyAdd.Id inner join address mailAddr on p.mailingaddress = mailAdd.Id).

[@JasonWynn]

Wow! I've been looking for something like this! I've tried learning EF before but it just didn't make sense to me. It seemed like Microsoft went to some great lengths with EF to keep coders from needing to write SQL. In my opinion, anyone that develops CRUD applications should learn SQL and how to design databases; it's not that hard. I've stuck with typed DataSets but this doesn't seem to be an option with .NET Core. It appears that I can create a DataSet in a .NET Standard class library but the generated code requires System.Data.SqlClient and my understanding is that we should be using Microsoft.Data.SqlClient going forward. Thanks for the great information!

[@IAmTimCorey]

I'm glad you enjoyed this video. Have you checked out SQL Server Data Tools yet? They pair well with this design style and they make it even easier to build SQL databases: kzbin.info/www/bejne/n5unlHt9rd6ke5Y

[@LakeAndLodgeGarage]

Hi Tim, Love your stuff. One quick question. I am working with dapper and SQLite. I have gone through this Advance Dapper video and its great. My question is you call out a Demo 1, Basic Dapper lesson but i can't find that one. I would like to go through that one as well to get the complete picture. Where can I find the video related to Demo1_BasicDapper?

[@IAmTimCorey]

Here is my intro to Dapper lesson: kzbin.info/www/bejne/e6WVnJt9o9d8p8U

[@mdrtoffee]

Hi Tim, thanks for the video. I am trying to justify Dapper over EF Core 3.x as I'm very comfortable writing my own SQL but also because of performance reasons and less overhead than EF. One issue I have with Dapper right now is mapping one-to-many relationships using multiple objects that are more than 1 level deep. For example - select c.*, b.*, p.* from dbo.Company c join dbo.Branch b on c.Id = b.CompanyId join dbo.Person p on b.Id = p.BranchId. Is this easily achievable using the Mapping technique in your example and also where I only want a subset of the fields in those tables?

[@IAmTimCorey]

Remember that you aren't locked into having a model that looks like your table. Too often people get caught up in creating these complex models that map to SQL when what they really need is a new, flat model. If you need the name from person, the branch name from branch, and company name from company, create a model that has those 3-4 properties in it and create a query that gets just those pieces of data. Whether you use EF or Dapper, getting deeply nested models when you aren't planning on using all of the data is expensive overkill. Create a flat model that works, even if you include the IDs so that you can pull full objects later if you need to.

[@mdrtoffee]

@@IAmTimCorey Thanks, this is actually a solution I've come up with before. I guess it's whether you believe repositories should always return domain objects or not? I could easily create a new model for this purpose. But then my repository method in this case is not returning a domain object. Do you see that as an issue? Personally I see it as a lesser of two evils but I know lots of people would be against it.

[@JasonHoningford]

Mark Rees did you go with Dapper? After watching this video I'm sticking with EF Core (database first) for my next project. That way the only mapping I'm left with is to my view models. Rolling my own feels like I'm writing what EF does automatically and if for some reason I ran into problems I could replace EF with ADO or Dapper with very little time wasted, especially if I write it in a .NET Standard library. ADO and Dapper will work in the same project too.

[@mdrtoffee]

@@JasonHoningford Right now I'm yet to finalise my decision but leaning towards Dapper. I hate the SQL generation of EF at times and feel it's more overhead than needed as well as being slower than Dapper. I have managed to map results from Dapper to my Domain objects using Slapper.Automapper so right now it feels I'm ticking the boxes with the Micro ORM solution. I will then be using Automapper to map from my Domain objects to DTO or ViewModels objects.

[@JasonHoningford]

@@mdrtoffee Cool thanks for the insight. I'll have to give the mappers a shot!

[@bencraft4593]

Hi Tim, thank you for another great video. I have been struggling with obtaining a return value when doing an execute with dapper. For instance when a new customer is added i would like the output from my stored procedure for the ID column. is there a workaround with dapper to make this work?

[@IAmTimCorey]

That's what I did here: kzbin.info/www/bejne/m3zOmWiOopWCorc

[@kirigamifukuda4267]

Hi, Tim. I've been having trouble when using Dapper. It won't show up in my Intellisense and returns error that The type 'IDbConnection' is defined in an assembly that is not referenced. You must add a reference to assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'.

[@IAmTimCorey]

It sounds like you created a .NET Standard class library instead of a .NET Framework class library. You might also be mixing library types. Try creating a .NET Framework class library instead. See if that solves the issue.

[@sodreigor]

Hi Tim. Can you do a video on advanced aggregations with dapper? Like some complex avg(column1), sum(column1) group by ...

[@IAmTimCorey]

Dapper doesn't handle that. Either you are going to do it on the SQL side (likely) or you will do it with LINQ on the C# side.

[@Rizzan8]

Hi, is there any way to add objects with foreign keys using dapper? Like we have Author and Book tables. The book table has Author ID as the foreign key and author has a list of book objects. And when I add an author object to database I would like to add whole object also with the list of books in one query.

[@IAmTimCorey]

You can either make multiple calls or you can pass all of the data to a stored procedure and have it do the multiple inserts. That's a limitation of SQL, not Dapper. EF does it by making multiple calls (actually one big call with multiple statements, I believe).

[@AbulHasanLakhani]

Can you please do a Dapper for beginners video. I mean video fully dedicated to Dapper only. Thanks a lot Tim.

[@IAmTimCorey]

Here you go: kzbin.info/www/bejne/e6WVnJt9o9d8p8U

[@AbulHasanLakhani]

Thanks a lot mate!

[@facundopugliese9632]

Thank you, Tim! I have two questions. How should be MapMultipleObjects() method if I have many entities (let's say15 for example) and I want to make a data mapping in this long class hierarchy? In some cases a class property is a List and any object of that List could have a property that's another List. Which is the best way to handle this mapping with Dapper? Thanks in advance!

[@IAmTimCorey]

It sounds like you would be better off doing multiple SQL calls instead. That would keep it clean and simpler. Still, that is going to be messy. If you have a choice, you might want to look into a NoSQL solution like MongoDB. That would reduce your calls to one.

[@OldCumbria]

Thanks for the video; it helps a lot. I do have one query: I normally get Db info from a ViewModel via a property in the data access class. How would I return the two lists to the ViewModel via the property, please?

[@IAmTimCorey]

I don't understand the question. Sorry.

[@OldCumbria]

In your video you obtain 2 lists, People and telephone numbers, and display on the screen. Instead of displaying the lists, how would I return both to a view model via a property that calls the method?

[@janedoe7280]

hi Tim. great video. .is there any video which explains the "Demo1_BasicDapper" folder on your project? I followed your "easiest way to connect to SQL with c#" video and thought this is the continuation. Please comment.. Thanks in advance :)

[@LuigiZambetti]

Hi Tim, what do you think of the NOSql databases, in particular MongoDB? Could be useful learn something about this topic for who has always use SQL Server in the past? Seem that are in demand nowadays.

[@IAmTimCorey]

NoSQL is definitely on the short list for upcoming videos.

[@carlosagudelo1035]

Hi Tim, how about when you get null for that parameters or the user can decide to pass the parameters or not? thanks a lot

[@harshilshah4455]

I have a doubt: Does Dapper handle connection close method in case of an error condition or do need to implement try catch and finally when we call the Query or Execute method of SqlConnection class.

[@IAmTimCorey]

That's outside of Dapper itself. Dapper just handles the extension methods that do Query and Execute. The actual connection is given to Dapper. That's a SqlDatabase connection (from C# itself). That's why we instantiate the connection in a using statement. That using statement will ensure that the connection gets closed even if there is an exception.

[@SocialExde]

Hi Tim, thanks for your videos about c#, wpf, sql,, ect. I learn a lot of things. For this Dapper video i have a question. If is posible to update multiple rows with dataset like the InsertDataSet example? Thanks for your answer.

[@IAmTimCorey]

Yep. That's just a change in how your SQL is written. No changes needed on the Dapper side. Just change what you do with the data once it gets to SQL.

[@mmuneebajaz]

Nice video , and that new sketch is awesome

[@IAmTimCorey]

Thanks! I'm glad you like it.

[@t4all]

can you make an example for datagridview?

[@tomthelestaff-iamtimcorey7597]

Thanks for the recommendation. I have added it to Tim's list of viewer suggestions for videos.

[@loganparker9923]

Hi Tim, thanks for the great video as always! Just one quick question. For the MapMultipleObjects example, How would we go about using this in practice, for example populating a list with the returned people rather than outputting to console? As well, is there a way for us to make it a more generic function so only the SQL code along with classes would have to be passed to the function rather than rewriting for each case? Thanks!

[@IAmTimCorey]

We outputted it to a console but you could just return those objects directly. I don't believe anything would need to change. As for making it generic, it can be a bit trickier but you can make this generic (if you limit it to a set number of mappings).

[@abhishekjadhav9289]

Hi Tim, Thank you so much for this priceless video. Great Explanation. Only one question regarding the inserting multiple dataset using data table. In the example if your sql table has an Id column which has auto-increment identity, how to handle Id column while creating and populating the DataTable ? Do we have to skip that Id from creating it in the Datatable ?

[@IAmTimCorey]

Yes, don't include that column. SQL will ignore it on insert and it will properly create the IDs for each record.

[@abhishekjadhav9289]

@@IAmTimCorey Hey Thank you Tim for always being so kind to answer the questions so quickly. Hats off to you. Keep up the great work.

[@ameytuljapurkar6627]

in the given example table value parameter we used "BasicDT" needs to be present in the sql server database ?

[@IAmTimCorey]

Yes it does.

[@mathewparsell2603]

Hi Tim, In data access videos prior to this one you've used generic methods like : public static List LoadData(string Sql) and passed in the relevant object and SQL statement. Is there a way to do something similar using Multi mapping by providing the object list and params?

[@IAmTimCorey]

That gets more complicated and not necessarily as useful, but it is possible.

[@MrTellus]

I thought I share I got InsertDataSet to work with troopers as a List instead of DataTable and without any manual conversion between List and DataTable. First install nuget Dapper.ParameterExtensions and add a using statement. Then change the declaration for p to: var p = new DynamicParameters(); p.AddTable("@people", "BasicUDT", troopers); Thats it :-) P.S You can change GetTroopers to be like GetPeople in the BasicDapper project if you want to try it.

[@IAmTimCorey]

Thanks for sharing.

[@raghuadiraju6166]

Was there any connection pooling mechanism in dapper

[@DeeArr]

Damn. I was hoping for output parameters with procs. I hate writing raw sql in my c#

[@IAmTimCorey]

You can do that with Dapper, too. Output parameters update the parameters you passed in. You just need to mark them as output parameters.

[@DeeArr]

@@IAmTimCorey Cheers for the quick reply. I'm trying to code it as I type. Excellent tutorials.

[@ramzes-1354]

Thanks a lot, especially for "Insert data set" section!

[@tomthelestaff-iamtimcorey7597]

Thanks Рамис for watching

[@RetrotechCarParts]

Hey Tim! I came here from your devops course on your site - I was wondering how using a second db for your application would work alongside the authorization database that's automatically created when you enable authentication in a .net webapp project. If I have a User in my domain and the auto-generated User in the auth db, is there a way to merge or link them?

[@IAmTimCorey]

Check out the TimCo Retail Manager series. I do exactly this scenario.

[@RetrotechCarParts]

@@IAmTimCorey holy crap there are 50 videos in that playlist. Here we goooooooo

[@rayt6867]

Hello Tim, Many thanks for this video, I need to insert data into a parent table and then the child table (one to many). How can this be accomplished with Dapper?

[@IAmTimCorey]

Do the parent insert, get the ID, then do the inserts for the child entries.

[@eduardrivas6964]

What if I have a property which is a list of strings, say List, for instance, stored on a different table, how can I load and associate them to an object property? Should I load the user object with a first query and then do a second query with the list of strings that belongs to that user and manually do a user.addresses = resultOfSecondQuery?

[@IAmTimCorey]

I would probably either do two queries (one for people, one for addresses) or do two result sets from one stored procedure. Either way, yes, you would have a List and a List that you could then join in C#. That would only be if one person could have multiple addresses. Otherwise, it could all be returned as one query. If you did the two queries, though, you wouldn't need to pass data back to SQL. SQL already knows what data it gave you for the People list, so you could use that as the where clause for the Address list.

[@eduardrivas6964]

@@IAmTimCorey In order to avoid doing multiple queries, I solved it with a single query with join, then I looped the results with the Func map of the QueryAsync method, it took me a while to understand how it works but finally made it and with this approach I returned the final list of objects with it's properties populated. I had to join 4 tables, I even asked ChatGPT for help but it wasn't that useful. A bit of debugging helped me to finally understand. Thanks anyways, it's weird you didn't cover that here. Yet I appreciate all your content.

[@IAmTimCorey]

I didn't cover that here because it is a noisy means of getting the data to your application. It transmits more data than is necessary (something EF does a lot too).

[@eduardrivas6964]

@@IAmTimCorey But a simple Distinct takes the noise away The difference with EF is that I never knew how did it work, now I have total control and executed way less IO operations

[@oliverguy3121]

can you do a tutorial on dapper with async methods?

[@tomthelestaff-iamtimcorey7597]

Topic suggestion noted and have added to Tim's list of viewer requests, thanks.

[@artintheraw6682]

Hello Tim, thanks for all the great videos. I am trying to find a solution to update complex objects to different tables. How does this work with dapper. Example, User object with Address Property, Roles Property etc. I can't find any examples. Everyone tends to just query. Thanks.

[@IAmTimCorey]

You've got a few options but it starts with looking at it from the SQL perspective. How do you insert data into SQL. You can update a table. That's about it. Sure, you can create a stored procedure that updates multiple tables but the base calls are all "update this table". Dapper just exposes that in C#. So, you can call multiple update/insert statements or pass all of the data into a stored procedure and update multiple tables inside of it. Either way, the same thing is happening. This is true no matter which ORM you use. The only difference is how much control you have over it.

[@artintheraw6682]

@@IAmTimCorey Thank you sir. I figured as much, but was hopeful. What I have planned is to deconstruct the User object by pulling out certain navigation properties and updating the individual tables accordingly. Dapper is so much faster and once I have the repository setup, this should be smoken. Appreciate your time. Take Care and Have a Great Day.

[@robertocallaghan576]

Excellent demo which i've already used (addicted to dapper)

[@IAmTimCorey]

Thank you!

[@praadeeprao2073]

Thanks Tim, this was really nice. just wanted to know if the above strategy of invoking procedure and sending parameter is same for calling oracle procedure except for putting colon(:) instead of @ before the parameter

[@IAmTimCorey]

I believe it is but I don't believe I have called Oracle using Dapper yet (I avoid Oracle if I can).

[@ibrahimgirisken8945]

Hello, I am creating a project using dapper with onion architecture. Since I was using Dapper, I could not adapt the identity and jwt token packages to the project. Can you help with this?

[@IAmTimCorey]

I don’t have an example of that to share. Sorry.