Computer Science: Autumn 2012 Transaction - here

townripeData Management

Jan 31, 2013 (4 years and 4 months ago)

120 views

1

UCN Technology: Computer Science:




Autumn 2012



ADO.NET 2.0


Architecture


DataReader


DataSet


Connection factory


Config file


2

UCN Technology: Computer Science:




Autumn 2012



Data access

Swap?

3

UCN Technology: Computer Science:




Autumn 2012



Data Providers


OLE DB:

A COM object, that maps the OLE DB api to the DBMS' api

Is Microsoft propritary.


ODBC:

Works similar to a printer driver undependent of DBMS and OS


.NET Data provider:

A set of classes that implements a set of interfaces and
abstract classes


Some are provided by Microsoft and are installed with the .Net
framework.


Others are written by the DBMS provider

4

UCN Technology: Computer Science:




Autumn 2012



Microsoft Data Providers


Data Provider

Namespace

Assembly


OLE DB

System.Data.OleDb

System.Data.dll


Microsoft SQL Server

System.Data.SqlClient

System.Data.dll


Microsoft SQL Server Mobile

System.Data.SqlServerCe

System.Data.SqlServerCe.dll


ODBC

System.Data.Odbc

System.Data.dll


Oracle

System.Data.OracleClient

System.Data.OracleClient.dll


MS Access

5

UCN Technology: Computer Science:




Autumn 2012



Other providers


Data Provider

Website


Firebird Interbase

http://www.mono
-
project.com/Firebird_Interbase


IBM DB2 Universal Database

http://www
-
306.ibm.com/software/data/db2


MySQL

http://dev.mysql.com/downloads/connector/net/1.0.html


PostgreSQL

http://www.mono
-
project.com/PostgreSQL


Sybase

http://www.mono
-
project.com/Sybase


Oracle (proprietary)

http://www.oracle.com/technology/tech/windows/odpnet/index.html


6

UCN Technology: Computer Science:




Autumn 2012



Classes and Interfaces


Object

Base Class

Implemented Interfaces



Connection

DbConnection

IDbConnection

-

Connection


Command

DbCommand

IDbCommand

-

SQL command


DataReader

DbDataReader

IDataReader

-

Forward reader


DataAdapter

DbDataAdapter

IDataAdapter

-

Used with Dataset


Parameter

DbParameter

IDataParameter

-

Parameters for Command


Transaction

DbTransaction

IDbTransaction

-

Transaction

7

UCN Technology: Computer Science:




Autumn 2012

Two ways of DB access


Connected:


Open connection.


Read/Write access (select, insert, update and delete) using a
Command object.


When reading (select) a DataReader object is returned. A
DataReader is an iterator (cursor) into the result table.


Close Connection.


Disconnected:


Fill a DataSet object (a copy of a part of the database) using a
DataAdapter.


DataAdapter wraps SQL
-
statement(s).


A DataSet object contains DataTable objects.


DataTable objects contain collections of rows and columns.

8

UCN Technology: Computer Science:




Autumn 2012

Connection vs. Connectionless


Connection:


Open Connection


Execute DB operations


Close Connection


Working on actual (live)
data


Other applications can not
access data.

Dom
æ
neklasser
SQL
Dataklasser
DB
9

UCN Technology: Computer Science:




Autumn 2012

Dom
æ
neklasser
SQL
Dataklasser
DB

Connectionless:


Create a copy of a part of
the database


Execute DB operations on
the copy


Other applications may
change date


The copy may be come
inconsistent.

Connection vs. Connectionless


Data are changed in the local copy:


at update it is checked if the data in the
database have been modified by others


in that case the update is rejected
(ConcurrencyException).

10

UCN Technology: Computer Science:




Autumn 2012

Overview of using databases


4 steps:

1.
Open connection to database

2.
Execute SQL for updating DB or fetching records

3.
Handle data

4.
Close connection

11

UCN Technology: Computer Science:




Autumn 2012

Step 1: Open Connection


Connection are opened according to
connection string

info


here is a connection to a MS Access database opened


The database is located here: @"c:
\
AnyPlace
\
MyDb.mdb"


@ means that escape characters are discarded

<%@ import namespace="System.Data"%>

<%@ import namespace="
System.Data.OleDb"%>


string sConnection;

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"




+ "Data Source="+@"c:
\
AnyPlace
\
MyDb.mdb");


OleDbConnection dbConn;

dbConn = new OleDbConnection(sConnection);

dbConn.Open();

connection

12

UCN Technology: Computer Science:




Autumn 2012

Open Connection


Connections are opened using a
connection string


May be found using Visual Studio:


// Create and open a connection.


SqlConnection cn = new SqlConnection();


cn.ConnectionString = "
Data Source
=PCM06073
\
\
SQLEXPRESS;”




+”Initial Catalog=vw;Integrated Security=True;";


cn.Open();


ShowConnectionStatus(cn);


// or should it be?


cn.ConnectionString = “
Server
=PCM06073
\
\
SQLEXPRESS;”






+”Initial Catalog=vw;Integrated Security=True;";


cn.Open();

Data Source

or

Server?

Server is preferable.

13

UCN Technology: Computer Science:




Autumn 2012

Connection Strings


Connection strings are product specific (DB specific) anf
often very well
-
documented.


Help may be found at:


www.connectionstrings.com



www.able
-
consulting.com/ADO_conn.htm




In many cases VS can help.

14

UCN Technology: Computer Science:




Autumn 2012

Step 2: Get / fetch data


Data are fetched by creating a Command
object and use it to execute a SQL statement.


Data can be stored in either a DataReader
object or a DataSet object.


15

UCN Technology: Computer Science:




Autumn 2012



Differences between DataReader and
DataSet/DataAdapter


DataReader can only be used for reading data.


It can only be traversed once (forward).


DBCommand can update the database by ExecuteNonQuery. This
update is executed immediately.



DataAdapter is the connection between DataSet and database.


Data are fetched to the DataSet, might be modified and sent back to
the database.


Updates are executed on a local copy. Concurrency problems must
be handled.


Possible to traverse forward and backward.


A DataSet can contain multiple tables.

16

UCN Technology: Computer Science:




Autumn 2012

Step 2: Get records

Method 1: DataSet


Get records by SQL Select query and DataAdapter


Data can be read and changed

string sql;

sql = " Select lastname, firstname"


+ " From employee"


+ " Order By lastname Asc, firstname Asc;";


OleDbCommand dbCmd;

dbCmd = new OleDbCommand(dbConn, sql);

adapter.SelectCommand = dbCmd;

dataset =new DataSet("MyTable");

adapter.Fill(dataset);


dataset

record

record

record

17

UCN Technology: Computer Science:




Autumn 2012

Step 2: Get records

Method 2: DataReader


Get records via SQL Select query


read
-
only access to the database

string sql;

sql = " Select lastname, firstname"


+ " From employee"


+ " Order By lastname Asc, firstname Asc;";


OleDbCommand dbCmd;

dbCmd = new OleDbCommand(sql, dbConn);


OleDbDataReader dbReader;

dbReader = dbCmd.ExecuteReader();


data reader

record

record

record

18

UCN Technology: Computer Science:




Autumn 2012

What is achieved?


We have created a connection to a
database.


The connection is placed in the
connection object.


We have done a search by using a
SQL
-
statement.


The search was executed by using a
command object.


The result of the search was stored in
a DataSet or as here a DataReader
object.


Now it is possible to get the data from
this object for viewing, passing on to
client or handle in other ways.

Example:
..
\
vwReader

19

UCN Technology: Computer Science:




Autumn 2012



Factory


Problem:

Unlike

ODBC,
knowledge

of the
implementation

might

be

spread

to
many

places

in the
application
.

That's

because

of the set of
dedicated

classes


The solution is to
use

a
factory

(from
factory

pattern)


In ADO.NET 2.0, it is
implementet

in the
framework
.



Use

factory

to
create

a Connection and a Command
object

Example:
\
ReaderWithConfig

20

UCN Technology: Computer Science:




Autumn 2012



Use factory to create Connection and Command objects


Here is no references in the source code to concrete classes


provider and connectionString may be placed in the config file

string provider =
"System.Data.OleDb";

string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=firma.mdb";

DbProviderFactory factory= DbProviderFactories.GetFactory(provider);

DbConnection conn=factory.CreateConnection();

conn.ConnectionString=connectionString;

conn.Open();

Console.WriteLine(conn.State);


DbCommand command = factory.CreateCommand();

command.CommandText =
"SELECT * FROM employee";

command.Connection = conn;

DbDataReader dataReader = command.ExecuteReader();

while (dataReader.Read()){


Console.WriteLine(dataReader[1]);

}

21

UCN Technology: Computer Science:




Autumn 2012



Connection strings


Connection strings depends on the provider and are not
well documented


Where to get help?


www.connectionstrings.com


www.carlprothman.net/Default.aspx?tabid=81


Providers documentation



Visual Studio can generate some of them


22

UCN Technology: Computer Science:




Autumn 2012



.Config fil


Configuration files are used several places in .Net


i.e in ASP.NET for setting site properties and security


In Remoting for setting remote object properties


In ADO.NET to set provider and connection string


You might also use own application dependent properties


In a .Net application the config file should be called

<full assemblyname.config>,

i.e test.exe.config or test.dll.config


If you call it app.config in Visual Studio, it automatically
copied and renamed when the application is built.


In ASP.NET it must be named web.config. You might define
one in different sub folders, but the security part
(authentification e.g.) must be in the root folder of the site.

23

UCN Technology: Computer Science:




Autumn 2012



.config fil and ADO.NET


Example of SQLExpress connection string:

<?
xml

version
="1.0"
encoding
="utf
-
8" ?>

<
configuration
>


<
configSections
>


</
configSections
>


<
connectionStrings
>


<
add

name
="
TestConnection
"


connectionString
="Data Source=pcm06463
\
sqlexpress;Initial Catalog=
dbtest
; User ID=
test
;Password="


providerName
="System.Data.SqlClient" />


</
connectionStrings
>

</
configuration
>

Machine name

Nessesary for sqlexpress

Database

Internal name

24

UCN Technology: Computer Science:




Autumn 2012



Get provider and connection string


From ConfigurationManager:

....


string provider =
ConfigurationManager.ConnectionStrings[
"TestConnection"].ProviderName;


string connStr =
ConfigurationManager.ConnectionStrings[
"TestConnection"].ConnectionString;

.....

Example:
\
ReaderWithConfig

Example:
\
ReaderWithConfig2

25

UCN Technology: Computer Science:




Autumn 2012



Parameters in SQL statements


Makes it possible to use the same SQL
-
statement in multible
situations.


But harder to debugge


string sqlString =
"INSERT INTO datotest Values (@dato)";


SqlParameter sqlParam =
new SqlParameter(
"@dato", System.Data.SqlDbType.DateTime);


sqlParam.Value =
DateTime.Now;



SqlCommand comm = _conn.CreateCommand();


comm.CommandText = sqlString;


comm.Parameters.Add(sqlParam);



int result = comm.ExecuteNonQuery();

ExecuteNonQuery is
used for insert, delete
and update

26

UCN Technology: Computer Science:




Autumn 2012

Transaction
-

Definition

September 2010

UCN T&B
-

CBH (FEN) Databases/Transactions

2
6


A transaction is an operation on data in the database.


A transaction may be composed of several database
operations, but is viewed as a logical unit of work


A transaction must be done completely or not done at all


A transaction must have the ACID properties:


A
: Either it is done in total or it is not done at all (
A
tomicity
)


C
: The database moves from one consistent state to an other
consistent state (
C
onsistency
)


I
: If more operations are accessing the same data, they are
not to disturb each other


they must execute as if they
executed alone (
I
solation
)


D
: When a transaction completes, its changes to the
database are permanent (
D
urability
)

27

UCN Technology: Computer Science:




Autumn 2012

Transactions


example:

T1 and T2 are executing concurrently

T1: Transfers N DKKs from
account X to account Y:


read_item
(X);

X:= X
-
N;


write_item
(X);

read_item
(Y);


Y:= Y+N;

write_item
(Y);

T2: Deposits M DKK on
account Y:




read_item
(Y);

Y:= Y+M;


write_item
(Y);

Any possible
problems?

time

28

UCN Technology: Computer Science:




Autumn 2012

Transactions


Problems


We want several transactions to execute concurrently
(Why?)


Three types of problems:


lost update


uncommitted dependency (temporary update)


inconsistent analysis (incorrect summary)


Crash during execution of a transaction must be handled

29

UCN Technology: Computer Science:




Autumn 2012

Lost Update

30

UCN Technology: Computer Science:




Autumn 2012

Uncommitted Dependency

31

UCN Technology: Computer Science:




Autumn 2012

Inconsistent Analysis

32

UCN Technology: Computer Science:




Autumn 2012

Transactions


In C#: Bank Transactions



Example:
demos
\
Transactions
\
BankTransaction1

33

UCN Technology: Computer Science:




Autumn 2012

Multiple Databases


Moving from (badly designed) database to another (better
designed) database:



Example:
demos
\
flytDataApp