what s new in ado net 2 0
Download
Skip this Video
Download Presentation
What’s New in ADO.NET 2.0

Loading in 2 Seconds...

play fullscreen
1 / 34

What’s New in ADO.NET 2.0 - PowerPoint PPT Presentation


  • 145 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'What’s New in ADO.NET 2.0' - kendall


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
what s new in ado net 2 0

What’s New in ADO.NET 2.0

Julie Lerman

The Data Farm

www.thedatafarm.com

[email protected]

New England Code Camp IV: “Developer’s Gone Wild”

julie lerman
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”

overview
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”

agenda
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”

evolution usability flexibility
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”

datatable grows up
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”

stream data into datatable dataset
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”

datatablereader
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”

demo datatablereaders datasetload
DEMODataTableReaders, DataSetLoad

New England Code Camp IV: “Developer’s Gone Wild”

dataview totable
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”

demo dataview totable distinctrows
DEMODataView.ToTable(DistinctRows)

New England Code Camp IV: “Developer’s Gone Wild”

rowstate
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”

demo rowstate setadded setmodified
DEMORowState.SetAdded, SetModified

New England Code Camp IV: “Developer’s Gone Wild”

provider independent api
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”

using provider independent apis
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”

demo independent data providers
DEMOIndependent Data Providers

New England Code Camp IV: “Developer’s Gone Wild”

evolution performance
EVOLUTION:Performance
  • Batch Updates
  • True Binary Serialization for Remoting

New England Code Camp IV: “Developer’s Gone Wild”

batch updating
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”

batch update bigger is not always better
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”

binary serializaton in remoting
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”

evolution sql server 2005 integration
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”

sql server query notification
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”

query notifications cont
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”

demo query notification and caching
DEMOQuery Notification and Caching

New England Code Camp IV: “Developer’s Gone Wild”

slide25
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”

asynchronous command object
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”

demo asynchronous command
DEMOAsynchronous Command

New England Code Camp IV: “Developer’s Gone Wild”

bulk copy
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”

demo bulk copy
DEMOBulk Copy

New England Code Camp IV: “Developer’s Gone Wild”

conclusion
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”

resources articles
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”

resources cont
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”

resources blogs
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”

contact info
Contact Info

Julie Lerman

[email protected]

www.thedatafarm.com

www.thedatafarm.com/blog

New England Code Camp IV: “Developer’s Gone Wild”

ad