Unit 05

Introduction

ADO.NET (ActiveX Data Objects .NET) is a data access technology provided by Microsoft as part of the .NET Framework. It is used to connect applications with various data sources such as databases and XML files.

Definition: ADO.NET is a set of classes that allows a .NET application to connect to data sources, retrieve data, insert/update/delete data (CRUD operations), and manage data efficiently.

Features of ADO.NET

ADO.NET Architecture

ADO.NET works mainly with two components:

Used to connect directly to the database. Requires an active connection. Faster but connection must remain open.

Works without continuous connection to the database. Stores data in memory.

Advantages of ADO.NET

• High performance and scalability
• Supports multiple data sources
• Enables offline data processing
• Easy data manipulation and integration with XML

Introduction

The ADO.NET Object Model defines the structure used to access and manage data in .NET applications. It provides a set of classes under the System.Data namespace to interact with data sources efficiently. It is divided into two parts: .NET Data Providers and DataSet.

A DataSet is an in-memory representation of data that allows working without an active database connection.

Working of ADO.NET Object Model

Introduction

ADO.NET supports two types of data access approaches — Connected and Disconnected Architecture — which define how an application interacts with a database.

Connected Architecture Flow

Disconnected Architecture Flow

Comparison Table

Introduction

In ADO.NET, DataAdapter and DataSet are used to implement disconnected architecture. They allow applications to retrieve data, work with it in memory, and update the database later.

DataAdapter acts as a bridge between the database and DataSet. It executes SQL commands to fetch and save data.

DataSet is an in-memory collection of data. It can hold multiple tables, supports relations, filtering, and sorting, and allows offline data manipulation.

Working of DataAdapter and DataSet

Example — Fetching Data using OLEDB

Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        ' Step 1: Create connection
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=student.accdb")

        ' Step 2: Create DataAdapter
        Dim da As New OleDbDataAdapter("SELECT * FROM Student", con)

        ' Step 3: Create DataSet
        Dim ds As New DataSet()

        ' Step 4: Fill DataSet
        da.Fill(ds, "Student")

        ' Step 5: Display first row, Name column
        MsgBox(ds.Tables("Student").Rows(0)("Name").ToString())

    End Sub
End Class

Updating Data

' Modify data in DataSet
ds.Tables("Student").Rows(0)("Name") = "Updated Name"

' Push changes back to database
da.Update(ds, "Student")

Introduction

The Command object in ADO.NET is used to execute SQL queries or commands on a data source. It acts as a bridge between the application and the database for performing operations. It works together with the Connection object.

Use of Command Object

Types of Command Execution

Methods of Command Object

Dim cmd As New OleDbCommand("SELECT * FROM Student", con)
Dim dr As OleDbDataReader = cmd.ExecuteReader()

Dim cmd As New OleDbCommand("UPDATE Student SET Name='Ali' WHERE ID=1", con)
cmd.ExecuteNonQuery()

Dim cmd As New OleDbCommand("SELECT COUNT(*) FROM Student", con)
Dim count As Integer = cmd.ExecuteScalar()

Introduction

In ADO.NET, DataReader is used to retrieve data from a database in a connected architecture. It provides a fast and efficient way to read data in a forward-only and read-only manner.

DataReader Overview

Steps for Database Connectivity using DataReader

Example using OLEDB

Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        ' Step 1: Create connection
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=student.accdb")

        ' Step 2: Open connection
        con.Open()

        ' Step 3: Create command
        Dim cmd As New OleDbCommand("SELECT * FROM Student", con)

        ' Step 4: Execute reader
        Dim dr As OleDbDataReader = cmd.ExecuteReader()

        ' Step 5: Read data row by row
        While dr.Read()
            MsgBox(dr("Name").ToString())
        End While

        ' Step 6: Close reader and connection
        dr.Close()
        con.Close()

    End Sub
End Class

Advantages and Disadvantages