Connection object in ADO.NET - ProgramIdea

Connection class in ADO.NET

Connection class is used to connect database. For example, SqlConnection object is used to connect SQL Server database and OleDbConnection object is used to connect OleDb database.

Properties of Connection Class


Gets or sets connection string information that used to open connection to the database. Connection string is combination of information like: data source, database, user id, password, provider, integrated security etc. Below example showing a simple connection string that contains data source, database, user id and password.

Basic connection string:

Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;

There are multiple ways to define connection string.

  1. Using SqlConnection class

using System.Data.SqlClient;

SqlConnection con = new 
("Data Source=JITESH-PC; Initial Catalog=db_Test; User Id=sa; Password=12345;");

  2. Using SqlConnection class object

using System.Data.SqlClient;

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;";

  3. Using Web.Config



    <add name="con" connectionString=" Data Source=JITESH-PC;Initial  Catalog=db_Test; User Id=sa;Password=12345;Integrated Security=True " providerName="System.Data.SqlClient" />


</configuration >

using System.Data.SqlClient;

using System.Configuration;

SqlConnection con =new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());


Gets the time that wait while establish a connection to the database before terminating. Default time is 15 seconds.


Gets the name of database.


Gets the name of the instance of the database server.


Gets the version information of the database.


Indicate the state of the connection object.

Open - Indicate that the connection is open.

Closed - Indicate that the connection is closed.

Connecting - Indicate that the connection object is connecting to the data source.

Executing - Indicate that the connection object is executing a command.

Fetching - Indicating that connection object is retrieving data.

Broken - Indicate that the connection to the data source is broken. This can occur only after the connection has been opened.


Methods of Connection Class


Opens a database connection with specified connection string.


Closes the connection to the database. This is used to close any open database connection.


Returns schema information for the data source.

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");



DataTable dt = con.GetSchema();    



Start a database transaction.

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");




SqlTransaction transaction = con.BeginTransaction();



    SqlCommand cmd;


    // Create first SqlCommand object

    cmd = new SqlCommand("update tblTest set Name='Jitesh' where id=1", con, transaction);


    // Create second SqlCommand object

    cmd = new SqlCommand("update tblTest set Points=100 where id=1", con, transaction);





catch (Exception Ex)