Solved

10774: transaction: database or .net framework?

Posted on 2014-11-29
4
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 80

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 167 total points
ID: 40472213
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
ID: 40472215
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
ID: 40473834
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
ID: 40473895
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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