Exploring Connection In ADO.NET

What is connection in ADO.NET?

ADO.NET connection is an object of connected architecture just like any other C# object. When the connection of an object is instantiated , use a constructor with single string type of an argument. This argument is called the connection string. Once the connection string is passed in the connection object, by connection of an object, you can establish a connection with the database. With the help of this connection, string will identify the database and the Server name, and authentication parameters (User ID, password). Basically, a connection string is typically stored in web.config file.

What namespace or provider is used for connection class?

ADO.NET provides connection to the multiple providers but it depends on your working condition, which means, what database is used in backend and how to communicate between client to server. Thus, now the data provider is used.

• Data Provider for SQL Server (System.Data.SqlClient).
• Data Provider for MSACCESS (System.Data.OleDb).
• Data Provider for MYSQL (System.Data.Odbc).
• Data Provider for ORACLE (System.Data.OracleClient).

How to use connection class with this provider is given below-

• Connection object for SQL Server (SqlConnection).
• Connection object for MSACCESS (OleDbConnection).
• Connection object for MYSQL (OdbcConnaction).
• Connection object for ORACLE (OracleConnection).

Before working with the database, you have to add the data provider namespace, by placing the following at the start of your code module.

For SqlClient .NET data provider namespace, using-

            Using System.Data.SqlClient  

Similarly, for OLE DB, ODBC, OracleClient .NET data provides namespace, using-

            Using System.Data.OleDb 

            Using System.Data.Odbc 

            Using System.Data.OracleClient 

Properties of connection object  

Property	Description
Attributes	We can get or set attributes of the connection object.
Command Timeout	By Command time out, we can get or set number of seconds to wait, while attempting to execute a command.
Connection Timeout	By Connection time out, we can get or set number of seconds to wait for the connection to open.
Connection String	Connection string is used to establish and create connection to data source by using server name, database name, user id and password.
Cursor Location	It gets or set slocation of cursor service.
Default Database	It gets or returns default database name.
Isolation Level	It gets or returns isolation level.
Mode	By mode property, we can check provider access permission.
Provider	By this property, we can get or set provider name.
State	By this property, we can check your current connection open or close before connection opening or closing
Version	This returns the ADO version number.

Method of connection object 

Method	Description
BeginTransaction	Begin to current transaction.
Cancel	Cancel an execution.
Close	Close method is used, when any current connection is open and finally its closed after completed execution.
Open	Open method is used, if current connection is close then before execution started. First of all You have opened connection must.
Execute	By this method it is used to execute query. Like as Statement, procedure or provider provides specific text.
OpenSchema	It returns schema information from the provider about the data source.
RollBackTransation	This method invokes, whenever you cancel any changes or any conflict occurs in the current transaction, it ends the current transaction.
CommitTransation	If current transaction execution is successfully completed, it ends the current transaction.

Connection Pooling 

When establishing a connection, the database Server is a heft and high resource consuming process. If any Application needs to fire any query against any database Server we need to first establish a connection with the Server and then execute a query against that database Server.

Afterwards, it involves the overhead of the network label handshaking. ADO.NET uses a technique called connection pooling, which is minimize the cast of opening and closing connections. Connection pooling is reused in an existing active connection with the same connection string, instead of creating a new connection string. Thus, several pools exist, if different connection string asks for the connection pooling.

You can turn off pooling for a specific connection by including the pooling=”false” key-value pair in your connection string.

The sqlconnection class also includes two method ClearPool and ClearAllPools, which lets you clear its associated pool.

Connection string pooling attributes 

• Connection Lifetime - When we have specified connection lifetime sizes, it means this indicates the length of time in seconds after connection creation. Thus, by default, it is 0. This indicates that the connection will have maximum timeout. 
• Connection Reset - This property specifies the connection is reset, when removed from the pool. This is by default is true.
• Load Balance Timeout - When we have specified connection lifetime sizes, this indicates the length of time in seconds. A connection can remain idle in a connection pool before being removed.
• Max Pool Size - Maximum pool sizes indicate the maximum number of connections allowed in the pool. The default is 100.
• Min Pool Size - Maximum pool sizes indicate the minimum number of connections maintained in the pool. The default is 0.
• Pooling: - When pooling is set true, the connection is drawn from the appropriate pool, else if it is necessary, create and add to the appropriate pool. By default, it is true.
•