Databases and Data Access

Document Sample
Databases and Data Access Powered By Docstoc
					Databases and
 Data Access

 Review database theory and history
 Review relational database concepts
 Learn about the evolution of data access
 Learn about the ADO.NET namespaces and
  core classes
 Learn how to use ADO.NET classes in an
   Databases
   Relational Databases
   ADO.NET Overview
   ADO.NET Classes

 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
                  Database Requirements

   Can store, view and modify data
   Can move, copy and transform data
   Can backup and restore data
   Has data integrity
   Is scaleable and available
       High number of users
       Lots of data
       High throughput with low response time
 Is secure
 Is capable of application development
       Evolution of Database Technology

   File-based
   Hierarchical
   Network
   Relational (RDBMS)
   Object-oriented
   XML
   Databases Theory and History
   Relational Databases
   ADO.NET Overview
   ADO.NET Classes
            Relational Databases

 Table (relation, entity)            AuthID   FirstName   LastName
                                   1           Joe         Smith
      A collection of data about
       a specific thing            2           Diane       Jones

      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
             Relational Databases
                          Relating 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
        Relational Databases
                    Relating Data

 Schema diagram depicts tables, columns,
  primary keys, foreign keys

          Books                 Authors
          BookID                AuthID
          AuthID    ∞           FirstName
          Title                 LastName

                   Schema Diagram
              Relational Databases
                             Relating Data

                        Books Table
Primary Key              BookID    AuthID   Title               Type
                         1         2        My Life as a DBA    Autobiography
                         2         1        Database Handbook   Reference

            PK/FK Relationship
                                                       Foreign Key
   AuthID   FirstName   LastName
   1        Joe         Smith
   2        Diane       Jones

  Authors Table
           Relational Databases
               Types of Relationships
                                         1    1
 One-to-One (1:1)            Books               LoC Entries
     One row in table X matches one row in table Y
     A book has at most one Library of Congress entry
                                         1    M
 One-to-Many (1:M)         Publishers                Books
     One row in table X matches 0+ rows in table Y
     A publisher publishes one or more books
                                         M    N
 Many-to-Many (M:N)          Authors                 Books

     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
              Relational Databases
                        M:N Relationships

   More complex
   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
                         1   M              M   1
              Authors            BookAuth           Books
                Relational Databases
                   M:N Relationships

tAuthors               tBooks
AuthID                 BookID

LastName               AuthID
FirstName              Title
                                               Data is duplicated here
Street                 PubDate
City                   Description
State                  Category

                                     1                       1 tBooks
                   AuthID                    tBookAuth         BookID
                   LastName                  BookID
                   FirstName                                   PubDate
                   Street                                      Description
                   City                                        Category
              Relational Databases

 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
            Relational Databases

 A join is a way of combining data in multiple
  tables, usually by resolving primary key/foreign
  key relationships

   Product table               Vendor table
   Product Cost    Vendor      Vendor   State   Contact
   Widget    $10   Acme        Acme     MA      Linda A.
   Thingy    $5    Acme        Blecco   WA      Adam P.
   Widget    $8    Blecco
   Foobar    $25   Blecco
          Relational Databases

 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.
           Relational Databases
       Structured 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
     Most vendors have proprietary extensions
 Subsets of SQL
     Data Definition Language (DDL)
     Data Manipulation Language (DML)
     Data Control Language (DCL)
         Relational Databases
                  DDL Examples

 Used to create and modify database objects

    BookID        INT IDENTITY(1,1) PRIMARY KEY,
    Title         VARCHAR(30) NOT NULL,
    PubDate       DATE NOT NULL,
    [Description] VARCHAR(50),
    Category      INT NOT NULL
         Relational Databases
                  DML 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
         Relational Databases
                  DML Examples

 Insert, update and delete data
    INSERT INTO tBooks
       (Title, PubDate, [Description], Category)
       („Database Design‟, GETDATE(),
        „How to design a database‟, 3)

UPDATE tAuthors                 DELETE FROM tAuthors
   SET Phone = „617-555-1234‟      WHERE AuthID = 5
   WHERE AuthID = 5
         Relational Databases
                  DCL Examples

 Set security options on database objects
          ON tAuthors
          TO Mary, John


       DENY ALL
          ON tAuthors, tBooks
          TO Sally
             Relational Databases

   A view is a virtual table
   Abstracts the underlying table structures
   Abstracts a (possibly complex) query
   Provides security abstraction from table
   In SQL Server, a view can be
       Indexed
       Updated and inserted into
         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
         Relational Databases
                View Usage Example

FROM vwCustomerOrders
WHERE CompanyName = 'My Favorite Customer'

          OrderId       CompanyName
          101           My Favorite Customer
          137           My Favorite Customer
           Relational Databases
                 Stored 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
         Relational Databases
           Stored Procedure Example

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
  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
         Relational Databases
          Stored Procedure Examples

exec CustOrderHist 'alfki'

            ProductName        Total
            Aniseed Syrup      6
            Chartreuse verte   21
            ...                ...
           Relational Databases
            Stored Procedure Examples

 Use RETURN statement to return status
     0 is default in SQL Server
     Can only be numeric
       RETURN 1

 Use OUTPUT parameters to return results
         SELECT @ReturnValue = ColumnName FROM Table
           Relational Databases

 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
 Enforces business rules
 FOR AFTER: trigger executes after triggering action
 FOR INSTEAD OF: trigger executes in place of
  triggering action
           Relational Databases

 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
            Relational Databases

 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
   Databases
   Relational Databases
   ADO.NET Overview
   ADO.NET Classes
             ADO.NET Overview
                       Looking 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)
             ADO.NET Overview
                      Looking Back

     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++
        ADO.NET Overview
                 Looking Back

                Your Application


                   OLE DB

    ODBC            OLE DB             OLE DB
                   Provider           Provider
ODBC Driver
                 File   Mainframe
ODBC Provider    Simple Provider    Native Provider
              ADO.NET Overview
                        Looking Back

 ADO was designed as a connected,
  tightly coupled model
      Appropriate for client/server architectures
 Primarily relational (XML is hierarchical)
 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
          ADO.NET Overview
                ADO.NET Goals

 Well-factored design
 Highly scaleable through a robust
  disconnected model
 Rich XML support (hierarchical as well
  as relational)
 Data access over HTTP
 Maintains familiar ADO programming model
 Keep ADO available via .NET COM
            ADO.NET Overview
                 Managed 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
       ADO.NET Overview
             Managed Providers

                Your Application

         ADO.NET Managed Provider

                                    OLE DB
   SQL Server
    Database                       Database

SQL Managed Provider        ADO Managed Provider
             ADO.NET Overview
                  Data Access Styles

 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
               ADO.NET Overview
                         Data 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 %>'/>
   Database Theory and History
   Relational Database Concepts and Terminology
   ADO.NET Overview
   ADO.NET Classes
                 ADO.NET Classes
                 IDbConnection Interface

 Creates a unique session with a data source
 Implemented by SqlDbConnection and
 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
                 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
            ADO.NET Classes
             IDataReader Interface

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

string sConnString = “Provider=SQLOLEDB.1;” +
                     “User ID=sa;Initial Catalog=Northwind;” +
                     “Data Source=MYSERVER”;
OleDbConnection conn = new OleDbConnection(sConnString);
string sQueryString = “SELECT CompanyName FROM Customers”;
OleDbCommand myCommand = new OleDbCommand(sQueryString, conn);
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read()) {
ADO.NET Classes
  DataReader Demo
             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
ADO.NET Classes
OleDb Data Access Demo
               ADO.NET Classes
      System.Data.SqlClient Namespace

 Managed provider native to SQL Server
 Built on TDS (Tabular Data Stream) for high
  performance in SQL Server
 SqlConnection, SqlCommand and
  SqlDataReader classes
 Classes for
     Error handling
     Connection pooling (implicitly enabled by default )
 System.Data.SqlTypes provides classes for
  native SQL Server data types
ADO.NET Classes
Stored Procedure Demo
               ADO.NET Classes
              System.Data Namespace

 Contains the core classes of the ADO.NET
 Disconnected DataSet is central
 Supports all types of applications
     Internet based
         ASP.NET
         XML
     Windows forms based
           ADO.NET Classes
      System.Data Namespace (cont.)

 Contains classes used by or derived from
  managed providers
 IDbConnection, IDbCommand,
             ADO.NET Classes

   A collection of tables
   Has no knowledge of source
   Keeps track of all relationships among tables
   Rich programming model (has objects for tables,
    columns, relationships, and so on)
   Remembers original and current state of data
   Can dynamically modify data and metadata
   Native serialization format is XML
   Located in System.Data
 ADO.NET Classes





ADO.NET Classes
Update DataSet Demo
              ADO.NET Classes

 In-memory object representing one table
     Columns
     Rows
 Schema defined by Columns collection
 Data integrity provided through Constraint
 Public events
     Modifying/deleting rows
     Modifying columns
           ADO.NET Classes

 Fundamental building block of a DataTable
  schema (contained in Columns collection)
 Defines what type of data may be entered (via
  DataType property)
 Other important properties include AllowNull,
  Unique, and ReadOnly
 Can contain Constraints (a collection on
 Can contain Relations (collection on DataSet)
           ADO.NET Classes

 Represents data in a DataTable (contained in
  Rows collection)
 Conforms to schema defined by DataColumns
 Properties for determining row state (for
  example, new, changed, deleted, and so on)
 All additions/modifications “committed” with
  AcceptChanges method of DataTable
           ADO.NET Classes

 Relates two DataTables via DataColumns
 DataType value of both DataColumns must
  be identical
 Updates can be cascaded to child DataTables
 Modifications that invalidate the relation are
           ADO.NET Classes
           IDataAdapter Interface

 Populates or sends updates to a DataSet
 Implemented by OleDbDataAdapter and
 Not connection based
 Represents an asynchronous approach
 A superset of a command object
 Contains four default command objects for
  Select, Insert, Update, and Delete
              ADO.NET Classes
                   DataSet Example

string sConnString = “Persist Security Info=False;” +
                     “User ID=sa;Initial Catalog=Northwind;” +
                     “Data Source=MYSERVER”;
SqlConnection conn = new SqlConnection(sConnString);
string sQueryString = “SELECT CompanyName FROM Customers”;
SqlDataAdapter myDSAdapter = new SqlDataAdapter();
DataSet myDataSet = new DataSet();
myDSAdapter.SelectCommand = new SqlCommand(sQueryString, conn);
          ADO.NET Classes
         Creating a DataSet in Code

 Create DataSet
 Define tables

DataSet dataset = new DataSet();
dataset.DataSetName = “BookAuthors”;

DataTable authors = new DataTable(“Author”);
DataTable books = new DataTable(“Book”);
                ADO.NET Classes
             Creating a DataSet in Code

   Define columns
   Define keys
DataColumn id = authors.Columns.Add("ID", typeof(Int32));
id.AutoIncrement = true;
authors.PrimaryKey = new DataColumn[] {id};

DataColumn name
  = new authors.Columns.Add("Name",typeof(String));

DataColumn isbn = books.Columns.Add("ISBN", typeof(String));
books.PrimaryKey = new DataColumn[] {isbn};

DataColumn title = books.Columns.Add("Title", typeof(String));
DataColumn authid = books.Columns.Add(“AuthID”,typeof(Int32));
DataColumn[] foreignkey = new DataColumn[] {authid};
            ADO.NET Classes
          Creating a DataSet in Code

 Add the tables to the DataSet

        dataset.Tables.Add (authors);
        dataset.Tables.Add (books);
               ADO.NET Classes
             Creating a DataSet in Code

 Add data and save the DataSet
DataRow shkspr = authors.NewRow();
shkspr["Name"] = "William Shakespeare";

DataRelation bookauth = new DataRelation("BookAuthors",
                              authors.PrimaryKey, foreignkey);
dataset.Relations.Add (bookauth);

DataRow row = books.NewRow();
row["AuthID"] = shkspr["ID"];
row["ISBN"] = "1000-XYZ";
row["Title"] = "MacBeth";

ADO.NET Classes
DataSet Creation Demo
              ADO.NET Classes
                    Typed DataSets

 Typed DataSet
     Derived from base DataSet class
     Uses XML schema to generate new class
     Tables, columns, etc. compiled into new class

 Untyped DataSet
   No built-in schema
   Tables, columns, etc. exposed only as collections

              ADO.NET Classes
                Errors and Exceptions

 Error class
     Contains information on an error or warning returned
      by data source
     Created and managed by Errors class
 Errors class
     Contains all errors generated by an adapter
     Created by Exception class
 Exception class
     Created whenever an unhandled error occurs
     Always contains at least one Error instance
               ADO.NET Classes
         Errors and Exceptions Example

try {
  DataTable myTable = new DataTable();
catch (DataException myException) {
  Console.WriteLine ("Message: " + myException.Message + "\n" +
      "Source: " + myException.Source + "\n" +
      “Stack Trace: " + myException.StackTrace + "\n");
ADO.NET Classes
DataException Demo
   Database Theory and History
   Relational Database Concepts and Terminology
   ADO.NET Overview
   ADO.NET Classes
 Introducing ADO+
 ADO+ Guides the Evolution of the Data Species
 ADO.NET for the ADO Programmer
 ADO Rocks and Rolls in .NET Applications
 Meditating on OLE DB and .NET
 Reading Data Reader Secrets
 Database-like Data Containers
 Universal Data Access
 SQL Server
               ADO vs. ADO.NET

 ADO is a slower automation layer over OLE DB
  for use in Visual Basic, etc.
 ADO.NET provides direct, fast access to data
  from any language
 ADO.NET essentially has merged OLE DB and
  ADO into a single layer
                       ADO vs. ADO.NET

Feature                ADO                         ADO.NET
Memory-resident Data   Uses RecordSet, which can   Uses DataSet, which can
Representation         contain one table           contain one or more tables
                                                   represented by DataTables

Relationship Between   Require the JOIN query      Supports the DataRelation
Multiple Tables                                    object
Data Visitation        Scans RecordSet rows        Uses a navigation
                       sequentially                paradigm for non-
                                                   sequential access

Disconnected Access    Provided by RecordSet but   Communicates with
                       typically supports          standardized calls to the
                       connected access            DataAdapter
                        ADO vs. ADO.NET

Feature                 ADO                         ADO.NET
Programmability         Uses Connection object      Uses strongly typed
                        to transmit commands        programming
                                                    characteristics of XML

Sharing Disconnected    Uses COM marshalling to     Transmits a DataSet with
Data Between Tiers or   transmit disconnected       an XML file
Components              Recordset
Transmitting Data       Problematic because         Supported, DataSet object
Through Firewalls       firewalls are typically     use XML, which can pass
                        configured to prevent       through firewalls
                        system-level requests
Scalability             Database locks and active   Disconnected access to
                        database connections for    database data without
                        long durations              retaining database locks