"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
@IAmTimCorey3 жыл бұрын
I agree. :-)
@theguire3 жыл бұрын
Brilliant!
@alanyoungjr3 жыл бұрын
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.
@IAmTimCorey3 жыл бұрын
You're very welcome!
@RalfsBalodis3 жыл бұрын
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
@IAmTimCorey3 жыл бұрын
Thank you. Much appreciated by MANY.
@RalfsBalodis3 жыл бұрын
@@IAmTimCorey As are your tutorials!
@adityarane2880 Жыл бұрын
Genuinely you have overpowered me with your communication skills and the way you explain each and every point.
@IAmTimCorey Жыл бұрын
Happy to hear that!
@DaniLearnsIT6 күн бұрын
I am so glad you mentioned that advanced dapper covers nested objects in your other video. This is exactly what I needed! :)
@IAmTimCorey6 күн бұрын
Great!
@DaniLearnsIT12 сағат бұрын
@@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!
@mrf72832 жыл бұрын
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!
@IAmTimCorey2 жыл бұрын
You are welcome.
@muhammadwaqasaziz40543 жыл бұрын
You are a teacher of teachers!!!! MAN YOU KNOW HOW TO TEACH!!
@IAmTimCorey3 жыл бұрын
Thank you. I strive to educate rather then just dump knowledge. I really appreciate your comment!.
@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.
@ob5005.3 жыл бұрын
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.
@IAmTimCorey3 жыл бұрын
I appreciate that.
@richardshi189611 ай бұрын
As always, Tim Corey gave a top level instruction on using Dapper.
@IAmTimCorey11 ай бұрын
I am glad it was helpful.
@nickpollard42034 жыл бұрын
Thanks Tim, your videos have really helped me professionally :)
@IAmTimCorey4 жыл бұрын
Great to hear!
@bardus_hobus4 жыл бұрын
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!
@IAmTimCorey4 жыл бұрын
I am glad my content has been so helpful.
@jopsuey4 жыл бұрын
The evolution of Microsoft Applications Blocks.. Excellent video Tim, greetings from Chile
@IAmTimCorey4 жыл бұрын
Thank you!
@summerbreeze25syria4 жыл бұрын
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.
@IAmTimCorey4 жыл бұрын
Glad it was helpful!
@TheBausahab3 жыл бұрын
Now I understand 'Dapper' . thank you sir.
@IAmTimCorey3 жыл бұрын
Great!
@winchin13603 жыл бұрын
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-iamtimcorey75973 жыл бұрын
Thank you for engaging with the community and sharing your thoughts!
@mdrahbarahmedkhan4 жыл бұрын
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
@IAmTimCorey4 жыл бұрын
You are welcome. I’m still trying to fit WCF in.
@hakimm.93993 жыл бұрын
Thanks Tim for the video and tutorial on Dapper.
@IAmTimCorey3 жыл бұрын
You bet!
@JohnMarsing5 жыл бұрын
Great video Tim, I've been using dapper for a couple of years and appreciated this video.
@IAmTimCorey5 жыл бұрын
Thank you!
@torrvic11569 ай бұрын
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.
@IAmTimCorey9 ай бұрын
Microsoft is working to make this easier, but it probably won't happen until .NET 9.
@prorakesh4 жыл бұрын
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.
@IAmTimCorey4 жыл бұрын
You are welcome.
@ameral-mahdawi27424 жыл бұрын
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 :)
@IAmTimCorey4 жыл бұрын
You're very welcome! I work hard to keep everything current
@shelleycurrie7644 жыл бұрын
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.
@IAmTimCorey4 жыл бұрын
You're very welcome!
5 жыл бұрын
It's soooo cool! Thanks for this tutorial Tim! Is totally worth his weight in gold!
@IAmTimCorey5 жыл бұрын
Thanks!
@TheNimbleCoder2 жыл бұрын
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
@IAmTimCorey2 жыл бұрын
I'm not sure I'd call it tight coupling. Order is almost always important. That's why we have FIFO objects.
@nelsonrivers85464 жыл бұрын
Videos on Dapper OR/M were great.Thank you for those.
@IAmTimCorey4 жыл бұрын
You are welcome.
@girornsveinsson79704 жыл бұрын
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.
@IAmTimCorey4 жыл бұрын
I will add it to the list. Thanks for the suggestion.
@marredcheese4 жыл бұрын
@@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 :)
@javiereduardo42453 жыл бұрын
+2 for this idea, EF and I prefer avoiding too.
@MauricioCaterino5 жыл бұрын
Excellent video Tim. This was very much needed. In fact, helped me a lot with some issues that I was having recently.
@IAmTimCorey5 жыл бұрын
Awesome!
@MauricioCaterino5 жыл бұрын
@@IAmTimCorey Happy Thanksgiving
@zoltan.halasz4 жыл бұрын
Thank you Tim for this tutorial. I really liked the insert set way with UDT types, I already used it in my project!
@IAmTimCorey4 жыл бұрын
Awesome!
@younessesoft5 жыл бұрын
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
@IAmTimCorey5 жыл бұрын
You are most welcome. Thanks for watching.
@rameshcse20053 жыл бұрын
Thanks Tim, Your way of explanation is crystal clear. Got a very good idea about dapper, thinking to implement in my new project.
@IAmTimCorey3 жыл бұрын
Glad it was helpful!
@rameshcse20053 жыл бұрын
@@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.
@tchpowdog4 жыл бұрын
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.
@IAmTimCorey4 жыл бұрын
Thanks for the suggestion.
@tchpowdog4 жыл бұрын
@@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!
@tchpowdog4 жыл бұрын
@@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
@bohuang31222 жыл бұрын
It's a so useful video which truly make learning C# much easier for me!
@IAmTimCorey2 жыл бұрын
Glad to hear that!
@ramzes-13543 жыл бұрын
Thanks a lot, especially for "Insert data set" section!
@tomthelestaff-iamtimcorey75973 жыл бұрын
Thanks Рамис for watching
@brunofrechette20635 жыл бұрын
Great vidéo! You've converted me to Dapper, I love this tool!
@IAmTimCorey5 жыл бұрын
Awesome!
@gregoriolopezvillagra11283 жыл бұрын
Great video! thanks Tim
@IAmTimCorey3 жыл бұрын
Very welcome
@alvinbernardo95513 жыл бұрын
Thanks for this course
@IAmTimCorey3 жыл бұрын
You are welcome.
@MrTellus4 жыл бұрын
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.
@IAmTimCorey4 жыл бұрын
Thanks for sharing.
@Tarabostes2023 Жыл бұрын
Also... if you want to retrieve the Object that was just added in the table... you can do this: var query = @" INSERT INTO TableName (Column1, Column2, ...) OUTPUT INSERTED.* VALUES (@Value1, @Value2, ...); "; var parameters = new { Value1 = "Some value", Value2 = 123, // Specify values for other columns }; var insertedObject = connection.QueryFirstOrDefault(query, parameters); NSERTED.*
@robertocallaghan5765 жыл бұрын
Excellent demo which i've already used (addicted to dapper)
@IAmTimCorey5 жыл бұрын
Thank you!
@vindigo524 жыл бұрын
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.
@IAmTimCorey4 жыл бұрын
I do that in the C# Application from Start to Finish course. Basically, update is just like insert, only it is an update command.
@vindigo524 жыл бұрын
@@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.
@mmuneebajaz5 жыл бұрын
Nice video , and that new sketch is awesome
@IAmTimCorey5 жыл бұрын
Thanks! I'm glad you like it.
@LivingTheDream214 жыл бұрын
Super clear and easy to follow along!
@IAmTimCorey4 жыл бұрын
Thank you!
@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.
@abhishekomal3 жыл бұрын
Thanks Tim, Nice video, I learned really this in details
@tomthelestaff-iamtimcorey75973 жыл бұрын
Thanks for watching
@milanmladenovic5 жыл бұрын
Great video! Thanks a lot !
@IAmTimCorey5 жыл бұрын
You are most welcome. Thanks for watching.
@rodrigo64593 жыл бұрын
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-iamtimcorey75973 жыл бұрын
I noted your recommendation by adding it to Tim's list of possible future topics, thanks.
@vijayalakshmimunji15114 жыл бұрын
Hi Tim, Can you please show an example for "Update list of Objects with Oracle Database" using Dapper.
@IAmTimCorey4 жыл бұрын
I will add it to the list. Thanks for the suggestion.
@joeingle17452 жыл бұрын
I find your cavalier use of UPPER and lower case SQL in the same query deeply troubling - I'm sure I'm not alone!!
@derDooFi Жыл бұрын
Yes, it is disgusting. And the indentation, good god!
@DamianWalczak5 жыл бұрын
if you concerned with the end performance of the.... APP you go straight (MS)SQL, if you concerned with the performance of the.... developer you go with Entity Framework both technologies supported by 1st party rather than stackoverflow folks but I guess to each their own
@IAmTimCorey5 жыл бұрын
I'm curious what you think going straight SQL means. You have to get the SQL results out of SQL and into your C# application. The "straightest" path would be ADO.NET but then you have to map a DataTable to your models, which is inefficient. Dapper is basically a very thin wrapper over ADO.NET that does the mapping for you in the most efficient way possible so...yeah, that's what I choose. As for EF allowing more developer performance, I would mostly agree. When it breaks, it causes a lot of issues for developers. However, the biggest thing I focus on is where I want the performance to be. Do I want an app that is developed a few days faster (because, let's be honest, that's all it would be in even a large app) or do I want the app to run even a second faster per use? In my case, I expect my apps to be used a lot. That means that the 1 second speed increase would multiply (a LOT). That means less server resources are being used. It means less network bandwidth. It means less congestion during busy times. It means less customers abandoning my page before it loads.
@bernardlogador53644 жыл бұрын
Thanks for this Tim! Great help
@IAmTimCorey4 жыл бұрын
You are welcome.
@tdwjiaodja2 жыл бұрын
Thanks a lot, I have coped with my problem really fast! Thank you!
@IAmTimCorey2 жыл бұрын
You are welcome.
@Marko-the-Beast-Master2 жыл бұрын
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.
@n94341785 жыл бұрын
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?
@chezchezchezchez5 жыл бұрын
Niels van Strien did u find out?
@GarethDoherty19855 жыл бұрын
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.
@IAmTimCorey5 жыл бұрын
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?
@n94341785 жыл бұрын
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 ).
@ernstpeterlegrand4 жыл бұрын
very clear, thanks a lot. Going to enjoy it.
@IAmTimCorey4 жыл бұрын
You are welcome.
@Mr.ad4m8 ай бұрын
thata our bro and our teacher too well done sir
@IAmTimCorey8 ай бұрын
You are welcome.
@Raziaar3 жыл бұрын
Another great vid Tim. Have you done any about SQL Database Migration techniques used alongside Dapper?
@IAmTimCorey3 жыл бұрын
Here you go: kzbin.info/www/bejne/n5unlHt9rd6ke5Y We use this system in the TimCo Retail Manager series.
@Raziaar3 жыл бұрын
@@IAmTimCorey Still replying to comments on years old videos, nice! Thanks!
@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.
@AbulHasanLakhani5 жыл бұрын
Can you please do a Dapper for beginners video. I mean video fully dedicated to Dapper only. Thanks a lot Tim.
@IAmTimCorey5 жыл бұрын
Here you go: kzbin.info/www/bejne/e6WVnJt9o9d8p8U
@AbulHasanLakhani5 жыл бұрын
Thanks a lot mate!
@KunalMukherjee37013 жыл бұрын
This videos are gold
@IAmTimCorey3 жыл бұрын
Thanks!
@niangel27045 жыл бұрын
You are a great teacher sir!! Thanx for this excellent tutorial!! You have ... twisted me towards dapper!! I don't want to be disrespectful but would it be challenging to you to presenting the Async variation of the current project using asynchronous calls to Dapper?! That would be very interesting. To take it a bit further up it would be a great topic for your upcoming "live" presentation Tim (as it was revealed at Vinay's comment!!!) Thanx again for your great tutorials Tim
@IAmTimCorey5 жыл бұрын
Async Dapper is something I'm considering. I'll make sure it is on the suggestion list. And please don't ever think it is disrespectful to ask me for video topics. Most of my videos are a result of people asking for a topic. I'm happy to oblige when I can.
@ajvenable79374 жыл бұрын
Fantastic video Tim! I'm really enjoying Dapper so far. Thanks for introducing me to it.
@IAmTimCorey4 жыл бұрын
My pleasure!
@loickbrouard8827 Жыл бұрын
Great video as always. Thank you ! 👍
@IAmTimCorey Жыл бұрын
You are welcome.
@longuinni5 жыл бұрын
Really nice. Thanks for sharing that!
@IAmTimCorey5 жыл бұрын
You are most welcome. Thanks for watching.
@praadeeprao20733 жыл бұрын
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
@rodrigo64595 жыл бұрын
as always...!! REALLY nice and clear
@IAmTimCorey5 жыл бұрын
Thank you!
@yureichudnovskyy10005 жыл бұрын
Hi, Tim you are great man, thank you for video. All of just and a understand how to work this is a Dapper that it.
@IAmTimCorey5 жыл бұрын
Thank you!
@adamflory38295 жыл бұрын
Thank you. This was very helpful.
@IAmTimCorey5 жыл бұрын
You are welcome.
@JasonWynn4 жыл бұрын
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!
@IAmTimCorey4 жыл бұрын
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
@marcostorres73805 жыл бұрын
Thanks Tim!
@IAmTimCorey5 жыл бұрын
You are most welcome. Thanks for watching.
@Mydad-et1el2 жыл бұрын
Thank you. Very good tutorial!
@IAmTimCorey2 жыл бұрын
You are welcome!
@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.
@kombokenedy47505 жыл бұрын
This is a Gold tutorial
@IAmTimCorey5 жыл бұрын
Thank you!
@muhammadwaqasaziz40543 жыл бұрын
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.
@simon-white4 жыл бұрын
Excellent tutorial Tim, the basics section was also helpful for reviewing all the different ways of passing data from pure sql, to anonymous types or dynamic parameters. The method MultipleSetsWithParameters() on testing I'm glad to see is safe from SQL injection (wasn't sure how it'd handle string concatenation despite parameterisation), however did find it's still open to what I've heard referred to as LIKE-injection, where you pass in metadata characters like %, _ or [ ] allowing the user control of how the search pattern works, although there are ways to stop the user doing this if it's an issue. References: security.stackexchange.com/a/25052 docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15#arguments Also noticed a change in stored proc behaviour. Normally all stored procs by default will return a status integer, usually 0, which you'd capture in a variable (e.g. EXEC @returnStatusInt = ProcName), where 0 indicates success and a non-zero integer indicates something went wrong. You can specify in the proc RETURN @@ROWCOUNT which would override the default behaviour, but spPerson_InsertSet didn't do this yet still returned a rowcount. Is the normal return status integer still accessible via Dapper? Or perhaps it's not really needed since we've got error handling anyway.
@IAmTimCorey4 жыл бұрын
Yes, you do need to be careful whenever getting data from a user. As for returning the success int, yes, Dapper still captures this. An Execute statement returns an int (which you can ignore if you want, which is typically what I do). It will do so even if you capture output variables. Now Dapper will also properly throw an error if something happens in SQL that is unexpected.
@hazlotumismo14193 жыл бұрын
Is it a best practice to use dirty reading inside SQL statements? Thank you in advance!
@wheelsam26592 жыл бұрын
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
@serhiy20204 жыл бұрын
Would be very useful to see how to convert one-to-many results to c# objects
@IAmTimCorey4 жыл бұрын
I will add it to the list. Thanks for the suggestion.
@joseandrade9175 жыл бұрын
Thank you for another lesson! Is it possible to make a video that includes integration with docker and jenkings?
@IAmTimCorey5 жыл бұрын
Docker is coming. I don't have a video for Jenkins planned but I do have a course that makes use of Azure DevOps (an alternative to Jenkins that, in my mind, is far superior to Jenkins). The course is called Application Lifecycle Design: www.iamtimcorey.com/p/application-lifecycle-design
@sidieudelphin16853 жыл бұрын
Hi Tim, is it possible to have a generic Query Multi-Mapping method?
@dominikblazic86673 жыл бұрын
Great question, was just wondering the same thing! Hope Tim can answer this one :)
@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.
@sunnyjia15334 жыл бұрын
Excellent, that's what I need. thanks
@IAmTimCorey4 жыл бұрын
Excellent!
@Chabass Жыл бұрын
after stackoverflow did not provide any solutions, i turned to chatgpt. i got a working generic: The Generic: public static async Task MapMultipleObjectsWithParameters(string sql, Func map, string splitOn = "Id") { using (IDbConnection cnn = new SqlConnection(_config.ConnString)) { await cnn.Open(); var result = await cnn.QueryAsync(sql, map, splitOn: splitOn); return result.ToList(); } } How to use: var result = await MapMultipleObjectsWithParameters(sql, (person, phone) => {person.CellPhone = phone; return person;});
@chrissimmon3148 Жыл бұрын
i am struggling to get this to work when a person has many phones.
@bobburger76482 жыл бұрын
Hi Tim, how do you separate your data access layer from your data layer in this instance if using generic types like you teach elsewhere?
@marcelbrown1 Жыл бұрын
I would also like to see how to do this
@Chabass Жыл бұрын
after stackoverflow did not provide any solutions, i turned to chatgpt. i got a working generic: The Generic: public static async Task MapMultipleObjectsWithParameters(string sql, Func map, string splitOn = "Id") { using (IDbConnection cnn = new SqlConnection(_config.ConnString)) { await cnn.Open(); var result = await cnn.QueryAsync(sql, map, splitOn: splitOn); return result.ToList(); } } How to use: var result = await MapMultipleObjectsWithParameters(sql, (person, phone) => {person.CellPhone = phone; return person;});
@andrewposs921 Жыл бұрын
I was also wondering about this.
@sodreigor2 жыл бұрын
Hi Tim. Can you do a video on advanced aggregations with dapper? Like some complex avg(column1), sum(column1) group by ...
@IAmTimCorey2 жыл бұрын
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.
@mathewparsell26034 жыл бұрын
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?
@IAmTimCorey4 жыл бұрын
That gets more complicated and not necessarily as useful, but it is possible.
@blutkraehe_official5 жыл бұрын
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_official5 жыл бұрын
lol, the error disappeared after a reboot, no idea what that was
@IAmTimCorey5 жыл бұрын
It sounds like you were missing a "using Dapper;" statement at the top.
@blutkraehe_official5 жыл бұрын
@@IAmTimCorey yeah it sounds like of course, but i must only reboot VS :-) thanks
@Gps22904 жыл бұрын
@@blutkraehe_official I had the same error and disappeared after rebooting VS, 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?
@LuigiZambetti5 жыл бұрын
Hi Tim, how about making an introductory video explaining what it Microservices Architecture and why is so in demand in these days?
@IAmTimCorey5 жыл бұрын
I can add that to the list. That definitely is a challenging topic that should be covered.
@sidieudelphin16853 жыл бұрын
Hi Tim, is it possible to have a generic Query Multi-Mapping method? Just like you craete the generic methods in the TimCORetail project.
@LakeAndLodgeGarage2 жыл бұрын
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?
@IAmTimCorey2 жыл бұрын
Here is my intro to Dapper lesson: kzbin.info/www/bejne/e6WVnJt9o9d8p8U
@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.
@MrMarkbad2 жыл бұрын
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?)
@troymitchel47903 жыл бұрын
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?
@BenKyoBaduk4 жыл бұрын
Hi Tim, thanks for the vids! Been following for some time now, the pace and clarity of your explanations is uncommon and perfect for a lot of people! Regarding multi-insert with Dapper, many answers online say the performance is poor for large data sets, that Dapper doesn't support bulk insert. People came up with workarounds like yours, but the performance I've seen reported wasn't great, and on some threads I saw people saying Dapper ends up looping anyway (making many individual inserts). Does your example here perform well for inserting hundreds or thousands of rows at once (eg. let's say, at LEAST under 2 seconds for 10,000 rows)? Thanks!
@IAmTimCorey4 жыл бұрын
Bulk inserting like that is not what Dapper is designed to do. In fact, it isn't what most data access mechanisms are designed to do. Inserting this many records requires an end-to-end plan. You need to set up SQL to handle that many inserts (turn off logging, triggers, indexes, etc. for the duration of the insert for example). Then you need to figure out how to send the data. Dapper can use a TVP to send over as many records as you want. It won't do an individual insert per row that way but you need to set up your stored procedure correctly to make use of the above SQL changes (you might execute those changes in the stored procedure before the insert). At the end of the day, Dapper is not much more than a wrapper around ADO.NET to make SQL calls easier. You can execute any SQL statement you want through Dapper. So, you can get great performance out of Dapper or you can get poor performance out of it. The reason has nothing to do with Dapper. It is in how you configure it.
@BenKyoBaduk4 жыл бұрын
@@IAmTimCorey Thanks! Then, suppose I'm using ADO.NET and about to make some changes to use Dapper, but I have one or more workflows with similar inserts ranging from 1 to let's say a max of 20k rows, and suppose I know I will never need to write more than 30k even in the future, would you suggest SQLBulkCopy, something else, or only optimize the current set up as much as possible with some of your suggestions above? I understand SQLBulkCopy has some overhead and is better on larger datasets, so I've been tiptoeing around the idea for a while - perhaps I can split the workflow into something like what you've shown in the video for small data sets, and a SQLBulkCopy workflow for data above size N? I'd prefer to keep things as simple as possible, another reason I wanted to avoid SQLBulkCopy, but I do need the user experience to be pretty snappy in this case, as the user must wait for it. Thanks again!
@t4all3 жыл бұрын
can you make an example for datagridview?
@tomthelestaff-iamtimcorey75973 жыл бұрын
Thanks for the recommendation. I have added it to Tim's list of viewer suggestions for videos.
@abhikdey40282 жыл бұрын
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
@henrygene58995 жыл бұрын
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" ?
@IAmTimCorey5 жыл бұрын
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).
@Pancho7635 жыл бұрын
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
@IAmTimCorey5 жыл бұрын
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.
@KunalMukherjee37013 жыл бұрын
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