Home » Articles


Sponsored links

CodeDigest Navigation

Technology News
No News Feeds available at this time.

Community News
No News Feeds available at this time.
ADO.Net 2.0 Features – PART 4

By Satheesh babu
Posted On Jul 06,2008
Article Rating:
Be first to rate
this article.
No of Comments: 6
Category: ADO.Net
Print this article.

ADO.Net 2.0 Features – PART 4


The PART IV of this article ADO.Net 2.0 Features will cover another 2 new features in ADO.Net 2.0,

·          Connection Statistics

·          TransactionScope Object


Connection statistics

SqlConnection object in ADO.Net 2.0 is packed with additional handy feature to get the statistics data of the object. We can obtain ServerRoundtrips, BytesSent, ExecutionTime, ConnectionTime, etc taken for a database operation. This data will help us to fine tune the performance of the underlying data access layer operation. By default, this feature is not enabled for a connection object. We can enable it by setting StatisticsEnabled property of connection object to true. The RetrieveStatistics() method of the SqlConnection exposes all the statistics values through a Dictonary object.


When this feature is switched on it will hit the performance to some extent and it should be disabled once we done with the fine tuning or analysis.



SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL Connection String"].ConnectionString);

            con.StatisticsEnabled = true;


//DB Code goes here

 IDictionary dict = con.RetrieveStatistics();         


            foreach (DictionaryEntry entry in dict)


                Response.Write(entry.Key.ToString() + ":" + entry.Value.ToString() + "<BR>");          




The output will be (For a select query),





















TransactionScope Object

TransactionScope is one of the new objects that are packed with ADO.Net 2.0 which is used for distributed transaction. With this object we can maintain the transaction across multiple databases or servers with very less effort. Using TransactionScope object we can maintain implicit transaction where transactions are automatically maintained. Implicit transaction are maintained between TransactionScope object instantiation and TransactionScope.Dispose() method. So, the best way of implementing TransactionScope object will be with the use of “using” statement. If we use this construct we no need to worry of calling Dispose explicitly.


using (TransactionScope scope = new TransactionScope())




If there are any exception in the using block the transaction will be automatically rolled back else we should call Complete() method to commit the transaction. The best way is to use Complete() method as the last statement in using block. Calling this method indicates the operations are consistent and it is ready to be committed. The real commit will take place when Dispose() method is called and indicates the distributed transaction is complete and it cannot be used after that. However, failing to call this method will abort the transaction because the transaction manager will interpret that there is a system failure or exception.



  string connectString1 = "Data Source=BABULIVES;Initial Catalog=master;Integrated Security=True";

    string connectString2 = "Data Source=BABULIVES;Initial Catalog=tempdb;Integrated Security=True";

    string commandText1 = "Delete from test";

    string commandText2 = "Delete from test";


    using (TransactionScope scope = new TransactionScope())


        using (SqlConnection connection1 = new SqlConnection(connectString1))






                SqlCommand command1 = new SqlCommand(commandText1, connection1);



                using (SqlConnection connection2 = new SqlConnection(connectString2))



                        SqlCommand command2 = new SqlCommand(commandText2, connection2);



                    catch (Exception ex)



                        Response.Write("Exception Message2: {0}", ex.Message);



            catch (Exception ex)


                Response.Write("Exception Message1: {0}", ex.Message);




        // The Complete method commits the transaction. If an exception has been thrown,

        // Complete is not called and the transaction is rolled back.




In the above code, when the first connection (connection1) is opened the transaction will be a lightweight transaction. On successful completion and moving to next connection (connection2) the transaction will be a full distributed transaction.


Error and Resolution

When we use the distributed transaction we may get the following error,

MSDTC on server 'SERVERNAME' is unavailable.



The MSDTC service is not started on the server.



Go to services and start Distributed Transaction Coordinator service.


CONTROL PANEL à ADMINISTRATIVE TOOLS à SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK on it and select Start.


Type Services.msc on RUN command. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) à Start.



These two new features, Connection Statistics and distributed transaction have made the ADO.Net a feature rich component of .Netframework 2.0. With Connection Statistics it is now possible for the developers to improve the performance of Data Access Layer to some extent. The TransactionScope object has made the management of distributed transaction simple.

Njoy Coding!!!

Similar Articles
You can contribute to CodeDiget.Com:
Donate to
Article Feedback
Wowza, problem solve
Wowza, problem solved like it never hadnpeep. [url=]fhfueuae[/url] [link=]muqykzwpwk[/link]
Your post is a timel
Your post is a timely <a href="">coouirbttinn</a> to the debate
Thiinnkg like that s
Thiinnkg like that shows an expert at work [url=]wtubhzp[/url] [link=]ddtctvqy[/link]
Gee <a href="http://
Gee <a href="">wiisrkell,</a> that's such a great post!
Well put, sir, well
Well put, sir, well put. I'll certlinay make note of that.
The System.Collection.Generic namespace contains a number of pre-build classes that represent common types, including Following:

Link List – A doubly Link List
List – An array that is dynamically sized as needed
Queue – Represents the first in first out collection objects
Stack – Represent s the Last in First out collection objects