Database programming in vb net

Prepared By: Sajid Hussain
Data access is making the application interactData access is making the application interact
with a database, where all the data is storedwith a database, where all the data is stored

The first data access model, DAO (data access
model) was created for local databases with the
built-in Jet engine which had performance and
functionality issues.
Next came RDO (Remote Data Object) and ADO
(Active Data Object) which were designed for
Client Server architectures but soon ADO took over
RDO.

ADO was a good architecture but as the language changes
so is the technology. With ADO, all the data is contained in
a recordset object which had problems when implemented
on the network and penetrating firewalls.
 ADO was a connected data access, which means that
when a connection to the database is established the
connection remains open until the application is closed.
 Leaving the connection open for the lifetime of the
application raises concerns about database security and
network traffic.

Also, as databases are becoming increasingly important
and as they are serving more people, a connected data
access model makes us think about its productivity.
For example, an application with connected data access
may do well when connected to two clients, the same may
do poorly when connected to 10 and might be unusable
when connected to 100 or more. Also, open database
connections use system resources to a maximum extent
making the system performance less effective. .

VB .NET uses ADO .NET (Active X Data Object) as it's
data access and manipulation protocol which also enables
us to work with data on the Internet.

ADO .NET interacts with databases by maintaining a
disconnected database access model.
 when an application interacts with the database, the
connection is opened to serve the request of the application
and is closed as soon as the request is completed.
if a database is Updated, the connection is opened long
enough to complete the Update operation and is closed.

Connections are open for only a minimum period of time,
ADO .NET conserves system resources and provides
maximum security for databases and also has less impact
on system performance.
ADO .NET uses XML when interacting with the database,
and converts all the data into XML format for database
related operations making them more efficient

Data Access in ADO.NET relies on two
omponents:
1- DataSet
2- Data Provider

The dataset is a disconnected, in-memory representation
of data. It can be considered as a local copy of the
relevant portions of the database.
The DataSet is persisted in memory and the data in it can
be manipulated and updated independent of the database.
When the use of this DataSet is finished, changes can be
made back to the central database for updating.
The data in DataSet can be loaded from any valid data
source like Microsoft SQL server database, an Oracle
database or from a Microsoft Access database.

The Data Provider is responsible for providing and
maintaining the connection to the database.
 A DataProvider is a set of related components that work
together to provide data in an efficient and performance
driven manner.

 The .NET Framework currently comes with two
DataProviders:
1- SQL Data Provider which is designed only to work with
Microsoft's SQL Server 7.0 or later.
2- OleDb DataProvider which allows us to connect to other
types of databases like Access and Oracle.

Each DataProvider consists of the following component
classes: .
Connection object
Command object
DataReader object
DataAdapter object

A connection object establishes the connection for the
application with the database
The command object provides direct execution of the
command to the database.
If the command returns more than a single value, the
command object returns a DataReader to provide the data
Alternatively, the DataAdapter can be used to fill the
Dataset object
 The database can be updated using the command object
or the DataAdapter

The Connection object creates the connection to the
database. Microsoft Visual Studio .NET provides two types
of Connection classes:
1-SqlConnection object: Designed specifically to connect
to Microsoft SQL Server 7.0 or later
2-OleDbConnection object: Provide connections to a wide
range of database types like Microsoft Access and Oracle.
 The Connection object contains all of the information
required to open a connection to the database

The Command object is represented by two corresponding classes:
SqlCommand and OleDbCommand.
Command objects are used to execute commands to a database
across a data connection.
The Command objects can be used to execute stored procedures on
the database, SQL commands, or return complete tables directly.
 Command objects provide three methods that are used to execute
commands on the database:
1- ExecuteNonQuery: Executes commands that have no return values
such as INSERT, UPDATE or DELETE
2- ExecuteScalar: Returns a single value from a database query
3- ExecuteReader: Returns a result set by way of a DataReader
object

The DataReader object provides a forward-only, read-only,
connected stream recordset from a database.
Unlike other components of the Data Provider, DataReader
objects cannot be directly instantiated. Rather, the
DataReader is returned as the result of the Command
object's ExecuteReader method.
 The SqlCommand.ExecuteReader method returns a
SqlDataReader object, and the
OleDbCommand.ExecuteReader method returns an
OleDbDataReader object.

The DataReader can provide rows of data directly to
application logic when you do not need to keep the data
cached in memory.
Because only one row is in memory at a time, the
DataReader provides the lowest overhead in terms of
system performance but requires the exclusive use of an
open Connection object for the lifetime of the DataReader

The DataAdapter is the class at the core of ADO .NET's
disconnected data access. It is essentially the middleman
facilitating all communication between the database and a
DataSet.
The DataAdapter is used either to fill a DataTable or DataSet
with data from the database with it's Fill method.
After the memory-resident data has been manipulated, the
DataAdapter can commit the changes to the database by
calling the Update method.

 The DataAdapter provides four properties that represent database
commands:
1. SelectCommand
2. InsertCommand
3. DeleteCommand
4. UpdateCommand
When the Update method is called, changes in the DataSet are
copied
back to the database and the appropriate InsertCommand,
DeleteCommand, or UpdateCommand is executed

System.Data
System.Data.Oledb
Sysetm.Data.SqlClient
System.XML

Main data access library for ADO.NET

It houses the components that provide access
via OLEDB provider

It houses the data access components for SQL
Server

This library contains XMLThis library contains XML
componentscomponents

VB . NET ClientVB . NET Client Internet ExplorerInternet Explorer Any BrowserAny Browser
ApplicationApplication
ADO .NET ClassesADO .NET Classes
Managed ProvidersManaged Providers
SQL ClientSQL Client Ole DbOle Db
.NET Framework.NET Framework
OracleOracle
AccessAccess
DB/2DB/2
Data StoreData StoreSemi-StructuredSemi-Structured
datadata
Message StoreMessage Store
DocumentsDocuments
SQL dataSQL data
SQL ServerSQL Server
DataData

Database programming in vb net

More Related Content

What's hot

Viewers also liked

Similar to Database programming in vb net

Recently uploaded

In this document

Database programming in vb net