Databases and Data Access

1. Databases and Data Access Mark SapossnekCS 594 Computer Science Department Metropolitan College Boston University

2. Objectives • Review database theory and history • Review relational database concepts • Learn about the evolution of data access technologies • Learn about the ADO.NET namespaces and core classes • Learn how to use ADO.NET classes in an application

3. Agenda • Databases • Relational Databases • ADO.NET Overview • ADO.NET Classes

4. Databases Databases • Virtually all interesting applications require a structured, persistent data store • E-Commerce: placing an order, fulfilling an order • HR: Personnel data • Sales • CRM: Customer data • Games • Database needs vary with the type of application • Transaction Processing/OLTP • Business Intelligence/Data Warehouse/OLAP

5. Databases Database Requirements • Can store, view and modify data • Can move, copy and transform data • Can backup and restore data • Enforces data integrity rules • Is scaleable and available • High number of users • Lots of data • High throughput with low response time • Is secure • Facilitates application development

6. DatabasesEvolution of Database Technology • File-based • Hierarchical • Network • Relational (RDBMS) • Object-oriented • XML

7. Agenda • Databases Theory and History • Relational Databases • ADO.NET Overview • ADO.NET Classes

8. Relational DatabasesTables • Table (relation, entity) • A collection of data about a specific type of thing • Organized in rows and columns • Column (attribute, field) • Describes part of an entity (e.g. FirstName) • Has a data type (e.g. integer, character, binary) • Can be null • Row (tuple, record) • A single instance of data in a table • Each row is unique

9. Relational DatabasesRelating Data • Tables can be related through primary/foreign key relationships (e.g., a book has an author) • Primary key • Guarantees the uniqueness of a row • Can be composed of one or more columns • Ensures entity integrity • Foreign key • Establishes logical relationship between tables • One or more columns of a table that match the primary or alternate key of another table • Referential integrity

10. Relational DatabasesRelating Data • Schema diagram depicts tables, columns, primary keys, foreign keys 1 ∞ Schema Diagram

11. Relational DatabasesRelating Data Books Table PrimaryKey PK/FK Relationship Foreign Key Authors Table

12. Relational DatabasesTypes of Relationships • One-to-One (1:1) • One row in table X matches one row in table Y • A book has at most one Library of Congress entry • One-to-Many (1:M) • One row in table X matches 0+ rows in table Y • A publisher publishes one or more books • Many-to-Many (M:N) • 1+ rows in table X matches 1+ rows in table Y • An author writes one or more books; a book is written by one or more authors 1 1 Books LoC Entries 1 M Publishers Books M N Authors Books

13. Relational DatabasesM:N Relationships • More complex • Can result in very large tables (repeated data) • Difficult to ensure data integrity • The remedy: Create a third table • The third table contains the primary key of the two original tables in a composite key • Data is repeated in the third table, but not in the two original tables Authors 1 M M 1 Books BookAuth

14. Relational DatabasesM:N Relationships 1 ∞ Data is duplicated here 1 1 ∞ ∞

15. Relational DatabasesNormalization/Denormalization • Normalization • The process of breaking large tables into multiple smaller tables • Goal: minimize redundant data, maximize correctness • Improves performance for updates • Desirable in transaction-based applications • Denormalization • The process of combining smaller tables into fewer larger tables • Goal: improve performance • Introduces redundant data • Improves performance for reads • Desirable in data warehouse applications

16. Relational DatabasesJoins • A join is a way of combining data in multiple tables, usually by resolving primary key/foreign key relationships Vendor table Producttable Vendor State Contact Product Cost Vendor Acme MA Linda A. Widget $10 Acme Blecco WA Adam P. Thingy $5 Acme Widget $8 Blecco Foobar $25 Blecco

17. Relational DatabasesJoins • Result of a natural join Product Cost Vendor State Contact Widget $10 Acme MA Linda A. Thingy $5 Acme MA Linda A. Widget $8 Blecco WA Adam P. Foobar $25 Blecco WA Adam P.

18. Relational DatabasesStructured Query Language (SQL) • Standard language for accessing a relational database, standardized by American National Standards Institute (ANSI); SQL-92 • Open, but not really • Common functions are mostly the same across products • Most vendors have proprietary extensions • Subsets of SQL • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL)

19. Relational DatabasesDDL Examples • Used to create and modify database objects CREATE DATABASE Bookstore CREATE TABLE tBooks ( BookID INT IDENTITY(1,1) PRIMARY KEY, Title VARCHAR(30) NOT NULL, PubDate DATE NOT NULL, [Description] VARCHAR(50), Category INT NOT NULL )

20. Relational DatabasesDML Examples • Select data to view SELECT * FROM tAuthors SELECT AuthID, FirstName, LastName FROM tAuthors SELECT AuthID, FirstName, LastName, Phone FROM tAuthors WHERE City = ‘Boston’ SELECT FirstName, LastName, Phone FROM tAuthors WHERE AuthID = 249

21. Relational DatabasesDML Examples • Using SELECT to join tables SELECT AuthID, FirstName, LastName, Phone, BookID, Title, PubDate, Description FROM tAuthors, tBooks WHERE tAuthors.AuthID = tBooks.AuthID SELECT AuthID, FirstName, LastName, Phone, BookID, Title, PubDate, Description FROM tAuthors INNER JOIN tBooks ON tAuthors.AuthID = tBooks.AuthID

22. Relational DatabasesDML Examples • Insert, update and delete data INSERT INTO tBooks (Title, PubDate, [Description], Category) VALUES (‘Database Design’, GETDATE(), ‘How to design a database’, 3) UPDATE tAuthors SET Phone = ‘617-555-1234’ WHERE AuthID = 5 DELETE FROM tAuthors WHERE AuthID = 5

23. Relational DatabasesDCL Examples • Set security options on database objects GRANT INSERT, UPDATE, DELETE ON tAuthors TO Mary, John REVOKE CREATE TABLE FROM Joe DENY ALL ON tAuthors, tBooks TO Sally

24. Relational DatabasesViews • A view is a virtual table • Abstracts the underlying table structures • Abstracts a (possibly complex) query • Provides security abstraction from table • In SQL Server 2000, a view can be • Indexed • Updated and inserted into

25. Relational Databases View Definition Example CREATE VIEW vwCustomerOrders AS SELECT o.OrderId, c.CompanyName FROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerID ORDER BY o.OrderId

26. Relational DatabasesView Usage Example SELECT * FROM vwCustomerOrders WHERE CompanyName = 'My Favorite Customer'

27. Relational DatabasesStored Procedures • A group of SQL statements that runs within the database • Not part of SQL standard • Provides greater performance • Can control access to data • Can accept parameters • Can return data • Output parameters • Return values • Result set

28. Relational Databases Stored Procedure Example CREATE PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName

29. Relational DatabasesStored Procedure Examples exec CustOrderHist 'alfki'

30. Relational Databases Stored Procedure Examples • Use RETURN statement to return status • 0 is default in SQL Server • Can only be numeric • Use OUTPUT parameters to return results RETURN 1 CREATE PROCEDURE MyProcedure @ReturnValue INT OUTPUT ... SELECT @ReturnValue = ColumnName FROM Table

31. Relational DatabasesTriggers • Like stored procedures, triggers are code that runs within a database • Not directly called by a user • Executed when a specified data modification takes place (INSERT, UPDATE or DELETE) • Enforces business rules • FOR AFTER: trigger executes after triggering action completes • FOR INSTEAD OF: trigger executes in place of triggering action

32. Relational DatabasesTransactions • Transaction: a sequence of SQL statements that constitute a logical unit of work • Must adhere to ACID properties • Atomic: All statements execute successfully or all fail • Consistent: Must leave the data in a consistent state when completed • Isolated: Cannot see the modifications made by concurrent transactions • Durable: Must be permanent when complete, even in the event of system failure

33. Relational DatabasesConcurrency • Isolation levels • Read Uncommitted • Read Committed • Repeatable Read • Serializable • Tradeoffs (concurrency vs. data integrity) • Locking • Ensures transactional integrity/database consistency • Prevents users from seeing “phantom” data • Can result in deadlocks

34. Agenda • Databases • Relational Databases • ADO.NET Overview • ADO.NET Classes

35. ADO.NET OverviewLooking Back • ODBC (Open Database Connectivity) • Interoperability to a wide range of database management systems (DBMS) • Widely accepted API • Uses SQL as data access language • DAO (Data Access Objects) • Programming interface for JET/ISAM databases • Uses automation (ActiveX, OLE automation) • RDO (Remote Data Objects) • Tighter coupling to ODBC • Geared more to client/server databases (vs. DAO)

36. ADO.NET OverviewLooking Back • OLE DB • Broad access to data, relational and other • Built on COM • Not restricted to SQL for retrieving data • Can use ODBC drivers • Low-level (C++) interface • ADO (ActiveX Data Objects) • Simple component-based, object-oriented interface • Provides a programming model to OLE DB accessible outside of C++

37. ADO.NET OverviewLooking Back Your Application ADO OLE DB ODBC Provider Simple Provider Native Provider ODBC OLE DB Provider OLE DB Provider ODBC Driver TextFile Mainframe Database Database

38. ADO.NET OverviewLooking Back • ADO was designed as a connected, tightly coupled model • Appropriate for client/server architectures • Primarily relational (not hierarchical like XML) • Object design is not well factored • Too many ways to do the same thing • Objects try to do too much • Not originally designed for a distributed, n-tier environment

39. ADO.NET OverviewWhat Is ADO.NET? • ADO .NET is a collection of classes, interfaces, structures, and enumerated types that manage data access from relational data stores within the .NET Framework • These collections are organized into namespaces: • System.Data, System.Data.OleDb, System.Data.SqlClient, etc. • ADO .NET is an evolution from ADO. • Does not share the same object model, but shares many of the same paradigms and functionality!

40. ADO.NET OverviewADO.NET Goals • Well-factored design • Highly scaleable through a robust disconnected model • Rich XML support (hierarchical as well as relational) • Data access over HTTP • Maintain familiar ADO programming model • Keep ADO available via .NET COM interoperability

41. ADO.NET OverviewManaged Providers • Merges ADO and OLEDB into one layer • Each provider contains a set of classes that implement common interfaces • Initial managed provider implementations: • ADO Managed Provider: provides access to any OLE DB data source • SQL Server Managed Provider: provides optimal performance when using SQL Server • Exchange Managed Provider: retrieve and update data in Microsoft Exchange

42. ADO.NET OverviewManaged Providers Your Application ADO.NET Managed Provider SQL Managed Provider ADO Managed Provider OLE DB Provider SQL ServerDatabase Database

43. ADO.NET OverviewData Access Styles • Connected: Forward-only, read-only • Application issues query then reads back results and processes them • “Firehose” cursor • DataReader object • Disconnected • Application issues query then retrieves and stores results for processing • Minimizes time connected to database • DataSet object

44. ADO.NET OverviewData Binding • Key component of Web Forms framework • Flexible and easy to use • Bind a control’s property to information in any type of data store • Provides control over how data moves back and forth • Simple controls for displaying a single value • Complex controls for displaying a data structure <asp:Label runat=server Text='<%# CustList(0).FirstName %>'/>

45. Agenda • Database Theory and History • Relational Database Concepts and Terminology • ADO.NET Overview • ADO.NET Classes

46. ADO.NET ClassesIDbConnection Interface • Creates a unique session with a data source • Implemented by SqlDbConnection and OleDbConnection • Functionality • Open, close connections • Begin transactions • IDbTransaction provide Commit and Rollback methods • Used in conjunction with IDbCommand and IDataAdapter objects • Additional properties, methods and collections depend on the provider

47. ADO.NET Classes IDbCommand Interface • Represents a statement to be sent to a data source • Usually, but not necessarily SQL • Implemented by OleDbCommand and SqlCommand • Functionality • Define statement to execute • Execute statement • Pass and retrieve parameters • Create a prepared (compiled) version of command • ExecuteReader returns rows, ExecuteNonQuery doesn’t, ExecuteScalar returns single value • Additional properties, methods and collections depend on the provider

48. ADO.NET Classes IDataReader Interface • Forward-only, read-only (“fire hose”) access to a stream of data • Implemented by SqlDataReader and OleDbDataReader • Created via ExecuteReader method of IDbCommand • Operations on associated IDbConnection object disallowed until reader is closed

49. ADO.NET Classes System.Data.OleDb Namespace • Managed provider for use with OLEDB providers • SQLOLEDB (SQL Server) – use System.Data.SQL • MSDAORA (Oracle) • JOLT (Jet) • OLEDB for ODBC providers • OleDbConnection, OleDbCommand and OleDbDataReader classes • Classes for error handling • Classes for connection pooling

50. ADO.NET Classes DataReader Example string sConnString = “Provider=SQLOLEDB.1;” + “User ID=sa;Initial Catalog=Northwind;” + “Data Source=MYSERVER”; OleDbConnection conn = new OleDbConnection(sConnString); conn.Open(); string sQueryString = “SELECT CompanyName FROM Customers”; OleDbCommand myCommand = new OleDbCommand(sQueryString, conn); OleDbDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { Console.WriteLine(myReader.GetString(0)); } myReader.Close(); conn.Close();