The Whidbey Release Of ADO 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! greg@whitebearconsulting.com