CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ADO Article » ADO.Net 2.0 Features - Connection Statistics and TransactionScope Object   You are not logged in.
Search
 

Technologies
 

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: (Login)
Be first to rate
this article.
No of Comments: 6
Print this article.
Category: ADO.Net

Subscribe to our feed!

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.

 

Implementation

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

            con.StatisticsEnabled = true;

            con.Open();

//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),

 

NetworkServerTime:15

BytesReceived:6066

UnpreparedExecs:2

SumResultSets:1

SelectCount:1

PreparedExecs:0

ConnectionTime:15

ExecutionTime:15

Prepares:0

BuffersSent:2

SelectRows:42

ServerRoundtrips:2

CursorOpens:0

Transactions:1

BytesSent:468

BuffersReceived:2

IduRows:1

IduCount:1

 


Recent Articles

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())

{

//Code

}

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.

 

Implementation

  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))

        {

            try

            {

                connection1.Open();                                         

               

                SqlCommand command1 = new SqlCommand(commandText1, connection1);

                command1.ExecuteNonQuery();

                 

                using (SqlConnection connection2 = new SqlConnection(connectString2))

                    try

                    { 

                        SqlCommand command2 = new SqlCommand(commandText2, connection2);

                        command2.ExecuteNonQuery();                      

                    }

                    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.

        scope.Complete();

    }

 

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.

 

Cause

The MSDTC service is not started on the server.

 

Resolution

Go to services and start Distributed Transaction Coordinator service.

Steps

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

Else

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

 

Conclusion

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 CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments
    Wowza, problem solve
    Wowza, problem solved like it never hadnpeep. http://watcyzlycc.com [url=http://fhfueuae.com]fhfueuae[/url] [link=http://muqykzwpwk.com]muqykzwpwk[/link]
    Your post is a timel
    Your post is a timely <a href="http://rbmfal.com">coouirbttinn</a> to the debate
    Thiinnkg like that s
    Thiinnkg like that shows an expert at work http://zanhfqsta.com [url=http://wtubhzp.com]wtubhzp[/url] [link=http://ddtctvqy.com]ddtctvqy[/link]
    Gee <a href="http://
    Gee <a href="http://zbtvrgtbdqw.com">wiisrkell,</a> that's such a great post!
    Well put, sir, well
    Well put, sir, well put. I'll certlinay make note of that.
    generics
    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