Exploring Connection In ADO.NET

Introduction

In this article, we will learn in depth about connection to object; i.e., what is connection to an object and how can we make use of connection of an object in ADO.NET, how to use and play with connection class in SQL Server, MS Access, MySQL and Oracle database.

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

Connection to an ADO.NET Database

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

The using statement should be written first in your code.

Now, we have to declare a connection string place, which is used in Web Config file, so that it exists in your application. Now, the connection code is written below-

<connectionStrings>
        <add name="" connectionString="" providerName=""/>
</connectionStrings>

Now, if your connection string is pointing to SQL Server database like “EmployeeDataBase.mdb”.

Establish connection string in Web Config file, given below-

<connectionStrings>
        <add name="Constr" connectionString="Data                       Source=RaviSERVER\RaviSERVER;Initial Catalog= EmployeeDataBase;User ID=sa,pwd=sa123" providerName="System.Data.SqlClient"/>
</connectionStrings>

It is also used most of the time. You just declare and instantiate SQLConnection, all at the same time, as shown below-

SqlConnection _Con = new SqlConnection("Data Source= (local); Initial Catalog= EmployeeDataBase; User ID=User Name; pwd=User Password" Integrated Security=”True”);

Data Source: This identifies the Server name, which could be the local machine, machine domain name or IP address

Initial Catalog: This identifies the database name.

Integrated Security: When you have started database authentication login with Windows authentication, Integrated Security specifies Integrated Security=”True” in connection string, else when you have started the database authentication login with Server authentication Integrated Security specifies Integrated Security=”false” in the connection string

User Id: Name of the user configured in SQL Server.

Password: Password matching SQL Server User ID.

Now, we have read this connection string by name="Constr” on page from Web Config file.

String _ConStr = System.Configuration.ConfigurationManager.ConnectionStrings ["Constr"].Connection String;

Now, let us see, how we can use SQLConnection class to establish connection string with SQL database.

Private SqlConnection _Con=null;
_Con = new SqlConnection (_ConStr);

Similarly, now we can use OleDbConnection, OdbcConnection, OracleConnection class to establish the connection string with MS Access, My SQL, Oracle database, given below-

Private OleDbConnaction _Con=null;
_Con = new OleDbConnaction (_ConStr);

Private OdbcConnaction _Con=null;
_Con = new OdbcConnaction (_ConStr);

Private OracleConnaction _Con=null;
_Con = new OracleConnaction (_ConStr);

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.

Some important method and property of SQLconnection class, we have used all the time in our code is we used and describe here is according to the code snippet, given below-

// add only useful and relevant namespace

using System;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleCRM
{
    class Ravi
    {
        //only declare the Ado classes hare not instantiate

        SqlConnection _Con = null;
        SqlCommand _cmd = null;
        SqlDataReader rd = null;
        SqlTransaction _Transation;

        static void Main(string[] args)
        {
            //Now create object of Ravi class and call method to this object

            Ravi _Ravi = new Ravi();
            _Ravi.GetResult();
            Console.ReadLine();
        }
        private void GetResult()
        {
            //Now instantiate connection with connection string

           // in connection string single space is not supported then we add @ sign                                                                                                                                                          with connection string

            _Con = new SqlConnection(@"Data Source=RaviSERVER\RaviSERVER;Initial Catalog=EmployeeDatabase;User ID=sa");

////we can also read connection string from WebConfig file

//string _StrCon = System.Configuration.ConfigurationManager.ConnectionStrings ["Constr"].ConnectionString;

//_Con = new SqlConnection (_StrCon);


            //Pass the connection with command object
            _cmd = new SqlCommand("select * from Product", _Con);
            //Now check if current connection is closed then further its open
            try
            {
                if (_Con.State == ConnectionState.Closed)//ConnectionState is enum its    comes under System.Data name space
                {
                    _Con.Open();
                }
                //Use the connection and get result from database

                //Now start to current transaction

                _Con.BeginTransaction();

                rd = _cmd.ExecuteReader();

                // Read ProductId from each record

                while (rd.Read())
                {
                    Console.WriteLine(rd["PrductId"]);
                }

                //Commit current transaction

                _Transation.Commit();
            }
            catch (SqlException Ex)
            {
                //RollBack Transaction after any conflict occur

                _Transation.Rollback();
            }

            // Now check current connection its open or close if connection is open then finally it's closed.

            finally
            {
                if (_Con.State == ConnectionState.Open)
                    _Con.Close();
            }
        }
    }
}

As shown in the above snippet, first of all, we declare a sqlconnection class. It comes under system.Data.SqlClient name space. Notice, when connection instantiation is required, we will instantiate with the connection string. Now, connection is successfully established and you open a connection by calling the Open () method of the SQL Connection object. In case, any operation on connection is performing that connection will not yet open and will generate exception. You must open connection before using it.

Notice, in case your current connection is already open, it must be closed before opening the current connection.
Thus, after connection opens, you pass connection with SQLCommand Class (which is described in latter session). You can perform any operation like (select, insert, update delete) by query or procedure with SQLcommend class.

Finally, your transition is successfully completed and you will close the connection by calling the Close () method of the SQLConnection object is called in final blocks and we ensure that the connection is not null before close.

Notice, we wrapped ADO.NET code in a try/finally block. As described in latter session of the C# Tutorial, finally blocks help guarantee that a certain piece of code will be executed, regardless of whether or not an exception is generated. Since connections are scarce system resources, you will want to make sure, they are closed in finally blocks.

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.

A connection string in web.Config file with connection pooling is given below-

<configuration>
    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
    <connectionStrings>
        <clear/>  ,
        <add name="Constr" connectionString="Data                                                  Source=RaviSERVER\RaviSERVER;Initial Catalog=EmployeeDatabase;Integrated Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />
    </connectionStrings>
</configuration>

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.