10774: transaction: database or .net framework?

Hello experts:

1. When should I use transactions in the database? (begin, commit, rollback)
2. When I use the .net framework?

Which is better?
enrique_aeoAsked:
Who is Participating?
 
Jacques Bourgeois (James Burger)PresidentCommented:
Usually for 2, but it depends for 1. You can never have a definitive answer, because there are many possible variations.

In my answer, I was giving an example where we do not only use 2 databases, but 2 different types of databases Since transactions are not handled the same way in SQL Server and Access, you cannot handle the transaction at the database level, and thus the transaction must be controlled by the code.

However, if both transactions are on different databases on the same server, you have an alternative that might not be evident at the database level: a server wide transaction. Transactions in a stored procedure are at the server level, so you could control a transaction between 2 databases in a stored procedure, as long as the 2 databases are on the same server. If they are on different servers however, you will have to do it through the framework.
0
 
David Johnson, CD, MVPOwnerCommented:
It is your choice.. for ease of coding learn entity framework this is when getting code out the door is paramount to total control or optimization for speed.  I think you meant entity framework and not .net framework. .net framework gives you api's for accessing items that otherwise require assembly or c++ code .. it allows you to program using a higher level language using constructs that are easier to again get code out the door.

I've written in C and ASM and can assure you that with todays compilers and using .net it is getting close in regards to speed but is far from close when the size of the execuatable is paramount.

Start transaction, commit transaction, rollback transaction are sql commands that rely on the sql server and give you more control but the downside is the amount of code you have to write. They both get the job done.  Good DBA's would rather everything possible was ina stored procedure but sometimes there are time and programmer ability constraints that getting working code out first is the priority.  Note: programming skills and dba skills are good to have but knowing 1 doesn't mean knowledge of the other. They are considered separate disciplines these days.  In a large organization graphics design (UI element) and database operations are done by separate groups with the programmer being the plumber that attaches everything into a workable solution.
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
You use transactions in the database when you need to perform many different operations but treat them as if they are one. If one of the operations fail, then you can simply rollback the transaction to cancel what was already done.

As an example, if you are creating a bill in one table and then store the details in another one, and then update the inventory in another one, 3 operations that need to be done in sequence, you want an easy way of cancelling the whole operation if something happens while writing the details of updating the inventory. That is a very good place to use a transaction.

You do it in the framework when you also need to perform a group of operations over many systems that manage their own transactions. Think of an operation that changes data both on SQL Server through the SqlSlient namespace and an Access database through OleDB. These could both have their own transaction going but since it is only one operation, you could use the classes in the Transaction namespace of the framework to manage them as one.
0
 
enrique_aeoAuthor Commented:
We may conclude that:

1. If the transaction affects multiple databases then you should control the programming language (.framework).

2. If it affects a single database; then control what transactions should be done in the same database
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.