002 ADO.Net Connection Class

Connection Class
                                Connection class is essential component of ADO.Net. An application connect database with the connection class.There are  two type of connection class, Sql connection and Oledb connection. SQL connection is for connecting Microsoft SQL server instance and Oledb  for Acess ,Excel etc.

                                       Instance of a connection class is created with a new operator. Connection string tell connection class to which server and database to connect. Open method create a channel between database and an application. Here is some most commonly used attribute of a connection class .

                           string ConncetionString = "Data Source= xxx.xxx.xx.xx; initial catalog=xxxx; Connect Timeout=6000; User ID= xxx;Password=xxxx;";

                          SqlConnection con = new SqlConnection(ConncetionString);

• Constructor

1. SqlConnection : New instance of an connection class is created with parameter connection string, string connection , Sql credential  integrated security to true  user id password is needed. 

• Property 

1. Connection String : Get or Set string to connect the database. 
2. Connection Timeout : Waiting time , will try to connect database, after that error is generated. 
3. Database: Get the name of a database, when the connection is open. 
4. Data sources : Get the name of the Sql server instance which to connect. 
5. Packet size : Network packet size in byte, while communicating with the data source. 
6. Server version : Get information about the version of SQL server. 
7. State : Get the state of SQL connection. Open close. 

• Method

1. Beginconnection : Starts transaction. 
2. Clear pools : All connection pool associate with the current connection is cleared. 
3. Close : Close a connection to the database. 
4. Dispose : Release all resources. 
5. Getschema : Return the schema of the data source 
6. Open : Open a connection to the database. 

Connection pool : Connection pool increase the performance of an application. Instead of creating a new connection, connection pool try to reuse existing connection to reduce overhead and time. To use connection pull , you need to do pooling true and set Max pool size and Mean pool size  properly. At the initial stage, SQL server create new connection  when connection is required. When this connection are free , connection pool put them in a queue. When request for a connection  comes, connection pool check its Queu, if there is any connection available , the existing connection assign immediately , either a new connection is established again . This makes an application is faster in performance  as  no connection established , resources and time saved,   and as it is using the existing resources, no extra overhead on the application.

1)Example of Sql Coneection to connect a Sql Server

using System;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

         class Program

         {

                static void Main(string[] args)

                 {

                          string conStr = "Data Source= ***.***.**.**; initial catalog=*****;Connect Timeout=6000; User ID= *****;Password=****;";

                         string cmd = "select top 5 * from tbl_student";

                         using (SqlConnection con = new SqlConnection(conStr))

                        {

                                SqlCommand command =new SqlCommand(cmd, con);

                                con.Open();

                                SqlDataReader reader = command.ExecuteReader();

                                DataTable schemaTable = reader.GetSchemaTable();

                                foreach (DataColumn column in schemaTable.Columns)

                                {

                                           Console.WriteLine(column.ColumnName + ":" + schemaTable.Rows[0][0]);

                                }

                              reader.Close();

                      }

                      Console.ReadKey();

             }

         }

}

2) Example of Sql Coneection to connect a Sql Server with connection pull

using System;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

            class Program

             {

                  static void Main(string[] args)

                 {

                       string conStr = "Data Source= ****.***.***.***; initial catalog=*****;Connect Timeout=6000; User ID= *****;Password=****;Min Pool Size=0;Max Pool Size=100;Pooling=true;";

                    string cmd = "select top 5 * from tbl_member";

                    using (SqlConnection con = new SqlConnection(conStr))

                    {

                             SqlCommand command =new SqlCommand(cmd, con);

                             con.Open();

                             SqlDataReader reader = command.ExecuteReader();

                            DataTable schemaTable = reader.GetSchemaTable();

                            foreach (DataColumn column in schemaTable.Columns)

                              {

                                       Console.WriteLine(column.ColumnName + ":" + schemaTable.Rows[0][0]);

                             }

                        reader.Close();

                }

                 Console.ReadKey();

            }

}

}