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

ConnectionString

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
SqlConnection ("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

</connectionStrings>

</configuration >

using System.Data.SqlClient;

using System.Configuration;

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

ConnectionTimeout

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

Database

Gets the name of database.

DataSource

Gets the name of the instance of the database server.

ServerVersion

Gets the version information of the database.

State

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.

Demo:

Methods of Connection Class

Open()

Opens a database connection with specified connection string.

Close()

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

GetSchema()

Returns schema information for the data source.

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

con.Open();

DataTable dt = con.GetSchema();

con.Close();

BeginTransaction()

Start a database transaction.

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

con.Open();

SqlTransaction transaction = con.BeginTransaction();

try

{

SqlCommand cmd;

// Create first SqlCommand object

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

cmd.ExecuteNonQuery();

// Create second SqlCommand object

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

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch (Exception Ex)

{

transaction.Rollback();

}

con.Close();