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
