Post
Ask Question

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: 
  1. Using System.Data.SqlClient   
Similarly, for OLE DB, ODBC, OracleClient .NET data provides namespace import code:
  1. Using System.Data.OleDb   
  2. Using System.Data.Odbc   
  3. 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:
  1. <connectionStrings>  
  2.         <add name="" connectionString="" providerName=""/>  
  3. </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:
  1. <connectionStrings>  
  2.         <add name="Constr" connectionString="Data Source= RaviSERVER\RaviSERVER;Initial Catalog= EmployeeDataBase;User ID=sa,pwd=sa123" providerName="System.Data.SqlClient"/>  
  3. </connectionStrings>  
Now, we create a SqlConnection. We can also pass a connection string direct in the constructor.
  1. 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:
  1. 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.
  1. Private SqlConnection _Con = null;  
  2. _Con = new SqlConnection (_ConStr);  
Similarly, we can use OleDbConnection, OdbcConnection, OracleConnection classes to establish connections with MS Access, MySQL, and Oracle databases.
  1. Private OleDbConnaction _Con=null;  
  2. _Con = new OleDbConnaction (_ConStr);  
  3.   
  4. Private OdbcConnaction _Con=null;  
  5. _Con = new OdbcConnaction (_ConStr);  
  6.   
  7. Private OracleConnaction _Con=null;  
  8. _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.

  1. // add only useful and relevant namespace  
  2.   
  3. using System;  
  4. using System.Data.SqlClient;  
  5. using System.Data;  
  6.   
  7. namespace ConsoleCRM  
  8. {  
  9.     class Ravi  
  10.     {  
  11.         //only declare the Ado classes hare not instantiate   
  12.   
  13.         SqlConnection _Con = null;  
  14.         SqlCommand _cmd = null;  
  15.         SqlDataReader rd = null;  
  16.         SqlTransaction _Transation;  
  17.   
  18.         static void Main(string[] args)  
  19.         {  
  20.             //Now create object of Ravi class and call method to this object  
  21.   
  22.             Ravi _Ravi = new Ravi();  
  23.             _Ravi.GetResult();  
  24.             Console.ReadLine();  
  25.         }  
  26.         private void GetResult()  
  27.         {  
  28.             //Now instantiate connection with connection string  
  29.            // in connection string single space is not supported then we add @ sign with connection string  
  30.             _Con = new SqlConnection(@"Data Source=RaviSERVER\RaviSERVER;Initial Catalog=EmployeeDatabase;User ID=sa");  
  31. ////we can also read connection string from WebConfig file 
  32. //string _StrCon = System.Configuration.ConfigurationManager.ConnectionStrings ["Constr"].ConnectionString;   
  33. //_Con = new SqlConnection (_StrCon);  
  34.   
  35.             //Pass the connection with command object
  36.             _cmd = new SqlCommand("select * from Product", _Con);  
  37.             //Now check if current connection is closed then further its open   
  38.             try  
  39.             {  
  40.                 if (_Con.State == ConnectionState.Closed)//ConnectionState is enum its    comes under System.Data name space   
  41.                 {  
  42.                     _Con.Open();  
  43.                 }  
  44.                 //Use the connection and get result from database  
  45.                 //Now start to current transaction  
  46.                 _Con.BeginTransaction();  
  47.                 rd = _cmd.ExecuteReader();  
  48.                 // Read ProductId from each record  
  49.                 while (rd.Read())  
  50.                 {  
  51.                     Console.WriteLine(rd["PrductId"]);  
  52.                 }  
  53.   
  54.                 //Commit current transaction  
  55.                 _Transation.Commit();  
  56.             }  
  57.             catch (SqlException Ex)  
  58.             {  
  59.                 //RollBack Transaction after any conflict occur  
  60.                 _Transation.Rollback();  
  61.             } 
  62.             // Now check current connection its open or close if connection is open then finally it's closed.  
  63.             finally  
  64.             {  
  65.                 if (_Con.State == ConnectionState.Open)  
  66.                     _Con.Close();  
  67.             }  
  68.         }  
  69.     }  
  70. }  
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:
  1. <configuration>  
  2.     <system.web>  
  3.         <compilation debug="true" targetFramework="4.0" />  
  4.     </system.web>  
  5.     <connectionStrings>  
  6.         <clear/>  ,
  7.         <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;" />  
  8.     </connectionStrings>  
  9. </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.


Similar Articles