1 / 34

What’s New in ADO.NET 2.0

What’s New in ADO.NET 2.0. Julie Lerman The Data Farm www.thedatafarm.com jlerman@thedatafarm.com. Julie Lerman. Independent Software Developer 20 years developing Microsoft .NET MVP ASPInsider Various publications & conferences Blog: www.thedatafarm.com/blog

kendall
Download Presentation

What’s New in ADO.NET 2.0

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. What’s New in ADO.NET 2.0 Julie Lerman The Data Farm www.thedatafarm.com jlerman@thedatafarm.com New England Code Camp IV: “Developer’s Gone Wild”

  2. Julie Lerman • Independent Software Developer • 20 years developing • Microsoft .NET MVP • ASPInsider • Various publications & conferences • Blog: www.thedatafarm.com/blog • Founder and leader of Vermont .NET • INETA Advisory Board Member New England Code Camp IV: “Developer’s Gone Wild”

  3. Overview • ADO.NET API is Evolving • It is not being overhauled • Backward compatible to ADO.NET 1.x • SqlClient has close ties to SQL Server 2005 New England Code Camp IV: “Developer’s Gone Wild”

  4. Agenda • Across the board changes in ADO.Net 2.0 • Usability • Flexibility • Power and performance • Changes to SQLClient namespace • Leveraging SQL Server 2005 features Demos will be on VS2005 RC & SQL Server 2005 Developer Sept CTP This box is 1.5 Ghz , 1 GB RAM New England Code Camp IV: “Developer’s Gone Wild”

  5. EVOLUTION:Usability & Flexibility • DataTable becomes iXMLSerializable • DataTable/DataSet.Load Method • Convert from and to DataReader • Dataview.ToTable Method • Rowstate.SetAdded/SetModified • Provider Independent API New England Code Camp IV: “Developer’s Gone Wild”

  6. DataTable Grows Up • DataTable – IXMLSerializable • No longer dependent on DataSet for • ReadXML & WriteXML • Transfer via Web Services • Merge • Merge multiple DataTables • Note: CANNOT merge DataRow Collections New England Code Camp IV: “Developer’s Gone Wild”

  7. Stream Data into DataTable & DataSet • DataSet/DataTable.Load • Populate a DataTable/DataSet from a DataReader • Load from dbDataReaders • OLEleDbDataReader • SqlDataReader • DataTableReader* • Etc. • Internal function of DataAdapter now exposed • Allows more granular control • Enables DataTable & DataSet Merge functionality with DataReaders • LoadOption (also new to DataAdapter) to deal with concurrency issues New England Code Camp IV: “Developer’s Gone Wild”

  8. DataTableReader • Inherits from dbDataReader • Forward only container for DataTable(s) data • Get a DataReader from a DataTable/DataSet • DataSet/DataTable.CreateDataReader Method • Iteration without a database connection • Populating list controls and more • Navigate similar to SqlDataReader • Read (to next record) • NextResult (to next resultset) • Handles changes to underlying data source well New England Code Camp IV: “Developer’s Gone Wild”

  9. DEMODataTableReaders, DataSetLoad New England Code Camp IV: “Developer’s Gone Wild”

  10. DataView.ToTable • Create a DataTable from a DataView! • Rather than building from iteration or a new database query • DistinctRows parameter is a small blessing New England Code Camp IV: “Developer’s Gone Wild”

  11. DEMODataView.ToTable(DistinctRows) New England Code Camp IV: “Developer’s Gone Wild”

  12. RowState • DataRow.RowState is no longer read only • “Unchanged” rows can be modified • SetAdded • SetModified • DataRowState enums • Added, Deleted, Detached, Unchanged, Modified New England Code Camp IV: “Developer’s Gone Wild”

  13. DEMORowState.SetAdded, SetModified New England Code Camp IV: “Developer’s Gone Wild”

  14. Provider Independent API • Database Independent Coding • System.Data.Common Namespace • Provider Factory Class • Flip in database type at run time • Current Factories • OleDBFactory • OdbcFactory • SQLClientFactory • OracleClientFactory New England Code Camp IV: “Developer’s Gone Wild”

  15. Using Provider Independent APIs ‘example APINameSpace=“System.Data.SQLClient” Public Function GetDS(byVal APINameSpace as String)as DataSet Dim provider as DbProviderFactory = dbProviderFactories.GetFactory(APINameSpace) Dim connection As DbConnection-provider.CreateConnection Dim adapter as DbDataAdapter = provider.CreateDataAdapter ‘code to connect command, connection and dataadapter… adapter.fill(ds) Return ds End Function New England Code Camp IV: “Developer’s Gone Wild”

  16. DEMOIndependent Data Providers New England Code Camp IV: “Developer’s Gone Wild”

  17. EVOLUTION:Performance • Batch Updates • True Binary Serialization for Remoting New England Code Camp IV: “Developer’s Gone Wild”

  18. Batch Updating • Works with DataAdapter.UpdateCommand • UpdateBatch property, default=1 • Sends groups of queries to the database • Fine tune based on client and network resources • Time to build multiple queries • Network latency • Processor • Connection pooling New England Code Camp IV: “Developer’s Gone Wild”

  19. Batch Update:Bigger is not always Better! Speed of 10,000 Record Update Speed of 100 Record Update Batch Size Batch Size Not official benchmarks. Based on my own tests Local=SQL2005 Remote Hard Wired=SQL2000 Web=SQL2000 New England Code Camp IV: “Developer’s Gone Wild”

  20. Binary Serializaton in Remoting • V1.x • DataSet is still xml • V2.0 • DataSet is truly serialized as binary • Major performance benefit with large amounts of data New England Code Camp IV: “Developer’s Gone Wild”

  21. EVOLUTION:SQL Server 2005 Integration • Flexibility • SQLDependency and SQLNotification • Usability • MARS (Multiple Active Resultsets) • Reuse Connections • Performance • Asynchronous SQL Command • Bulk Copy New England Code Camp IV: “Developer’s Gone Wild”

  22. SQL Server Query Notification • Tied into SQL Server Service Broker (2005) • System.Data.SQLNotification • System.Data.SqlDependency • ASP.NET has it’s own implementation • System.Web.Caching.SqlCacheDependency • Backwards compatible to SQL7 & SQL 2000 through polling • Use wisely New England Code Camp IV: “Developer’s Gone Wild”

  23. Query Notifications cont. • Many rules and caveats • Select explicit columns, never select * • Use two part table names: dbo.authors, person.contact • Turn on Service Broker in your database ALTER DATABASE AdventureWorks SET ENABLE_BROKER ; • Ensure that database compatibility level is 9.0 • Attached older databases such as pubs may be 8.0 or less • SQL Server Permission requirements for Non-Admins • Permissions to call SqlDependency.Start • Permissions to receive notifications • Until these are in documention, see: • Blogs.msdn.com/dataaccess (see 9/27/05 post) New England Code Camp IV: “Developer’s Gone Wild”

  24. DEMOQuery Notification and Caching New England Code Camp IV: “Developer’s Gone Wild”

  25. MARS • Enable reuse of database connections • Off by default (beta2 still has it on) • MultipleActiveResultSets=false in connection string to turn off • Benefits • Multiple SqlDataReaders on one connection • Allows multiple updates in one transaction • note: System.Transactions.TransactionScope • Interleave read with update on one connection • Caveats • Possible performance costs compared to connection pooling New England Code Camp IV: “Developer’s Gone Wild”

  26. Asynchronous Command Object • Great for middle tier • Keep working while waiting for a command to complete processing New England Code Camp IV: “Developer’s Gone Wild”

  27. DEMOAsynchronous Command New England Code Camp IV: “Developer’s Gone Wild”

  28. Bulk Copy • Load data into SQL Server via ADO.NET • Nearly as fast as DTS! • Sample: Loading 104,225 records into SQL2005 New England Code Camp IV: “Developer’s Gone Wild”

  29. DEMOBulk Copy New England Code Camp IV: “Developer’s Gone Wild”

  30. Conclusion • ADO.NET Team says: • We like ADO.NET and want to keep it! • API stays consistent in it’s design • ADO.NET 2 improves on • Usability • Flexibility • Performance • ADO.NET 2.0 was designed in tandem with SQL Server 2005 for powerful results New England Code Camp IV: “Developer’s Gone Wild”

  31. Resources: Articles • What’s New in ADO.NET 2.0 Julia Lerman, MSDN Magazine April 2005 • Caching Improvements in ASP.NET WhidbeyG. Andrew Duthie, MSDN Online February 2004 • Improved Caching in ASP.NET 2.0Stephen Walther, MSDN Online, June 2004 • Asynchronous Command Execution in ADO.NET 2.0Pablo Castro, MSDN Online, July 2004 • New Features for Web Service Developers in Beta 1 of the .NET Framework 2.0Elliot Rapp,Yasser Shohoud and Matt Travis, MSDN Online, July 2004 • Transaction Processing in ADO.NET 2.0William Ryan, Sept 14, 2004, 15 Seconds website (www.15seconds.com) • Query Notifications in ADO.NET 2.0Bob Beauchemin, MSDN Online, September 2004 New England Code Camp IV: “Developer’s Gone Wild”

  32. Resources cont. • Using CLR Integration in SQL Server 2005Multiple Authors from Microsoft Corporation, MSDN Online, Nov 2004 • ADO.NET 2.0 Multiple Active Result Sets per connection in Sql Server 2005 (MARS) FAQAngel Saenz-Badillos, Weblog Post, Sept 7, 04 (blogs.msdn.com/angelsb) BOOKS______________________________ • ADO.NET and System.Xml v.2.0 – The Beta VersionAlex Homer, Dave Sussman, Mark Fussell, Addison-Wesley, March 2005 • Pro ADO.NET 2.0 Sahil Malik, APress, 2005 New England Code Camp IV: “Developer’s Gone Wild”

  33. Resources: Blogs • Don’t Be Iffy, ADO.NET Category • www.thedatafarm.com/blog • Microsoft Data Access Blog • blogs.msdn.com/dataaccess • Sushil Chordia blog • Blogs.msdn.com/sushilc • Angel Saenz-Badillos • blogs.msdn.com/angelsb New England Code Camp IV: “Developer’s Gone Wild”

  34. Contact Info Julie Lerman jlerman@thedatafarm.com www.thedatafarm.com www.thedatafarm.com/blog New England Code Camp IV: “Developer’s Gone Wild”

More Related