Databases, Sql, And

Databases, SQL, and ADO.NET (part 1)

Objectives “The majority of today's applications revolve around relational databases and the SQL programming language (Structured Query Language). In .NET, ADO.NET (Active Data Objects) is the primary means of database programming. ADO.NET is a vendorneutral, object-oriented, SQL-based approach.”

• • • •

Overview of relational databases, DBMSs, and SQL ADO.NET Reading a database Modifying a database

Microsoft

2

Part 1 •

Overview of relational databases and SQL…

Microsoft

3

Relational databases •

Most of today's databases are relational: – database contains 1 or more tables – table contains 1 or more records – record contains 1 or more fields – field contains data, or NULL

•

So why is it called "relational"? – tables are related (joined) based on common fields

Microsoft

DB

4

Example •

Here's a simple database for tracking sales: – 3 tables, related by primary keys (CID, OID, PID) – primary keys (in boldface) are unique record identifiers

Microsoft

5

Database management systems • A DBMS consists of 2 main pieces: – the data – the DB engine

request

DB Engine

Data

– the data is typically stored in one or more files

Microsoft

6

Common types of DBMS •

Two most common types of DBMS are: – Local – Server

Microsoft

7

Local DBMS • A local DBMS is where DB engine lives within client

DB Engine

Data

client process

• Example? – MS Access – underlying DB engine is JET ("Joint Engine Technology") Microsoft

8

Server DBMS • A server DBMS is where DB engine runs as a separate process – typically on a different machine (i.e. server)

network?

client

DB Engine

Data

server

– Examples? • MS SQL Server, Oracle, DB2, MySQL Microsoft

9

Structured Query Language •

SQL is the standard language of relational databases – SQL is written in the form of queries – action queries insert, update & delete data – select queries retrieve data from DB

Microsoft

10

Select queries •

Select queries retrieve data w/o changing DB – you specify what data – you specify which table(s) to pull from – you specify under what conditions to retrieve data (if any) – you specify how to present data (if any)

•

Key concept: – what you get back is another (temporary) table!

Microsoft

11

Example •

Select all customers, in no particular order: Select * From Customers;

Microsoft

12

Example #2 •

Select all customers with a balance, in sorted order: Select * From Customers Where Balance > 0.0 Order By LastName Asc, FirstName Asc;

Microsoft

13

Computed fields • •

SQL is a programming language, so it can do computation Example: – compute # of customers, average balance, and max balance Select count(*) as COUNT, avg(Balance) as AVG, max(Balance) as MAX From Customers;

Microsoft

14

Joins • •

You have to "join" tables when you want to combine data Example: – what's the name of the customer that placed order #12351? – we need to join Customers table with Orders table…

Select FirstName, LastName From Customers Inner Join Orders On Customers.CID = Orders.CID Where Orders.OID = 12351;

Microsoft

15

Nested queries • •

Often you need to nest queries Example: – what products have been ordered by "Jim Bag"? – requires 2 steps: 1. TEMP = join Orders with Customers to get his orders 2. then join Products with TEMP to get product names Select Distinct Name From Products Inner Join (Select PID From Orders Inner Join Customers On Orders.CID = Customers.CID Where FirstName = 'Jim' and LastName = 'Bag') as TEMP On Products.PID = TEMP.PID Order By Name Asc;

Microsoft

16

Part 2 •

ADO.NET and read-only database access…

Microsoft

17

Architecture •

ADO.NET architecture based on data providers – data providers encapsulate DB-specific implementation details .NET Programmer

ADO.NET

Data Provider

DB Microsoft

18

Existing data providers •

.NET currently ships with 3 data providers: – one for Microsoft SQL Server – one for Oracle (written by Microsoft) – one for everyone else

•

More are available from vendors themselves… – Oracle – DB2 – etc.

Microsoft

19

ADO.NET object model • •

ADO.NET is based on OOP Classes are spread across a number of FCL namespaces – some are provider-neutral, others are provider-specific providerneutral

System.Data

System.Data.Common SQL Server

System.Data.SqlClient System.Data.OleDb

Microsoft

other DBs

20

Overview of database access • Three steps: 1. open connection to database 2. execute SQL to update DB / retrieve records 3. close connection

Microsoft

21

Step 1: open connection •

Connections are opened based on connection string info – here we open a connection to a MS Access 2000 database – "Sales.mdb" must exist in same dir as .EXE (e.g. bin\Debug) using System.Data; using System.Data.OleDb; string sConnection; sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Sales.mdb";

connection

OleDbConnection dbConn; dbConn = new OleDbConnection(sConnection); dbConn.Open(); MessageBox.Show( dbConn.State.ToString() );

Microsoft

22

Building connection strings • •

Connection strings are vendor-specific, not well-documented Where to turn for help? – www.connectionstrings.com – www.able-consulting.com/ADO_conn.htm

Microsoft

23

Step 2: retrieve records •

Retrieve records via SQL Select query – read-only access via data reader & field names string sql; Customer c; sql = "Select * From Customers Order By LastName Asc, FirstName Asc;";

OleDbCommand dbCmd; dbCmd = new OleDbCommand(); dbCmd.CommandText = sql; dbCmd.Connection = dbConn;

command

connection

data reader

record record record

OleDbDataReader dbReader; dbReader = dbCmd.ExecuteReader(); while (dbReader.Read()) {

// retrieve records 1-by-1...

c = new Customer(dbReader["LastName"].ToString(), ...); this.listBox1.Items.Add(c); } Microsoft

field name of data value in current record 24

Step 3: close connection •

Be sure to close reader and connection… – to flush pending updates (in general) – so others can access DB (connections are limited resources)

dbReader.Close(); dbConn.Close();

Microsoft

25

Guaranteed close? •

Ensure reader & DB are closed via Try-Catch-Finally: OleDbConnection OleDbDataReader

dbConn = null; dbReader = null;

try { dbConn = new OleDbConnection(sConnection); dbConn.Open(); . . . } catch(Exception ex) { System.Diagnostics.Debug.WriteLine("Call stack: " + ex.StackTrace); System.Diagnostics.Debug.WriteLine("DB Error: " + ex.Message); throw ex; } finally { if (dbReader != null) dbReader.Close(); if ((dbConn != null) && (dbConn.State != ConnectionState.Closed)) dbConn.Close(); } Microsoft

26

Part 3 •

Modifying a database…

Microsoft

27

Action queries • •

Action queries update a database Main actions are insert, update, and delete – note date and string values require special delimiters in SQL – bad things happen if you forget them…

Insert Into Orders(OID, CID, PID, Quantity, DateOfPurchase) Values(33411, 14, 1, 1, #06-01-2003#); Update Customers Set CreditLimit = 40000000000.0, Balance = 0.0 Where LastName = 'Gates' and FirstName = 'Bill'; Delete From Customers Where CID = 666;

Microsoft

28

Example #1 •

Example: – delete selected customer…

string

sql;

int

result;

Customer

c;

c = (Customer) this.listBox1.SelectedItem; sql = String.Format("Delete From Customers Where CID={0};", c.ID); OleDbCommand dbCmd; dbCmd = new OleDbCommand(); dbCmd.CommandText = sql; dbCmd.Connection = dbConn; dbConn.Open(); result = dbCmd.ExecuteNonQuery(); dbConn.Close();

// perform deletion

if (result != 1) throw new Exception("Query ran, but failed to delete customer?"); this.listBox1.Items.Remove(c);

Microsoft

// update GUI as well

29

Example #2 •

Delete customer based on full name…

string

sql, sFormat;

c = (Customer) this.listBox1.SelectedItem; sFormat = "Delete From Customers Where FirstName='{0}' and LastName='{1}';" ;

sql = String.Format(sFormat, c.FirstName, c.LastName); . . .

// execute as before…

Microsoft

30

Summary •

Just the tip of the iceberg… – really just scratched the surface of ADO.NET

•

There's lots more to discuss: – design considerations? – reducing the load on the database server? – stored procedures? – transactions? – ...

Microsoft

31