Programming ADO.NET in Whidbey

1. Session Code:ARC403 Programming ADO.NET in Whidbey Pablo Castro/Brad Rhodes ADO.NET Team Microsoft Corporation [pablocas|bradrhod]@microsoft.com

2. Tools Client Application Model Web & Service Application Model Data Systems Application Model Mobile PC & Devices Application Model Command Line Compact Framework System.Console Win FS Yukon Mobile PC Optimized Avalon Windows Forms ASP.NET / Indigo NTService System.Data.SqlServer System.Windows.Forms System.Windows System.Storage System.Web System.Windows System.Windows.Forms System.ServiceProcess Communication Data Presentation System.Data System.Collaboration System.Search System.Discovery System.Windows System.Messaging SqlClient DataSet RealTimeEndpoint UI Element Explorer Media Annotations System.DirectoryServices ActiveDirectory SqlTypes TransientDataSession Mapping Documents Controls Animation System.Remoting Monitoring SqlXML SignalingSession ObjectSpaces Text Element Dialogs Controls System.Runtime.Remoting Logging Uddi Media OdbcClient ObjectSpace Shapes SideBar Control Relevance Activities OleDbClient Query Shape Notification Panel Navigation OracleClient Schema Ink Design System.Web.Services System.MessageBus Transport Queue Web.Service System.Storage Port PubSub System.Windows.Forms System.Web.UI Description Item Core Channel Router Forms Page WebControls Discovery Relationship Contact Service Policy Control Control Adaptors Protocols Media Location Peer Group Print Dialog HtmlControls Design Audio Message Design MobileControls Video Document System.Net System.Web Images Event HttpWebRequest NetworkInformation System.Help System.Speech Personalization System.Xml FtpWebListener Sockets System.Drawing Recognition Synthesis Caching Schema Xpath SslClientStream Cache SessionState System.NaturalLanguageServices Serialization Query WebClient Fundamentals Security Base & Application Services Configuration Deployment/Management System.Windows.TrustManagement System.Timers System.Text System.Web.Configuration System.Collections System.Web System.Security System.Globalization System.Design Generic System.MessageBus.Configuration Administration Permissions Authorization System.Web.Security System.Serialization System.IO System.Configuration Management System.ComponentModel Policy AccessControl System.Threading Ports System.Resources System.CodeDom Principal System.Management Credentials System.MessageBus.Security System.Runtime System.Reflection Token Cryptography System.Deployment Serialization InteropServices System.EnterpriseServices System.Diagnostics CompilerServices System.Transactions

3. Agenda • Key ADO.NET Whidbey Themes • Faster • Easier • Yukon-enabled • Common base classes

4. ADO.NET Whidbey Themes • Strictly incremental • No revolutions • Address customer requests • Lots of new customer-driven features • More power for power users • Performance, Performance, Performance • First-class support for SQL Server “Yukon” No Model Changes. If it works now, will work in Whidbey :)

5. Agenda • Key ADO.NET Whidbey Themes • Faster • Easier • Yukon-enabled • Common base classes

6. Asynchronous Data Access • Great for: • Client apps: avoid blocking the UI • Server apps: avoid blocking threads • Consistent with the .NET async pattern • BeginOpen, EndOpen, BeginExecute, … • API available for 3rd party providers • We ship async support in SqlClient • Works with SQL Server 7, 2000, “Yukon” • True asynchronous data access

7. Waiting for the DB (not)

8. Batch Updates • Currently DataAdapter.Update does a round-trip per row • New support for batching in Whidbey • adapter.UpdateBatchSize = your_batch_size • em…That’s it :) • Available for SqlClient and OracleClient • Batches can execute inside a transaction • The transaction has to be set in the command objects in the adapter

9. Fast Updates

10. DataSet Performance • Remoting • One of the most common requests: • “datasets are slow when used in remoting” • We tweaked here and there • Support for binary serialization • Needs to be enabled on the DataSet instance • Very fast serialize/deserialize • Critical reduction of memory usage • New, much faster index engine • Great improvement on insert performance

11. Binary DataSet Remoting Up to ~80 times faster (scale is logarithmic)

12. Agenda • Key ADO.NET Whidbey Themes • Faster • Easier • Yukon-enabled • Common base classes

13. Simplified Deployment • No MDAC requirement at all • ADO.NET base, common and disconnected classes • .NET managed providers for SQL Server & Oracle • Use MDAC, but no new requirements • OLEDB and ODBC managed providers • 2.6, 2.7, 2.8 or 9.0 will do • :) !!! (the PDC build still requires MDAC)

14. Paging • Straightforward API: ExecutePageReader • Provide starting row and number of rows • No consistency guarantees • Outside a transaction, rows can be skipped or repeated • Way faster than skipping rows in a large table

15. One page at a time

16. Agenda • Key ADO.NET Whidbey Themes • Faster • Easier • Yukon-enabled • Common base classes

17. User-Defined Types • UDTs are CLR types that live in the DB • Extend the database scalar type-system • Instances ‘travel’ between server and client • Seamless integration with ADO.NET • You “just use objects” in the API • i.e. datareader.GetValue or Parameter.Value=… • Maintains encapsulation of the UDTs • Same methods can be called inside and outside the server • State representation remains private

18. Exchanging Objects with the Database

19. Multiple Active Result-Sets • More than one simultaneous pending request per connection • Multiple active data-readers • Execute batches between Read() and Read() • One active data-reader per command • Built on top of SQL Server “Yukon” MARS System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.

20. SQL Server XML Data Type • Seamless integration • “It’s just a type” • Data-reader • Use SqlDataReader.GetSqlXmlReader • Parameters • Provide an XmlReader or a string as the value • DataSet • Surfaced as a XPathDocument • For PDC build, DataSet will contain a XmlReader

21. Agenda • Key ADO.NET Whidbey Themes • Faster • Easier • Yukon-enabled • Common base classes

22. Common Provider Model • ADO.NET v1.0/1.1 was based on interfaces • Not enough to write provider-agnostic code • Hard to evolve • We’re introducing a common model • Abstract base classes instead of interfaces • Better versioning story • 3rd party providers can reuse our common code • They can even re-use our connection pooler • Provider-independent apps fully enabled at the API level • This is an extension, no breaking changes

23. Db* Base Classes • A set of classes to program to • DbConnection, DbCommand, etc. • This provides API independence • SQL syntax is still database-specific • Factory model to create provider objects • Enables both connected and disconnected scenarios • Can enumerate the installed providers • Each provider has an invariant name • ie. System.Data.SqlClient, System.Data.OracleClient

24. Provider-agnostic

25. General connected stack Database schema retrieval Provider enumeration Integration with System.Transactions Tracing Connection pool reset API DataSet DataTable more stand-alone Full namespaces for tables DataSet.Load DataTableReader Flexibility on relationships DataTable from DataView with projection and unique rows SqlClient Notifications SQL Server ‘Yukon’ types varchar/varbinary(max) Bulk-copy Server enumeration Change password on login Transparent failover Promotable transactions More to come… OleDb Managed pooling option OracleClient Batching for updates More features

26. ADO.NET Data Access Support Data is relational is objects is XML is Data Technology Strengths Use if… • You are comfortable with the relational model • You require maximum control/performance/functionality • You are using UI bound controls DataReaderand DataSet • Relational (tabular) model • Highest performance • Explicit control • Fully exposes database functionality • Business level objects • Relational mapping via metadata • Decoupled from database schema • Smaller working set than other object abstractions • You need a strong business object layer ObjectSpaces in ADO.NET • You need to query data from XML data sources e.g. XML Web Services • You use vertical industry XML schemas for content publishing e.g. XBRL, RIXML, FinXML • You need to load XML documents into database tables • You are using UI bound controls with XML • Interoperability. Format for the Web – B2B, A2A • Sparse (semi-structured) data • XML Services e.g. XQuery, XSD • Relational mappingvia metadata • Decoupled fromdatabase schema SQLXML in ADO.NET

27. Related Sessions • DAT400 - Programming SQL Server "Yukon" Using Managed Code • ARC404 - Data Access Design Patterns: Navigatingthe Data Access Maze • DAT402 - Building Database Applications with SQL Server "Yukon": XQuery, XML Datatype • DAT405 - Caching Techniques forScalable Enterprise Applications • DAT410 - .NET Framework: Developing Applications Using the New Object-Relational Technologiesin "Whidbey“ • ARC380 - .NET Framework: What's New inSystem.Xml for "Whidbey" • PNL05 - Client Architecture: The Zen ofData-Driven Applications

28. Summary • Whidbey: customer-driven ADO.NET • Addressing customer requests • Features, performance, usability • Integration • Great support for SQL Server “Yukon” • ADO.NET continues to evolve • No revolutions, no compatibility issues • We’d love to hear your feedback! • Ask The Experts, SQL Lounge and Booths • Members of the dev team will be there!

29. SQL Server Resources • Week long SQL Server “Ask the Experts” lounge in: • Foyer outside Room 309 • Support for SQL Server “Yukon” PDC Preview at SQLJunkies • http://www.sqljunkies.com/forums • SQL Server “Yukon” FAQ Blog at SQLTeam • http://yukonblog.sqlteam.com/ • Other Key Resources • http://www.microsoft.com/sql/community • http://msdn.microsoft.com/sqlserver/ • 34 world wide user groups, • http://msdn.microsoft.com/usergroups/find.asp • Our most active SQL Server newsgroups, • Microsoft.public.sqlserver.programming • Microsoft.public.sqlserver.server • Microsoft.public.sqlserver.dts • Microsoft.public.sqlserver.olap • Microsoft.public.sqlserver.setup • Microsoft.public.sqlserver.replication • Microsoft.public.sqlserver.msde

30. © 2003-2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.