Solved

10774: transaction: database or .net framework?

Posted on 2014-11-29
4
329 Views
Last Modified: 2014-12-01
Hello experts:

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

Which is better?
0
Comment
Question by:enrique_aeo
  • 2
4 Comments
 
LVL 78

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 167 total points
Comment Utility
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
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 333 total points
Comment Utility
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
 

Author Comment

by:enrique_aeo
Comment Utility
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
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 333 total points
Comment Utility
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now