Solved

10774: transaction: database or .net framework?

Posted on 2014-11-29
4
334 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 79

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 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

21 Experts available now in Live!

Get 1:1 Help Now