Browse DevX
Sign up for e-mail newsletters from DevX


ADO.NET Best Practices : Page 4

ADO.NET is a powerful toolbox but it's not a software magic wand. Learn about common best practices for using three key element of any data access strategy: connections, security, and transactions.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Transaction Models
Enterprise distributed applications can hardly work without transactions: the logical entities that group multiple operations into a single monolithic step. The transacted operations can be local to a particular database or span over a variety of resource managers deployed on various nodes of the network. There are two ways to incorporate transaction management into your data access code—manual and automatic transactions.
A large part of the systems that require user-level authorization could be more efficiently rewritten to use roles and service identities. A "trusted model" based on roles and using a fixed number of accounts alleviates pooling issues and keeps scalability and security high.
Manual transactions mean that the programmer is responsible for writing any code that configures and uses the transaction support provided by either ADO.NET or the SQL dialect of the database. Instead, automatic (or COM+) transactions add declarative attributes to your .NET classes that specify the transactional requirements of your objects at run time. This model allows you to easily configure multiple components to perform work within the same transaction. Both techniques can be used to perform local or distributed transactions but the automatic transaction model greatly simplifies distributed transaction processing.

Another key thing to bear in mind is that transactions are computationally expensive. As a result, you should always ask yourself if you really need transactions. If you do, you should try to minimize the duration of transactions to minimize locks on any involved database. Transactions are not needed for individual SQL statements because SQL Server automatically runs each statement as an individual transaction. Manual local transactions are always significantly faster because they do not require interaction with the Distributed Transaction Coordinator (DTC). This statement holds true even if you're using automatic transactions against a local single resource manager with no occurrence of interprocess communication with the DTC. In ADO.NET, you manually create a transaction by calling the BeginTransaction method on the connection object of the specific managed data provider. The method returns a data source-specific transaction object with Commit and Rollback methods, an isolation level (ReadCommitted by default), plus any extra features that the underlying data provider supports. For example, the SqlTransactionobject—the transaction object for SQL Server 2000—supports named save points for partial rollbacks. The OleDbConnectionobject, instead, supports nested transactions. The isolation level remains in effect until explicitly changed. It can be changed at any time though. If changed during a transaction, the server is expected to apply the new locking level to all statements remaining. In the SQL Server .NET data provider, the BeginTransaction method is implemented as a wrapper around the T-SQL BEGIN TRAN statement. The method and the statement are therefore equivalent. However, using the ADO.NET method is recommended because it adds more flexibility to your code and abstracts you from the details of the underlying SQL dialect.

Automatic transactions simplify the programming model because they do not require that you explicitly begin a new transaction or explicitly commit or abort the transaction. What the programmer is required to do is declare what type of transactional support is expected for a given component (i.e., ASP.NET page, Web service method, .NET Framework class). The most significant advantage of automatic transactions is that they can work in conjunction with the DTC. In this way, you declaratively configure multiple components to perform tasks that comprise of a single transaction. Automatic transactions rely on the distributed transaction support features provided by the COM+ environment. Only serviced components can use automatic transactions. Typically, a serviced component is a class created from the base ServicedComponentclass. However, ASP.NET pages that set the Transaction attribute on the @Page directive, and Web service methods with the TransactionOption attribute set, are equally considered as serviced components.

Manual and automatic transactions should be considered as two distinct, mutually exclusive technologies. When you are performing a transaction against a single database, use manual transactions. Use automatic transactions when a single transaction spans multiple remote databases or when a single transaction touches multiple resource managers (i.e., a database and a MSMQ resource manager). In any case, you should avoid mixing the two transaction models. If you're not particularly concerned about performance, consider using automatic transactions even against a single database because it results in simpler (but slower) code. Automatic transactions make it easy for multiple components to perform operations that are part of the same transaction. Effective database programming is the outcome of many effective practices applied to a common layer of services. In .NET, the ADO.NET object model provides the common API that all types of applications use—be it Windows Forms, ASP.NET pages, or Web services. ADO.NET, though, is not a black box that takes input data and returns result sets. ADO.NET is rather a toolbox made of several objects designed to let programmers build more powerful enterprise applications more easily.

To build an effective data access layer you must combine together several objects in the ADO.NET model—connection, transaction, and commands. These object must be configured and parameterized and must work against a data source such as SQL Server or Oracle. In doing so, you hit the security context of the database and must interact with it. But security is too far large a topic to limit it to the database. Enterprise distributed applications have security built into the foundation and not just (or not necessarily) insulated in the data tier. In this context, even an apparently insignificant element such as the connection string can have key importance.

Dino Esposito is Wintellect's ADO.NET and XML expert, and a trainer and consultant based in Rome, Italy. A speaker at many industry events including TechEd WinSummit, and DevConnections, Dino is the author of "Applied XML Programming with the .NET Framework" and "Programming ASP.NET," both for Microsoft Press. Reach him at dinoes@wintellect.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date