ADO NET Objectives n Introduce n Show Microsoft

ADO. NET

Objectives n Introduce n Show Microsoft® ADO. NET the evolution of ADO to ADO. NET n Introduce the primary components of ADO. NET

Contents n Differences n Benefits Between ADO and ADO. NET of ADO. NET n ADO. NET Core Concepts and Architecture n The ADO. NET Object Model n The Data. Set and Data Views n Managed Providers

ADO. NET and the. NET Framework Microsoft. NET Framework Web Services User Interface Data and XML ADO. NET XML . . . Base Classes Common Language Runtime . . .

ADO vs. ADO. NET 1/2 n ADO n Designed for connected access n Tied to the physical data model n The Record. Set is the central data container n Record. Set is one (1) table that contains all the data n Retrieving data from > 1 table or source requires a database JOIN n Data is “flattened”: lose relationships; navigation is sequential n Data types are bound to COM/COM+ data types n Data sharing via COM marshalling n Problems marshalling through firewalls (DCOM, binary)

ADO vs. ADO. NET 2/2 n ADO. NET n Designed for disconnected access n Can model data logically! n The Data. Set replaces the Record. Set n Data. Set can contain multiple tables n Retrieving data from > 1 table or source does not require a JOIN n Relationships are preserved: navigation is relational n Data types are only bound to XML schema n No data type conversions required n XML, like HTML, is plaintext: “Firewall friendly”

Benefits of ADO. NET n Interoperability through use of XML (more later!) n Open standard for data that describes itself n Human readable and decipherable text n Used internally but accessible externally n n Scalability through the disconnected Data. Set n Connections are not maintained for long periods n Database locking does not occur n n Can use XML to read and write and move data n Locking support with Service. Components n Optimistic locking otherwise Works the way the Web works: “Hit and Run!” Maintainability n Separation of data logic and user interface

Visual Studio. NET Enhancements n Typed programming—a programming style n Uses end-user words: Easier to read and write n Statement completion in Microsoft Visual Studio. NET n Safer: Provides compile-time checking n Examples: n Untyped: Table("Customer")("Jones"). Column(“Balance”) n Typed: my. Data. Set. Customer("Jones"). Balance n Wizard support n Generates queries for you n Graphical way to select data you want to work with n XML Designer (for creating Data. Sets)

Core Concepts and Architecture n The ADO. NET Object Model n Objects of System. Data n . NET data providers n ADO. NET namespace hierarchy n Organizes the object model n Includes: n System. Data. Ole. Db n System. Data. Common n System. Data. Sql. Client n System. Data. Sql. Types

ADO. NET-related Namespaces ADO. NET System. Data . Sql. Types . Sql. Client . Common . Ole. Db

System. Data Namespace n Contains the basis and bulk of ADO. NET n Data-centric n Provides namespace the means to work on and with your data! n Classes and methods to manipulate your data n Ability to create views of your data n Means to logically represent your data n Enables the use of XML to view, share, and store data

Introducing the Objects… System. Data n Contains the “main” classes of ADO. NET Data. Set n In-memory cache of data Data. Table n In-memory cache of a database table Data. Row n Used to manipulate a row in a Data. Table Data. Column n Used to define the columns in a Data. Table Data. Relation n Used to relate 2 Data. Tables to each other Data. View. Manager n Used to create views on Data. Sets

Putting the Objects Together… Data. Set Tables Data. Table Data. View Data. Row(s) Relations Data. Relation Data. Column Constraint(s) Data. Relation Data. Table Data. View. Manager

Working Data - The Data. Set n An in-memory cache of data from a data source n Common way to represent and manipulate data n Universal data container n Not just for use with databases n Logical or physical representation of data n Designed n to be disconnected from the data source Connect, execute query, disconnect n Can use XML n To read and write data n To read and write XMLSchema

Properties & Methods of Interest n Collections n Properties are used to add & remove tables & relations of Interest: n Tables: Returns the collection of Data. Table objects n Relations: Returns the collection of Data. Relations n Namespace: Gets or sets the namespace of the Data. Set n Using Properties Samples: n my. Data. Set. Tables. Add( my. Table ); n my. Data. Table. Collection = my. Data. Set. Tables

All About Data! Universal Data Container Data. Set: It’s not just for Databases

The Data. Table n May be mapped to a physical table in the data source n Can be related to one another through Data. Relations n Optimistic concurrency or locking - model n Properties of Interest: n Columns: Returns Columns. Collection of Data. Columns n Rows: Returns Data. Row objects as a Rows. Collection n Parent. Relations: Returns the Relations. Collection n Constraints: Returns the table’s Constraints. Collection n Data. Set: Returns the Data. Set of the Data. Table n Primary. Key: Gets the Data. Columns that make up the table’s primary key

System. Data—Data. Set and Data. Table n Create a Data. Table and add it to a Data. Set ds = new Data. Set(); // Create Data. Table object: “Customers”. Data. Table dt= new Data. Table( “Customers” ); // Create and add columns to the table // 1. Explicitly create and Add a Data. Column dc; dc = new Data. Column( “Cust. ID”, Type. Get. Type("System. Int 16")); dt. Columns. Add( dc ); // 2. Implicitly Create and Add columns (Data. Column). dt. Columns. Add( “First_Name”, Type. Get. Type("System String”)); dt. Columns. Add( “Last_Name”, Type. Get. Type("System String”)); // Add the Data. Table object to the Data. Set ds. Tables. Add( dt );

Relating Data - The Data. Relation n Used to create logical relations between your data n Create relations between two (2) Data. Table objects n Requires a Data. Column object from each Data. Table n The Data. Type of both Data. Columns must be the same n Cannot n relate a Int 32 Data. Column and a String Data. Column The relation is named (by you!) n Data. Relation dr=new Data. Relation( “my. Relation”, . . . ) n Makes relational navigation possible n Relations. Collection n used to hold/group them Accessed through the Data. Set’s Relations property

Creating Relations With Data. Relations // Building on the Data. Table example earlier. . . // Get the Data. Table Data. Columns we want to relate. . . Data. Column parent. Col, child. Col; parent. Col= Data. Set. Tables["Customers"]. Columns["Cust. ID"]; child. Col = Data. Set. Tables["Orders“]. Columns["Cust. ID"]; // Create Data. Relation with the name “Customer. Orders”. . . Data. Relation dr = new Data. Relation("Customers. Orders", parent. Col, child. Col); // Add the relation to the Data. Set. . . ds. Relations. Add( dr );

XML and the Data. Set n Data. Set can read/write XML for its data and/or schema n You can create or modify data in a Data. Set using XML n You can create or modify the Data. Sets schema using XML n XML-related Data. Set methods for reading: n Read. Xml: Reads an XML schema and data into the Data. Set n Read. Xml. Schema: Reads an XML schema into the Data. Set n And for writing: n Write. Xml, Write. Xml. Schema n Get. Xml, Get. Xml. Schema n Namespace n Full property: sets the namespace for serialization support for SQL Server-style Diff. Grams

Methods of Reading and Writing XML // Code for creating the Data. Set mds and loading the // Data. Set from a data source not shown. String o. File = “C: \My_ADO. NET\my. Xml. Output. xsd”; String i. File = “C: \My_ADO. NET\my. Xml. Input. xsd”; // Write the Data. Set’s XMLSchema to an XML Document mds. Write. Xml. Schema( o. File ); // Read/Upload XML Data into the Data. Set mds. Read. Xml( i. File); // modify the data //. . . // Write the existing Data to an XML Document mds. Write. Xml( "C: \My_ADO. NET\my. Xml. Data. txt", Xml. Write. Mode. Diff. Gram);

Data. Set, Data. Relation, Data…Views Data. Set Tables Data. Table Data. View. Manager Data. Row(s) Relations Data. Relation Data. Column Data. View. Settings Constraint(s) Data. View. Setting Data. Relation Data. View. Setting Data. Table

Viewing Data - The Data. View n Create multiple views on Data. Table objects n Bindable to user interface controls n Properties of Interest: n n Table: Retrieves or sets the associated Data. Table Sort: Gets or sets the table’s sort columns and sort order Row. Filter: Gets or sets the expression used to filter rows Row. State. Filter: Gets or sets the row state filter n None, Unchanged, New, Deleted, Modified. Current, and others

Creating a Data. View by Example // Code for my. Table “Customers” with “Name” column not shown Data. View view 1 = new Data. View( my. Table ); Data. View view 2 = new Data. View( my. Table ); // Creates Ascending view of Customers by “Name” view 1. Sort = “Name ASC”; // Set the view to show only modified (original) rows view 2. Row. State. Filter= Data. View. Row. State. Modified. Original; // Bind to UI element(s). . . Data. Grid my. Grid = new Data. Grid(); my. Grid. Set. Data. Binding( view 1, “Customer”); //. . .

Viewing More Data. View. Manager n Similar n Used n to a Data. View but Data. Set oriented to create multiple views on a Data. Set Ability to automatically set filters on the tables n Properties of Interest: n Data. View. Settings: Gets the Data. View for on each Data. Table n Data. Set: Gets or sets the Data. Set to be viewed n Create. Data. View n method Creates a Data. View on a Data. Table

Data. View. Manager By Example // Create the Data. View. Manager & views. . . Data. View. Manager dv. Mgr = new Data. View. Manager( my. DS ); dv. Mgr. Create. Data. View( ds. Tables[“Orders"] ); dv. Mgr. Data. View. Settings[“Orders"]. Sort = “Cust. ID ASC"; dv. Mgr. Create. Data. View( ds. Tables[“Customers"] ); dv. Mgr. Data. View. Settings[“Customers"]. Sort = “Name DESC"; // Bind to a UI elements/controls. . . data. Grid 1. Data. Source = view. Mgr; data. Grid 1. Data. Member = "Table 1"; data. Grid 2. Data. Source = view. Mgr; data. Grid 2. Data. Member = "Table 2"; // Update the control with the data. . . data. Grid 1. Update(); data. Grid 2. Update();

The (ADO). NET Data Providers n. A collection of classes for accessing data sources: n Microsoft SQL Server™ 2000, SQL Server 7, and MSDE n Any OLE Database (OLE DB) providers n n Including: Oracle, JET, and SQL OLE DB Providers Establish connection between Data. Sets and data stores n Two . NET data providers: n ADO: via the System. Data. Ole. Db namespace n SQL Server: via the System. Data. Sql. Client namespace n System. Data. Ole. Db is the. NET data provider

. NET Data Providers Hierarchy. Common Contains classes shared by both System. Data. Sql. Client Sql. Command Sql. Connection Sql. Data. Reader Sql. Data. Adapter . Ole. Db. Command Ole. Db. Connection Ole. Db. Data. Reader Ole. Db. Data. Adapter

Ole. Db. Connection and Sql. Connection n Represent n Create, a unique session with a data source open, close a connection to a data source n Functionality and methods to perform transactions n Ole. Db. Connection example: String con. Str="Provider=Microsoft. Jet. OLEDB. 4. 0; " + "Data Source=NWIND_RW. MDB"; Ole. Db. Connection a. Conn = new Ole. Db. Connection(con. Str); a. Conn. Open(); // Execute Queries using Ole. Db. Data. Adapter Class a. Conn. Close();

Ole. Db. Data. Adapter Class 1/2 n Bridge n Inherited from the Data. Adapter class n Means data store between the Data. Set and the data store to modify the Data. Set and data source Data. Adapter Data. Set

Ole. Db. Data. Adapter Class 2/2 n Properties n of Interest: n Delete. Command: The delete command expressed in SQL n Insert. Command: Gets or sets insert command n Select. Command: Gets or sets select command n Update. Command: Gets or sets update command n Table. Mappings: Maps source table and a Data. Table Ole. Db. Commands retrieved or set by command properties n Implements abstract methods of the Data. Adapter class: n public abstract int Fill( Data. Set data. Set ); n public abstract int Update( Data. Set data. Set );

Ole. Db. Command Class n Represents n a query to execute on the data source May be a SQL statement or stored procedure n Properties of Interest: n Connection: Get or set the data source connection n Command. Text: Get or set the query (text) command n n Command. Type: Get/set how the command is interpreted n n A SQL statement or the name of the stored procedure Text, Stored. Procedure, or Table. Direct Command. Timeout: The seconds until connection timeout

Ole. Db. Data. Reader 1/2 n Forward-only data access n “Lightweight” programming model n Less overhead than using Ole. Db. Data. Adapter n Instantiated n Ties & returned by Ole. Db. Command. Execute. Reader up the Ole. Db. Command until it is finished reading

Ole. Db. Data. Reader 2/2 n Properties of Interest: n Field. Count: Returns the number of fields in the result set n Records. Affected: Number of affected records n Methods to retrieve data: n By column type and/or index: Get. Value; Get. String; etc. n Read(): Advances reader to next record n Next. Result(): Advanced to next result set in batch n Get. Values(): Gets the current row

Ole. Db. Data. Reader Sample // Code for creating the Ole. Db. Connection “ado. Conn” not shown String my. Query = “SELECT * FROM Customers”; ado. Conn. Open(); Ole. Db. Command my. Cmd = new Ole. Db. Command( my. Query, ado. Conn ); // Declare the Ole. Db. Data. Reader & // then instantiate it with Execute. Reader(. . . ). . . Ole. Db. Data. Reader reader = my. Cmd. Execute. Reader(); // Always call Read before accessing data. while( reader. Read() ) { Object [] cols = new Object[10] ; reader. Get. Values( cols ); Console. Write. Line( cols[0]. To. String() + " | " + cols[1] ); } // Always Close the reader and the connection when done reader. Close(); ado. Conn. Close();

Summary n ADO. NET n It is the evolution of ADO is a disconnected, Web-centric model n Flexible in its ability to work with data n Increases your ability to logically organize data n Extensive support for XML n Facilitates working with and sharing data n Interacts with a wide variety of data sources

Questions ?

Duwamish Books n. A Sample Application for Microsoft. NET

Installing the Sample 1/2 n Install the "Enterprise Samples" with Visual Studio. NET n Location of the C# Version n Visual Studio. NET folder n Directory. Enterprise. SamplesDuwamish. Online CS n Location n of the Visual Basic® Version Directory. Enterprise. SamplesDuwamish. Online VB n Installation n n Tasks Check the prerequsites n Microsoft Windows® 2000 Server, SQL Server 2000 with English Query optional and supported n Read the Readme. htm Run Installer Duwamish. msi (double-click it)

Installing the Sample 2/2 n The installation wizard will guide you n Defaults n Setup n After should be OK for almost everybody will install database, Web site, and code installation is complete: n File/Open Solution with the Duwamish. sln file n Can build the sample with Build/Build Solution

Duwamish Architecture Overview User / Browser ASP. NET IIS Web Business. Rules Data. Access ADO. NET Database Common. Data System. Framework Business. Facade

Common Components n Duwamish 7. Common n Contains systems configuration options n Contains common data definitions (classes) n n Namespace Duwamish. Common. Data "Internal" data representation for Book, Category, Customer, Order. Data n Duwamish 7. System. Framework n Diagnostics utilities n Pre and post condition checking classes n Dynamic configuration n In short: n Everything that's pure tech and not business code

Duwamish 7. Data. Access n Contains n Uses all database-related code ADO. NET architecture n Using SQL Server managed provider n Shows Data. Set, Data. Set. Command usage n Optimized for performance by using stored procs

Duwamish 7. Business. Rules n Implements all business rules n Validation of business objects (for example, Customer EMail) n Updating business objects n Calculations (Shipping Cost, Taxes) n All data access performed through Data. Access

Duwamish 7. Business. Facade n Implements logical business subsystems n Customer. System: Profile management n Order. System: Order management n Product. System: Catalog n Reads n Data data through Data. Access validated and updated using Business. Rules n Business. Facade functionality encapsulates all business-related

Duwamish 7. Web n Implements n Uses the user interface for Web access ASP. NET architecture n Employs Web Forms model n Uses code behind forms n Manages state n Uses custom Web Controls n All functionality accessed through Business. Facade

Shop at Duwamish Online. NET n Demo: Duwamish in Action

Exploring Duwamish ADO. NET n Exploring ADO. NET Features in Duwamish

Legal Notices Unpublished work. Ó 2001 Microsoft Corporation. All rights reserved. Microsoft, Visual Basic, Visual Studio, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.