ADO.NET

A SqlConnection is an object,as any other C# object. we just declare and instado.netiate 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 instado.netiated 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.

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. Instado.netiate 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.

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();
}
}
}
}