Transcript ADO.NET
ADO.NET
Objectives
Introduce Microsoft ® ADO.NET
Show 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.NET and the .NET Framework
Microsoft .NET Framework
Web Services User Interface ADO.NET
Data and XML XML ...
Base Classes Common Language Runtime ...
ADO vs. ADO.NET
1/2
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
2/2
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 Data types are only bound to XML schema No data type conversions required XML, like HTML, is plaintext: “Firewall friendly”
Benefits of ADO.NET
Interoperability through use of XML (more later!) 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 Works the way the Web works: “Hit and Run!” Maintainability Separation of data logic and user interface
Visual Studio.NET Enhancements
Typed programming —a programming style Uses end-user words: Easier to read and write Statement completion in Microsoft Visual Studio.NET
Safer: Provides compile-time checking Examples: Untyped: Table("Customer")("Jones").Column(“Balance”) Typed: myDataSet.Customer("Jones").Balance
Wizard support Generates queries for you Graphical way to select data you want to work with XML Designer (for creating
DataSet
s)
Core Concepts and Architecture
The ADO.NET Object Model Objects of System.Data
.NET data providers ADO.NET namespace hierarchy Organizes the object model Includes: System.Data System.Data.OleDb
System.Data.Common
System.Data.SqlClient
System.Data.SqlTypes
ADO.NET-related Namespaces
ADO.NET
System.Data
.SqlTypes
.SqlClient
.Common
.OleDb
System.Data Namespace
Contains the basis and bulk of ADO.NET
Data-centric namespace Provides 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 DataTable DataRow DataColumn DataRelation
Contains the “main” classes of ADO.NET
In-memory cache of data In-memory cache of a database table Used to manipulate a row in a
DataTable
Used to define the columns in a
DataTable
Used to relate 2
DataTable
s to each other
DataViewManager
Used to create views on
DataSets
Putting the Objects Together…
DataSet
Relations DataRelation DataRelation Tables DataTable DataRow(s) DataColumn Constraint(s) DataView DataViewManager DataTable DataTable
Working Data - The DataSet
An in-memory cache of data from a data source Common way to represent and manipulate data Universal data container Not just for use with databases Logical
or
physical representation of data Designed to be disconnected from the data source Connect, execute query, disconnect Can use XML To read and write data To read and write XMLSchema
Properties & Methods of Interest
Collections are used to add & remove tables & relations Properties of Interest:
Tables
: Returns the collection of
DataTable
objects
Relations
: Returns the collection of
DataRelation
s
Namespace
: Gets or sets the namespace of the
DataSet
Using Properties Samples: myDataSet.Tables.Add( myTable ); myDataTableCollection = myDataSet.Tables
All About Data!
Universal Data Container DataSet: It’s not just for Databases
The DataTable
May be mapped to a physical table in the data source Can be related to one another through
DataRelation
s Optimistic concurrency or locking - model Properties of Interest:
Columns
: Returns
ColumnsCollection
of
DataColumn
s
Rows
: Returns
DataRow
objects as a
RowsCollection
ParentRelations
: Returns the
RelationsCollection
Constraints
: Returns the table’s
ConstraintsCollection
DataSet
: Returns the
DataSet
of the
DataTable
PrimaryKey
: Gets the
DataColumn
s that make up the table’s primary key
System.Data—DataSet and DataTable
Create a
DataTable
and add it to a
DataSet
DataSet ds = new DataSet(); // Create DataTable object: “Customers”.
DataTable dt= new DataTable( “Customers” ); // Create and add columns to the table // 1. Explicitly create and Add a DataColumn DataColumn dc; dc = new DataColumn( “CustID”, Type.GetType("System.Int16")); dt.Columns.Add( dc ); // 2. Implicitly Create and Add columns (DataColumn).
dt.Columns.Add( “First_Name”,Type.GetType("System String”)); dt.Columns.Add( “Last_Name”, Type.GetType("System String”)); // Add the DataTable object to the DataSet ds.Tables.Add( dt );
Relating Data - The DataRelation
Used to create logical relations between your data Create relations between two (2)
DataTable
objects Requires a
DataColumn
object from each
DataTable
The
DataType
of both
DataColumns
must be the same Cannot relate a
Int32 DataColumn
and a
String DataColumn
The relation is named (by you!) DataRelation dr=new DataRelation( “myRelation”,...) Makes relational navigation possible
RelationsCollection
used to hold/group them Accessed through the
DataSet
’s
Relations
property
Creating Relations With DataRelations
// Building on the DataTable example earlier...
// Get the DataTable DataColumns we want to relate...
DataColumn parentCol, childCol; parentCol= DataSet.Tables["Customers"].Columns["CustID"]; childCol = DataSet.Tables["Orders“].Columns["CustID"]; // Create DataRelation with the name “CustomerOrders”... DataRelation dr = new DataRelation("CustomersOrders", parentCol, childCol); // Add the relation to the DataSet... ds.Relations.Add( dr );
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
DataSet
s 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:
WriteXml, WriteXmlSchema
GetXml, GetXmlSchema
Namespace
property: sets the namespace for serialization Full support for SQL Server-style DiffGrams
Methods of Reading and Writing XML
// Code for creating the DataSet mds and loading the // DataSet from a data source not shown. String oFile = “C:\\My_ADO.NET\\myXmlOutput.xsd”; String iFile = “C:\\My_ADO.NET\\myXmlInput.xsd”; // Write the DataSet’s XMLSchema to an XML Document mds.WriteXmlSchema( oFile ); // Read/Upload XML Data into the DataSet mds.ReadXml( iFile); // modify the data // ...
// Write the existing Data to an XML Document mds.WriteXml( "C:\\My_ADO.NET\\myXmlData.txt", XmlWriteMode.DiffGram);
DataSet, DataRelation, Data…Views
DataSet
Relations DataRelation DataRelation Tables DataTable DataRow(s) DataColumn Constraint(s) DataView DataViewManager DataViewSettings DataViewSetting DataViewSetting DataTable DataTable
Viewing Data - The DataView
Create multiple views on
DataTable
objects Bindable to user interface controls Properties of Interest:
Table
: Retrieves or sets the associated
DataTable
Sort
: Gets or sets the table’s sort columns and sort order
RowFilter
: Gets or sets the expression used to filter rows
RowStateFilter
: Gets or sets the row state filter
None
,
Unchanged
,
New
,
Deleted
,
ModifiedCurrent
, and others
Creating a DataView by Example
// Code for myTable “Customers” with “Name” column not shown DataView view1 = new DataView( myTable ); DataView view2 = new DataView( myTable ); // Creates Ascending view of Customers by “Name” view1.Sort = “Name ASC”; // Set the view to show only modified (original) rows view2.RowStateFilter= DataViewRowState.ModifiedOriginal; // Bind to UI element(s)... DataGrid myGrid = new DataGrid(); myGrid.SetDataBinding( view1, “Customer”); //...
Viewing More Data - DataViewManager
Similar to a
DataView
but
DataSet
oriented Used to create multiple views on a
DataSet
Ability to automatically set filters on the tables Properties of Interest:
DataViewSettings
: Gets the
DataView
for on each
DataTable
DataSet
: Gets or sets the
DataSet
to be viewed
CreateDataView
method Creates a
DataView
on a
DataTable
DataViewManager By Example
// Create the DataViewManager & views...
DataViewManager dvMgr = new DataViewManager( myDS ); dvMgr.CreateDataView( ds.Tables[“Orders"] ); dvMgr.DataViewSettings[“Orders"].Sort = “CustID ASC"; dvMgr.CreateDataView( ds.Tables[“Customers"] ); dvMgr.DataViewSettings[“Customers"].Sort = “Name DESC"; // Bind to a UI elements/controls...
dataGrid1.DataSource = viewMgr; dataGrid1.DataMember = "Table1"; dataGrid2.DataSource = viewMgr; dataGrid2.DataMember = "Table2"; // Update the control with the data...
dataGrid1.Update(); dataGrid2.Update();
The (ADO).NET Data Providers
A collection of classes for accessing data sources: Microsoft SQL Server ™ 2000, SQL Server 7, and MSDE Any OLE Database (OLE DB) providers Including: Oracle, JET, and SQL OLE DB Providers Establish connection between
DataSet
s and data stores Two .NET data providers: ADO: via the
System.Data.OleDb
namespace SQL Server: via the
System.Data.SqlClient
namespace
System.Data.OleDb
is
the
.NET data provider
.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 a unique session with a data source Create, open, close a connection to a data source Functionality and methods to perform transactions
OleDbConnection
example: String conStr="Provider=Microsoft.Jet.OLEDB.4.0;" + aConn.Open(); "Data Source=NWIND_RW.MDB"; OleDbConnection aConn = new OleDbConnection(conStr); // Execute Queries using OleDbDataAdapter Class aConn.Close();
OleDbDataAdapter Class
1/2
Bridge between the
DataSet
and the data store Inherited from the
DataAdapter
class Means to modify the
DataSet
and data source
data store DataAdapter DataSet
OleDbDataAdapter Class
2/2
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
OleDbCommand
s 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
1/2
Forward-only data access “Lightweight” programming model Less overhead than using
OleDbDataAdapter
Instantiated & returned by
OleDbCommand
.
ExecuteReader
Ties up the
OleDbCommand
until it is finished reading
OleDbDataReader
2/2
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
OleDbDataReader Sample
// Code for creating the OleDbConnection “adoConn” not shown String myQuery = “SELECT * FROM Customers”; adoConn.Open(); OleDbCommand myCmd = new OleDbCommand( myQuery,adoConn ); // Declare the OleDbDataReader & // then instantiate it with ExecuteReader(...) ...
OleDbDataReader reader = myCmd.ExecuteReader(); // Always call Read before accessing data. while( reader.Read() ) { Object [] cols = new Object[10] ; reader.GetValues( cols ); Console.WriteLine( cols[0].ToString() + " | " + cols[1] ); } // Always Close the reader and the connection when done reader.Close(); adoConn.Close();
Summary
ADO.NET is the evolution of ADO It is a disconnected, Web-centric model Flexible in its ability to work with data Increases your ability to logically organize data Extensive support for XML Facilitates working with and sharing data Interacts with a wide variety of data sources
Questions ?
Duwamish Books
A Sample Application for Microsoft .NET
Installing the Sample
1/2
Install the "Enterprise Samples" with Visual Studio.NET
Location of the C# Version Visual Studio.NET folder Directory .\EnterpriseSamples\DuwamishOnline CS Location of the Visual Basic® Version Directory .\EnterpriseSamples\DuwamishOnline VB Installation Tasks Check the prerequsites Microsoft Windows ® 2000 Server, SQL Server 2000 with English Query optional and supported Read the Readme.htm
Run Installer Duwamish.msi (double-click it)
Installing the Sample
2/2
The installation wizard will guide you Defaults should be OK for almost everybody Setup will install database, Web site, and code After installation is complete:
File/Open Solution
with the Duwamish.sln file Can build the sample with
Build/Build Solution
Duwamish Architecture Overview
User / Browser ASP.NET
IIS Web BusinessFacade BusinessRules DataAccess ADO.NE
T Database
Common Components
Duwamish7.Common
Contains systems configuration options Contains common data definitions (classes) Namespace Duwamish.Common.Data
"Internal" data representation for Book, Category, Customer, OrderData Duwamish7.SystemFramework
Diagnostics utilities Pre and post condition checking classes Dynamic configuration In short: Everything that's pure tech and not business code
Duwamish7.DataAccess
Contains all database-related code Uses ADO.NET architecture Using SQL Server managed provider Shows
DataSet
,
DataSetCommand
usage Optimized for performance by using stored procs
Duwamish7.BusinessRules
Implements all business rules Validation of business objects (for example, Customer EMail) Updating business objects Calculations (Shipping Cost, Taxes) All data access performed through DataAccess
Duwamish7.BusinessFacade
Implements logical business subsystems CustomerSystem: Profile management OrderSystem: Order management ProductSystem: Catalog Reads data through DataAccess Data validated and updated using BusinessRules BusinessFacade encapsulates all business-related functionality
Duwamish7.Web
Implements the user interface for Web access Uses ASP.NET architecture Employs Web Forms model Uses code behind forms Manages state Uses custom Web Controls All functionality accessed through BusinessFacade
Shop at Duwamish Online.NET
Demo: Duwamish in Action
Exploring Duwamish ADO.NET
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.