Scribd
Upload
10 views

Chapter 5 (Data Access Methods)

Uploaded by

633611sse
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views

Chapter 5 (Data Access Methods)

Uploaded by

633611sse
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 27

.

NET Data Access and


Manipulation

Chapter 5
Microsoft Data Access Technologies
◼ Over the years Microsoft has introduced an
alphabet soup of database access
technologies.
❑ They have acronyms such as ODBC, OLE DB,
ADO, ADO.NET...
◼ The overall goal is to provide a consistent set
of programming interfaces that can be used
by a wide variety of clients to talk to a wide
variety of data sources, including both
relational and non-relational data.
Data Storage

Data Storage
Unstructured Structured Hierarchical Relational Database

Excel CSV XML SQL Server Oracle

Active Directory Access


Microsoft Data Access Technologies

◼ Microsoft Data Access Technologies


❑ ODBC
❑ OLE DB
❑ ADO
❑ ADO.NET
❑ LINQ
❑ Entity Framework

4
ODBC
◼ Microsoft's first initiative in this direction was ODBC, or
Open Database Connectivity.
◼ ODBC provides a C interface to relational databases.
◼ The standard has been widely adopted, and all major
relational databases have provided ODBC drivers.
ODBC Drawback
◼ Slow with large databases: when used with
larger database management systems ODBC
will be slow.
OLE DB

◼ OLE DB (Object Linking and Embedding, Database,


sometimes written as OLEDB or OLE-DB),
an API designed by Microsoft, allows accessing data from
a variety of sources in a uniform manner.
◼ Microsoft originally intended OLE DB as a higher-level
replacement for, and successor to, ODBC, extending its
feature set to support a wider variety of non-relational
databases, such as spreadsheets that do not necessarily
implement SQL.

7
ActiveX Data Objects (ADO)
◼ OLE DB was architected for maximum efficiency for C++
programs. Not so with many other languages, such as
Visual Basic.
◼ Therefore to provide an easy to use interface for Visual
Basic Microsoft created ActiveX Data Objects or ADO.
◼ The ADO model has two advantages:
1) It is somewhat easier to use.

2) ADO is based on OLE DB and thus gives


programmers a very broad reach in terms of data
sources.
ADO.NET
◼ ADO.NET is a data access technology from
the Microsoft .NET Framework that provides
communication between relational and non-relational
systems through a common set of components.
◼ It is a part of the base class library that is included with
the Microsoft .NET Framework.
◼ ADO.NET is cross-platform compatible, and we can use
it with nearly all major database implementations such
as SQL Server, Oracle, and MS Access.

9
Where does ADO.net sit?

10
ADO.NET Architecture

11
XML Support

◼ ADO.NET is tightly integrated with XML

XML Web Services


Client Data Source
Request data SQL query
1 2
Results
4 XML DataSet 3
SQL updates
5 Updated XML DataSet 6
Using Namespaces
◼ C#
using System.Data;
using System.Data.SqlClient;
ADO.NET Core Components
◼ Many components form the core foundation
of ADO.NET. Here are some of them:
Object Description
Connection Establishes a connection to a specific data source.
(Base class: DbConnection)
DataAdapter Populates a DataSet and resolves updates with the data source.
(Base class: DbDataAdapter)
DataSet Represents a cache of data. Consists of a set of DataTables and
relations among them
Command Executes a command against a data source.
(The base class: DbCommand)
DataSets

◼ DataSets are an in-memory data structure


❑ easily filled with data from a database
❑ easily displayed in a GUI app

DataAdapter Command Connection


DataSet DB

Name Price Stock

Ants $ 0.49 5000

Birds $ 4.49 500

Cats $29.95 100

Dogs $79.95 20 • DataSet mirrors the database


"Table"
DataSets and DataTables

DataSet
DataTable

DataTable

Connection Stored
Procedure

Database

Data Store
DataAdapter

◼ DataSets don’t interact with databases directly;


instead, they interact through DataAdapters
◼ The main methods of the DataAdapters are Fill
and Update
ADO.NET Data Access Model

◼ In ADO.NET there are two models of data


access, which are:
❑ Disconnected Data Access Model
❑ Connected Data Access Model

18
Disconnected Data Access Model

◼ ADO.NET supports the disconnected model,


which is essential for performance.
◼ We can work with a disconnected copy of the
database in memory, and then save the
changes to the database.
◼ This feature is vital if we’re working with a
network shared database because traffic is
expensive.

19
Disconnected Mode …(2)
◼ In the Disconnected Mode Data Adapter is
used to Open and Close the connection.
◼ All the operations took place in Dataset.
◼ Component mainly used are:
❑ Connection
❑ DataAdapter
❑ DataSet

20
Disconnected Data Access Model
ADO.NET Disconnected mode Example

using System.Windows.Forms;
using System.Data.SqlClient;

namespace ADODisconnectedMode
{
public partial class frmMain : Form
{
public frmMain ()
{
InitializeComponent();
}

private void frmMain_Load(object sender, EventArgs e)


{
SqlConnection cn = new SqlConnection("server=.;uid=sa;pwd=12345;database=stdDB");
SqlDataAdapter da = new SqlDataAdapter("select * from students", cn);
DataSet ds = new DataSet();
da.Fill(ds, "students");
dgvStudents.DataSource = ds.Tables["students"];
}
}
}
ADO.NET Connected Mode

◼ In the Connected Mode as soon as you Open


the connection using ConnectionObject.Open()
the connection will always open until your close
in manually using Close().
◼ Components mainly used are:
◼ Connection
◼ Command
◼ DataReader
Connected Data Access Model
ADO.NET Connected mode Example
using System;
using System.Data.SqlClient;

namespace ADOConnectedMode

public partial class frmMain : Form


{
public frmMain()
{
InitializeComponent();
}

private void frmMain_Load(object sender, EventArgs e)


{
SqlConnection cn = new SqlConnection("server=.;uid=sa;pwd=12345;database=stdDB");
cn.Open();

SqlCommand cmd = new SqlCommand("select * from students", cn);


SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvStudents.DataSource = dt;

cn.Close();

}}}
Results
Thanks…

You might also like