SQL Transactions in C# using Dapper - A TimCo Retail Manager Video 

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).

Thanks for this course, Tim. It's really great that your share knowledge like this on YT.

This course is awesome Tim and it has been so helpful to me. Thank you so much for making these!

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.

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

Awesome video, I immediately became a Patreon, your videos are as valuable as college lectures... Thank You !

When someone asked me who was my teacher, I told them, Tim Corey)

Thanks Tim, you are the best!

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! Dapper is being used more and more in the industry.

Hi Tim , I am huge fan of you , Thank you very much.

thank you for your efforts! Nice video :).

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.

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?

Thank you very much .

Thank you Tim

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 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..

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.

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.

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 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.

While Corona is around ... I will keep learning ...

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

Can you show how we can execute store procedure with multiple join with dapper and populate the model. Thanks

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

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.

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.

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?

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.

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.

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.

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 noticed that all of your methods in SqlDataAccess are all synchronous. Are asynchronous methods ok to use with transactions?

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 ?

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.

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.

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 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?

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!

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

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...

Hi Tim, can't you just wrap the code in "using (TransactionScope scope = new TransactionScope())" block?

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.

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

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#.

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.

Naming is hard.

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.

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.

why not using Linq to Entities or Linq to Sql it can handl all that in one transaction ?

I take it your talking about “Ess-Cue-Ell” server? Not a server hosting the next chapter of a famous movie!

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..

Please i need help, can someone point me to a tuturial series to learn how to transfer money? This video is too advanced

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.