Transcript Introduction to ADO.NET

Introduction to ADO.NET
High Level Overview
Topics
What is ADO.NET?
Three-tier model
Data Providers
SQL
Relational Database
What is ADO.NET?
Microsoft ActiveX Data Object (old)
ADO.NET object provides an API for accessing database systems
programmatically
Interface/Interaction
–
–
–
–
Relational database
XML
Excel Spreadsheet
Text file
Object-orientated set of libraries
Used in
– Windows
– Console
– Web (asp)
Three Tier Architecture
C# Front-end
Client
Or
User Interface
C# Middle-layer
ADO.NET
Database
Management
System
Business
Layer
Data
Store
Windows,
Console,
Web
Classes,
Components
A Web Example
Two Tier Architecture
C# Front-end
Client
And
Business
Rules
ADO.NET
Database
Management
System
Data
Store
Windows,
Console,
Web
Classes,
Components
Data Providers
Data sources (e.g. databases)
– Expose different protocols
– Choose the right protocol for the right database
Older data sources use ODBC
Newer data sources use OleDb
ODBC and OleDb are APIs
Data Providers
Provider
Name
API
Prefix
Data Source Description
ODBC
Data
Provider
Odbc
Data Sources with an ODBC interface. Normally older data bases.
OleDb
Data
Provider
OleDb
Data Sources that expose an OleDb interface, i.e. Access or Excel.
Oracle
Data
Provider
Oracle
For Oracle Data Bases.
SQL Data
Provider
Sql
For interacting with Microsoft SQL Server.
Borland
Data
Provider
Bdp
Generic access to many data bases such as Interbase, SQL Server,
IBM DB2, and Oracle.
Namespaces Available
System.Data
– Root namespace
System.Data.OleDB
– Works with any data source
System.Data.SqlClient
– Designed to work with Microsoft SQL Server
Some ADO.NET Objects
SqlConnection Object
SqlCommand Object
SqlDataReader Object
DataSet Object
SqlDataAdapter Object
SQL
Structured Query Language
ADO.NET interacts with relational database with
the help of SQL
Need to know SQL in order to interact with a
Database Management System (DBMS)
Relational Database
Uses tables
Each row is a record
Each record has a key field
A key field should be unique
Tables are “connected” via foreign keys (a key from
another table)
Simple ERD
Entity Relationship Diagram
Entity more-or-less equates to a table
Each primary key can become a foreign key in another
table
Sample Table: Student
Student#
Name
Surname
Address
Phone#
20412345 Bob
Smith
3 3rd Ave
1233456
20132059 Nigel
Brown
5 3rd Ave
2049248
20213049 Barbara
Ngeni
4 3rd Ave
1204938
Sample Table: Subject
Code
Subject Name
Diploma
Prerequisites
PRT2311
Technical
N. Dip IT
Programming II (Technical
Applications)
PRT1211
PRT1211
Technical
Programming I
PRT1121
N. Dip IT
(Technical
Applications)
Sample Table: Registration
Year of Study
Code
Student#
2005
PRT2311
20412345
2005
PRT2311
20213049
2004
PRT1211
20412345
2004
PRT2311
20213049
Relational Databases
Use SQL to write data into tables
– INSERT statement
Use SQL to retrieve data from tables
– SELECT statement
Use SQL to delete data from tables
– DELETE statement
Use SQL to edit data in tables
– UPDATE statement
See page 905 in Deitel & Deitel
Asking Specific Questions
SELECT <fields>
FROM <tables>
WHERE <conditions>
INNER JOIN <tables>
GROUP BY <fields>
ORDER BY <fields>
Structure of a query
Fields and tables are separated by commas (,)
Conditions use the usual operators (=, >, <, and, or)
SQL Examples
MS Access Chocolate Factory
MS Access
– Own version of SQL (called Jet)
– Queries similar to Oracle
– Only good for SMALL applications
•
•
•
•
•
Not very robust
Not extensible (doesn’t support a large number of users)
Doesn’t delete data
Doesn’t handle concurrency issues very well
Gets big very quickly (bloats)
Sources
C# Station
– http://www.csharpstation.com/Tutorials/AdoDotNet/Lesson01.aspx
Deitel & Deitel (Chapter 19)
Deitel & Deitel - C# 2005- (Chapter 20)
Gittleman (Chapter 15)
http://msdn.microsoft.com/library/default.asp?url=/library/en
-us/cossdk/html/a03327c1-e427-4c07-b3d4808ce81c2c96.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en
-us/xmlsql/ac_xml1_64md.asp