Transcript Data Access - Tonga Institute of Higher Education

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 results
 Connection
 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

2.
Located in System.Data.SQLClient namespace
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
Local DB
Network DB


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 always
need a User ID
and Password
Opening and Closing Connections

Before a Connection object can work with
a database, the connection must be
opened
 Use

the Open() method
After the Connection object is used, the
connection must be closed
 Use
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 does
not 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 DataReader
to display data from
database
ExecuteReader returns
A DataReader
Keeps looping
until all rows
are read
Gets string from first index
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 members

 Fill
 Update
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
Data
And
 “”


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 string
 Dim
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 twice

 Dim
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