1 / 24

Advanced Data Access Patterns

Advanced Data Access Patterns. with ADO.NET 2.0. Julie Lerman The Data Farm. About Me. .NET Consultant and Mentor 20+ years developing Microsoft .NET MVP ASPInsider INETA Speaker Various publications & conferences Blogs: thedatafarm.com/blog, blog.ziffdavis.com/devlife

angelica-saunders
Download Presentation

Advanced Data Access Patterns

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. Advanced Data Access Patterns with ADO.NET 2.0 Julie Lerman The Data Farm

  2. About Me • .NET Consultant and Mentor • 20+ years developing • Microsoft .NET MVP • ASPInsider • INETA Speaker • Various publications & conferences • Blogs: thedatafarm.com/blog, blog.ziffdavis.com/devlife • Founder and leader of Vermont .NET • INETA Speaker Committee, VTSDA Board

  3. Agenda • ADO.NET 2.0 DataSet performance • Pushing huge data back to SQL Server • Caching huge amounts of data • Querying huge amounts of data in memory

  4. New in ADO.NET 2.0 • New features used in this session • Batch Updating with DataAdapter • SqlBulkCopy Class • SqlDependency Class

  5. ADO.NET 2.0 Performance • DataSet Scalability across the board • Can handle lots of rows • Indexing Engine completely re-written • Incremental indexing updates • much more • Low overhead for using Data Views • Serialization: Faster, more compact • Supports Binary & Schema-less serialization

  6. Demonstration Indexing Engine Performance

  7. Moving BIG Data • Getting data is easy and fast • Uploading is a different story • 1.1 Update Options • DataAdapter.Update – one row at a time • Controlled stored procedure update – one row at a time

  8. Updating in 2.0 • DataAdapter Batch Update • Pushes groups of rows up to SQL Server • SQL Server still updates one at a time • Find sweet spot for UpdateBatchSize value • SqlDataAdapter & OracleDataAdapter • Bulk Copy • Streamed Insert using SQL Server Bulk Copy *slide slightly modified from original printed version

  9. Demonstration Batch Update & Bulk Copy Class

  10. Fast Updates w/BCP • Use BCP to stream Inserts & Updates and Deletes to temptables • Create & run a DML TSQL command to process inserts, updates and deletes from the temptables into the real tables • Wrap it all in a transaction • End to End streaming with DataReader DML: Database Manipulation Language

  11. Demonstration Using BCP & DML to process huge amounts of data

  12. DataSet Caching • DataSet improvements enable storing lots of data into cache • SqlDependency enables automated notification of changes to .NET • Combine SqlDependency and Caching for serious resource reduction

  13. Demonstration SqlDependency & DataSet CachePart 1

  14. Break up Big cache • SqlDependency is all or nothing • Create separate dependencies for logical sections of your cache

  15. Demonstration SqlDependency & DataSet CachePart 2: Achieve granularity

  16. Query BIG DataSet • Query a huge DataSet in cache • In-Memory Querying is coming! • LiNQ • VB9/C#3 • What about today? • ADO.NET 2.0 has everything you need to build a simple Query Processor • Great example of QP from ADO.NET Team Technical Lead Pablo Castro (“for demo only”)

  17. Sample Query Processor • Takes advantage of new ADO.NET 2.0 tools • Ability to query huge datasets • Uses Caching • Super Fast indexing • DataTable.Select • DataView • Very simplistic compared to T-SQL • *NOT* for production use

  18. Demonstration A look at a sample Query Processor written with ADO.NET 2.0

  19. Summary • ADO.NET 2.0’s performance and functional improvements put new power in our hands • You can work with huge amounts of data efficiently • Functionality like BatchUpdate gives you easy access to some of this power • With a little more code, you can do wonders!

  20. Resources • PDC05 Session DAT408, Pablo Castro, ADO.NET Team Technical Lead • ADO.NET Team Blog: blogs.msdn.com/dataaccess • MSDN Data Access Dev Center • msdn.microsoft.com/data • What’s New in ADO.NET 2.0, Julia Lerman, MSDN Magazine April 2005

  21. ADO.NET 2.0 Bookshelf • ADO.NET and System.Xml v 2.0 – The Beta Version:Alex Homer, Dave Sussman & Mark Fussell, Addison-Wesley • PRO ADO.NET 2.0, Sahil Malik, APress • Professional ADO.NET 2: Programming with SQL Server 2005, Oracle, and MySQL, Wallace B. McClure, Gregory A. Beamer, IV John J. Croft, J. Ambrose Little, Bill Ryan, Phil Winstanley, David Yack, Jeremy Zongker, WROX Press • Programming Microsoft ADO.NET 2.0 Applications: Advanced Topics, Glenn Johnson, Microsoft Press • Microsoft ADO.NET 2.0 Step by Step, Rebecca Riordan, Microsoft Press

  22. Contact Info Julie Lerman jlerman@thedatafarm.com www.thedatafarm.com Blogs www.thedatafarm.com/blog blogs.ziffdavis.com/devlife

  23. Please fill out the survey forms!They are the key to amazing prizes that you can get at the end of each day Thank you!

More Related