data access

35
Data Access Tonga Institute of Higher Education

Upload: iden

Post on 21-Mar-2016

22 views

Category:

Documents


0 download

DESCRIPTION

Data Access. Tonga Institute of Higher Education. Introduction. Many programs work with large amounts of data Databases are an good place to store data Programs need a way to connect to, get data from and save data to databases - PowerPoint PPT Presentation

TRANSCRIPT

Page 1: Data Access

Data Access

Tonga Institute of Higher Education

Page 2: Data Access

Introduction Many programs work with large amounts

of data Databases are an good place to store data Programs need a way to connect to, get

data from and save data to databases ActiveX Data Objects .Net (ADO .Net)

provides way to access data from Object Linking and Embedding Database (OLE DB)-compliant data sources such as SQL Server and Oracle.

Page 3: Data Access

ADO .Net Components

.Net Data Providers – Objects designed for connecting to a database, executing commands and retrieving resultsConnection Command DataReader DataAdapter

Page 4: Data Access

Connection Object

Connection Object – Provides connectivity to a data source

1. SQLConnection object – Allows connection to a Microsoft SQL Server 7.0 or later database Located in System.Data.SQLClient namespace

2. OLEDbConnection object – Allows connection to an OLE DB data source Located in System.Data.OleDb namespace Microsoft Access is an OLE DB compliant database

Page 5: Data Access

Connection Strings The SQLConnection and OLEDbConnection objects both

use connection strings to connect to a data source Connection String – Provides information needed to

connect to a database You do not need to include everything in a connection

string. Only include what is needed. SQLConnection object

SQL Server Initial Catalog=pubs;Data Source=(local);User ID=sa;password=;

OLEDbConnection object Oracle

Provider=msdaora;Data Source=MyOracleDB;User ID=myUsername;Password=myPassword;

Microsoft Access Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\northwind.mdb

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\tihe1\student.mdb

You don’t alwaysneed a User IDand Password

Local DB

Network DB

Page 6: Data Access

Opening and Closing Connections

Before a Connection object can work with a database, the connection must be openedUse the Open() method

After the Connection object is used, the connection must be closedUse the Close() method

Page 7: Data Access

Command Object

Command Object – Enables the execution of queries and return of data from a data source A Connection object must be specified to connect to a

data source A SQL statement must be specified for execution at

the data source There are different ways to execute SQL statements

ExecuteNonQuery() – Use this when data will not be returned

ExecuteReader() – Use this when data will be returned

Page 8: Data Access

ExecuteNonQuery Code

ExecuteNonQuery doesnot return anything

Page 9: Data Access

Demonstration

Connections, Commands and ExecuteNonQuery

Page 10: Data Access

DataReader Object DataReader Object – Provides a stream of data

from the data source Useful members

FieldCount Get<DataType>

GetByte GetString And more

HasItems Item Read

Page 11: Data Access

DataReader and ExecuteReader Code

Using a DataReaderto display data fromdatabase

Keeps loopinguntil all rowsare read

Gets string from first index

ExecuteReader returnsA DataReader

Page 12: Data Access

Demonstration

Connections, Commands and DataReader.ExecuteReader Code

Page 13: Data Access

DataSet Object DataSet –An in-memory database Useful members (* covered later)

Clear Tables*

DataSet

Page 14: Data Access

DataTable Object A DataTable object is an in-memory database table. It contains:

Rows and columns of data Primary key, foreign key, constraint and relation information about the

data Useful members (* covered later)

ChildRelations* Clear Columns* Constraints* DataSet NewRow ParentRelations* PrimaryKey TableName

DataTables

Page 15: Data Access

DataColumn Object A DataColumn object represents a table column Useful members

AllowDBNull AutoIncrement AutoIncrementSeed AutoIncrementStep Caption ColumnName DataType DefaultValue MaxLength Ordinal Table Unique

DataColumn

Page 16: Data Access

DataRow Object A DataRow Object represents a row of data in a table Useful members

Item Table

DataRow

Page 17: Data Access

Demonstration

DataSets, DataTables, DataColumns and DataRows

Page 18: Data Access

DataTableCollection

A DataTableCollection contains DataTables Access the DataTableCollection of a DataSet by

using the Tables property Useful members

Add Count Clear Item Remove

Page 19: Data Access

Demonstration

DataTableCollection

Page 20: Data Access

DataColumnCollection A DataColumnCollection contains DataColumns Access the DataColumnCollection of a

DataTable by using the Columns property Useful members

Add Count Clear Item Remove

Page 21: Data Access

Demonstration

DataColumnCollection

Page 22: Data Access

DataRowCollection A DataRowCollection contains DataRows Access the DataRowCollection of a DataTable

by using the Rows property Useful members

Add Count Clear Item Remove

Page 23: Data Access

Demonstration

DataRowCollection

Page 24: Data Access

DataAdapter Object

DataAdapter – Used to retrieve data from a database and put it in a DataSet. Can also send changes from the DataSet to the database

Useful membersFillUpdate

Page 25: Data Access

DataAdapter Code

The Fill method opens the Connection if the connection is not already open. If Fill opened the connection, it will also close the connection.

Page 26: Data Access

Demonstration

DataAdapter Object

Page 27: Data Access

Programmatically Creating DataSet Objects DataSets, DataTables, DataRows and

DataColumns are all objects We can create and customize all of these

objects

Page 28: Data Access

Programmatically Creating DataSet Objects Code

Page 29: Data Access

Demonstration

Programmatically Creating DataSet Objects

Page 30: Data Access

DBNull, Nothing and “” There is a difference between having

No DataAnd “”

In a database, use null when you have no data In VB.Net

Use “” when you have an empty string Use nothing when there is no data in a variable Use dbnull when there is no data in a database field

Page 31: Data Access

Demonstration

DBNull, Nothing and “”

Page 32: Data Access

Storing Single Quotation Marks in a Database

We often store SQL queries in a stringDim SQL as String = “Insert into Students

(FirstName) values (‘Tevita’)” SQL queries use single quotation marks To store a single quote in a database, use

the single quote twiceDim SQL as String = “Insert into Students

(FirstName) values (‘Ma’’afu’)”

Page 33: Data Access

Demonstration

Storing Single Quotation Marks in a Database

Page 34: Data Access

Data Access Exceptions Often, errors occur when databases are used.

The name of the file is changed The location of a file is incorrect The program does not have the proper username and password

to access the database Common Data Access Exceptions

DataException - Exception that is thrown when errors are generated using ADO.NET components.

ConstraintException - Exception that is thrown when attempting an action that violates a constraint.

NoNullAllowedException - Exception that is thrown when attempting to insert a null value into a column where AllowDBNull is set to false.

ReadOnlyException - Exception that is thrown when attempting to change the value of a read-only column.

Page 35: Data Access

Demonstration

Data Access Exceptions