Getting Started with ADO.NET - Tutorial For Absolute Beginners

In this tutorial, you will learn about ADO.NET, its architecture along with its usage. You will also learn how to use ADO.NET with SQL Data Provider.

Introduction

ADO stands for ActiveX Data Objects. It is a Microsoft technology. Microsoft ActiveX provides a programming interface to access data from the database. ADO.NET is designed to efficiently access data, efficiently with the help of object-oriented libraries. It is automatically installed with Microsoft IIS Server. ADO.NET provides support for XML and disconnected recordsets.

A recordset is a data source which is disconnected from the database to assign the connection to some other recordset.

The architecture of ADO.NET

ADO.NET act as an adapter between data source. i.e. database and the Forms which displays and process the data. The below image can explain the architecture of ADO.NET, more appropriately.

ADO.NET Data Providers

ADO.NET has three commonly used data providers.

OleDb Data Provider
SQL Data Provider
ODBC Data Provider

Other Data providers include Oracle Data Provider and Borland Data Provider.

1. OleDb Data Provider Description

Include: Using System.data.OleDb;
API Prefix: OleDb
Description: To communicate with the Data Sources that expose an OleDb interface, i.e. Access or Excel.
Data Adapter: OleDbDataAdapter
Command Builder: OleDbCommandBuilder
Command: OleDbCommand
Reader: OleDbDataReader
Connection: OleDbConnection

2. SQL Data Provider Description

Include: Using System.data.SqlClient;
API Prefix: Sql
Description: To interact with Microsoft SQL Server.
Data Adapter: SqlDataAdapter
Command Builder: SqlCommandBuilder
Command: SqlCommand
Reader: SqlDataReader
Connection: SqlConnection

3. ODBC Data Provider Description

Include: Using System.data.Odbc;
API Prefix: Odbc
Description: To interact with Data Sources which have an ODBC interface. Normally older data bases.
Data Adapter: OdbcDataAdapter
Command Builder: OdbcCommandBuilder
Command: OdbcCommand
Reader: OdbcDataReader
Connection: OdbcConnection

4. Oracle Data Provider Description

API Prefix: Oracle
Description: To interact with Oracle Databases.

5. Borland Data Provider Description

API Prefix: Bdp
Description: It provides Generic access to many databases such as Interbase, SQL Server, IBM DB2, and Oracle.

SQL Data Provider

The SQL Data Provider is widely used with ADO.NET, it includes the following objects.

SQL Connection Object

The SQL Connection Object is used to connect to the database with the help of a connection string. SqlConnection constructor takes the connection String as parameters.

Syntax

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

1

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

Connection String

Connection string provides exact attributes of the database.

Components of Connection String

Connection String Include Data Source, Initial Catalog, Integrated Security, User ID and Password.

Authentication

The Connection String of SQL Connection Object has two types of authentication.

– Trusted Authentication

To use Trusted Authentication you must use
Integrated Security = SSPI.

Data Source = <ServerName>; Initial Catalog= <DatabaseName>; Integrated Security=SSPI

1

Data Source = <ServerName>; Initial Catalog= <DatabaseName>; Integrated Security=SSPI

– SQL Server Security Authentication

To use Integrated security you need to specify the User ID and the Password.

Data Source = <ServerName>; Initial Catalog = <DatabaseName>; User Id= UserName; Password= UserPassword

1

Data Source = <ServerName>; Initial Catalog = <DatabaseName>; User Id= UserName; Password= UserPassword
SQL Command Object

The SQL Command Object sends the command i.e. SQL Query to the database. SqlCommand takes SQL query and SqlConnection object as parameters.

Syntax

SqlCommand cmd = new SqlCommand("select ProductName from Products",conn);

1

SqlCommand cmd = new SqlCommand("select ProductName from Products",conn);

You can also create the Command object as

SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select ProductName from Products"; cmd.Connection = conn;

1
2
3

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select ProductName from Products";
cmd.Connection = conn;

SQL DataReader Object

When the command is executed then SqlDataReader is used to read the record with the help of ExecuteReader() method.

SqlCommand cmd = new SqlCommand("select ProductName from Products",conn);
SqlDataReader reader=cmd.ExecuteReader();//The reader now contains list of product name from Products table.

1 2	SqlCommand cmd = new SqlCommand("select ProductName from Products",conn); SqlDataReader reader=cmd.ExecuteReader();//The reader now contains list of product name from Products table.

CRUD SQL Example

Learn more about Database Connectivity.

Public class DbConnection {
//Try catch block is not mentioned here but it is required when you implement the program
    private SqlConnection conn;
    
    public void createConnection() { //creating connection object
        conn = new SqlConnection ("Data Source=(local);Initial Catalog=Northwind; Integrated Security=SSPI");
    } 
    //end createConnection method

    public void InsertProductRecord() {
        createConnection();
        conn.Open();
        String sqlQuery="Insert into Products(ProductName) values('Vanial')";
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        cmd.ExecuteNonQuery();
    } 
    //end InsertProductRecord

    public void selectProductRecord() {
        createConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("Select ProductName from Products", conn);
        SqlDataReader rdr = cmd.ExecuteReader();
        while(rdr.Read()) {
            Console.WriteLine(rdr[0]); //from selected row of reader it selects first column
        }
    } 
    //end selectProductRecord

    public void updateProductRecord() {
        createConnection();
        conn.Open();
        String sqlQuery="update Products set ProductName='Vanail chlorid' where ProductID=78";
        SqlCommand cmd = new SqlCommand(sqlQuery);
        cmd.Connection = conn; //passing connection as separtate variable
        cmd.ExecuteNonQuery();
    } 
    //end updateProductRecord

    public void deleteProductRecord() {
        createConnection();
        conn.Open();
        String sqlQuery="Delete from Products where ProductID=78";
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = sqlQuery;
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();
    } 
    //end deleteProductRecord

    public void numberOfRecords() {
        int count = -1;
        createConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("select count(*) from Products", conn);
        count = (int)cmd.ExecuteScalar();
    } 
    //end numberOfRecords method

} 
//end DbConnection

Public class DbConnection {

//Try catch block is not mentioned here but it is required when you implement the program

private SqlConnection conn;

public void createConnection() { //creating connection object

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

}

//end createConnection method

public void InsertProductRecord() {

createConnection();

conn.Open();

String sqlQuery="Insert into Products(ProductName) values('Vanial')";

SqlCommand cmd = new SqlCommand(sqlQuery, conn);

cmd.ExecuteNonQuery();

}

//end InsertProductRecord

public void selectProductRecord() {

createConnection();

conn.Open();

SqlCommand cmd = new SqlCommand("Select ProductName from Products", conn);

SqlDataReader rdr = cmd.ExecuteReader();

while(rdr.Read()) {

Console.WriteLine(rdr[0]); //from selected row of reader it selects first column

}

//end selectProductRecord

public void updateProductRecord() {

createConnection();

conn.Open();

String sqlQuery="update Products set ProductName='Vanail chlorid' where ProductID=78";

SqlCommand cmd = new SqlCommand(sqlQuery);

cmd.Connection = conn; //passing connection as separtate variable

cmd.ExecuteNonQuery();

}

//end updateProductRecord

public void deleteProductRecord() {

createConnection();

conn.Open();

String sqlQuery="Delete from Products where ProductID=78";

SqlCommand cmd = new SqlCommand();

cmd.CommandText = sqlQuery;

cmd.Connection = conn;

cmd.ExecuteNonQuery();

}

//end deleteProductRecord

public void numberOfRecords() {

int count = -1;

createConnection();

conn.Open();

SqlCommand cmd = new SqlCommand("select count(*) from Products", conn);

count = (int)cmd.ExecuteScalar();

}

//end numberOfRecords method

}

//end DbConnection

Hope that you like this tutorial, Stay tuned for more upcoming tutorials. Stay Blessed!

Getting Started with ADO.NET – Tutorial For Absolute Beginners

Introduction

The architecture of ADO.NET