The ProblemsHTTP is disconnectedSo many database vendorsCreate a simple consistent versatile interface

on the data

Look at ADO.NET classesOleDbSQL server optimised

ADO.NET ClassesSystem.Data.OleDb.OleDbConnection 

The connection object is used to create a connection between our code and the database.

 System.Data.OleDb.OleDbCommand 

Used to store a command to be applied to the database. May be either raw SQL or a stored procedure.

System.Data.OleDb.OleDbParameter 

Used to model parameters passed to the stored procedures / queries. System.Data.OleDb.OleDbDataAdapter 

Used to fill a data table with the results of a command.

ADO.NET Classes ContinuedSystem.Data.DataTable 

Used to model data obtained from the database.

 Data Readers

We are also going to take a look at data readers even though they are not used on the module.

Connected v Disconnected DataThe need for locking…

User A opens a recordUser B opens the same record and makes changesUser B saves the changes on the recordUser A makes their changes to the recordUser A now saves their changes and overwrites

the changes made by User B

Relatively easy to lock records on a local area network

Disconnected DataWhen a client application access a record it

communicates with the server via the HTTP request.

The server locates and processes the data returning it to the client at which point the connection is lost.

User A opens a record on the browser, the server locks the record to stop User B accessing it

User B tries to access the record and is presented with a message stating that the record is locked

User A is in the middle of editing the record and his browser crashes thus not telling the server he is done with the record

User B sits there waiting and waiting for the record to come free!

Add a Time – Out?User A opens the record and goes and makes a

cup of teaThe lock times outUser B opens the recordWhat does User A do with their data when the

save the changes?

I am not planning on exploring solutions to these problems, simply to bring your attention to them.

OleDb v SQL Optimised ClassesOleDB is a technology devised by Microsoft

for connecting to a wide range of database management systems (DBMS), e.g. Access, Oracle MySQL.

SQL optimised classes perform in exactly the same way as the OleDB classes but fine tuned to work with SQL server.

UsageOleDb 

OleDbConnection connectionToDB = new OleDbConnection (); 

SQL 

SqlConnection connectionToDB = new SqlConnection(); 

The classes also have different namespace... 

using System.Data.SqlClient; and 

using System.Data.OleDb;

The Execute Function

The Connection Object connectionToDB = new

OleDbConnection(connectionString);

DSN / DSN(less) ConnectionsDSN = Data Source Name

The database may be file on the disk (the way that we have been connecting to the database in this module!) (DSN(less))

Or the database may be running on a server with an IP address on a specific port number (remember TCP/IP allows a program on one computer to talk to another!) (DSN)

Data ProvidersDBMS specific driver

Connection StringsContains DBMS specific configuration dataSpecifies the data provider

//open the database connectionToDB.Open();

The Command ObjectApplies a “Command” to the dataInitialise with stored procedure name and

connection //initialise the command builder for this connection OleDbCommand dataCommand = new OleDbCommand(SProcName,

connectionToDB);

Add parameters //loop through each parameter for (int Counter = 0; Counter < SQLParams.Count; Counter += 1) { //add it to the command object dataCommand.Parameters.Add(SQLParams[Counter]); }

Set the Command Type //set the command type as stored procedure dataCommand.CommandType =

CommandType.StoredProcedure;

So far…

The Mincing Machine

Data AdapterInitialisation

//set the select command property for the data adapter

dataChannel.SelectCommand = dataCommand;

Filling the Data Table

//fill the data adapter dataChannel.Fill(queryResults);

Data Tables

Select * from tblAddress

To reference “Nottingham” in our code we would do the following...

 AnAddress.Town = queryResults.Rows[3]

["Town"].ToString();

Data ReadersRead-only, forward-only stream of dataFaster and more light weight than data tablesLock the data so may have problems with

multiple connectionsOne way flow of data

Example