  
                     
|
| |
 |
ORACLE APPLICATIONS USING ADO.NET AND C#
| Course Description |
|
Oracle is a robust and powerful data storage engine that provides services on all levels of an application infrastructure – combining programmer-definable objects and tools, data manipulation tools that are capable of retrieving data from a host of data sources and storing those data in a database, and the capability to automate tasks and run them under developer control. This comprehensive, five-day course is designed to provide programmers an introduction to database programming, focusing on database design working with Oracle as your primary data store.
Course Length: 5 Days
Course Tuition: $1890 (US)
|
| Prerequisites |
|
| .NET Framework fundamentals, including building simple Windows Forms and Web Forms applications. Exposure to relational databases of some sort and prior knowledge of Oracle is helpful, but not required.
|
• Volume 1
• Introduction to Oracle9i
Oracle RDBMS Terminology
Oracle’s Physical Database Structures
Oracle’s Logical Database Structures
Oracle Segments
Oracle Tablespaces
Oracle Datafiles
Oracle Redo Log Files
Oracle Enterprise Manager
Benefits of Oracle Enterprise Manager
OEM Architecture
Two-Tiered OEM
Tier One: OEM Console
OEM Console
Tier Two: Oracle Management Server
Multiple Management Servers
Tier Three: Managed Nodes
Intelligent Agents
OEM Common Services
Oracle Database Objects
Oracle Table Functionality
Relating Information Between Tables
Using Table Aliases in SELECT Statements
Oracle Procedural Functionality
Oracle View Functionality
Oracle Synonym Functionality
Oracle Type Functionality
Oracle Object Functionality
Oracle Information Storage
Using SQL*PLUS
• Relational Database Basics and Design
Relational Database Concepts
High-Level Database Types: OLAP Databases
High-Level Database Types: OLTP Databases
High-Level Database Types: OLAP vs. OLTP
Functions of a Relational Database
Making the Conceptual Move
Structuring Tables Using Keys
Enforcing Relationships Using Foreign Keys
Relating Foreign Keys and Primary Keys
Second Normal Form
Third Normal Form
Deriving a Design from a Scenario
Going to First Normal Form
Going to Second Normal Form
Going to Third Normal Form
• Programming SQL Statements
Oracle SQL Language
Database Object Permissions
Object Privileges
System Privileges
Roles: Groups of Privileges
Using Roles to Manage User Permissions
Data Definition Language (DDL) Statements: CREATE …
Data Definition Language (DDL) Statements: DROP …
Data Definition Language (DDL) Statements: ALTER …
Data Modification (DML) Statements: INSERT …
Retrieving Information from Tables: SELECT …
Displaying Information from Tables: SELECT …
Displaying Information from Tables: Customizing Output with Column Headers
Using the WHERE Keyword to Select Specific Rows of Data
Using Relational Operators in the WHERE Clause
Using Pattern Matching in the WHERE Clause
Checking for Known and Unknown Quantities in the Database
Looking for Multiple Values in the Database: IN and BETWEEN
Creating Complex Where Clauses
Sorting Output from a SELECT Query
Sub-Sorting Output from a SQL Query
Sorting on Non-Displayed Columns
Reversing Order of Sorting Output
Removing Data From a Table: DELETE
Modifying Existing Data in a Table: UPDATE
Using Data Control Language (DCL) to Control Transactions in a Database
Committing Changes
Rolling Back Changes
Organizing Information into Groups
Using the GROUP BY Clause
Aggregating Functions
Using Aggregate Functions with GROUP BY
Using Non-Aggregated Columns with a GROUP BY
Joining Tables Using ANSI Standard Syntax
Joining Tables Using Oracle Syntax
• Implementing Oracle Objects
Creating a Database
Oracle’s Physical Database Structures
Oracle’s Logical Database Structures
Manually Creating a Database
Explanation of CREATE DATABASE Command
Using Control Files
Guidelines for Creating Control Files
Oracle Redo Log Files
Creating New Tablespaces
Review of CREATE TABLE
Constraints That May be Added to a Table
PRIMARY KEY Constraint
Naming the Constraints
UNIQUE Constraint
Using the UNIQUE Constraint in Table Creation
Specifying the Location of a Table
Creating Stored Procedures and Functions
Creating and Executing a Procedure
Using Data Dictionary Views to See Information About Procedures
Using the USER_SOURCE View
Using Data Dictionary Views to Find Information about Tables
Using Triggers
Testing the Trigger
Viewing the Contents of Triggers
• Indexes and Storage Types
Overview of Indexes
Creating Indexes
Creating Unique and Non-Unique Indexes
Building a Composite Index
Using Indexes to Speed Data Retrieval
Physical Storage of Indexes
Creating an Index-Organized Table
Hidden Indexes
Creating a PRIMARY KEY Constraint Using an Explicit Index
Tracking Usage of an Index
• Transactions and Locks within Oracle9i
Oracle Transactions
Transaction Management Overview
Marking your Place within a Transaction: SAVEPOINT
Rolling Back Uncommitted Transactions: ROLLBACK
Creating Transactions
Implicit Transactions
Locks
Oracle Transactions and Database Concurrency
Explicit (Manual) Data Locking
Explicit (Manual) Locking at the Table Level
Table Locking Modes
Implicit Row Locking
Implicit Row Locking using the FOR UPDATE OF Clause
Serializable Isolation Level
Modifying the Isolation Level
Choosing an Isolation Level
Recovery from a Serialization Error
Isolation Levels and Internal Locking
• Oracle PL/SQL and User-Defined Data Types
Oracle Programming Objects
Benefits of PL/SQL
Structure of a PL/SQL Block
DECLARE Section
PL/SQL Data types
PL/SQL Control Structures
PL/SQL Conditional Statements: IF
PL/SQL Conditional Statements: CASE
PL/SQL Iterative Statements: LOOP
PL/SQL Iterative Statements: LOOP; EXIT; RETURN
PL/SQL Iterative Statements: EXIT-WHEN
PL/SQL Iterative Statements: WHILE … LOOP
PL/SQL Iterative Statements: FOR … LOOP:
PL/SQL Sequential Control: GOTO and NULL
PL/SQL Error Handling: Exceptions
Exception Handling Block
Complete PL/SQL Block
Managing Cursors
Overview of Explicit Cursors
Declaring a Cursor
Declaring Cursors
Simplifying Cursor Coding with a FOR Loop
Instantiating User-Defined Data Types
Creating and Using a User-Defined Data Type
• Oracle and XML Integration
Introduction to XML
Benefits of XML
Selecting Elements from an XML Document
Transforming an XML Document
Structure of XSL Stylesheet
Oracle Utilities and Technologies for Use with XML
Creating a Well-Formed XML Document
Rules for Well-Formatted XML Documents
Document Type Definition (DTD)
Storing XML Datagrams
XML Extensions to Oracle
Generating XML from a Table's Contents: DBMS_XMLGEN Package
Generating XML from Existing Table Data
Summary of DBMS_XMLGEN Methods
Combining XSQL, XSLT and XML
Retrieving Data: MODEL.XSQL
Formatting the Data into XML: MODEL.XSL
|
• Appendix A Acme Computer Case Study
• Appendix B Learning Resources
• Volume 2
• Database Application Basics
Data Modeling
Data Modeling – Key Concepts
Conceptual Model
Logical Model
Physical Data Model
Performance Considerations
Acme Computer Case Study
Buy Computer
Model
Component
Part
PartsConfiguration
System
Tracking the System ID
SystemDetails
StatusCode
Relationships
Stored Procedure
Additional Tables
• Moving to ADO.NET
Microsoft Data Access Technologies
ODBC
OLE DB
ActiveX Data Objects (ADO)
ADO Is Connection Oriented
ADO.NET
ADO.NET Architecture
.NET Data Providers
• ADO.NET Basics
ADO.NET Block Diagram
.NET Data Providers
Namespaces for .NET Data Providers
Oracle Data Provider for .NET
Basic Connection Programming
Using Interfaces
IDbConnection Properties
Connection String
ODP.NET Connection String
OLE DB Connection String
IDbConnection Methods
Connection Life Cycle
ODPConnect (Step 2)
Connection Pooling
Pool Settings for Oracle
Connection Events
ADO.NET Error Handling
DataReader
Using a DataReader
IDataRecord
Type-Safe Accessors
Finding the .NET Type
GetOrdinal()
Null Data
Testing for Null
Using List Boxes
• ADO.NET Commands
Command Objects
Creating Commands
Executing Commands
Dynamic Queries
Parameterized Queries
OracleDbType Enumeration Type
Command Types
Stored Procedures
Testing the Stored Procedure
Stored Procedures in ADO.NET
Using an Oracle REF CURSOR
Client Program Using REF CURSOR
• Data Sets and Disonnected Access
DataSet
DataSet Architecture
Why DataSet?
DataSet Components
DataAdapter
Filling a DataSet
Accessing a DataSet
Updating a DataSet Scenario
Adding a New Row
Searching and Updating a Row
Deleting a Row
Row Versions
Row State
BeginEdit and CancelEdit
DataTable Events
Updating a Database
Insert Command
Update Command
Delete Command
Command Builders
Filtering DataSets
• XML and ADO.NET
ADO.NET and XML
Rendering XML from a DataSet
XmlWriteMode
Writing Data As Attributes
Reading XML into a DataSet
DataSets and XML Schema
ModelSchema.xsd
Schema in Visual Studio .NET
Reading XML Schema
XmlReadMode
Typed DataSets
VS.NET Database Wizards with Oracle
Oracle Connection in Server Explorer
Using the Typed DataSet
Synchronizing DataSets and XML
Using XmlDataDocument
XML Serialization
Default Constructor
• Data Binding
Data Binding Concept
Simple Data Binding
Binding to a List
Binding to Combobox
Binding to DataGrid
DataView
Filtering and Sorting
DataView and Data Binding
Data Binding in Web Forms
Creating a Virtual Directory
• Creating and Using a Data Access Layer
One-Tier Systems
Disadvantages of One-Tier
PCs
PC LANs
Database Servers
Client/Server Systems
Fat Clients
Scalability and Flexibility
Three-Tier Systems
Application Servers
Benefits of Three-Tier Architecture
Three-Tier Architecture using .NET
User Interface Components
Designing User Process Components
Data Access Components
• Appendix A Acme Computer Case Study
• Appendix B Learning Resources
Contact us for course schedules or more
information.
|
|
|
|
|
|
|
