ADO.NET Connection State

 ADO.NET Connection State 

 In this post, we learn about ado.net Connection State. The connection state help we to know the status of our connection request to the database. We can use the Connection object's State Property to get the current state of the connection to the database. The State property returns a constant from the ConnectionState enumeration.

  ConnectionState CONSTANTS:

Sr.No	CONSTANT NAME	Description
1)	Broken	The Connection is broken. This can happen after we have opened the Connection object. Then we can close the Connection and reopen it.
2)	Closed	The Connection is closed.
3)	Connecting	The Connection is establishing access to the database.
4)	Executing	The Connection is running a command.
5)	Fetching	The Connection is retrieving information from the database.
6)	Open	The Connection is open.

 To Known more about ado.net click here.

 Now here An example of using the State property would be to check if our Connection object is currently open before calling its Open() method. We might need to do that if we have a complex application and we are using a Connection object created somewhere else in the application: we might not know the current state of that Connection object and we don't want to call the Open() method on an already open Connection because that will raise an exception.

  Code: Program.cs  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;

namespace CsApp_Connection
{
    class  Program
    {
        public static void Main()
        {
            string connectionString = "data source=LearnAsp;user
                id=LearnAsp;"
            + "password=LearnAsp; Pooling=true; Max pool size=200; Min pool size=0;";

            Console.WriteLine("\tConnection State Example.");
            OracleConnection myConn = new OracleConnection(connectionString);
             Console.WriteLine("\tConnection State : "+myConn.State);
             try
            {
                // Open connection
                myConn.Open();
                 Console.WriteLine("\tConnection opened.");
                  Console.WriteLine("\tConnection State : "+myConn.State);
            }
            catch (OracleException ex)
            {
                // Display error
                Console.WriteLine("\tError: " + ex.Message + ex.StackTrace);
            }

            finally
            {
                // Close connection 
                myConn.Close();
                 Console.WriteLine("\tConnection closed.");
                 Console.WriteLine("\tConnection State : "+myConn.State);
            }
            myConn.Close();
        }
    }
} 

  Output