Document Sample
ado_net Powered By Docstoc

Andrei Otcheretianski
Tomer Rothschild
     Introduction
           What is ADO.NET?
           Data Access
     Motivation - Why Moving to ADO.NET?
   ADO.NET Objects
      Content components
      Managed-provider components

   ADO.NET & Transactions

     ADO.NET and XML
     .NET Framework Data Providers
     ADO.NET 2.0 Added Features
     Summary - Pros & Cons

February 2nd, 2006                 ADO .NET   2
What is ADO.NET?

      ADO.NET (ActiveX Data Objects) is the primary relational data
       access model for Microsoft .NET-based applications.

      The data access objects role is to serve as an interface between
       the client application and the data provider – the DBMS. This
       modularity results in two big advantages:

            Allowing the writing of applications that use standard types
             and features, resulting in DBMS independent code.
            Unified API for the database driver implementers.

 February 2nd, 2006                   ADO .NET                              3
Things Weren’t Always So Simple…

      Let’s have a Brief Look at the evolution that lead to ADO.NET
         At first, programmatic access to databases was performed by
           native libraries, such as DBLib for SQL Server, and the Oracle
           Call Interface (OCI) for Oracle.

                  This allowed for fast database access because no extra layer
                   was involved.
                  However, it also meant that modularity was absent. i.e. loosing
                   the two advantages discussed on the previous slide.

 February 2nd, 2006                       ADO .NET                                   4
Data Access Evolution                                                     (1)
            ODBC:
             As a solution, Microsoft and other companies developed the
             Open Database Connectivity, or ODBC.

                  This provided a common data access layer, which could be used
                   to access almost any relational database management system.

                  In practice, there were some differences in the SQL dialect
                   supported hence, violating the main principle of modularity.

                  Nonetheless, perhaps the most important feature of ODBC is the
                   fact that it was an open standard, widely adopted even by the
                   Open Source community.

 February 2nd, 2006                       ADO .NET                                  5
Data Access Evolution                                                   (2)
            DAO:
                  As the importance of Visual Basic grew, there was a need for a
                   data access technology that could be used more naturally from
                  DAO (Direct Access Objects) provided a simple object model for
                   talking to Microsoft's Access desktop database. As DAO was
                   optimized for Access, it was very fast.

            RDO:
                  Due to its optimization for Access, DAO was very slow when
                   used with ODBC data sources. To get round this, Microsoft
                   introduced Remote Data Objects (RDO).
                  RDO was designed specifically for access to ODBC data
                   sources. RDO is essentially a thin wrapper over the ODBC API.

 February 2nd, 2006                      ADO .NET                                   6
Data Access Evolution                                                     (3)
            OLE DB:
                  As part of the OLE (Object linking and Embedding) distributed
                   object system, the OLE DB is used for accessing different types
                   of data stores in a uniform manner.

            ADO:
                  ActiveX Data Objects is the technology that gave its name to
                   ADO.NET (although in reality the differences are far greater than
                   the similarities).
                  ADO is merely an OLE DB consumer – a thin layer allowing
                   users of high -level languages to access OLE DB through a
                   simple object model.
                  In a way, ADO is to OLE DB more or less what RDO was to

 February 2nd, 2006                       ADO .NET                                     7
Why Moving to ADO.NET?
     Why not simply continue to use ADO in the .NET framework?
       It's perfectly possible to carry on using ADO in .NET
        applications through COM interoperability.
       However, there are some very good reasons why ADO wasn't
        really suited to the new programming environment:

                 Using Managed Classes
                 Cleaner Architecture
                 Cross-Language Support
                 XML Support
                 Optimized Object Model

February 2nd, 2006                    ADO .NET                     8
Why Moving – Managed classes &
Cleaner Architecture
      Using Managed Classes
         The alternative - using .NET then COM interoperability - adds
          overhead to the application.
         Takes advantage of the benefits of the CLR such as JIT
          compilation and the managed execution environment.

      Cleaner Architecture
         As we noted above, ADO is no more than a thin layer over
          OLE DB.
         ADO.NET can be much faster than ADO, as the providers
          communicate directly with the data source.

 February 2nd, 2006               ADO .NET                                9
Why Moving – Cross-Language Support
& XML Support
      Cross Language Support
         ADO was aimed primarily at VB programmers. This lead to
          using optional method parameters, which are not supported
          by C-based languages such as C#.
         This means that if you use ADO from C#, you will need to
          specify all parameters in method calls. This makes ADO
          programming under .NET considerably more time-consuming

      XML Support
         XML is absolutely integral to ADO.NET, and not just an add-
         As we shall see later on in the demo, XML is the format used
          to serialize and transport DataSets.

 February 2nd, 2006               ADO .NET                               10
Why Moving – Optimized Object Model

      Optimized Object Model
         The .NET Framework is aimed squarely at developing
          distributed applications, and particularly Internet-enabled

            In this context, for example, we don't want to hold a
             connection open for a long time, as this could create a
             bottleneck and destroy scalability.

            ADO didn't encourage disconnected recordsets, whereas
             ADO.NET has different classes for connected and
             disconnected access, and doesn't permit updateable
             connected recordsets.

 February 2nd, 2006                   ADO .NET                          11
ADO .NET - Objects
          .Net Data Provider                        DataSet
      Connection       Data Adapter

         Transaction      Select
                                                       Data Table

       Command            Insert
         Parameters                                           Constraints
       DataReader         Delete

                  DB                              <XML>

 February 2nd, 2006                   ADO .NET                              12
ADO .NET – Objects(2)

     In ADO .NET there are two class groups.

           Content components

           Managed-provider components

February 2nd, 2006         ADO .NET         13
ADO .NET – Objects(3)
     Content components
           The content components hold actual data and include
              DataSet
              DataTable
              DataView
                  DataColumn
                  DataRow

              DataRelation

     Managed-provider components
           These components actually talk to the database to assist in data
            retrievals and updates. Such objects include the Connection,
            Command, Data Reader and Data Adapter.

                 The managed-provider components are split into several groups
                  designed for each provider as we will see later.

February 2nd, 2006                      ADO .NET                                  14
     Connection

           Provides a connection to the database . There are typical Open() and
            Close(), plus BeginTransaction() returning an object to control a
            database transaction.

           To open a connection to a data source you need to provide a
            connection string.

                 Example:
                 "Network Library=DBMSSOCN; Data Source=,1433;Initial

February 2nd, 2006                       ADO .NET                                  15
      Managing Database Connections

            DB connection represent a critical expensive and limited resource.

                  Connection Pooling

                  Open connections late and close them early

 February 2nd, 2006                      ADO .NET                                 16
Connection Pooling
      Connection Pooling
            Database connection pooling enables an application to reuse an
             existing connection from a pool, instead of repeatedly establishing a
             new connection with the database.
            ADO .NET data providers provide transparent connection pooling,
             the exact mechanics of which vary for each provider.

            For example, using SQL Server .Net data provider you can configure
             connection pooling by adding a name-value pairs to the connection
             string: “[…];Max Pool Size=75; Min Pool Size=5”
            Connections are pooled through an exact match algorithm on the
             connection string!!! The pooling mechanism is even sensitive to
             spaces between name-value pairs.

 February 2nd, 2006                     ADO .NET                                     17
     Command
           This is the pipeline to the backend data.

           The command object provides direct execution of the SQL
            command to the database.

           You can use the command to either ExecuteNonQuery(), which will
            action an SQL statement (such as a DELETE command) upon the

           ExecuteReader() links straight in to the Data Reader object.

February 2nd, 2006                      ADO .NET                              18
Commands and Parameters
     Commands and Parameters

           When a SQL statement is executed, the database has to generate
            an execution plan for it.

           Use Prepare() method to generate and store an execution plan in
            advance. The plan will be saved in the database cache so
            subsequent statements will be executed much faster.

           This method should be used only if the SQL statement will be
            executed multiple times.

           Use Parameter object, instead of hardcoding parameter values in a
            SQL statement.

February 2nd, 2006                    ADO .NET                                  19
Parameters - Example
  string sConnString = "Server=(local);Database=Northwind;Integrated Security=True;";
  string sSQL = "UPDATE Customers SET City=@sCity WHERE CustomerID=@sCustomerID";
          SqlConnection oCn = new SqlConnection(sConnString))
          SqlCommand oCmd = new SqlCommand(sSQL, oCn))
          oCmd.CommandType = CommandType.Text;
          oCmd.Parameters.Add("@sCustomerID", SqlDbType.NChar, 5);
          oCmd.Parameters.Add("@sCity", SqlDbType.NVarChar, 15);
          oCmd.Prepare(); // Prepare the execution plan
          oCmd.Parameters["@sCustomerID"].Value = "ALFKI";
          oCmd.Parameters["@sCity"].Value = “Jerusalem";
          oCmd.Parameters["@sCustomerID"].Value = "CHOPS";
          oCmd.Parameters["@sCity"].Value = “Tel-Aviv";


February 2nd, 2006                       ADO .NET                                       20
Data Reader
     Data Reader

           This object essentially takes a stream of data from the Command
            object and allows you to read it.

           It's like a forward-only Recordset from the ADO and is very efficient
            because it stores only one record in the memory at a time.

           However this uses a server-side cursor, so you should avoid it too
            much as it naturally requires an open connection.

February 2nd, 2006                      ADO .NET                                    21
Data Adapter
      Data Adapter

            It essentially serves as a middle man, going through your connection
             to retrieve data, then passing that into a DataSet.

            You can then pass the DataSet back to the Data Adapter, which will
             go and update the database.

            The SQL statements for each command are specified in the
             InsertCommand, UpdateCommand, SelectCommand and
             DeleteCommand properties.

 February 2nd, 2006                     ADO .NET                                    22




     XML Schema

February 2nd, 2006   ADO .NET                 23
      DataSet

            The “king” of the ADO .NET

            This object is actually a memory resident representation of data that
             provides a consistent relational programming model regardless of the
             data source.

            The data stored in the DataSet object is disconnected from the

 February 2nd, 2006                     ADO .NET                                 24
      The DataSet object model is made up of three collections,
       Tables, Relations and ExtendedProperties. These collections
       make up the relational data structure of the DataSet

            Tables Collection

            Relations Collection

            ExtendedProperties Collection

 February 2nd, 2006                    ADO .NET                      25
     Tables Collection
          DataSet.Tables
          Each DataTable represents a table of data from the data source.
          DataTable is made up of a Columns collection and a Rows collection.

     Relations Collection
          DataSet.Relations
          The DataRelation objects define a parent-child relationship between
           two tables based on foreign key values.

     ExtendedProperties Collection
          DataSet.ExtendedProperties
          The ExtendedProperties is a user-defined properties collection.
          Can be used to store custom data related to the DataSet, such as the
           time when the DataSet was constructed.

 February 2nd, 2006                    ADO .NET                                   26




                                               DataView                   DataRow





                                      ExtendedProperties                 DataColumn

                                             PrimaryKey                   ExtendedProperties

 February 2nd, 2006                                  ADO .NET                                  27
      Connecting a DataSet to a data source

            In order to connect a DataSet to a data source, we need to use the
             DataAdapter as an intermediary between the DataSet and the .NET
             data provider.

      Let’s see some code…

 February 2nd, 2006                    ADO .NET                                   28
DataSet – Example
     Filling the DataSet:

        // Connection String
        String conStr =
        //SQL Command
        String sqlStr = "SELECT EmployeeID, FirstName, LastName FROM
        // Create a new DataAdapter object
        OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conStr);
        // Create a new DataSet
        DataSet ds = new DataSet();
        // Fill the DataSet
        da.Fill(ds, "Employees");

February 2nd, 2006                 ADO .NET                                29
      Why do we need transactions?
            Consider a Web-based retail application that processes purchase
             orders. Each order requires three distinct operations that involve
             three database updates:
                  The inventory level must be reduced by the quantity ordered.
                  The customer’s credit level must debited by the purchase amount.
                  A new order must be added to the orders database.

            It is essential that these three distinct operations be performed as a
             unit and in atomic fashion.

 February 2nd, 2006                         ADO .NET                                  30
      Performing Manual Transactions with ADO .NET
            ADO .NET supports a transaction object that you can use to begin a
             new transaction and then explicitly control whether it should be
             committed or rolled back.

            The transaction object is associated with a single database
             connection and obtained by the BeginTransaction() method of the
             connection object.

            You must explicitly associate each command objects with the
             transaction by setting the Transaction property.

            Note that since a transaction object associated with a single
             connection you can perform transactions only with a single database.

 February 2nd, 2006                    ADO .NET                                   31
Transactions – Example
       Transaction Example
       SqlConnection db = new SqlConnection(conStr);
       SqlTransaction transaction;

       transaction = db.BeginTransaction();
         new SqlCommand("INSERT INTO TransactionDemo(Text) VALUES ('Row1')", db,
         new SqlCommand("INSERT INTO TransactionDemo(Text) VALUES ('Row2');",db,
         new SqlCommand("INSERT INTO CrashMeNow VALUES ('Die', 'Die', 'Die');", db,
       catch (SqlException sqlError)

 February 2nd, 2006                                ADO .NET                           32
     XML and Traditional Data Access

           We have talked about data access as it relates to traditional data
            access and relational data.

           A wide variety of data can be represented in a non relational way.

           ADO .NET makes it extremely easy to create and read XML

           The data in ADO.NET is transported in XML format.

           You can parse XML in .NET programmatically using the
            System.Xml.XmlDataDocument object.

February 2nd, 2006                     ADO .NET                                  33
ADO .NET and XML (2)
     Saving DataSet as XML

           Fill the DataSet with data.
           Use DataSet.WriteXml(fileName) to write an XML file.

     Loading XML files

           Loading the XML file is not more complex than writing it.
           Use DataSet.ReadXml(fileName) to load the data.
           Wrap ReadXml call with try and catch because we can’t guarantee
            that the file contains valid XML.

February 2nd, 2006                    ADO .NET                                34
ADO .NET and XML (3)
     Schemas

           Schemas provide a mechanism for defining rules that XML
            documents must adhere to, and which help everyone understand
            what the data held in an XML document actually is.

           Generating a Schema

                 Fill the dataset
                 DataSet.WriteXmlSchema(SchemaFileName);

February 2nd, 2006                      ADO .NET                           35
ADO .NET and XML (4)

February 2nd, 2006   ADO .NET   36
ADO .NET and XML (5)
     Typed DataSets

           Typed DataSets can provide a more intuitive mechanism for the
            manipulation of data.
           A typed DataSet is early bound to an XML Schema Definition (XSD)
           Schemas provide very rigorous definitions for the types of particular
            objects. In conjunction with the typed DataSet, they can allow access
            to the tables and columns of a DataSet using meaningful names.
           This not only improves the readability of the code
           Enables Visual Studio .NET's IntelliSense feature to make context-
            sensitive suggestions as you type in code.

February 2nd, 2006                     ADO .NET                                 37
Data Providers Diagram
                                                                 .NET Managed App
               OLE DB .Net Data Provider
ADO          OleDbConnection OleDbDataAdapter             DataSet
              OleDbCommand OleDbDataReader          Data Table

                      COM InterOp                         SQL Server/Oracle
                                                          .Net Data Provider
               OLE DB                                    Connection    DataAdapter
                                                         Command        DataReader


        Misc DB            SQL Server 6.5 and earlier    SQL Server 7.0 and later
                                                                  / Oracle

 February 2nd, 2006                     ADO .NET                                     38
Data Providers in the .NET                                        (1)
      SqlClient Provider
            Should be used to access SQL Server 7.0 or later, and MSDE
            Can't be used with SQL Server 6.5 or earlier.

      OleDb Provider
            Good for almost anything other than SQL Server 7.0 or later,
             or Oracle.
            Using the ODBC data access through the OleDb is
             discouraged – think of the architecture involved:
                ADO.NET – COM interop – (optional) OLE DB services –
                 OLE DB provider – ODBC driver – data source!

 February 2nd, 2006                  ADO .NET                               39
Data Providers in the .NET                                      (2)
      The Odbc Provider
            Should be used whenever you need to access a data source
             with no direct or OLE DB provider (PostgreSQL, Paradox,

      OracleClient Provider
            Supports Oracle data types, as well as ref cursors
            Avoids the cost of COM interop, and also employs Oracle-
             specific optimizations

 February 2nd, 2006                 ADO .NET                            40
Generic Interfaces
      ADO.NET was designed from the beginning to allow the provider
       writer the space to support database-specific features.
      Programmers who wish to build applications that interact with
       databases of more than one brand may resort to the generic
            IDbConnection
            IDbCommand
            IDataReader
            IDbTransaction
            IDbDataParameter
            IDataParameterCollection
            IDbDataAdapter

      There are problems involved with a common interface instead of
       a common base class.
            This issue will be covered in more depth in the slides about

 February 2nd, 2006                     ADO .NET                                    41
ADO.NET 2.0 Added Features
     ADO.NET 2.0 comes with a plethora of new features.
      We shall cover the main features in short:

           Based-Class-Based Provider Model
           Provider Factories
           Asynchronous Commands
           SqlDependency
           MARS (Multiple Active Resultsets)

February 2nd, 2006                   ADO .NET              42
ADO.NET 2.0 Added Features                                                (1)

      Based-Class-Based Provider Model
            In ADO.NET 1.X provider writers implemented a series of
             provider-specific classes. Generic coding was based on the
             generic interface each of the classes implemented.
            The new provider model in ADO.NET 2.0 is based on a series
             of base classes.
                  Each of the base classes implements the still-required generic
                   interface for backward compatibility.
      Provider Factories
            The base ProviderFactory class (DbProviderFactory) and the
             ProviderFactories class
             (System.Data.Common.ProviderFactories) simplify things a

 February 2nd, 2006                       ADO .NET                                  43
ADO.NET 2.0 Added Features                                                     (2)
      Conditional code that used to be written like this:

              enum provider {sqlserver, oracle, oledb, odbc};
               // determine provider from configuration provider
               prov = GetProviderFromConfigFile();
               IDbConnection conn = null;
               switch (prov) {
                          case provider.sqlserver:
                                    conn = new SqlConnection(); break;
                                    conn = new OracleConnection(); break;
                          case provider.oledb:
                                    conn = new OleDbConnection(); break;
                          case provider.odbc:
                                    conn = new OdbcConnection(); break;
                          // add new providers as the application supports them …

 February 2nd, 2006                        ADO .NET                                  44
ADO.NET 2.0 Added Features                                              (3)
      ...Can now be written like this:

            // get ProviderInvariantString from configuration string
             provstring = GetProviderInvariantString();
             DbProviderFactory fact =
             IDbConnection = fact.CreateConnection();

 February 2nd, 2006                   ADO .NET                                45
ADO.NET 2.0 Added Features                                        (4)

      Asynchronous Commands
            database command execution can take a long time.
            ADO.NET 2.0 SqlClient now provides built-in SqlCommand
             methods that provide asynchronous execution.

      SqlDependency
            Caching is good. But we should make sure it’s consistent with
             the database.
                Until now, this task was accomplished through triggers
                 that updated a file upon update of the database, or by
                 refreshing the cache every so often.

 February 2nd, 2006                  ADO .NET                                46
ADO.NET 2.0 Added Features                                                  (5)

            SqlDependency contacts you when the underlying rows

                  When notified, the entire set of rows is fetched again.
                  This functionality is good for a single cache or a limited set of
                  However, when using it with large numbers of users listening at
                   the same time - the SELECT statements used for refresh could
                   be a significant hit on the database.

 February 2nd, 2006                        ADO .NET                                    47
ADO.NET 2.0 Added Features                                             (6)
      MARS (Multiple Active Resultsets)

            SQL Server doesn't automatically produce a cursor. Instead, it
             uses an optimized method to pull the data in packet-size

            In the versions prior to SQL Server 2005, there could only be
             one cursorless resultset active on a connection at a time.
                  ADO.NET 1.X throw an error if you attempt to open a second
                   cursorless resultset.
                  ADO "classic" actually opened a new database connection
                   behind the scenes.
                      This convenience feature was inadvertently abused by some
                       programmers and resulted in more database connections
                       than they bargained for.

 February 2nd, 2006                     ADO .NET                               48
ADO.NET 2.0 Added Features                                             (7)

      MARS (contd)

            In SQL Server 2005, the database has been
             enhanced to permit MARS.
                Each SqlCommand can accommodate a
                 SqlDataReader, and multiple SqlDataReaders
                 can be used in tandem.
                It’s not just about reducing errors. It can be
                 extremely useful in conjunction with
                 asynchronous operations described above.
                         Example: Filling 20 drop-down list boxes on a form at
                          the same time, using a single connection.

 February 2nd, 2006                       ADO .NET                                49
Summary: Pros & Cons
     Pros
           Performance
           Managed Classes
           XML Support (and Reliance)
           Disconnected Operation Model
           Rich Object Model
     Cons
           Managed-Only Access
           Only four Managed Data Providers (so far)
           Learning Curve

February 2nd, 2006                 ADO .NET             50
      Installing .Net Framework 1.1 is free

      Getting Visual Studio 2003 is not free
            There is VS Academic “only” for $99

      Installing SQL Server 2000

      NEXT->NEXT->… -> FINISH

 February 2nd, 2006                    ADO .NET                                 52
It’s DEMO Time
Remote DB Access Through
    DataSet - Demo

           Web Page                           Web Service

                                        Data Provider

                Client                         SQL Server

February 2nd, 2006           ADO .NET                       55

Shared By: