Connection Object & Properties in ASP.Net C#

The Connection object represents a connection to a data source. When you instantiate a Connection, you pass a connection string to the constructor, which contains information about the location and security credentials required for connecting to the data source.

SqlConnection that represents a connection to a Microsoft SQL Server database named Pubs that is located on the local machine:

SqlConnection con = new SqlConnection(“Data Source=localhost;Integrated Security=True;Initial Catalog=Pubs”);

There are a number of ways to write a connection string that does exactly the same thing.

SqlConnection con = new SqlConnection(“Data Source=localhost;Initial Catalog=Pubs;Persist Security Info=True;User ID=sa;;Password=sa;Min Pool Size=5;Max Pool Size=500;Connect Timeout=500;Pooling=true;MultipleActiveResultSets=False;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio"”);

The SqlConnection object supports the following properties and methods related to gathering statistics:

• StatisticsEnabled : Enables you to turn on statistics gathering.
• RetrieveStatistics() : Enables you to retrieve statistics represented with an IDictionary collection.
• ResetStatistics() : Resets all statistics to 0.

You can call the RetrieveStatistics() method multiple times on the same SqlConnection. Each time you call the method, you get another snapshot of the Connection statistics.

List of the statistics that you can gather:

• BuffersReceived : Returns the number of TDS packets received.
• BuffersSent : Returns the number of TDS packets sent.
• BytesReceived : Returns the number of bytes received.
• BytesSent : Returns the number of bytes sent.
• ConnectionTime : Returns the total amount of time that the connection has been opened.
• CursorsOpen : Returns the number of cursors opened.
• ExecutionTime : Returns the connection execution time in milliseconds.
• IduCount : Returns the number of INSERT, DELETE, and UPDATE commands executed.
• IduRows : Returns the number of rows modified by INSERT, DELETE, and UPDATE commands.
• NetworkServerTime : Returns the amount of time spent waiting for a reply from the database server.
• PreparedExecs : Returns the number of prepared commands executed.
• Prepares : Returns the number of statements prepared.
• SelectCount : Returns the number of SELECT commands executed.
• SelectRows : Returns the number of rows selected.
• ServerRoundtrips : Returns the number of commands sent to the database that received a reply.
• SumResultSets : Returns the number of resultsets retrieved.
• Transactions : Returns the number of user transactions created.
• UnpreparedExecs : Returns the number of unprepared commands executed.