"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
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.
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.
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 !
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!
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
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!
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.
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 :)
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.
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 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.
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
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
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.
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.
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 +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 :)
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.
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.
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 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.
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 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!
@@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
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?)
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
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.
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?
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
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
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
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?
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?
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
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?
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.
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.
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"
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
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.
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?
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
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.
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!
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).
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!
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.
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.
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.
Hi, Tim. Your video tutorials are always top notch! I've been using ADO.Net DataReader and/or DataSet since the early days of .Net, even with the advent Entity Framework (EF) I still use ADO.NET for most of my projects. I use EF not more than to derive models from the database, then doing the operation with Dapper. One day my colleague introduced me to Petapoco, I haven't tried it before. What are your thoughts on Petapoco?
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.
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).
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.
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
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.
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#.
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?
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).
What if after a join there are 2 ID columns? How does it know which one goes to hwich model? If I alias them will it figure it out? But what if for both models the property is named just ID?
Think of the result set in the query window. That should make this easier to visualize. You wouldn't want to have two columns named the same thing (ID) since you don't know what each communicates. Therefore, you would alias one (or both). Dapper just looks at the result set and maps that, so if you have two IDs in your model, only one can be called ID. The other will probably be called AddressId (for example). If you aliased the column in the result set as AddressId then it will properly map to your C# model.
"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
I agree. :-)
Brilliant!
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.
I appreciate that.
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.
You're very welcome!
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 !
You are welcome.
Genuinely you have overpowered me with your communication skills and the way you explain each and every point.
Happy to hear that!
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!
You are welcome.
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.
Thank you for engaging with the community and sharing your thoughts!
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
I'm not sure I'd call it tight coupling. Order is almost always important. That's why we have FIFO objects.
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!
I am glad my content has been so helpful.
Thanks Tim, your videos have really helped me professionally :)
Great to hear!
Great video Tim, I've been using dapper for a couple of years and appreciated this video.
Thank you!
Thank you Tim for this tutorial. I really liked the insert set way with UDT types, I already used it in my project!
Awesome!
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.
You're very welcome!
Videos on Dapper OR/M were great.Thank you for those.
You are welcome.
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 :)
You're very welcome! I work hard to keep everything current
Thanks Tim for the video and tutorial on Dapper.
You bet!
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.
Glad it was helpful!
Excellent video Tim. This was very much needed. In fact, helped me a lot with some issues that I was having recently.
Awesome!
@@IAmTimCorey Happy Thanksgiving
As always, Tim Corey gave a top level instruction on using Dapper.
I am glad it was helpful.
The evolution of Microsoft Applications Blocks.. Excellent video Tim, greetings from Chile
Thank you!
It's soooo cool! Thanks for this tutorial Tim! Is totally worth his weight in gold!
Thanks!
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.
You are welcome.
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.
Microsoft is working to make this easier, but it probably won't happen until .NET 9.
You are a teacher of teachers!!!! MAN YOU KNOW HOW TO TEACH!!
Thank you. I strive to educate rather then just dump knowledge. I really appreciate your comment!.
It's a so useful video which truly make learning C# much easier for me!
Glad to hear that!
Thanks Tim, Your way of explanation is crystal clear. Got a very good idea about dapper, thinking to implement in my new project.
Glad it was helpful!
@@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.
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
You are welcome. I’m still trying to fit WCF in.
Now I understand 'Dapper' . thank you sir.
Great!
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
Thank you. Much appreciated by MANY.
@@IAmTimCorey As are your tutorials!
Great vidéo! You've converted me to Dapper, I love this tool!
Awesome!
Thanks a lot, especially for "Insert data set" section!
Thanks Рамис for watching
Great video! thanks Tim
Very welcome
Nice video , and that new sketch is awesome
Thanks! I'm glad you like it.
Thanks for this Tim! Great help
You are welcome.
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
You are most welcome. Thanks for watching.
Super clear and easy to follow along!
Thank you!
Thanks for this course
You are welcome.
very clear, thanks a lot. Going to enjoy it.
You are welcome.
Thanks a lot, I have coped with my problem really fast! Thank you!
You are welcome.
Thanks Tim,
Nice video, I learned really this in details
Thanks for watching
Excellent demo which i've already used (addicted to dapper)
Thank you!
Great video as always. Thank you ! 👍
You are welcome.
Really nice. Thanks for sharing that!
You are most welcome. Thanks for watching.
Great video! Thanks a lot !
You are most welcome. Thanks for watching.
as always...!! REALLY nice and clear
Thank you!
This videos are gold
Thanks!
Fantastic video Tim! I'm really enjoying Dapper so far. Thanks for introducing me to it.
My pleasure!
Thank you. This was very helpful.
You are welcome.
Thank you. Very good tutorial!
You are welcome!
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.
Thank you!
Excellent, that's what I need. thanks
Excellent!
top notch as always :)
Thank you!
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.
You can write a custom type map (inherit from DefaultTypeMap) and create an attribute to ignore properties.
Thanks Tim!
You are most welcome. Thanks for watching.
Great vid.! Thank you 🙂
You are welcome.
thata our bro and our teacher too well done sir
You are welcome.
Nice video, thank you.
You are welcome.
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.
you are amazing, Thank you
You are welcome.
amazing man
Thank you!
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.
I will add it to the list. Thanks for the suggestion.
@@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 :)
+2 for this idea, EF and I prefer avoiding too.
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.
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.
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.
I do that in the C# Application from Start to Finish course. Basically, update is just like insert, only it is an update command.
@@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.
Thank you !!!
You are welcome.
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.
Thanks for the suggestion.
@@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!
@@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
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.
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
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?)
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
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.
Thanks
You are welcome.
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?
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
Another great vid Tim. Have you done any about SQL Database Migration techniques used alongside Dapper?
Here you go: czcams.com/video/ijDcHGxyqE4/video.html
We use this system in the TimCo Retail Manager series.
@@IAmTimCorey Still replying to comments on years old videos, nice! Thanks!
Is it a best practice to use dirty reading inside SQL statements? Thank you in advance!
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
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
Hi Tim, is it possible to have a generic Query Multi-Mapping method? Just like you craete the generic methods in the TimCORetail project.
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
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?
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?
Here is my intro to Dapper lesson: czcams.com/video/Et2khGnrIqc/video.html
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
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?
That gets more complicated and not necessarily as useful, but it is possible.
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.
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.
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"
I noted your recommendation by adding it to Tim's list of possible future topics, thanks.
Thank you for another lesson! Is it possible to make a video that includes integration with docker and jenkings?
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
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.
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.
Hi Tim, is it possible to have a generic Query Multi-Mapping method?
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?
That's what I did here: czcams.com/video/eKkh5Xm0OlU/video.html
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
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.
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!
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).
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!
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.
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.
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.
Hi, Tim.
Your video tutorials are always top notch!
I've been using ADO.Net DataReader and/or DataSet since the early days of .Net, even with the advent Entity Framework (EF) I still use ADO.NET for most of my projects. I use EF not more than to derive models from the database, then doing the operation with Dapper. One day my colleague introduced me to Petapoco, I haven't tried it before.
What are your thoughts on Petapoco?
It is an interesting tool but to be honest, I haven't used it in a couple years. I'll have to revisit it and see what I think.
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.
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).
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.
On an insert, you would need to pass in the CellPhoneId. So, you would probably need to add it to the model.
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
I believe it is but I don't believe I have called Oracle using Dapper yet (I avoid Oracle if I can).
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.
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#.
If i could give you a million like, i would have.
Wow, thanks! Just share a course with a friend maybe?
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?
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).
What if after a join there are 2 ID columns? How does it know which one goes to hwich model? If I alias them will it figure it out? But what if for both models the property is named just ID?
Think of the result set in the query window. That should make this easier to visualize. You wouldn't want to have two columns named the same thing (ID) since you don't know what each communicates. Therefore, you would alias one (or both). Dapper just looks at the result set and maps that, so if you have two IDs in your model, only one can be called ID. The other will probably be called AddressId (for example). If you aliased the column in the result set as AddressId then it will properly map to your C# model.