Topic 3

ADO.NET

Outline

3.1 Explain relational database and ADO.NET.

3.1.1 Define relational database.
3.1.2 Define basic database terminology example
database, table, record, field, and key.
3.1.3 Describe ADO.NET object model:
a. DataAdapter
b. Command
c. DataReader
d. DataSet
3.2 Create a database program.
3.2.1 Create and open a connection to a database.
3.2.2 Create, read, update, and delete records in a
database.
3.2.3 Use the Data Form Wizard to create a simple
data access application.
3.2.4 Display and modify data extracted from a
database.
Q&A
Activity

Relational Database
A collection of data items organized as a set
of formally described tables from which data
can be accessed easily

Database Terminology
Field - contains an item of data

Database Terminology
Record - composed of a group of related fields.
- A record contains a collection of attributes related
to an entity such as a person or product

Database Terminology
Database file - defined as a collection of related records.
- A database file is sometimes called a table.

Database Terminology
DATABASE - composed of related files that are
consolidated, organized and stored
together.
Database management system - software package that
enables users to edit, link, and update files as needs dictate

Database Terminology
Key - unique identifier , required by each record, in order
to track and analyze data effectively

ADO.NET
(ActiveX Data Objects ADO)
ADO.NET - technique of developing computer databases
using the Microsoft .NET Framework.
It is meant to serve stand-alone computers, client/server
scenarios at the enterprise level, and web applications.
Its full support for security, XML, file processing, and ease
of deployment makes it a very viable solution to creating
powerful applications.
This is the FunctionX support site of ADO.NET, providing
lessons and topics on how to use ADO.NET to develop
various types of data and list-based applications.

Function of ADO.NET
ADO.NET makes it possible to establish a connection with
a data source, send queries and update statements to the
data source (SQL Server & Ms Access), using XML for
passing the data, and process the results.

ADO.NET Architecture
Diagram
The following figure shows a scenario in
which the application and the ADO.NET
data provider are both built with managed
code. The application can connect to a
database using either a DataSet and a
DataAdapter or using a DataReader.

ADO.NET Architecture
Diagram

ADO.NET
(Data and XML)

New objects (e.g., DataSets)

Separates connected / disconnected issues
Language neutral data access
Uses same types as CLR
Great support for XML
Can fill a DataSet from an XML stream or
document

ADO.NET
ADO.NET is the preferred data access
method in the .NET Framework
Better support for disconnected data
access
Specialized namespaces
System.Data.SQL Tuned for SQL Server
System.Data.ADO OLEDB

Portable
Native XML Support

ADO.NET
ADO.NET object model:
a. DataAdapter
b. Command
c. DataReader
d. DataSet

ADO.NET
ADO.NET is comprised of many classes, but five take
center stage:
Connection - Represents a connection to a data source.
Command - Represents a query or a command that is to
be executed by a data source.
DataSet - Represents data. The DataSet can be filled
either from a data source (using a DataAdapter object)
or dynamically.
DataAdapter - Used for filling a DataSet from a data
source.
DataReader - Used for fast, efficient, forward-only
reading of a data source.

ADO.NET
ADOConnection
Similar to Connection object in ADO

ADOCommand
Similar to Command object in ADO

ADODataSetCommand
Somewhat like Recordsets for ADO.NET (DataSet)
Designed for stateless operations

ADODataReader
For streaming methods, similar to Stream

SQLConnection, SQLCommand, and

SQLDataSetCommand, SQLDataReader

Visual Basic and Databases

You can use VB to write projects that
display and update the data from
databases
VB uses Microsofts ADO.NET technology
to access databases in many different
format
VB uses ADO.NET which is the next
generation of database technology, based
on Microsofts previous version called
ActiveX Data Objects (ADO)
Advantage of ADO.NET is that information
is stored and transferred in Extensible
Markup Language (XML)
10/30/16

18

Visual Basic and Databases

ADO.NET allows you to access
database data in many formats
The basic types of provider are OleDb,
SQLClient for SQL Server (Microsofts
proprietary DBMS), Odbc and Oracle
Using OleDb you can obtain data from
sources such as Access, Oracle,
Sybase or DB2
10/30/16

19

Visual Basic and Databases

In VB, you can display data from a
database on a Windows Form or a Web
Form
You add controls to the form and bind data
to the controls
The controls may be labels or text boxes
or one of the special controls designed
just for data
You will write database application using
both Windows Forms and Web Forms
10/30/16

20

ADO.NET Object model

Databases
Microsoft SQL Server
Microsoft Access
Oracle

10/30/16

C
o
n
n
e
c
ti
o
n

Data providers
Data Adapter-Dataset
Command Data Reader

Data consumers
Windows Form
Web Form
Other

21

ADO.NET Object model

Web
Form
Data
Source

Binding
Source

Table
Adapter

Dataset

Windows
Form

BindingSource
object
Specific file
or database

10/30/16

Handles data transfer

and provides data for
dataset.Uses SQL to
specify data to retrieve
or update

Data display on
the form
in bound
controls
Actual data.
Can contain
multiple tables
and relationships
22

Visual Basic and Databases

Databases are composed of tables of related
information. Each table is organized into rows
representing records and columns containing
field of data
The primary key field uniquely identifies a row
or record
Many controls can be bound to a database
including labels, textboxes, list boxes or a
DataGridView

10/30/16

23

Visual Basic and Databases

A binding source establishes a link to a
data source, which is a specific data file or
server
A table adapter handles the transfer of
data between a data source and a
dataset.
A dataset stores information from the
database in the memory of the computer.
A dataset can contain multiple tables and
their relationships

10/30/16

24

Database Connectivity
Following are important to get connected
to the database and perform operations
Connection Object
Command Object
Operation on the Command Object
Using Dataset and Data adapter
Using Data Reader

If we use data adapter, it is called as

disconnected architecture
If we use data reader, it is called as
connected architecture

Connection Object
Dim con as New
OdbcConnection(connectionstring)

Where connectionstring is a string that

contains details about the server
where the database is located, the
name of the database, user id and
password required for getting
connected and the driver details

Command Object
Dim cmd as New
OdbcCommand(strCmd,con)
strCmd is the
select/insert/update/delete statement
or exec <<storedProc>> command
Con is the connection object created in
the first step
Properties cmd.CommandType
This can be either Text or StoredProcedure

Command Methods
Cmd.ExecuteReader() Returns one or
more table row(s) for select
statement
Cmd.ExecuteScalar() Returns a single
value for select statement with
aggregate function
Cmd.ExecuteNonQuery() Used for
executing stored procedure or
insert/update/delete statements

Data Reader and Data Set

Data Reader
Dim dr as OdbcDataReader
This dr holds the result set present in
datareader object

Data Set
Dim ds as New DataSet()
ds holds the result of select statement
Data adapter is used to fill the data set

Data Adapter
Data adapter fills in the data set with
the result of the select query
Dim da as New OdbcDataAdapter(cmd)
Cmd is the command object created
da.Fill(ds) fills the dataset
The data set can be set as a data
source for various controls in the web
form or windows form

The different methods to

access Database in .NET
* OleDB,ODBC,SQLClient
* OleDB, JDBC, SQLServer
* ODBC-JDBC, DataSet, SQLClient
* Datasource, DataSet, DSN

Create Database using

ADO.NET

Microsoft SQL Server and Microsoft Access both include a sample

database called Northwind.

Examples, assume that the following declaration appears in the same

file as the code:
Imports System.Data
Examples that use SQL Server also assume this declaration:
Imports System.Data.SqlClient
Examples that use Access assume this declaration:
Imports System.Data.OleDb

ADO versus ADO.NET

Feature

ADO

ADO.NET

Primary Aim

Client/server coupled

Disconnected collection of
data from data server

Form of data in
memory

Uses RECORDSET object

(contains one table)

Uses DATASET object

(contains one or more
DATATABLE objects)

Disconnected
access

Uses CONNECTION object

and RECORDSET object with
OLEDB

Uses DATASETCOMMAND
object with OLEDB

Disconnected
access across
multi-tiers

Uses COM to marshal

RECORDSET

Transfers DATASET object via

XML. No data conversions
required

ADO versus ADO.NET

(continued)
Feature

ADO

ADO.NET

XML
capabilities

XML aware

XML is the native transfer

medium for the objects

Firewalls

Firewalls block system-level

COM marshalling

XML flows through the firewall

via HTTP

Code

Coupled to the language

used, various implementation

Managed code library Uses

Common Language Runtime,
therefore, language agnostic

Exercise
Create a Windows application that
displays data from the Book table of
the Book.mdb database. You will
display the fields from the table in a
DataGridView control on a Windows
Form.

10/30/16

35

Resources
http://en.wikipedia.org/wiki/Comparison_of_ADO_and_ADO
.NET
http://msdn.microsoft.com/en-us/library/dw70f090(v=vs.80).aspx
ADO.Net Sample Application. Retrieved 03 March 13
Mahesh Chand (2009 ). Simple ADO.NET program with Visual Studio
.NET IDE
http://www.c-sharpcorner.com/uploadfile/mahesh/simple-ado-netprogram-with-visual-studio-net-ide/ Retrieved on 3 March 13
http://oreilly.com/catalog/progvbdotnet/chapter/ch08.html Retrieved
on 3 March 13

Question & Answer

What is ADO.NET?
What is the function of ADO.NET?
Comparison between ADO &
ADO.NET

Extra Activity
Refer to Extra Lab