ADO.NET Introduction and SqlConnection Object

ADO.NET is an object-oriented set of libraries that allows to interact with the data sources.
Data source are mostly data based but it could be an excel sheet, text file or xml file.

Data providers

1. ADO.NET provides a common way to interact with the data sources.
2. It comes in different sets of libraries and these libraries are called Data Providers.

Examples of Data providers

1. ODBC Data provider -- used in older databases
2. OleDB Data provider -  used in MS Access  or Excel
3. Oracle Data provider -  used for oracle databases
4. SQL    Data provider - used for Microsoft sql server
5. Borland Data provider - Generic access to many databases such as interbase, Sql Server, IBM DB2, and Oracle.

SqlConnection object

1. SqlConnection object is used to create a connection with the database which can be located over the LAN or over the internet.
2. Sql connection object enable other ADO.NET code to work with the database.
3. Other ADO.NET objects us connection object as parameter.

The sequence of operations occur in the llfe of sqlConnection are as follows :

1.  Instantiate the sql connection
2.  Open the connection
3.  pass the connection to other ADO.NET objects.
4.  Perform the database operations
5.  Close the connection.

Connection string

1. Connection string is a key value pairs for specifying how to make a database connection.
2. It includes the location, Name of the database and security credentials.

Connection string parameters

1. Data source - Identifies the server
2. Initial Catalog - Database name
3. 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

Integrated security

Integrated security is secure when we are on a single machine doing development.

   SqlConnection conn = new SqlConnection(
        "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

 Sql server security

Sql server security is secure when database located on a different machine, over a LAN, or over the internet. User ID and password replace the integrated security parameters

SqlConnection conn = new SqlConnection(
        "Data Source=DatabaseServer;Initial Catalog=Northwind;User ID=YourUserID;Password=YourPassword");

Example :

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

/// <summary>
/// Demonstrates how to work with SqlConnection objects
/// </summary>
class SqlConnectionDemo
{
    static void Main()
    {
        // 1. Instantiate the connection
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Northwind;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 Customers", 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();
            }
        }
    }
}