Transcript for data table objects

CTEC2902
Advanced Programming
Introduction
to
ActiveX Data Objects.NET
(.NET Class Library for Database Processing)
Intro to ADO.NET
1
Getting Started
The story so far…
You know how to
•Use existing classes (essential when using ADO.NET)
•Structure data in a RDB
•Use SQL to process relational data
You are now ready to start developing DB applications
Let’s carry on…
Intro to ADO.NET
2
Introduction to ADO.NET
RDB Classes in ADO.NET
RDB Component
ADO.NET class
Database
DataSet (for future use)
Table
DataTable
The focus of
Record
DataRow
our module
Field
Item
All underpinned by SQL
Intro to ADO.NET
3
Introduction to ADO.NET
ADO.NET Programming Components
DBMS
(Source)
SQL Server
or
Access
or
MySQL
or
Oracle
Connector
Data
Table
Data Adapter
No need to get involved;
Instead, use custom-made
class, named … clsSQLServer
SQL-populated
data tables
in VB + ADO.NET
Application
Written in
ASP + VB.NET
Make sure you have
a copy of its API
Intro to ADO.NET
4
Introduction to ADO.NET
Programming Steps
1.Create a database object, using clsSQLServer
(to link to & process a database, via SQL in stored procedures )
2.Create a data table object, using DataTable class
(to save the data returned by SELECT)
3.Use the QueryResults property of your database
object to populate your data table object, for viewing and
generating reports
Intro to ADO.NET
5
Introduction to ADO.NET
Step 1 – Creating Database objects
Dim object As New clsSQLServer(DBName)
Name of database to open
Your local object
Class name
E.g.
Dim MyDB As New clsSQLServer(“BSC_Computing.mdf”)
Dim AnotherDB As New clsSQLServer(txtDBName.Text)
Intro to ADO.NET
6
Introduction to ADO.NET
Step 2 – Creating Data Table Objects
Dim datatable_object As New DataTable
e.g. to create data table objects
Dim dtStudents As New DataTable
Dim dtModules As New DataTable
Note the prefix “dt” for data table objects
When data tables are created, they are empty;
so we must populate them
Intro to ADO.NET
To hold the records
returned by SELECT
7
Introduction to ADO.NET
Step 3 - Populating Data Tables
Dim DB As New clsSQLServer(“MyDB.mdf")
Dim dtStaff As New DataTable
DB.Execute("sp_tblStaff_GetName")
dtStaff = DB.QueryResults
Name of stored procedure
Your table
Results from SELECT after execution
Intro to ADO.NET
8
Introduction to ADO.NET
Sample Stored Procedure
CREATE PROCEDURE dbo.sp_tblStaff_GetAll
AS
SELECT * FROM tblStaff
Intro to ADO.NET
9
Introduction to ADO.NET
Another Sample Stored Procedure
CREATE PROCEDURE dbo.[sp_tblStaff_GetName]
AS
SELECT Surname + ', ' + FirstName AS Fullname
FROM tblStaff
Intro to ADO.NET
10
Introduction to ADO.NET
Fields, Columns, Attributes
Structure of a Data Table
0
Records
Rows
Tuples
1
0
1
2
2
3 etc.
Can also be
referred to by
field name
3
A data table can be
viewed as a
zero-based, 2-D array
Data item
Cell value
etc
Intro to ADO.NET
11
A Data table may contain (depending on the SELECT command):
1.
All the records from an RDB table
e.g. all students on a given module
2.
Some of the records from an RDB table; called “subset”
e.g. students who have not yet sat the phase test
3.
Records from 2 or more RDB tables; called “superset”
e.g. students + their course & mentors
Intro to ADO.NET
12
How do you view
data table contents?
Use Data-Bound List Boxes
(see example)
Use Data-Grid
(see example)
Intro to ADO.NET
13