Transcript The connection

Lecture 14 C new
Introduction to Databases - DB
Processing: The Disconnected
Model (Using DataAdapters,
DataSets, and DataTables)
By Chris Pascucci and FLF
The Disconnected Architecture – Data Sets

ADO.NET provides classes that are used to create
applications that utilize a database.



You can create instances of these classes and use them in your
application to manage and use database connections.
To use these classes in the .NET Framework you must import the
namespace System.Data that contains all these useful classes.
When working in the disconnected mode the data
retrieved from a database and utilized by your program
is stored in a DataSet.




The DataSet contains one or more DataTables.
Data in the DataSet is independent of the database that was used to
retrieve it.
A data set can contain the result of multiple queries
The connection to the database is typically closed after data is
retrieved. The connection is opened again when it’s needed.
ADO.NET – The Data Adapter

DataAdapter:





Manages the exchange of data between the DataSet and a database.
It issues an SQL Command (Select, Insert, Update or Delete) that is
stored in the Command object.
The Command object uses a Connection object to connect to the
database and retrieve or update the data.
The data is transferred back to the DataAdapter, which then stores it
in a DataSet that can be used by the application.
Note carefully – everything we manipulate is an object
ADO.NET Explained (skip this slide)
Data Adapters - how the process works
D a ta se t
.N E T d a ta p ro vid e r
D a ta ta b le
D a ta a d a p te r
C om m and
D a ta b a se se rve r
C o n n e ctio n
D a ta b a se
ADO.NET Explained (skip this slide)

The DataSet Object Model:
ADO.NET Explained (skip)

The DataSet Object Model:
Dataset
Data table
Data column
Data relation
Data row
Constraint
ADO.NET Explained


An ADO.NET DataProvider connects to a data source such as SQL
Server, Oracle, or an OLE DB data source, and provides a way to
execute commands against that data source.
The ADO.NET classes responsible for working directly with a
database are provided by the
NET DataProviders:


The .NET Framework includes
data providers for SQL Server,
Oracle, OLE DB, ODBC, etc…
The Data Providers included in the
.NET Framework contain the same
objects, although their names and
some of their properties and
methods are different.



For example, the SQL Server version of a database connection is the SqlConnection,
while the OLE DB version is an OleDbConnection.
ODBC = Open Database Connectivity (Relational/Procedural/First Abstract Model)
OLE = Object Linking and Embedding (OO Based – Microsoft)
Connection (Review 1)


The connection component is used to establish a
connection to a database and manage communications
between the data source and your program.
Use the appropriate connection class.


OleDbConnection, SqlConnection, OracleConnection, …
Example:
' Access Connection
Dim strConnection As String = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\path\Inventory.mdb;"
Dim myConnection As New OleDbConnection(strConnection)
' SQL Server Connection
Dim strConnection As String = "provider=Microsoft.ACE.OLEDB.12.0;" & _
"server=dwarf.cis.temple.edu;" & _
"database=pascucci; User id=xxx;" & _
"Password=xxx;"
Dim myConnection As New SqlConnection(strConnection)
Connection (Review 2)


The connection string is a simple string that is used to
create a connection object.
The connection string contains key-value pairs



The assignment operator (=) is used to separate the key and the value.
A semi-colon (;) is used to separate each key-value pair.
The connection string is different for each DataProvider.
' Access 2007 - 2010 Connection
Dim strConnection As String = "provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\path\Inventory.accdb;"
Dim myConnection As New OleDbConnection(strConnection)
Connection (Review 3)
Two constructors for the SqlConnection class
New SqlConnection()
New SqlConnection(connectionString)
Common properties and methods of the
SqlConnection class
Property
ConnectionString
Method
Open()
Close()
Description
Provides information for accessing a SQL
Server database.
Description
Opens the connection using the specified
connection string.
Closes the connection.
DataAdapter


Executes commands against a database and manages
the transfer of information from the database to the
DataSet.
Use the appropriate DataAdapter class.


OleDbDataAdapter, SqlDataAdapter, OracleDataAdapter, …
Example:
' Access Connection
Dim strConnection As String = "provider=Microsoft.Ace.OLEDB.12.0;" & _
"Data Source=c:\path\Inventory.accdb;"
Dim myConnection As New OleDbConnection(strConnection)
Dim strSQL As String = "SELECT * FROM Product"
Dim myDataAdapter As New OleDbDataAdapter(strSQL, myConnection)

Note similarity with ExecuteQuery in connected mode
Using a Command with a DataAdapter

The command component is used to take an SQL
command you provide, prepare it for transport through
the connection object, and for processing in the DBMS.



For our purposes, this is optional and can be easily replaced with a
simple string.
The command object is created by passing it a simple
string with SQL statements, and the connection object.
Example:
' Access Connection
Dim strConnection As String = "provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\path\Inventory.accdb;"
Dim myConnection As New OleDbConnection(strConnection)
Dim strSQL As String = "SELECT * FROM Product"
Dim myCommand As New OleDbCommand(strSQL, myConnection)
Dim myDataAdapter As New OleDbDataAdapter(myCommand)
Commands (Review)
Common properties of the SqlCommand class
Property
Connection
CommandText
CommandType
Parameters
Description
The connection used to connect to the database.
A SQL statement or the name of a stored procedure.
A member of the CommandType enumeration that
determines how the value in the CommandText
property is interpreted.
The collection of parameters for the command.
CommandType enumeration members
Member
Text
StoredProcedure
TableDirect
Description
The CommandText property contains a SQL
statement. This is the default.
The CommandText property contains the name
of a stored procedure.
The CommandText property contains the name
of a table (OLE DB only).
ADO.NET Programming

Example:
Dim
Dim
Dim
Dim
Dim
strSQL As String
strConnection As String
objDataSet As New DataSet()
objAdapter As SqlDataAdapter
objConnection As SqlConnection
DataSet used to hold data
retrieved from the database.
SQL Server DataProviders for
working with an SQL Server
data source.
strConnection = "server=dwarf.cis.temple.edu;Database=4376nn;” & _
“User id=4376nn;Password=yourpassword“
strSQL = "SELECT * FROM Product“
objConnection = New SqlConnection(strConnection)
objAdapter = New SqlDataAdapter(strSQL, objConnection)
objAdapter.Fill(objDataSet)
DataAdapter object used to
manage the flow of data from
the data source to the
DataSet.
Connection object used to
establish a connection.
Fills the DataSet object with
data retrieved from the
database.
ADO.NET


All elements of a database, tables, rows (set of values
that make up a record), columns (database fields), and
individual cells (actual field values) are all represented
as abstractions (classes) in .NET.
Basically, every table, row, and column are represented
as instances of these abstractions (classes) that are
part of the ADO.NET support structure.
Disconnected vs. Connected

The major advantage of the disconnected approach is
that it improves system performance



Uses less resources for maintaining connections when
working with a data source.
Work is done on local data: DataSets, DataTables, etc
However, there is a disadvantage to this approach that
occurs when two or more users try to update the same
row of a table, which is called a concurrency problem.
Database server
User 1
DBMS
User 2
Products
data table
Products
table
Products
data table
Disconnected vs. Connected


ADO.NET contains two different approaches to work with data in a
database: Connected Data Architecture and the Disconnected Data
Architecture.
Connected Data Architecture:



Represents the objects that insist on having an open connection available for
them to work and interact with the data source.
ADO.NET provides classes to communicate directly with a data source.
Disconnected Data Architecture:




Represents the objects that open and close a connection to the data source as
needed.
Your application works with data that was copied from the data source. Any
changes made to the “copied” data in the Dataset will be later reconciled with
the data source.
Connected applications alone don't fulfill the demands of today’s distributed
applications.
Improved performance, more efficient and more complex than the connected
approach.
Concurrency Issues


An old time problem when dealing with shared
resources such as memory, files, DBs etc
Occurs when multiple users try to access the
same resource such as a row in a database
table



Not a problem if all users are reading the resource
Becomes a problem if one or more are writing
Locking mechanisms need to be implemented
to ensure the integrity of the resource
Disconnected vs. Connected Modes


When using the disconnected mode of DB access concurrency
problems occur because once data is retrieved by User A from the
database, the connection is dropped. Now User B has can access
the DB
There are two ways that ADO.NET can handle concurrency
problems:
1. Optimistic Concurrency – The program checks to see if there has
been a change to the row since it was retrieved. If it has, the update
or deletion will be refused and a concurrency exception will be
thrown that your program must handle.
2. “Last In Wins” – Since no checking is done, the row that was
updated by the last user will overwrite changes made to the row by
the previous user.

Another way to avoid concurrency problems is to retrieve and
update only one row at a time.
Steps to Working with ADO.NET (disconnected)





First, a database connection is established
Then the database is opened
SQL commands are sent over the open connection
ADO.NET builds an in-memory representation of the
returned data from any SELECT command and changes
are made to this representation
When processing is complete, the database connection
is closed

It is suggested that you use the Open and Close methods to
explicitly open and close your connections. In this way, you
keep your connections open only as long as needed to
complete the required processing
Processing database elements


All elements of a data base, tables (entities),
rows (sets of values), columns (database
fields), and individual cells (database values)
are represented as abstractions in .NET
In other words, every table, row, and column
are represented as instances of the
abstractions (classes) that are part of the
ADO.NET support structure
How Do the Pieces Fit Together?


Study the code example from Chris Pascucci
first (see Lecture Set 14 C Ex)
Then study the code sample provided by your
lab assistant (Jessica Clark) at the end of the
Client-Server Word document (also look at the
starter code for the Final Project, Part III – ref .
HW #12)
An Underlying Framework –




The Connection
Keeping a mental picture of this process in your head is
important
It all begins with the connection
Already visited this idea for files. Now we revisit it
again for databases
Remember …


In your programs, you manipulate objects using methods
belonging to the class that defines the type of the object
The class (an abstract data type) is an abstraction (model)
of the object being manipulated
The Object Framework (drawing/files)

Recall …

We must have a class to model the entity to be
manipulated (the surface to be drawn on)





The class is said to define an “abstract data type”
We can then create objects (instances of the class
we just created)
Next comes the tricky part …
We now have to connect that object to the
actual entity to be drawn on OR
Have to connect a stream to a sequential file
The Object Framework 2



For databases, the object is an instance of a class
that provides an abstract view of the database
including an abstraction of database tables, rows,
and columns
All of these database features are modeled using
ADO.NET classes
So we program against elements of the ADO.NET
classes without concern for the underlying structures
The Object Framework 3 – The Connection
The database
abstraction
(methods and data
stores) that you
program against
(DB commands,
internal datasets
and data tables)
The
connection –
via creation
of a database
connection
object
The actual,
physical
database to be
manipulated
‘Example - Connection to a Microsoft Access Database
Dim myConnectionObj As New OleDbConnection(ConnectString)
‘Example - Connection to SQL Server Database
Dim myConnectionSqlObj As New SqlConnection(SqlConnectString)
Remember …



Why do this – what is this all about?
The abstraction enables us to manipulate
database objects in a uniform, consistent way,
regardless of the DBMS (Access, MySQL, SQL
Server, Oracle)
The underlying DBMS is hidden under several
layers of ADO.NET software which does all the
work
Connection Strings – (disconnected mode)

All we need to do is connect the program’s ADO.NET database
object to the underlying database to be used
‘SQL Server Connection
Dim SqlConnectString As String = _
"server=dwarf.cis.temple.edu;Database=fa06_c342132;” & _
"User id=fa06_c342132;Password=jimbo“
Dim myConnectionSql As New SqlConnection(SqlConnectString)
‘Access Connection
Dim ConnectString As String = "provider=Microsoft.Jet.OLEDB.4.0;” _
“Data Source=c:\users1\dl\Inventory.mdb“
Dim myConnection As New OleDbConnection(ConnectString)



Programmer layer – build commands (SELECT, INSERT,
DELETE, and UPDATE) for execution against the database
Logical layer – uses a DataAdapter to send commands from
your software (across the data adapter) to the database getting
back tables to be processed
Physical layer – manages the actual (physical) execution of the
commands on the database
ADO.NET (skip)
ADO.NET Terms Summary (Review)

DataSet:


DataAdapter:



Executes commands (SQL statements) against the data source.
Data Providers:


Connects to the data source.
Command:


Manages the exchange of data between the Dataset and a database.
Allowing a DataSet and DataTable to be filled from the data source and later
reconciled with the data source.
Connection:


Data in the Dataset is independent of the database that was used to retrieve it.
The ADO.NET classes responsible for working directly with a specific database.
Data Reader

Retrieves query results in a read-only, forward-only connected result set.
ADO.NET Resources

Connection Strings:


OleDbConnection Class:


http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.aspx
OleDbDataAdapter Class:


http://msdn.microsoft.com/enus/library/system.data.oledb.oledbconnection.aspx
OleDbCommand Class:


http://www.connectionstrings.com
http://msdn.microsoft.com/enus/library/system.data.oledb.oledbdataadapter.aspx
OleDbDataReader Class:

http://msdn.microsoft.com/enus/library/system.data.oledb.oledbdatareader.aspx