1 / 42

ADO.NET Overview ADO.NET Classes

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)

alain
Download Presentation

ADO.NET Overview ADO.NET Classes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADO.NET Overview • ADO.NET Classes

  2. ADO.NET OverviewLooking 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)

  3. ADO.NET OverviewLooking Back • OLE DB • 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++

  4. ADO.NET OverviewLooking Back Your Application ADO OLE DB ODBC Provider Simple Provider Native Provider ODBC OLE DB Provider OLE DB Provider ODBC Driver TextFile Mainframe Database Database

  5. ADO.NET OverviewLooking Back • ADO was designed as a connected, tightly coupled model • Appropriate for client/server architectures • Primarily relational (not hierarchical like XML) • 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

  6. ADO.NET OverviewWhat Is ADO.NET? • ADO .NET is a collection of classes, interfaces, structures, and enumerated types that manage data access from relational data stores within the .NET Framework • These collections are organized into namespaces: • System.Data, System.Data.OleDb, System.Data.SqlClient, etc. • ADO .NET is an evolution from ADO. • Does not share the same object model, but shares many of the same paradigms and functionality!

  7. ADO.NET OverviewADO.NET Goals • Well-factored design • Highly scaleable through a robust disconnected model • Rich XML support (hierarchical as well as relational) • Data access over HTTP • Maintain familiar ADO programming model • Keep ADO available via .NET COM interoperability

  8. ADO.NET OverviewManaged 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

  9. ADO.NET OverviewManaged Providers Your Application ADO.NET Managed Provider SQL Managed Provider ADO Managed Provider OLE DB Provider SQL ServerDatabase Database

  10. ADO.NET OverviewData Access Styles • Connected: 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

  11. ADO.NET OverviewData 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 %>'/>

  12. Agenda • Database Theory and History • Relational Database Concepts and Terminology • ADO.NET Overview • ADO.NET Classes

  13. ADO.NET ClassesIDbConnection Interface • Creates a unique session with a data source • Implemented by SqlDbConnection and OleDbConnection • 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

  14. ADO.NET Classes IDbCommand Interface • Represents a statement to be sent to a data source • Usually, but not necessarily SQL • Implemented by OleDbCommandandSqlCommand • 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

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

  16. 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

  17. ADO.NET Classes DataReader Example string sConnString = “Provider=SQLOLEDB.1;” + “User ID=sa;Initial Catalog=Northwind;” + “Data Source=MYSERVER”; OleDbConnection conn = new OleDbConnection(sConnString); conn.Open(); string sQueryString = “SELECT CompanyName FROM Customers”; OleDbCommand myCommand = new OleDbCommand(sQueryString, conn); OleDbDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { Console.WriteLine(myReader.GetString(0)); } myReader.Close(); conn.Close();

  18. ADO.NET ClassesSystem.Data Namespace • Contains the core classes of the ADO.NET architecture • Disconnected DataSet is central • Supports all types of applications • Internet based • ASP.NET • XML • Windows forms based

  19. ADO.NET ClassesSystem.Data Namespace • Contains classes used by or derived from managed providers • IDbConnection, IDbCommand, IDbDataReader

  20. ADO.NET Classes DataSet • A collection of tables • Has no knowledge of the source of the data • 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

  21. ADO.NET Classes DataSet DataSet DataTable DataColumn DataRow DataRelation

  22. 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

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

  24. 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); conn.Open(); string sQueryString = “SELECT CompanyName FROM Customers”; SqlDataAdapter myDSAdapter = new SqlDataAdapter(); DataSet myDataSet = new DataSet(); myDSAdapter.SelectCommand = new SqlCommand(sQueryString, conn); myDSAdapter.Fill(myDataSet); conn.Close();

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

  26. ADO.NET Classes DataColumn • 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 DataTable) • Can contain Relations (collection on DataSet)

  27. ADO.NET Classes DataRow • Represents data in a DataTable (contained in Rows collection) • Conforms to schema defined by DataColumns • Properties for determining row state (e.g., new, changed, deleted, etc.) • All additions/modifications “committed” with AcceptChanges method of DataTable

  28. ADO.NET Classes DataRelation • 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 disallowed

  29. 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”);

  30. 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};

  31. ADO.NET Classes Creating a DataSet in Code • Add the tables to the DataSet dataset.Tables.Add (authors); dataset.Tables.Add (books);

  32. ADO.NET Classes Creating a DataSet in Code • Add data and save the DataSet DataRow shkspr = authors.NewRow(); shkspr["Name"] = "William Shakespeare"; authors.Rows.Add(shkspr); 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"; books.Rows.Add(row); dataset.AcceptChanges();

  33. ADO.NET ClassesTyped DataSets • Typed DataSet • Derived from base DataSet class • Uses XML schema to generate new class • Tables, columns, etc. compiled into new class ds.Customers.FirstName • Untyped DataSet • No built-in schema • Tables, columns, etc. exposed only as collections ds.Tables[“Customers”].Rows[0][“FirstName”]

  34. 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

  35. ADO.NET Classes Errors and Exceptions Example try { DataTable myTable = new DataTable(); myTable.Columns.Add(“myCol”); myTable.Columns.Add(“myCol”); //whoops! } catch (DataException myException) { Console.WriteLine ("Message: " + myException.Message + "\n" + "Source: " + myException.Source + "\n" + “Stack Trace: " + myException.StackTrace + "\n"); }

  36. Conclusion • Database Theory and History • Relational Database Concepts and Terminology • ADO.NET Overview • ADO.NET Classes

  37. Resources • Introducing ADO+ • http://msdn.microsoft.com/msdnmag/issues/1100/adoplus/adoplus.asp • ADO.NET • http://msdn.microsoft.com/library/default.asp?URL=/library/dotnet/cpguide/cpconaccessingdata.htm • ADO+ Guides the Evolution of the Data Species • http://msdn.microsoft.com/library/techart/adoplus.htm • ADO.NET for the ADO Programmer • http://msdn.microsoft.com/library/techart/adonetdev.htm • ADO Rocks and Rolls in .NET Applications • http://msdn.microsoft.com/library/welcome/dsmsdn/data02222001.htm • Meditating on OLE DB and .NET • http://msdn.microsoft.com/library/welcome/dsmsdn/data03222001.htm

  38. Resources • Reading Data Reader Secrets • http://msdn.microsoft.com/library/welcome/dsmsdn/data04122001.htm • Database-like Data Containers • http://msdn.microsoft.com/library/default.asp?URL=/library/welcome/dsmsdn/data04122001.htm • ADO • http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/ados4piv.htm • Universal Data Access • http://www.microsoft.com/data/ • SQL Server • http://www.microsoft.com/sql/default.asp

  39. AppendixADO 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

  40. AppendixADO vs. ADO.NET

  41. AppendixADO vs. ADO.NET

More Related