SQLCONNECTION OBJECT IN ADO.NET

The SQLCONNECTION OBJECT

A SqlConnection is an object,as any other C# object.  we  just declare and instantiate the SqlConnection all at the same time, as shown below:

SqlConnection con = new SqlConnection(
    "Data Source=(local);Initial Catalog=Emp;Integrated Security=sspi");

The SqlConnection object instantiated above  by using  a constructor with a single argument of type string and this argument is called a connection string. 

There are four Connection String Parameter Name:-

1)Data Source: Data Source Identifies the server and it  Could be local machine, machine domain name, or IP Address.

2)Initial Catalog: Database name

3)Integrated Security: Integrated Security set to SSPI to make connection with user's Windows login.

4)User ID:  Name of user configured in SQL Server.

5)Password: Password matching SQL Server User ID.

The following shows a connection string, using the User ID and Password parameters:
SqlConnection conn = new SqlConnection(
"Data Source=DatabaseServer;Initial Catalog=Northwind;User ID=YourUserID;Password=YourPassword");

Using a SqlConnection

The aim of creating a SqlConnection object is so we can enable other ADO.NET code to work with a database and  SqlCommand and a SqlDataAdapter take it a connection object as a parameter.  The sequence of operations occurring in the lifetime of a SqlConnection are given below:

1. Instantiate the SqlConnection.
2. Open the connection.
3. Pass the connection to other ADO.NET objects.
4. Perform database operations with the other ADO.NET objects.
5. Close the connection.

The Open() Method:

The Open() method of the Connection object establishes a connection  to the data source.,because database connections are a very expensive resource memory-wise, so we should only call the Open() method just before we're ready to retrieve the data. This ensures that the connection is not open any longer than it needs to be.

The Close() Method

After we are done retrieving data,we should call the Close() method of the Connection object. This closes the connection to the database.

Example Using  SQLCONNECTION Object

using System;
using System.Data;
using System.Data.SqlClient;

class sqlConnectivityexample
{
    static void Main()
    {
      
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Emp;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        try
        {
            // 2. Open the connection
            conn.Open();

            // 3. Pass the connection to a command object
            SqlCommand cmd = new SqlCommand("select * from emp", conn);

            //
            // 4. Use the connection
            //

            // get query results
            rdr = cmd.ExecuteReader();

            // print the CustomerID of each record
            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
        }
        finally
        {
            // close the reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // 5. Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}