Connection object in ADO.NET is the basic object that enables you to connect with database. Connection object contains the information about connection string. It always works with other objects as Command, or DataReader object.
It does not fetch or update data but it helps to do this task. It does not execute SQL queries, and it does not contain the results of SQL queries.
Connection object supports many properties and methods to perform database connection with data source.
Following are the important connection objects available in ADO.NET
- SqlConnection
- OleDbConnection
- OdbcConnection
- OracleConnection
Example
using System;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
string conString = "Data Source=(local);Initial Catalog=Employee;Integrated Security=True";
protected void Button1_Click(object sender, EventArgs e)
{
// Creating the object of SqlConnection class
SqlConnection conObject = new SqlConnection(conString);
try
{
conObject.Open();
if (conObject.State == ConnectionState.Open)
Response.Write("Database Connection is Open");
}
catch(SqlException sqlexception)
{
Response.Write("ERROR ::" + sqlexception.Message);
}
catch(Exception ex)
{
Response.Write("ERROR ::"+ex.Message);
}
finally
{
conObject.Close();
}
}
}
In connection string, we can write or Server or Address or Addr or Network Address in place of Data Source attribute. To specify the database we can use Database or the Initial Catalog attribute.
You can provide yes, no, true, SSPI and false value to Integrated Security attribute of connection string. If Integrated Security = false then User ID, and Password must be specified in the connection string and if true then current Windows account credentials are used for authentication. SSPI is equivalent to value True.
In connection string, the Data Source attribute holds the name of the server to which one wishes to connect, Initial Catalog is the name of the database, and the Integrated Security property refers to the type of security to use.
In a given example the name of the database is employee and the name of server is (local). You can provide these names according to your system and database.
Important properties of connection object
- ConnectionString
- ConnectionTimeout
- Database
- DataSource
- ServerVersion
- State
Open() and Close() are two important methods of Connection object.
For understanding of above properties we have used console application.
Example
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string conString = "Data Source=(local);Initial Catalog=Employee;Integrated Security=True";
SqlConnection conObject = new SqlConnection(conString);
conObject.Open();
Console.WriteLine("Connection String :: "+conObject.ConnectionString);
Console.WriteLine("DataBase :: "+conObject.Database);
Console.WriteLine("DataSource :: " + conObject.DataSource);
Console.WriteLine("ConnectionTimeout :: " + conObject.ConnectionTimeout);
Console.WriteLine("Connection State :: " + conObject.State);
Console.WriteLine("ServerVersion :: " + conObject.ServerVersion);
Console.ReadLine();
}
}
}
Connection String :: Data Source=(local);Initial Catalog=Employee;Integrated Security=True
DataBase :: Employee
DataSource :: (local)
ConnectionTimeout :: 15
Connection State :: Open
ServerVersion :: 10.50.1600
Storing connection string in configuration file
If you did not write connection string in config file, then it may create problem. Suppose that, after deploying the project, you have to change the connection string, then you have to change the connection string in every page.
It is a good practice to store the connection string for your application in a config file rather than in every page.
In this scenario, if you have written the connection string in config file, then the required change will be done in single place (config file) and the change will be reflect automatically in every page.
<configuration>
<connectionStrings>
<add name="conString" connectionString="Data Source=(local);Initial Catalog=Employee;Integrated Security=True"/>
</connectionStrings>
</configuration>
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
protected void Button1_Click(object sender, EventArgs e)
{
string conn = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
// Creating the object of SqlConnection class
SqlConnection conObject = new SqlConnection(conn);
conObject.Open();
if (conObject.State == ConnectionState.Open)
Response.Write("Database Connection is Open");
}
By default, a connection string is enabled with connection pooling.
By default, the maximum number of pools is 100, minimum is 0.
Connection pooling is the ability of re-use your connection to the Database. This means if you enable Connection pooling in the connection object, actually you enable the re-use of the connection to more than one user.
Connection pooling
Connection pooling enables you to re-use your connection to the Database. It is an optimization technique that will increase the performance of database programming. By default connection pooling feature is enabled with connection string. The maximum number of pools is 100, and minimum is zero.
Connecting to database is time consuming and resource intensive task. Whenever user wants to connect database, connection string is parsed and it checks whether given credentials in connection stings are correct or not. Therefore it takes lots of time to open and close the connection.
To reduce the cost of opening and closing the same connection repeatedly and increasing the performance ADO.NET uses connection pooling.
