Whats New in ADO'NET 2'0

1
Whats New in ADO.NET 2.0

• Julie Lerman
• The Data Farm
• www.thedatafarm.com
• jlerman_at_thedatafarm.com

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

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

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

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

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

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

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

9
DEMODataTableReaders, DataSetLoad
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

11
DEMO DataView.ToTable(DistinctRows)
12
RowState

• DataRow.RowState is no longer read only
• Unchanged rows can be modified
• SetAdded
• SetModified
• DataRowState enums
• Added, Deleted, Detached, Unchanged, Modified

13
DEMO RowState.SetAdded, SetModified
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

15
Using Provider Independent APIs
example APINameSpaceSystem.Data.SQLClient Publ
ic Function GetDS(byVal APINameSpace as String)as
DataSet Dim provider as DbProviderFactory
dbProviderFactories.GetFactory(APINameSpace)
Dim connection As DbConnection-provider.CreateConn
ection Dim adapter as DbDataAdapter
provider.CreateDataAdapter code to connect
command, connection and dataadapter
adapter.fill(ds) Return ds End Function
16
DEMO Independent Data Providers
17
EVOLUTION Performance

• Batch Updates
• True Binary Serialization for Remoting

18
Batch Updating

• Works with DataAdapter.UpdateCommand
• UpdateBatch property, default1
• 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

19
Batch UpdateBigger 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 LocalSQL2005 Remote Hard WiredSQL2000
WebSQL2000
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

21
EVOLUTIONSQL Server 2005 Integration

• Flexibility
• SQLDependency and SQLNotification
• Usability
• MARS (Multiple Active Resultsets)
• Reuse Connections
• Performance
• Asynchronous SQL Command
• Bulk Copy

22
SQL Server Query Notification

• Tied into SQL Server Service Broker (2005)
• System.Data.SQLNotification
• System.Data.SqlDependency
• ASP.NET has its own implementation
• System.Web.Caching.SqlCacheDependency
• Backwards compatible to SQL7 SQL 2000 through
  polling
• Use wisely

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)

24
DEMOQuery Notification and Caching
25
MARS

• Enable reuse of database connections
• Off by default (beta2 still has it on)
• MultipleActiveResultSetsfalse 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

26
Asynchronous Command Object

• Great for middle tier
• Keep working while waiting for a command to
  complete processing

27
DEMO Asynchronous Command
28
Bulk Copy

• Load data into SQL Server via ADO.NET
• Nearly as fast as DTS!
• Sample Loading 104,225 records into SQL2005

29
DEMO Bulk Copy
30
Conclusion

• ADO.NET Team says
• We like ADO.NET and want to keep it!
• API stays consistent in its design
• ADO.NET 2 improves on
• Usability
• Flexibility
• Performance
• ADO.NET 2.0 was designed in tandem with SQL
  Server 2005 for powerful results

31
Resources Articles

• Whats New in ADO.NET 2.0
• Julia Lerman, MSDN Magazine April 2005
• Caching Improvements in ASP.NET Whidbey G.
  Andrew Duthie, MSDN Online February 2004
• Improved Caching in ASP.NET 2.0 Stephen
  Walther, MSDN Online, June 2004
• Asynchronous Command Execution in ADO.NET 2.0
  Pablo Castro, MSDN Online, July 2004
• New Features for Web Service Developers in Beta 1
  of the .NET Framework 2.0 Elliot Rapp,Yasser
  Shohoud and Matt Travis, MSDN Online, July 2004
• Transaction Processing in ADO.NET 2.0 William
  Ryan, Sept 14, 2004, 15 Seconds website
  (www.15seconds.com)
• Query Notifications in ADO.NET 2.0 Bob
  Beauchemin, MSDN Online, September 2004

32
Resources cont.

• Using CLR Integration in SQL Server 2005
  Multiple Authors from Microsoft Corporation, MSDN
  Online, Nov 2004
• ADO.NET 2.0 Multiple Active Result Sets per
  connection in Sql Server 2005 (MARS) FAQ
  Angel Saenz-Badillos, Weblog Post, Sept 7, 04
  (blogs.msdn.com/angelsb)
• BOOKS______________________________
• ADO.NET and System.Xml v.2.0 The Beta Version
  Alex Homer, Dave Sussman, Mark Fussell,
  Addison-Wesley, March 2005
• Pro ADO.NET 2.0 Sahil Malik, APress, 2005

33
Resources Blogs

• Dont 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

34
Contact Info
Julie Lerman jlerman_at_thedatafarm.com www.thedatafa
rm.com www.thedatafarm.com/blog