data access

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 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.

ADO .Net Components

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

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

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

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

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

ExecuteNonQuery Code

ExecuteNonQuery doesnot return anything

Demonstration

Connections, Commands and ExecuteNonQuery

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

DataReader and ExecuteReader Code

Using a DataReaderto display data fromdatabase

Keeps loopinguntil all rowsare read

Gets string from first index

ExecuteReader returnsA DataReader

Demonstration

Connections, Commands and DataReader.ExecuteReader Code

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

Clear Tables*

DataSet

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

DataColumn Object A DataColumn object represents a table column Useful members

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

DataColumn

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

Item Table

DataRow

Demonstration

DataSets, DataTables, DataColumns and DataRows

DataTableCollection

A DataTableCollection contains DataTables Access the DataTableCollection of a DataSet by

using the Tables property Useful members

Add Count Clear Item Remove

Demonstration

DataTableCollection

DataColumnCollection A DataColumnCollection contains DataColumns Access the DataColumnCollection of a

DataTable by using the Columns property Useful members

Add Count Clear Item Remove

Demonstration

DataColumnCollection

DataRowCollection A DataRowCollection contains DataRows Access the DataRowCollection of a DataTable

by using the Rows property Useful members

Add Count Clear Item Remove

Demonstration

DataRowCollection

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

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.

Demonstration

DataAdapter Object

Programmatically Creating DataSet Objects DataSets, DataTables, DataRows and

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

objects

Programmatically Creating DataSet Objects Code

Demonstration

Programmatically Creating DataSet Objects

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

Demonstration

DBNull, Nothing and “”

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’)”

Demonstration

Storing Single Quotation Marks in a Database

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.

Demonstration

Data Access Exceptions