ADO.NET Objectives  Introduce  Show Microsoft® ADO.NET the evolution of ADO to ADO.NET  Introduce the primary components of ADO.NET Contents    Differences Between ADO and ADO.NET Benefits of ADO.NET ADO.NET Core Concepts and Architecture    The ADO.NET Object Model The DataSet and Data Views Managed Providers ADO vs. ADO.NET  ADO     Designed for connected access Tied to the physical data model The RecordSet is the central data container RecordSet is one (1) table that contains all the data  Retrieving data from > 1 table or source requires a database JOIN  Data is “flattened”: lose relationships; navigation is sequential    Data types are bound to COM/COM+ data types Data sharing via COM marshalling Problems marshalling through firewalls (DCOM, binary) ADO vs. ADO.NET  ADO.NET     Designed for disconnected access Can model data logically! The DataSet replaces the RecordSet DataSet can contain multiple tables  Retrieving data from > 1 table or source does not require a JOIN  Relationships are preserved: navigation is relational   No data type conversions required XML, like HTML, is plaintext: “Firewall friendly” ADO.NET Goals  New applications have become loosely coupled based on the Web application model data architecture,  Disconnected  Tight integration with XML, data representation with the ability to combine data from multiple and varied data sources. facilities for interacting with a database, all native to the .NET Framework.  Common  Optimized ADO.NET and the .NET Framework Microsoft .NET Framework Web Services User Interface Data and XML ADO.NET XML Base Classes Common Language Runtime ... ... Core Concepts and Architecture  The  ADO.NET Object Model DataSet - core component of the disconnected architecture .NET Framework data provider   Used for connecting to a database, executing commands, and retrieving results. core objects that make up a .NET Framework data provider.  .Net Hierarchy & Core Object  Core object - provides connectivity to a data source - enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information - provides a high-performance stream of data from the data source.  Connection  Command  DataReader  DataAdapter - provides the bridge between the DataSet object and the data source  .Net     hierarchy System.Data.SqlTypes System.Data.Odbc System.Data System.Data.OleDb System.Data.SqlClient System.Data.Common System.Data.OracleClient How ADO.NET Works … Client (Console,Win,Web) DataSet Command DataSetCommand DataReader Connection Managed Provider Data Source System.Data Namespace  Contains the basis and bulk of ADO.NET  Data-centric  Provides    namespace the means to work on and with your data! Classes and methods to manipulate your data Ability to create views of your data Means to logically represent your data  Enables the use of XML to view, share, and store data Introducing the Objects… System.Data DataSet  Contains the “main” classes of ADO.NET  In-memory cache  In-memory cache  Used  Used  Used of data of a database table DataTable DataRow DataColumn DataRelation to manipulate a row in a DataTable to define the columns in a DataTable to relate 2 DataTables to each other DataViewManager  Used to create views on DataSets Putting the Objects Together… DataSet Tables DataTable DataRow(s) DataView Relations DataRelation DataRelation DataColumn Constraint(s) DataViewManager DataTable DataTable Working Data - The DataSet  An in-memory cache of data from a data source way to represent and manipulate data Universal data container Not just for use with databases  Common    Logical or physical representation of data to be disconnected from the data source  Designed  Connect, execute query, disconnect  Can   use XML To read and write data To read and write XMLSchema XML and the DataSet  DataSet   can read/write XML for its data and/or schema You can create or modify data in a DataSet using XML You can create or modify the DataSets schema using XML  XML-related   DataSet methods for reading: ReadXml: Reads an XML schema and data into the DataSet ReadXmlSchema: Reads an XML schema into the DataSet  And  for writing: property: sets the namespace for serialization WriteXml, WriteXmlSchema  Namespace .NET Data Providers Hierarchy .Common Contains classes shared by both System.Data .SqlClient SqlCommand SqlConnection SqlDataReader SqlDataAdapter .OleDb OleDbCommand OleDbConnection OleDbDataReader OleDbDataAdapter OleDbConnection and SqlConnection  Represent  Create, a unique session with a data source open, close a connection to a data source and methods to perform transactions example:  Functionality  OleDbConnection String conStr="Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=NWIND_RW.MDB"; OleDbConnection aConn = new OleDbConnection(conStr); aConn.Open(); // Execute Queries using OleDbDataAdapter Class aConn.Close(); OleDbDataAdapter Class  Bridge  between the DataSet and the data store to modify the DataSet and data source Inherited from the DataAdapter class  Means data store DataAdapter DataSet OleDbDataAdapter Class  Properties      of Interest: DeleteCommand: The delete command expressed in SQL InsertCommand: Gets or sets insert command SelectCommand: Gets or sets select command UpdateCommand: Gets or sets update command TableMappings: Maps source table and a DataTable  OleDbCommands retrieved or set by command properties  Implements   abstract methods of the DataAdapter class: public abstract int Fill( DataSet dataSet ); public abstract int Update( DataSet dataSet ); OleDbCommand Class  Represents  a query to execute on the data source May be a SQL statement or stored procedure  Properties   of Interest: Connection: Get or set the data source connection CommandText: Get or set the query (text) command  A SQL statement or the name of the stored procedure  CommandType: Get/set how the command is interpreted  Text, StoredProcedure, or TableDirect  CommandTimeout: The seconds until connection timeout OleDbDataReader  Forward-only data access  “Lightweight”  programming model returned by OleDbCommand.ExecuteReader Less overhead than using OleDbDataAdapter  Instantiated &  Ties up the OleDbCommand until it is finished reading OleDbDataReader  Properties   of Interest: FieldCount: Returns the number of fields in the result set RecordsAffected: Number of affected records  Methods   to retrieve data: By column type and/or index: GetValue; GetString; etc. Read(): Advances reader to next record   NextResult(): Advanced to next result set in batch GetValues(): Gets the current row Benefits of ADO.NET  Interoperability through use of XML    Open standard for data that describes itself Human readable and decipherable text Used internally but accessible externally  Can use XML to read and write and move data  Scalability through the disconnected DataSet   Connections are not maintained for long periods Database locking does not occur   Locking support with ServiceComponents Optimistic locking otherwise  Maintainability  Separation of data logic and user interface Summary  ADO.NET  It is the evolution of ADO is a disconnected, Web-centric model in its ability to work with data your ability to logically organize data support for XML  Flexible  Increases  Extensive  Facilitates working with and sharing data  Interacts with a wide variety of data sources