Exploring SqlConnection In ADO.NET

Introduction

SqlConnection in ADO.NET represents a connection to a SQL Server database. In this article, we will learn in depth about SqlConnection class including what is SqlConnection, create a connection, and use SqlConnection in C# and how to use ADO.NET classes to work with SQL Server, MS Access, MySQL and Oracle databases.

What is a connection in ADO.NET?

ADO.NET connection is an object that provides database connectivity and the entry point to a database. When the connection of an object is instantiated, the constructor takes a connection string that contains the information about the database server, server type, database name, connection type, and database user credentials. Once the connection string is passed and the connection object is created, you can establish a connection with the database. A connection string is usually stored in the web.config file or app.config file of an application.

What namespace or provider is used for connection class?

ADO.NET provides connection to multiple providers. Each provider has a functionality to connect with different database. Here is a list of data providers in ADO.NET and their purpose.

Data Provider for SQL Server (System.Data.SqlClient).
Data Provider for MS ACCESS (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 must import a data provider namespace, by placing the following in the beginning your code module.

For SqlClient .NET data provider namespace import code:

Using System.Data.SqlClient

Similarly, for OLE DB, ODBC, OracleClient .NET data provides namespace import code:

Using System.Data.OleDb
Using System.Data.Odbc
Using System.Data.OracleClient

Now, we have to declare a connection string, which is usually defined in the App.Config or Web Config file, so its availalbe in your application. The typical entry of a connection string is written below:

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

Now, if your connection string is pointing to SQL Server database like “EmployeeDataBase”, here os the connection string with

Establish connection string in Web Config file using the below code:

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

Now, we create a SqlConnection. We can also pass a connection string direct in the constructor.

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

In the connection string:

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.

If the connection string is stored in the config file with its name Constr”, we can read as following:

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

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

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

Similarly, we can use OleDbConnection, OdbcConnection, OracleConnection classes to establish connections with MS Access, MySQL, and Oracle databases.

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.

The following code snippet uses some of these useful properties and methods of SqlConnection class.

// 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 code snippet, first of all, we declare a SqlConnection class. It is defined in the System.Data.SqlClient namespace. Notice, when connection instantiation is required, we will instantiate with a connection string. Now, connection is successfully established and you open a connection by calling the Open() method of the SqlConnection 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. You can perform any operation like (select, insert, update delete) by query or procedure with SqlCommend class. Learn how to work with Command objects in ADO.NET.

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. The finally block helps guarantee that a certain piece of code will be executed no matter what. Database connections are costly resources so we must close them.

If you want to learn more about try catch finally, visit Try..catch..finally in C#

Connection Pooling

As I said earlier, database connection resources are limited and costly. Everytime an application needs to execute a quert, a database connection is required. Instead of creating and releasing connection objects, we can use ADO.NET connection pooling, that is a pool of database connection resources availalble in-memory on-demand. Once connections are done using, they go back to the pool to be available to other applications.

You can turn off pooling for a specific connection by setting pooling=”false” key-value pair in your connection string.

The SqlConnection class also includes two methods, 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.