What s new in ado net 2 0
Download
1 / 34

What s New in ADO 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 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 l.jpg

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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
DEMODataTableReaders, DataSetLoad

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


Dataview totable l.jpg
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 l.jpg
DEMODataView.ToTable(DistinctRows)

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


Rowstate l.jpg
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 l.jpg
DEMORowState.SetAdded, SetModified

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


Provider independent api l.jpg
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 l.jpg
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 l.jpg
DEMOIndependent Data Providers

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


Evolution performance l.jpg
EVOLUTION:Performance

  • Batch Updates

  • True Binary Serialization for Remoting

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


Batch updating l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
DEMOQuery Notification and Caching

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


Slide25 l.jpg
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 l.jpg
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 l.jpg
DEMOAsynchronous Command

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


Bulk copy l.jpg
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 l.jpg
DEMOBulk Copy

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


Conclusion l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Contact Info

Julie Lerman

[email protected]

www.thedatafarm.com

www.thedatafarm.com/blog

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


ad