I'm really thankful for this example, I had a job a few years ago where I had to clean up a lot of these failures to close etc. And I learned this exact pattern as a way to solve that leak issue. This is perhaps the best and most succinct explanation of it I've seen in 10 years. Well done, Tim.
Great video again Tim, I know there is another "bug fix" video after this so what I'm about to say you might have fixed in there. Just a couple of issues I spotted: 1. When you commit the transaction you also close the connection, this leads to an error in your using statement on the dispose, the error basically tells you that you don't have a transaction open so there is nothing to commit again. - not sure why your code doesn't stop on the lines when it throws an error (maybe an option I have turned on). 2. I would suggest NOT to close the connection when you commit the transaction - the commitTransaction() method should do one thing. commit. I would have the close connection on the dispose only. This way the user (developer) can open & close several transaction within the using statement. (hope this makes sense).
Yep, I fixed issue 1 in the follow-up video. As for not closing the connection, interesting idea. However, I would rather have the person explicitly open a new connection when they needed it again rather than risk having a long-running connection open to my database. Opening a connection isn't a big deal performance-wise. Leaving one open is a big deal.
Yep! The transaction seems to be tried twice although failing the second time. Definitely a great debugging exercise for learning quite a lot like finding the Dispose procedure being hit under any condition despite of not being not explicitly referred to! Thanks again for the Treasure Tim! Can't wait to start the next video!
Tim Corey you are the best tutor. You make very effective series for the beginners and also for them who can not afford to buy online course. i am big fan of you And learn a lots from your tutorial
Wonderful implementation, I was just scrolling trough your video's in seach for yet another useful video and you didn't disapoint. Transactions are important guys 'm kay.
One small tip: Instead of adding a temporary variable to look at the exception, when you are in the catch block with the debugger, you can look in the locals window to the variable $exception.
What I usually use to rename stuff like property names or method names is crtl+r+r key combination, which also renames pretty much everything including fields in interfaces. Never had any problems with it even in kinda complex solution I'm working on in my workplace so I recommend it to everyone here :)
Hi Tim, suggestion here. I think adding the functionality of entering an amount after checking out, for displaying the change is part of a basic register too.
I've debated how I would go about that. We don't really have a "check out" screen where they enter cash/credit and the amount. Have to think that one through to see if it is necessary for version 1. Right now, it is up to the cashier to indicate that they got paid, whether they use a totally separate credit card machine or a cash drawer and made change manually.
No problem Tim. I just thought that it's a better experience if the system had that feature. Anyways, thank you so much for this tutorials. I learned a lot of things from this series and will learn a lot more. You're the best!
Do you ever use Unit of Work and have a transaction distributed over multiple repositories? I use aggregates a lot and keep messing with the pattern I use and would be very interested to hear your take on that
I am really liking this series. Being out of work during Covid has given me the opportunity to investigate a number of technologies I have not used before. One suggestion though, would it be better to create a second SQLDataAccess class specifically for transactions to avoid the possibility of SaveData(...) being called mid transaction..
This playlist is truly a life Saver I’ve been following your content for some time and it’s just amazing. I’m trying to implement tab control using the caliburn micro I’m running through some issues do have any course on that?
Hi Tim, it does not clear out the cart for you, because for you the same thing happens as for me and what RC mentioned in the comment. When you try to commit the transaction the second time (through Dispose), it gives an exception telling it is already commited. For me the debugger breaked at the exception but it seems like because of your exception settings it did not break, that's why you didn't notice it.
Hi Tim Thanks for this example, pretty good. One thing in running mode if transaction is rolled back how user will know that my data is not recorded. can we provide any message at this state? Regards
At 35:08 on line 80, we roll back the transaction in the catch but then continue the exception (the throw). That will allow us to handle the exception in the UI, allowing the user to know there was a problem with saving the data.
Tim, I used for transaction System.Transactions class: using (var transactionScope = new TransactionScope()) { SqlDataAccess sql = new SqlDataAccess(); ....... save //sale ....... save //sale detail transactionScope.Complete(); } This is bad or worse then your manner? I checked and I throw exception after first save, all is rollback, so it works.
Nice video again. I implemented the transaction mechanism using SQL transaction in the stored procedure. I used TVP to pass the sale details to the stored procedure. If anyone is interested to know the details, please let me know.
IAmTimCorey I have used it a few years now was excited when they said efcore would get server side filtering but that it would not be coming to winforms core i don’t get why not
Hi Tim, Thanks for all the videos you put out! A question (that maybe has no right or easy answer) is, how far "out of the mainstream recommendation" is it to encapsulate "a fair bit of logic" in stored procedures? I see you've done it here, and this is definitely the route I chose, but I have a few peers who keep reminding me that this is "Doing It Wrong(TM)" and that the API should handle the business logic. The book I'm using as guide (to use PostreSQL sprocs) was written by a founder of Skype. That approach worked well (until MS bought it!) so it seems to me that sprocs, done carefully, can work swimmingly. But I always feel I'm breaking industry standards in doing so. Your thoughts appreciated, as you have more interaction with other experts.
My philosophy is to let SQL do what it does best and let C# do what it does best. That means I let SQL look up data, sort data, filter data, and even protect data. Then I let C# break the data apart, work on it, etc. That means I don't put a TON of logic in my stored procedures but I also am ok with some.
@@IAmTimCorey thx for your thoughts! I found myself slipping down the slope when I realized that editing the data and giving good FK constraint violation feedback ("here's why I couldn't save your record") led me to the point where I felt the db was faster in-and-out doing all those checks and saving than having the API do a pile of calls to validate fields prior to save.
I have recently had to change out an Oracle DB to a Postgresql DB for an application for one of the large pharma companies and believe me you want AS LITTLE logic as possible in your DB :)
First of all, I want to say thank you for sharing all your videos, I am learning so much with your courses. I want to ask you a question, My class sqlDataAccess implements the interface IsqlDataAccess, and for that reason, the constructor of the class sqlDataAccess initializes the object ISqlDataAccess public SqlData(ISqlDataAccess db) { this.db = db; } for that reason, I can't use the statement using(sqlDataAccess db = new sqlDataAccess()) inside my methods, and I don't know how to do it, please, I appreciate any help.
From the Dapper side of things, it is no different than a normal query - you return a set of data. You just need a model that matches that set. From the SQL side, you just need to know how to properly do joins. That I covered some in the Tournament Tracker project as well as in my SQL Databases from Start to Finish course on iamtimcorey.com
I would suggest that your dispose does a Rollback if the transaction hadn't been committed. ie, explicit commit, implicit rollback. You could also start the transaction implicitly since you have separate SaveWithTransaction methods. Also, not too familiar with dapper, but adding the rows in a loop is making a call to sql in every iteration. Is it not possible to push the list (batch insert) with Dapper somehow other than using the table value param?
I am following the typical pattern for using statements, which is why it commits by default. I debated on that a bit but in the end, I defaulted to what is probably the assumption. As for starting it with the SaveWithTransaction method, that wouldn't work well since the LoadWithTransaction method might be called first. As for Dapper making each call one by one, that is how all data access/ORMs make calls to stored procedures unless, as you pointed out, we used a TVP. In theory you could call an insert with many values attached but that wouldn't save you anything really.
@@IAmTimCorey I was comparing to the TransactionScope which will rollback if you don't explicitly call Complete. Which makes sense... assuming an error occurs in your using..... saves you from having to do a try...catch at that level. But, to each his own.
Hi Tim, when I ran this after adding the transaction code. I got an error saying it was already commited. This is due to the explicit then implicit call to CommitTransaction. I fixed this by setting _transaction and _connection to null after the commit and close calls.
@@IAmTimCorey Just to add here, I just had the exact same problem following the video to the point... I think the issue here is, that _transaction is actually never null and "?" is checking for that object to be null and as far as I can see only the properties of _transaction object are "nulled"... Solved it for now by explicitly setting _transaction to null after commit: public void CommitTransaction() { _transaction?.Commit(); _transaction = null; _connection?.Close(); }
Hi Tim. Thanks for all of the videos, you're a great teacher! I have a question and hope you or someone else who sees this can help. I have a transaction that's in a stored procedure in the ms sql server (not in c#). I call it from c# and it works perfectly, but I can't figure out how to get a notification in case of a rollback. It does its thing in the sql server and I can see the result in case if commits (both in c# and sql server), but I have no idea how to get a message box if it rolls back.
What would you say about having StartTransaction accept a lambda / delegate that would basically setup the connection start transaction and pass the transaction ref to the lambda ? After the lambda is done we could clean up in StartTransaction - i am thinking template method pattern / aspect oriented programming - that way the actual load / save wouldn't need to worry about clean up, and we should be able to limit code duplication for setup . Also can we use JSON instead of TVP? I am a noob when it comes to T-SQL. I've not used C# for a decade, but with .NET Core and your wonderful content, i am considering it for a couple projects. Thank you so much.
Good questions. I would have to map it out to see exactly what you mean (having a hard time visualizing) but the big thing for me is that we don't bubble up data access code into higher layers. I don't want anything caring about database "stuff" beyond the bottom layer. Even telling it to open a transaction is pushing it for me. As for passing JSON, that would be a performance issue and it is something that only more modern versions of SQL even support.
Hey Tim, I had recently an interview where I didn't had enough experience anyways and they asked me about garbage collector and dispose method and what happens behind the scenes, can you make something similar by your hand or just address the subject? I tried to explain on my own junior terms but they didn't liked it, I think they wanted some deep answer only a professional would know. I can google it, but I think you can make it easier to understand :) Also Happy Hollidays.
I can add that suggestion to the list. In general, Dispose safely closes out a resource. Each method that implements IDisposable determines what shutdown tasks need to go in the Dispose method. That method gets called either manually or when the object is instantiated as part of a using statement and that using statement concludes. The garbage collector cleans up any memory that is no longer needed when the system needs memory. It cleans out unused variables and references when they go out of scope. However, it isn't an instantaneous thing. The GC does its work when it determines the system needs the memory.
@@IAmTimCorey Yea I used the "using" and the interface IDisposable example and I said something like, they are freeing the memory, and he asked how does that happen and I didn't know what to say, he said I only know how to use them but I don't understand the process and I should learn more, maybe he wanted to see if I know how to create an IDisposable mysef or at least the logic from scratch. Or maybe is just what you said but I didn't used the proper language since I am unexperienced. Thank you for reading the comment and give your expertise, means a lot :) Real teacher that can handle hundrents of comments when a real teacher can't handle 15 people in a class.
I am not sure if this is the appropriate video to ask this question., but are you using the repository pattern for data access in the TimCo series? Is it easy to switch from Dapper to another ORM?
How to protect DB .MDF & .LDF files from re-attache to another computer server or getting open by my client's to prevent checking DB tables, schema or any other objects ?
Hi Tim, I'm not sure I understand the reason behind using transactions in c#, maybe you can help me with that I understand that we don't use TVP because the inserts would be small for this application in the real world, but why is that a reason to not use transactions inside the procedure? even with the current parameters (not TVP) we could still use transaction in SP It feels like TVP and SP vs C# transactions are 2 unrelated issues or there's something I don't get. Also, the few articles i found regarding this (pros/cons about using it in c# or in sp) mentioned the use of distributed transactions when multiple data sources are used at once, but for single database use, transactions are better used in sql Thank you
The data is coming from C# so there is a second data source. The user is the second data source (they create the order). The reason why we use a transaction is because we have one parent record (the order) and then multiple child records (the order details). So, the only way to send all of that data to one stored procedure would be to create a TVP. Otherwise, we have to call the order detail stored procedure multiple times to enter all of the rows. That is why we use a transaction. We can send the order information (the summary) and insert each detail record. If anything were to happen halfway through, we would want to roll back. The transaction in C# code will allow us to do that.
Tim is a great lecturer, and this may be off track because I watched this single video from the series, but if the theme is Dapper, he should only speak about Dapper. I lost 10min in the beginning just looking at VCS explanations. SRP, Tim... Despite that, great lectures!
Yeah, this is a video in a series, not an individual tutorial. The goal isn't to teach Dapper. I do that here th-cam.com/video/Et2khGnrIqc/w-d-xo.html and here th-cam.com/video/eKkh5Xm0OlU/w-d-xo.html Instead, this is on how to implement Dapper into our application design (and actually quite a bit more). What we are doing is building a complete application using the knowledge we already have. Basically, think of this series as implementing what we know. Now I do work hard to show context around anything I teach, though. The reason is that just watching an isolated tutorial is not good enough. You will have a hard time putting that isolated piece into the "real world" since it has to connect to real things. That's what I teach: how to connect it, what the best practices are, what pitfalls to avoid, etc.
Hi Tim, Thank you so much for this series of videos, it helps me to improve my C# programming skills. I just have this issue I remove the sql.CommitTransaction(); at the bottom of the foreach (var item in details) because when using statement call the Dispose method it throws an error (This sqltransaction has completed; it is no longer usable), it that ok?
Good morning? I have some questions. 1. using () { ... } means whatever object created in the bracket calls its Dispose() at the end of curly brace? Just that? Or everything that are created in the using curly braces are disposed? What if I create object that doesn’t implemented IDisposable? What i understand is a=b where a. using(var someObj = new SomeClass() { ... } b. var someObj = new SomeClass(); ... someObj.Dispose(); 2. Does transaction.Commit(); destroy the transaction object at the end of method? Same with the connection. I don’t need to do something like transaction=null, connection=null? 3. You called transaction.Commit() implicitly and explicitly. Doesn’t that affect your application or database? IMO you’d do like this(below) because you already called commit explicitly and explained reason why you are doing that. Dispose() { _transaction.Close(); // if it has Close method. Or whatever method that will closes the transaction. _transaction=null; _connection.Close(); _connection=null; } Calling commit on the dispose looks unconfortable. Setting nulls depends on the answer of second question.
For question 1, using is a bit better because it calls that dispose even if the code crashes. For 2 and 3, I'll be doing a bug fix video on Friday (hopefully) that will clarify that a bit. We need to set the object to null and close it only once.
I have a question. You said a person should not do most transactions in c#. However, i'm not sure how else a person can do transactions without c#. I've done database development for many years and I always did transactions directly in vb.net before i learned c#.
I did not see my solution in the comments so I'll share it here for everyone who is interested: The SqlDataAccess class is doing two things now: isolated acces and transactions. So I created and used the SqlDataAccessTransaction class which only handles transactions. I start the transaction in the constructor, and commit in the Dispose method, so you don't have to do that explicitly. Another thing I changed is that I pass the connectionStringName in the constructor and cache the connectionString that I read from config. Then you don't have it to pass it in the Load/Save methods anymore... For this I added a base class for both SqlDataAccessand SqlDataAccessTransaction to hold the connectionString.
Hi Tim, I am confused why you did not get that error, which I got: In the using Block of SqlDataAccess (in SaleData.cs) you commit at the end the transaction. But because of using 'using' syntax at the end Dispose() will be called, where is another commit, which causes an Exception then, because the transaction has already commited ... So I deleted the Commit all in the using block and now it works.
spSale_Insert I have been hung up on this for the past couple of days, cross-referenced other lessons. It has a select @Id (47:48) at the end for the newly inserted Id, later corrected in another video. Maybe could have been: output inserted.Id, it doesn't seem to matter, because it doesn't seem to have been used. Instead another round trip was made to the DB to get it. Can that be done in one Dapper exec call when passing the stored proc the object? Or does it require mapping all the parameters in order to declare Id as output? Thanks...
You are correct, he didn't use it. You can create a transaction method that returns an integer so it makes the insert and then returns the ID. See below: public int SaveDataInTransactionWithOutPut(string storedProcedure, T parameters, string OutputParameterName) { var parametersNew = new DynamicParameters(parameters); int outputID; parametersNew.Add($"{OutputParameterName}", dbType: DbType.Int32, direction: ParameterDirection.Output); _connection.Execute(storedProcedure, parametersNew, commandType: CommandType.StoredProcedure, transaction: _transaction); outputID = parametersNew.Get($"{OutputParameterName}"); return outputID; }
Hi, Tim Thank you for the great video. I am thinking to add a register user view to register new user. Then write aspnetusers Id to dbo.user table. Any suggestion? Thanks.
hi tim i have a question so am using mvvm like u and i have a datagridview i want to implement a search textbox to search from the datagrid so my question is whats the best i can work with or do to get a proper result thank u for helping ur the best
@@IAmTimCorey good can y do a video of difference between Dapper and Linq, it will be helpfull thanks for all your videos its a good source of informations and updates in C#
@@IAmTimCorey I took 70h worth of C# courses and used it for about 2000h with Unity engine, now im looking into a way to make a transaction app, i know its going to be hard thats a given I just can't seem to find the right tutorials (from start to finish) I would love to be able to make the app in Unity engine but I dont think thats possible I just need a point in the right direction 😅 and when you linked your 7h course in the video it said the page could not be loaded
Hmmmm.. In regard to the logging off of the user and you wanting to use LoggedInUserModel as a Model, I think it would have been better to have an APIHelper.LogInUser(Emailaddress, Password) which in itself calls the APIHelper.Authenticate and APIHelper.GetUserLoggedInInfo. And remove the UserLoggedInModel.LogOutUser function, and moved the implementation to the APIHelper.LogOutUser so it also clears the user info (persionally I'd rather have it as a separate function APIHelper.ClearUserLoggedInInfo and call it from APIHelper.LogOutUser.. And in reality I'd rather have the User object handle all that stuff for me, so I only have to call _user.LogIn(EmailAddress, Password) and later _user.LogOut (as I don't need to know anything about how to log in or out, now you have to explicitly use the API Client to do that. Also I just now noticed you using inconsistent names, If you use LogIn you should use LogOut, if you use LogOn you should use LogOff. And why did "connection = new SqlConnection(connectionString)" work when using "using" as it seems to "Open" it by itself, but not if we instanciate it ourselfs regularly? And ofcourse the Commit in the Dispose is a bug, but I think you'll handle it in your next video. To be honest, I'd probably would have liked it to see you actually use the SQL transaction way as it is much safer, even for 'simple' stuff..
That connection opening does work on its own but you don't want to do it that way. Wrapping it in the using just ensures it will be disposed. That's all. As for putting the transaction into SQL instead of C#, to do that we would need to build in some added complexity. We would need to create a TVP in SQL in order to send all of the data over to SQL in one call. That's not ideal, and something I discussed briefly on how I didn't want to add that complexity yet. We can in the future if we want with very little effort.
The real world is complicated. In this particular instance, if you don't do it this way, you might end up with partial data in your database. That isn't ideal. In fact, that could be very bad. So, we added this safety mechanism to ensure we protected our data and our database. It is kind of like saying that putting guardrails along dangerous curves isn't important because cars drive on the road, not off the edge. That is true until there is an accident and then you are glad you spent the time adding in the safety mechanism.
@@IAmTimCorey I don't think you know what you are doing-especially with database technology. From the basics of table design right thru the shebang. This whole lesson would have been cleaner and so much easier to understand and implement had you wrote it in SQL. You seem to swing from brilliant insight to pure dumb and winging it on others ignorance. What is worse, then you defend yourself with the above. It is not as though implementing stored procedures with transactions is hard.
@@michaelcollins4075 If you have done a better course of training videos than Tim has then please post a link to your 100 hours+ of free content. I agree there are easier ways to do some things and as programmers we are all stronger or more experienced in some aspects of software engineering than others but on the whole this is by far the best, most comprehensive start-to-finish course on the internet. I guarantee if you posted up a similar amount of your own work and had 30k programmers looking over your shoulder there would be a number of them who could pick holes in your weak areas, so why be rude to someone who has put so much effort into this project, maybe you need to take a course on humility.
I'm really thankful for this example, I had a job a few years ago where I had to clean up a lot of these failures to close etc. And I learned this exact pattern as a way to solve that leak issue. This is perhaps the best and most succinct explanation of it I've seen in 10 years. Well done, Tim.
I appreciate the kind words.
Great video again Tim, I know there is another "bug fix" video after this so what I'm about to say you might have fixed in there. Just a couple of issues I spotted:
1. When you commit the transaction you also close the connection, this leads to an error in your using statement on the dispose, the error basically tells you that you don't have a transaction open so there is nothing to commit again. - not sure why your code doesn't stop on the lines when it throws an error (maybe an option I have turned on).
2. I would suggest NOT to close the connection when you commit the transaction - the commitTransaction() method should do one thing. commit. I would have the close connection on the dispose only. This way the user (developer) can open & close several transaction within the using statement. (hope this makes sense).
Yep, I fixed issue 1 in the follow-up video. As for not closing the connection, interesting idea. However, I would rather have the person explicitly open a new connection when they needed it again rather than risk having a long-running connection open to my database. Opening a connection isn't a big deal performance-wise. Leaving one open is a big deal.
Yep! The transaction seems to be tried twice although failing the second time. Definitely a great debugging exercise for learning quite a lot like finding the Dispose procedure being hit under any condition despite of not being not explicitly referred to! Thanks again for the Treasure Tim! Can't wait to start the next video!
You can fix it by checking _connection.State equals to ConnectionState.Closed before CommitTransaction at Dispose.
Tim Corey you are the best tutor. You make very effective series for the beginners and also for them who can not afford to buy online course. i am big fan of you And learn a lots from your tutorial
I appreciate the kind words.
Hey Tim will you ever make a series of MVC Project
MVC is coming to this project soon.
@@IAmTimCorey thanks
Awesome video, I immediately became a Patreon, your videos are as valuable as college lectures... Thank You !
You are welcome.
Wonderful implementation, I was just scrolling trough your video's in seach for yet another useful video and you didn't disapoint. Transactions are important guys 'm kay.
Glad it was helpful!
This course is awesome Tim and it has been so helpful to me. Thank you so much for making these!
You're very welcome!
One small tip: Instead of adding a temporary variable to look at the exception, when you are in the catch block with the debugger, you can look in the locals window to the variable $exception.
Thanks for sharing!
Thanks for this course, Tim. It's really great that your share knowledge like this on YT.
You are welcome.
When someone asked me who was my teacher, I told them, Tim Corey)
Awesome!
What I usually use to rename stuff like property names or method names is crtl+r+r key combination, which also renames pretty much everything including fields in interfaces. Never had any problems with it even in kinda complex solution I'm working on in my workplace so I recommend it to everyone here :)
Thanks for sharing!
Hi Tim, suggestion here. I think adding the functionality of entering an amount after checking out, for displaying the change is part of a basic register too.
I've debated how I would go about that. We don't really have a "check out" screen where they enter cash/credit and the amount. Have to think that one through to see if it is necessary for version 1. Right now, it is up to the cashier to indicate that they got paid, whether they use a totally separate credit card machine or a cash drawer and made change manually.
No problem Tim. I just thought that it's a better experience if the system had that feature.
Anyways, thank you so much for this tutorials. I learned a lot of things from this series and will learn a lot more. You're the best!
Do you ever use Unit of Work and have a transaction distributed over multiple repositories? I use aggregates a lot and keep messing with the pattern I use and would be very interested to hear your take on that
I am really liking this series. Being out of work during Covid has given me the opportunity to investigate a number of technologies I have not used before.
One suggestion though, would it be better to create a second SQLDataAccess class specifically for transactions to avoid the possibility of SaveData(...) being called mid transaction..
That could work.
Thanks! Dapper is being used more and more in the industry.
You are welcome.
This playlist is truly a life Saver I’ve been following your content for some time and it’s just amazing. I’m trying to implement tab control using the caliburn micro I’m running through some issues do have any course on that?
No I don't. Sorry.
Hi Tim, it does not clear out the cart for you, because for you the same thing happens as for me and what RC mentioned in the comment. When you try to commit the transaction the second time (through Dispose), it gives an exception telling it is already commited. For me the debugger breaked at the exception but it seems like because of your exception settings it did not break, that's why you didn't notice it.
Good catch. I'll probably do a video on that this week. The fix is fairly simple.
Hi Tim Thanks for this example, pretty good. One thing in running mode if transaction is rolled back how user will know that my data is not recorded. can we provide any message at this state?
Regards
At 35:08 on line 80, we roll back the transaction in the catch but then continue the exception (the throw). That will allow us to handle the exception in the UI, allowing the user to know there was a problem with saving the data.
Tim, I used for transaction System.Transactions class:
using (var transactionScope = new TransactionScope())
{
SqlDataAccess sql = new SqlDataAccess();
....... save //sale
....... save //sale detail
transactionScope.Complete();
}
This is bad or worse then your manner?
I checked and I throw exception after first save, all is rollback, so it works.
I didn't want to move my transaction code into the next layer up. I wanted the data access class to handle that.
OK, right. You right. Better keeps this kind of code lower for better separation and more flexible code.
Thank you.
Thanks Tim, you are the best!
You are welcome.
Nice video again. I implemented the transaction mechanism using SQL transaction in the stored procedure. I used TVP to pass the sale details to the stored procedure. If anyone is interested to know the details, please let me know.
Thanks for the tip and offering to share
Hi Tim , I am huge fan of you , Thank you very much.
You are most welcome. Thanks for watching.
I have changed to dapper now it’s great dapper dapper plus and dapper contrib. Amazing utils that helps with the short comings of entity framework.
I am glad you are enjoying it.
IAmTimCorey I have used it a few years now was excited when they said efcore would get server side filtering but that it would not be coming to winforms core i don’t get why not
Hi Tim, Thanks for all the videos you put out! A question (that maybe has no right or easy answer) is, how far "out of the mainstream recommendation" is it to encapsulate "a fair bit of logic" in stored procedures? I see you've done it here, and this is definitely the route I chose, but I have a few peers who keep reminding me that this is "Doing It Wrong(TM)" and that the API should handle the business logic. The book I'm using as guide (to use PostreSQL sprocs) was written by a founder of Skype. That approach worked well (until MS bought it!) so it seems to me that sprocs, done carefully, can work swimmingly. But I always feel I'm breaking industry standards in doing so. Your thoughts appreciated, as you have more interaction with other experts.
My philosophy is to let SQL do what it does best and let C# do what it does best. That means I let SQL look up data, sort data, filter data, and even protect data. Then I let C# break the data apart, work on it, etc. That means I don't put a TON of logic in my stored procedures but I also am ok with some.
@@IAmTimCorey thx for your thoughts! I found myself slipping down the slope when I realized that editing the data and giving good FK constraint violation feedback ("here's why I couldn't save your record") led me to the point where I felt the db was faster in-and-out doing all those checks and saving than having the API do a pile of calls to validate fields prior to save.
I have recently had to change out an Oracle DB to a Postgresql DB for an application for one of the large pharma companies and believe me you want AS LITTLE logic as possible in your DB :)
First of all, I want to say thank you for sharing all your videos, I am learning so much with your courses. I want to ask you a question, My class sqlDataAccess implements the interface IsqlDataAccess, and for that reason, the constructor of the class sqlDataAccess initializes the object ISqlDataAccess
public SqlData(ISqlDataAccess db)
{
this.db = db;
}
for that reason, I can't use the statement using(sqlDataAccess db = new sqlDataAccess()) inside my methods, and I don't know how to do it, please, I appreciate any help.
Change the name of one of your "db" objects. They are two different things.
Can you show how we can execute store procedure with multiple join with dapper and populate the model.
Thanks
From the Dapper side of things, it is no different than a normal query - you return a set of data. You just need a model that matches that set. From the SQL side, you just need to know how to properly do joins. That I covered some in the Tournament Tracker project as well as in my SQL Databases from Start to Finish course on iamtimcorey.com
I would suggest that your dispose does a Rollback if the transaction hadn't been committed. ie, explicit commit, implicit rollback.
You could also start the transaction implicitly since you have separate SaveWithTransaction methods.
Also, not too familiar with dapper, but adding the rows in a loop is making a call to sql in every iteration. Is it not possible to push the list (batch insert) with Dapper somehow other than using the table value param?
I am following the typical pattern for using statements, which is why it commits by default. I debated on that a bit but in the end, I defaulted to what is probably the assumption. As for starting it with the SaveWithTransaction method, that wouldn't work well since the LoadWithTransaction method might be called first. As for Dapper making each call one by one, that is how all data access/ORMs make calls to stored procedures unless, as you pointed out, we used a TVP. In theory you could call an insert with many values attached but that wouldn't save you anything really.
@@IAmTimCorey I was comparing to the TransactionScope which will rollback if you don't explicitly call Complete. Which makes sense... assuming an error occurs in your using..... saves you from having to do a try...catch at that level.
But, to each his own.
Hi Tim, can't you just wrap the code in "using (TransactionScope scope = new TransactionScope())" block?
We could but then that brings data access code into the business logic.
thank you for your efforts! Nice video :).
You are welcome.
One question, Can't we inject sqlconnection instead of wrapping it in a using block everytime, what are the pros and cons of this approach
Hi Tim, when I ran this after adding the transaction code. I got an error saying it was already commited. This is due to the explicit then implicit call to CommitTransaction. I fixed this by setting _transaction and _connection to null after the commit and close calls.
Interesting. Did you use the question mark after the item name?
@@IAmTimCorey Yes I did.
I had the same problem
@@IAmTimCorey Just to add here, I just had the exact same problem following the video to the point... I think the issue here is, that _transaction is actually never null and "?" is checking for that object to be null and as far as I can see only the properties of _transaction object are "nulled"... Solved it for now by explicitly setting _transaction to null after commit:
public void CommitTransaction()
{
_transaction?.Commit();
_transaction = null;
_connection?.Close();
}
@@IAmTimCorey Nevermind :-) I see you fixed it in next video. Great job and thanks!
Thank you Tim
You are welcome
I noticed that all of your methods in SqlDataAccess are all synchronous. Are asynchronous methods ok to use with transactions?
Hi Tim. Thanks for all of the videos, you're a great teacher!
I have a question and hope you or someone else who sees this can help. I have a transaction that's in a stored procedure in the ms sql server (not in c#). I call it from c# and it works perfectly, but I can't figure out how to get a notification in case of a rollback. It does its thing in the sql server and I can see the result in case if commits (both in c# and sql server), but I have no idea how to get a message box if it rolls back.
Never mind, found the answer in your next video 😄. Thanks again, you're a lifesaver!
Thanks for sticking with the series!
Thank you very much .
You are welcome.
What would you say about having StartTransaction accept a lambda / delegate that would basically setup the connection start transaction and pass the transaction ref to the lambda ? After the lambda is done we could clean up in StartTransaction - i am thinking template method pattern / aspect oriented programming - that way the actual load / save wouldn't need to worry about clean up, and we should be able to limit code duplication for setup . Also can we use JSON instead of TVP? I am a noob when it comes to T-SQL. I've not used C# for a decade, but with .NET Core and your wonderful content, i am considering it for a couple projects. Thank you so much.
Good questions. I would have to map it out to see exactly what you mean (having a hard time visualizing) but the big thing for me is that we don't bubble up data access code into higher layers. I don't want anything caring about database "stuff" beyond the bottom layer. Even telling it to open a transaction is pushing it for me. As for passing JSON, that would be a performance issue and it is something that only more modern versions of SQL even support.
While Corona is around ... I will keep learning ...
It would be good to keep going after as well.
Hey Tim, I had recently an interview where I didn't had enough experience anyways and they asked me about garbage collector and dispose method and what happens behind the scenes, can you make something similar by your hand or just address the subject? I tried to explain on my own junior terms but they didn't liked it, I think they wanted some deep answer only a professional would know. I can google it, but I think you can make it easier to understand :) Also Happy Hollidays.
I can add that suggestion to the list. In general, Dispose safely closes out a resource. Each method that implements IDisposable determines what shutdown tasks need to go in the Dispose method. That method gets called either manually or when the object is instantiated as part of a using statement and that using statement concludes. The garbage collector cleans up any memory that is no longer needed when the system needs memory. It cleans out unused variables and references when they go out of scope. However, it isn't an instantaneous thing. The GC does its work when it determines the system needs the memory.
@@IAmTimCorey Yea I used the "using" and the interface IDisposable example and I said something like, they are freeing the memory, and he asked how does that happen and I didn't know what to say, he said I only know how to use them but I don't understand the process and I should learn more, maybe he wanted to see if I know how to create an IDisposable mysef or at least the logic from scratch. Or maybe is just what you said but I didn't used the proper language since I am unexperienced. Thank you for reading the comment and give your expertise, means a lot :) Real teacher that can handle hundrents of comments when a real teacher can't handle 15 people in a class.
I am not sure if this is the appropriate video to ask this question., but are you using the repository pattern for data access in the TimCo series? Is it easy to switch from Dapper to another ORM?
Yes, the data is all wrapped in an abstraction so that it can be replaced with a different data access layer if you wanted.
How to protect DB .MDF & .LDF files from re-attache to another computer server or getting open by my client's to prevent checking DB tables, schema or any other objects ?
You apply database security at least, and maybe encryption at rest.
Hi Tim,
I'm not sure I understand the reason behind using transactions in c#, maybe you can help me with that
I understand that we don't use TVP because the inserts would be small for this application in the real world, but
why is that a reason to not use transactions inside the procedure? even with the current parameters (not TVP) we could still use transaction in SP
It feels like TVP and SP vs C# transactions are 2 unrelated issues or there's something I don't get.
Also, the few articles i found regarding this (pros/cons about using it in c# or in sp) mentioned the use of distributed transactions when multiple data sources are used at once, but for single database use, transactions are better used in sql
Thank you
The data is coming from C# so there is a second data source. The user is the second data source (they create the order). The reason why we use a transaction is because we have one parent record (the order) and then multiple child records (the order details). So, the only way to send all of that data to one stored procedure would be to create a TVP. Otherwise, we have to call the order detail stored procedure multiple times to enter all of the rows. That is why we use a transaction. We can send the order information (the summary) and insert each detail record. If anything were to happen halfway through, we would want to roll back. The transaction in C# code will allow us to do that.
Tim is a great lecturer, and this may be off track because I watched this single video from the series, but if the theme is Dapper, he should only speak about Dapper. I lost 10min in the beginning just looking at VCS explanations. SRP, Tim... Despite that, great lectures!
Yeah, this is a video in a series, not an individual tutorial. The goal isn't to teach Dapper. I do that here th-cam.com/video/Et2khGnrIqc/w-d-xo.html and here th-cam.com/video/eKkh5Xm0OlU/w-d-xo.html Instead, this is on how to implement Dapper into our application design (and actually quite a bit more). What we are doing is building a complete application using the knowledge we already have. Basically, think of this series as implementing what we know. Now I do work hard to show context around anything I teach, though. The reason is that just watching an isolated tutorial is not good enough. You will have a hard time putting that isolated piece into the "real world" since it has to connect to real things. That's what I teach: how to connect it, what the best practices are, what pitfalls to avoid, etc.
Hi Tim,
Thank you so much for this series of videos, it helps me to improve my C# programming skills.
I just have this issue
I remove the sql.CommitTransaction(); at the bottom of the foreach (var item in details) because when using statement call the Dispose method it throws an error (This sqltransaction has completed; it is no longer usable), it that ok?
I see the solution on the next video :D
Great! Glad you found it.
@@ranielgarcia8685 Thank you friend, glad I wasn't the only one with this issue haha!
Good morning?
I have some questions.
1. using () { ... } means whatever object created in the bracket calls its Dispose() at the end of curly brace? Just that? Or everything that are created in the using curly braces are disposed? What if I create object that doesn’t implemented IDisposable? What i understand is a=b where
a. using(var someObj = new SomeClass()
{ ... }
b. var someObj = new SomeClass();
...
someObj.Dispose();
2. Does transaction.Commit(); destroy the transaction object at the end of method? Same with the connection. I don’t need to do something like transaction=null, connection=null?
3. You called transaction.Commit() implicitly and explicitly. Doesn’t that affect your application or database? IMO you’d do like this(below) because you already called commit explicitly and explained reason why you are doing that.
Dispose() {
_transaction.Close(); // if it has Close method. Or whatever method that will closes the transaction.
_transaction=null;
_connection.Close();
_connection=null;
}
Calling commit on the dispose looks unconfortable. Setting nulls depends on the answer of second question.
For question 1, using is a bit better because it calls that dispose even if the code crashes. For 2 and 3, I'll be doing a bug fix video on Friday (hopefully) that will clarify that a bit. We need to set the object to null and close it only once.
I have a question. You said a person should not do most transactions in c#. However, i'm not sure how else a person can do transactions without c#. I've done database development for many years and I always did transactions directly in vb.net before i learned c#.
You would do the transaction in SQL itself. That is the preferred method.
I did not see my solution in the comments so I'll share it here for everyone who is interested:
The SqlDataAccess class is doing two things now: isolated acces and transactions. So I created and used the SqlDataAccessTransaction class which only handles transactions. I start the transaction in the constructor, and commit in the Dispose method, so you don't have to do that explicitly.
Another thing I changed is that I pass the connectionStringName in the constructor and cache the connectionString that I read from config. Then you don't have it to pass it in the Load/Save methods anymore... For this I added a base class for both SqlDataAccessand SqlDataAccessTransaction to hold the connectionString.
Excellent, thank you for sharing.
Hi Tim, I am confused why you did not get that error, which I got:
In the using Block of SqlDataAccess (in SaleData.cs) you commit at the end the transaction.
But because of using 'using' syntax at the end Dispose() will be called, where is another commit, which causes an Exception then, because the transaction has already commited ...
So I deleted the Commit all in the using block and now it works.
ok, just saw, that you already talked about that :-)
I am glad you figured it out.
spSale_Insert I have been hung up on this for the past couple of days, cross-referenced other lessons. It has a select @Id (47:48) at the end for the newly inserted Id, later corrected in another video. Maybe could have been: output inserted.Id, it doesn't seem to matter, because it doesn't seem to have been used. Instead another round trip was made to the DB to get it. Can that be done in one Dapper exec call when passing the stored proc the object? Or does it require mapping all the parameters in order to declare Id as output? Thanks...
You are correct, he didn't use it. You can create a transaction method that returns an integer so it makes the insert and then returns the ID. See below:
public int SaveDataInTransactionWithOutPut(string storedProcedure, T parameters, string OutputParameterName)
{
var parametersNew = new DynamicParameters(parameters);
int outputID;
parametersNew.Add($"{OutputParameterName}", dbType: DbType.Int32, direction: ParameterDirection.Output);
_connection.Execute(storedProcedure, parametersNew,
commandType: CommandType.StoredProcedure, transaction: _transaction);
outputID = parametersNew.Get($"{OutputParameterName}");
return outputID;
}
Hi, Tim
Thank you for the great video. I am thinking to add a register user view to register new user. Then write aspnetusers Id to dbo.user table. Any suggestion?
Thanks.
You can definitely do that.
hi tim i have a question so am using mvvm like u and i have a datagridview i want to implement a search textbox to search from the datagrid so my question is whats the best i can work with or do to get a proper result thank u for helping ur the best
The search box is just another property and then its value should filter your list (which will filter the datagridview).
@@IAmTimCorey i didnt fully understand are you saying that that the searchbox will filter my bindablecollection ??? thank you
I take it your talking about “Ess-Cue-Ell” server? Not a server hosting the next chapter of a famous movie!
Yep, SQL Server. There are two common pronunciations: Ess Cue Ell and sequel. I use the latter usually.
one bug i found here if i add items to the cart and then log out and log in again the cart wont get clear.
Yep, thanks. A few people spotted that one. We will fix it soon.
why not using Linq to Entities or Linq to Sql it can handl all that in one transaction ?
I'm using Dapper here for my data access so I continued to do so. I'm not a big fan of mixing data access methods inside this small an application.
@@IAmTimCorey good can y do a video of difference between Dapper and Linq, it will be helpfull thanks for all your videos its a good source of informations and updates in C#
Please i need help, can someone point me to a tuturial series to learn how to transfer money? This video is too advanced
How to transfer money? Using C#? That is going to be complex, since there is a lot of security that needs to surround money transfer.
@@IAmTimCorey I took 70h worth of C# courses and used it for about 2000h with Unity engine, now im looking into a way to make a transaction app, i know its going to be hard thats a given
I just can't seem to find the right tutorials (from start to finish)
I would love to be able to make the app in Unity engine but I dont think thats possible
I just need a point in the right direction 😅 and when you linked your 7h course in the video it said the page could not be loaded
Hmmmm.. In regard to the logging off of the user and you wanting to use LoggedInUserModel as a Model, I think it would have been better to have an APIHelper.LogInUser(Emailaddress, Password) which in itself calls the APIHelper.Authenticate and APIHelper.GetUserLoggedInInfo. And remove the UserLoggedInModel.LogOutUser function, and moved the implementation to the APIHelper.LogOutUser so it also clears the user info (persionally I'd rather have it as a separate function APIHelper.ClearUserLoggedInInfo and call it from APIHelper.LogOutUser..
And in reality I'd rather have the User object handle all that stuff for me, so I only have to call _user.LogIn(EmailAddress, Password) and later _user.LogOut (as I don't need to know anything about how to log in or out, now you have to explicitly use the API Client to do that.
Also I just now noticed you using inconsistent names, If you use LogIn you should use LogOut, if you use LogOn you should use LogOff.
And why did "connection = new SqlConnection(connectionString)" work when using "using" as it seems to "Open" it by itself, but not if we instanciate it ourselfs regularly?
And ofcourse the Commit in the Dispose is a bug, but I think you'll handle it in your next video.
To be honest, I'd probably would have liked it to see you actually use the SQL transaction way as it is much safer, even for 'simple' stuff..
That connection opening does work on its own but you don't want to do it that way. Wrapping it in the using just ensures it will be disposed. That's all. As for putting the transaction into SQL instead of C#, to do that we would need to build in some added complexity. We would need to create a TVP in SQL in order to send all of the data over to SQL in one call. That's not ideal, and something I discussed briefly on how I didn't want to add that complexity yet. We can in the future if we want with very little effort.
Why would you do anything so complicated and you get paid for this too, unbelievable? I must have got you on a good day sometime back.
The real world is complicated. In this particular instance, if you don't do it this way, you might end up with partial data in your database. That isn't ideal. In fact, that could be very bad. So, we added this safety mechanism to ensure we protected our data and our database. It is kind of like saying that putting guardrails along dangerous curves isn't important because cars drive on the road, not off the edge. That is true until there is an accident and then you are glad you spent the time adding in the safety mechanism.
@@IAmTimCorey I don't think you know what you are doing-especially with database technology. From the basics of table design right thru the shebang. This whole lesson would have been cleaner and so much easier to understand and implement had you wrote it in SQL. You seem to swing from brilliant insight to pure dumb and winging it on others ignorance. What is worse, then you defend yourself with the above. It is not as though implementing stored procedures with transactions is hard.
@@michaelcollins4075 If you have done a better course of training videos than Tim has then please post a link to your 100 hours+ of free content. I agree there are easier ways to do some things and as programmers we are all stronger or more experienced in some aspects of software engineering than others but on the whole this is by far the best, most comprehensive start-to-finish course on the internet. I guarantee if you posted up a similar amount of your own work and had 30k programmers looking over your shoulder there would be a number of them who could pick holes in your weak areas, so why be rude to someone who has put so much effort into this project, maybe you need to take a course on humility.
Naming is hard.
Yup, try naming a kid!