The Whidbey Release Of ADO.NET Part 1

1. The Whidbey Release Of ADO.NET Part 1 Greg Low Director White Bear Consulting & Lowell Computing greg@whitebearconsulting.com

2. What we will cover: • This material combines content from a two part series. • Part 1 (level 200) covers the fundamental enhancements to ADO.NET in the Whidbey release. • Part 2 (level 300) covers the more advanced enhancements.

3. Session Prerequisites • Experience with development in Visual Basic® .NET or Visual C#® using ADO.NET • Experience with the ADO.NET classes in the .NET Framework (either v1.0 or v1.1)

4. So Why This Presentation? • ADO.NET has significant enhancements for the Whidbey release. • The product manager Pablo Castro describes it as an evolution, not a revolution ie: your existing code will still work. • Developers need to look at what can now be achieved, often improving performance with very little code modifications required.

5. Agenda (from Session 1) • Lots of code examples! • Server Enumeration • .NET Data Provider Enumeration • Multiple Active Result Sets • MDAC Dependency Removal • Batch Processing • Binary Serialization of Datasets

6. Agenda (from Session 1 continued) • Paging • Yukon Data Type Support • Common Base Classes

7. Agenda (from Session 2) • Again, lots of code examples! • Asynchronous Processing • Bulk-copy Operations • Yukon User-Defined Data Types • SQL Server Notifications • DataTableReader • DataSet.Load • Transactions with SQL Server Snapshot Isolation

8. Agenda (from Session 2 continued) • Miscellaneous Enhancements • FireInfoMessageEventOnUserErrors • UpdatedRowSource • StatementCompleted • StatisticsEnabled

9. Session Notes • We are discussing a product that has not been released yet. • Other significant changes will no doubt occur prior to release. • All material covered in this session is based on the editions of Whidbey and Yukon released at the PDC. • And to head off the obvious question, I don't know when it will be released!

10. Server Enumeration • Very, very common request in the newsgroups is "How can I populate a list of the available servers?" • DBProviderFactory class • Data Providers need to provide their own mechanisms for enumerating servers. • SQL Server™ demo

11. .NET Data Provider Enumeration • You can also use the DBProviderFactories to get a list of data providers installed on your computer. • Listed in the machine.config file. • A DataTable called providerconfiguration is returned (Name, Description, InvariantName, AssemblyQualifiedName,SupportedClasses) • This should be useful when using the new common base classes. • Demo code

12. Multiple Active Result Sets • MARS allows more than one SQLDataReader to be open on a single connection. • Automatically supported when using Yukon. • Parameter in ConnectionString to disable it (MultipleActiveResultSets=false). • OLEDB provider provides support when connected to Yukon via MDAC v9.0. Simulates support on earlier MDAC or SQL Server versions. • Oracle provider provides support. • Demo code

13. MDAC Dependency Removal • For the PDC release, on Windows XP or later, MDAC 9.0 is installed in side-by-side mode. • You must provide an application manifest with your ADO.NET application. • Version 2.0 of the .NET framework will not require MDAC to be installed when using the SQLClient. • For OLEDB and ODBC managed providers, versions 2.6, 2.7, 2.8 and later will do. • Manifest example code

14. Batch Processing • DataAdapter.Update method can now update more than a single row in each remote procedure call to the database server. • UpdateBatchSize property. • -1 means "all rows" • Transaction support still present. • Demo code

15. Binary Serialization of DataSets • Developers have avoided using DataSets when data needs to be passed across boundaries. • Performance has been the key reason. • Binary Serialization can drastically improve performance of both serialization and deserialization. • Reduced memory utilization. • Other DataSet performance improvements have also been made eg. Faster index engine. • Demo code

16. Paging • New ExecutePageReader method • Fast mechanism for skipping rows in tables • Consistency issues need to be considered. • Demo code

17. DbTable Class (future uncertain) • Standalone object. • Provides all functionality required to retrieve and manipulate data from a data source. • Extends the DataTable class. • Virtual class. • Instantiate via SqlDataTable, OleDbDataTable, OracleDataTable, ODBCDataTable, etc. • Demo code

18. Yukon Data Type Support • SQL Server Yukon provides new datatypes • varchar(max), nvarchar(max), varbinary(max) • xml • ADO.NET / Whidbey natively supports these. • SqlDataReader.GetSqlXmlReader • Demo code

19. Common Base Classes • Db*Base Classes • DbConnection, DbCommand, DbDataAdapter • Allows a degree of provider-independent applications. • SQL syntax is still database-specific • Demo code

20. Asynchronous Processing • The .NET Framework has a standard design pattern for async processing. • Caller determines whether the call will be async or sync. • ADO.NET now offers the same pattern. • Supported in SqlClient. • Requires MDAC 9.0 for the PDC build. • Less need for the dreaded hourglass! • Demo code

21. Bulk-Copy Operations • Transfer large amounts of data into a table or view. • Much faster than SQL INSERT statements. • SqlBulkCopyOperation class can be used to copy data from a DataReader or DataTable. • Full transaction support. • Demo code

22. Yukon User-Defined Data Types • CLR types implemented in the SQL Server. • Objects returned in ADO.NET. • Methods can be called both on the server and on the client. • Demo code

23. SQL Server Notifications • SqlCommand object can be configured to request a notification when the results of a given query would have changed. • Applications can refresh displays or caches. • Disconnected scenarios are possible. • SqlNotificationRequest, SqlDependency objects • Users may need special privileges to request a notification. • Application polls the notification queue. • Demo code

24. Transactions with SQL Server Snapshot Isolation • Used to reduce blocking. • Stores a version of data that can be read by one app while another is modifying the original data in a transaction. • Similar effect to serializable transaction isolation level in terms of the view of the data. • Locks are not taken on the data. • Demo code

25. DataTableReader • Reads the contents of a DataTable or a DataSet. • Ignores deleted rows. • DataSet.GetDataReader method • Reading a DataSet returns multiple resultsets if the DataSet contains more than one DataTable. • Demo code

26. DataSet.Load • Loads a DataTable with rows from a data source. • Read from a DataReader. • Control loading via LoadOption parameter (Overwrite row, PreserveCurrentValues, UpdateCurrentValues) • Demo code

27. Miscellaneous Enhancements • FireInfoMessageEventOnUserErrors • UpdatedRowSource • StatementCompleted • StatisticsEnabled • Demo code

28. Session Summary • ADO.NET in the Whidbey release offers an evolution, not a revolution. • ADO.NET in the Whidbey release can enhance your applications with little code modifications required. • ADO.NET in the Whidbey release opens up new possibilities for your applications.

29. For More Information • PDC Sessions • http://microsoft.sitestream.com/PDC2003 • But particularly: • http://microsoft.sitestream.com/PDC2003/ARC/ARC403_files/Default.htm • MSDN help files on PDC Whidbey DVD

30. Get Up to Speed on .NET Get Trained on Microsoft Developer Technologies • Register for upcoming webcasts at http://www.microsoft.com/usa/webcasts/

31. Thanks for listening! greglow@lowell.com.au greg@whitebearconsulting.com