1 / 38

ADO.NET 2.0: Evolution or Revolution

ADO.NET 2.0: Evolution or Revolution. Shawn Wildermuth MVP, MCSD.NET, Author and Speaker. Who I Am. Shawn Wildermuth swildermuth@adoguy.com Independent Consultant INETA Speaker Book Author “Pragmatic ADO.NET” “MCTS Self-Paced Training Kit (Exam 70-536)”

fletcher-carroll
Download Presentation

ADO.NET 2.0: Evolution or Revolution

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. ADO.NET 2.0: Evolution or Revolution Shawn Wildermuth MVP, MCSD.NET, Author and Speaker

  2. Who I Am • Shawn Wildermuth • swildermuth@adoguy.com • Independent Consultant • INETA Speaker • Book Author • “Pragmatic ADO.NET” • “MCTS Self-Paced Training Kit (Exam 70-536)” • “Prescriptive Data Architectures” - Upcoming • This Presentation can be found at: • http://adoguy.com/presentations

  3. Agenda • Revolution or Evolution? • ADO.NET 2.0 Changes • Provider Model • DataAdapter Enhancements • DataTable Enhancements • DataSet Enhancements • Typed DataSet Enhancements • TableAdapters • ADO.NET Related Topics • System.Transactions • SQL Server 2005 • ASP.NET 2.0 • ADO.Next • Parting Shots

  4. Evolution or Revolution? • Did they change everything? • In ADO->ADO.NET, the world changed • The Data Team Took a Hard Look • Kept What Worked • Fixed What Didn’t

  5. Evolution or Revolution? (2) • Evolution of the Platform • Better Provider Support • Better DataAdapters • First Class DataTables • Performance Improvements for DataSets • New Typed DataSets • New Table Adapters • Better Transaction Support • SQL Server 2005 Integration

  6. Managed Provider Enhancements • Provider Model • ADO.NET v1.1 was based on interfaces • Not enough for provider-agnostic code • Hard to evolve • ADO.NET v2.0 introduces a common API • Abstract base classes instead of interfaces • Better versioning story • Provider factories enabled at the API level • SQL syntax is still back-end specific

  7. Managed Provider Enhancements (2) Db* abstract base classes (e.g. DbConnection) SqlClient OleDb Odbc Oracle Client 3rd Party 3rd Party Db*Base implementation classes • Complete Reworking of Hierarchy IDb* interfaces (e.g. IDbConnection) Provider- independent apps Code to this layer Provider- specific apps Code to this layer

  8. Managed Provider Enhancements (3) • Provider Factories • Can enumerate Managed Providers • Can create a DbProviderFactory from Name • Factory to create common objects DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection conn = factory.CreateConnection(); DbCommand cmd = factory.CreateCommand(); DbDataReader rdr = cmd.ExecuteReader(); DbDataAdapter da = factory.CreateDataAdapter(); DataSet theSet = new DataSet(); da.Fill(theSet);

  9. Managed Provider Enhancements (4) • Asynchronous Execution • Added BeginXXX, EndXXX to most Executions • BeginExecuteNonQuery, EndExecuteNonQuery • BeginExecuteReader, EndExecuteReader • Only supported on SqlClient so far • Must add “Asynchronous Processing=true” conn = new SqlConnection( "Server=.;database=...;Asynchronous Processing=true"); cmd = conn.CreateCommand(); // ... conn.Open(); cmd.BeginExecuteReader(new AsyncCallback(SomeMethod), this);

  10. DataAdapter Enhancements • Batch Updates • Updates are normally processed one by one • Batching reduces network round-trips • Works inside transactions • Works with SQL Server 7.0, 2000, 2005 • Also available for OracleClient classes DataSet mySet = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); da.UpdateBatchSize = 100; da.Update(mySet); DataSet mySet = new DataSet(); OracleDataAdapter da = new OracleDataAdapter(); da.UpdateBatchSize = 100; da.Update(mySet);

  11. CommandBuilder Enhancements • Better Concurrency Support • DbCommandBuilder.ConflictOption • CompareAllSearchableValues • CompareRowVersion • OverwriteChanges • Who cares? • Tool builders • Typed DataSets • ASP.NET 2.0 should have but doesn’t • (Still) Don’t use CommandBuilders at runtime

  12. DataTable Enhancements • First Class Support • DataSet operations now available on DataTable: • ReadXml/WriteXml • ReadXmlSchema/WriteXmlSchema • Clear • Clone • Copy • Merge • GetChanges DataTable tbl = new DataTable(); // Fill the table DataTable newTbl = tbl.Clone(); tbl.Merge(newTbl); DataTable changes = tbl.GetChanges();

  13. DataTable Enhancements (2) • First Class Support (continued) • DataTable is now auto-serializable: • Web Service or Remoting Friendly • MarshalByValue still an issue!

  14. DataTable Enhancements (3) • DataSet not Required • DataAdapters • Fill(DataTable) • Update(DataTable) • DataReader • DataTable.Load(DataReader) • Stream from a DataTable • DataTable.GetTableReader()

  15. DataView Enhancements • DataView.ToTable • Creates a new DataTable from a DataView • Supports Distinct and Column Filtering // Returns the distict rows and only // CompanyName and ContactName columns DataTable filtered = view.ToTable(true, "CompanyName", "ContactName");

  16. DataSet Enhancements • Performance and scalability improvements • Internal Indexing of Rows • Inserts and deletes much faster • Updates almost constant • Selects are faster too • Binary Serialization of Contents • V 1.x DataSet always serialized to XML • Binary serialization an option in V 2.0

  17. DataSet Enhancements (2) • More Loading Options • DataAdapter.Fill • DataAdapter.FillLoadOption • LoadOptions.OverwriteChanges • LoadOptions.PreserveChanges • LoadOptions.Upsert DataSet theSet = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers", "..."); da.FillLoadOption = LoadOption.OverwriteChanges; da.Fill(theSet, "Customers");

  18. DataSet Enhancements (3) • More Loading Options • DataAdapter.Fill (continued) • AcceptChangesDuringUpdate • Mirrors AcceptChangesDuringFill from 1.x • DataSet.Load • Load from DataReader directly • FillErrorEventHandler to handle loading errors • Also supports LoadOptions

  19. DataSet Enhancements (4) • Stream data From DataSet • DataSet.GetDataReader method • returns a DataTableReader • RowState values are now updateable • DataRow.SetAdded and DataRow.SetModified

  20. DataSet Enhancements (5) • Common Issue • “Invalid Row Index #5” • Causes when row(s) change and DataViews are in use • Use BeginEdit/EndEdit or remove dead DataViews • Introduced with the index optimization • There are official workarounds but no fix yet…

  21. Typed DataSet Enhancements • Completely Changed Environment • Support for Discovery of Relationships • Works across databases • E.g. Oracle, SQL Server, Access, etc. • TableAdapters • Generated Code to Load/Save Tables • By default uses same robust update code as 1.x • No support for batch queries (SELECTs)

  22. Typed DataSet Enhancements (2) • Completely Changed Environment • Partial Classes • Allows for adding code • Can’t override existing implementation • Good place for Load/Save Code

  23. Typed DataSet Enhancements (3) • Concurrency Changes • Supports Changes in CommandBuilder, sort of… • Timestamp concurrency supported • 1.x Style Brute Force works too • Can specify no concurrency

  24. Typed DataSet Enhancements (4) • What’s Wrong? • Microsoft’s RAD solution • Designer is fragile • Tends to make lots of connection strings • Changing Queries can break other queries • No support for multiple tables in one round-trip • Still no support for inheritance • Events are still the main expansion model • No re-use/upgrade model

  25. BulkCopy • INSERTs aren’t fast enough • SqlBulkCopy • High-performance bulk-insert API • DataReader • DataTable

  26. System.Transactions • Great integration • Transparent transaction enlistment • SQL Server 2005 • Transaction promotion • Nice programming model, no performance hit • Also integrated into SQLCLR • SQL Server 2000 • Auto-enlistment requires DTS • Can disable autoenlistment in Connection String • Not as lightweight as 2005

  27. System.Transactions (2) • Example: using (TransactionScope scope = new TransactionScope()) { using (SqlConnection conn = new SqlConnection("...")) using (SqlCommand cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = "..."; cmd.ExecuteNonQuery(); cmd.CommandText = "..."; cmd.ExecuteNonQuery(); } }

  28. System.Transactions (3) • Continue to use SqlClient.Transaction • SQL Server only transactions • When you know they will never change

  29. SQL Server 2005 • New Data Types • Fully integrated in client and server • XML data-type • XML as a first-class language in the server • Query XML data using XQuery • User-defined types • Extend the scalar server type-system using your own CLR types • “MAX” types • Use varchar/varbinary even for large data

  30. SQL Server 2005 (2) • Multiple Active Result Sets • Simple Enabling • Disabled by default • "MultipleActiveResultSets=True“ in ConnString • Multiple readers on a single connection • Can Open a DataReader while another is open

  31. SQL Server 2005 (2) • Query Notification • SqlDependency • Tracks dependencies on query results • Works in conjunction with the server • Built on top of SQL Server 2005 Query Notifications • Straightforward use • Bind the dependency to the command • Execute the command • Event will be fired if the data changes • Requires SQL Server 2005 and .NET 2.0

  32. SQL Server 2005 (3) • In-Process • Special In-Process Managed Provider • Works with current connection • Allows execution of ResultSets • Similar but *not* the same as SqlClient • Too big to discuss in this talk

  33. ASP.NET 2.0 • DataSources • Bi-directional Databinding • SqlDataSource • Supports RAD methology • Own version of Concurrency Methodology • Relationship to GridView/FormView’s tenuous • Not a ‘best practice’ because the DAL is in the UI • Supports caching • ObjectDataSource • Does not work objects unless specifically created for it • No support for Typed DataSets or ORMs

  34. ASP.NET 2.0 (2) • Data Controls • GridView • Huge improvement on DataGrid • Supports sorting, footers and headers • No good support for a ‘new’ row • FormView • Great control that contains templates for CRUD’ing • EditTemplate, InsertTemplate, ReadOnlyTemplate, etc. • Controls require DataSources • Problematic unless you use the right DAL • DataKeys must be right to work with DataSources

  35. ASP.NET 2.0 • Conclusion • For data access, changes are of limited value • DataSources cause more confusion than clarity • Binding your own objects is harder than it should be • Own type of concurrency is ridiculous • NULL’s in SqlDataSource is just broken • Using 1.1 methodology is hidden in special designer • No upgrade path to evolve projects • Suggestions • Use 1.1 methodology and do it all in code • Use the ASP.NET 2.0 Web Application • http://webproject.scottgu.com/default.aspx

  36. ADO.Next? • Lots of Chatter • LINQ, XLINQ, DLINQ • Entity Data Model • Object Spaces 2.0? • WinFS…DOA

  37. Parting Shots • Doug Tenure…please leave the room! • Data Teams Fractured • ADO.NET -> Data Team • Typed DataSets -> Visual Studio Team • ASP.NET -> ASP.NET Team • Can’t get Data Access that is cohesive • As customers we have to insist they do better…

  38. Questions?

More Related