1 / 57

What’s New in System.Data

What’s New in System.Data. Agenda. The Common Programming Model The Schema Discovery API Enhancements to the DataSet class SQL Server-specific enhancements. ADO.NET 2.0 goals. ADO.NET 2.0 evolves without revolutions People like ADO.NET the way it is :) Just upgrade; your code will work

jayme
Download Presentation

What’s New in System.Data

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. What’s New in System.Data

  2. Agenda • The Common Programming Model • The Schema Discovery API • Enhancements to the DataSet class • SQL Server-specific enhancements

  3. ADO.NET 2.0 goals • ADO.NET 2.0 evolves without revolutions • People like ADO.NET the way it is :) • Just upgrade; your code will work • Changes for performance & scalability • DataSet and DataTable enhanced • Provider-independent API • Integration with SQL Server 2005

  4. Common programming model

  5. Factory Class Hierarchy IDb* interfaces (e.g. IDbConnection) Provider-Independent apps code to this layer Db* abstract base classes (e.g. DbConnection) Db*Base implementation classes Provider-specific apps code to this layer Sql OleDb ODBC Oracle 3rd Party 1 3rd Party 2

  6. Common Provider Classes • In System.Data.Common namespace: • for example:

  7. Using a Provider Factory • Import the Required Namespace: using System.Data.Common • Create the Factory Instance: static DbProviderFactory factory = DbProviderFactories.GetFactory("provider-name") • Create Required Object Instances: DbConnection con = factory.CreateConnection() DbCommand cmd = con.CreateCommand()

  8. Provider Enumeration • Each provider has an invariant name • for example: "System.Data.SqlClient", "System.Data.OracleClient" • Get a List of Installed Provider Factories DataTable dt = DbProviderFactories.GetFactoryClasses() DbProviderFactory factory = DbProviderFactories.GetFactory(dt.Rows[x]) • ... or ... DbProviderFactory factory = DbProviderFactories.GetFactory( dt.Select("InvariantName='System.Data.SqlClient'") [0]["InvariantName"].ToString());

  9. When Should I Use It? • An OPTION only • Designed for tool and IDE suppliers • Connection string details depends on database type • Parameter names depend on provider type • Performance • No layer of overhead • Create factory once and reuse • Otherwise equal Performance • the base class is returned from factory object

  10. Schema Discovery API

  11. Schema Discovery API • Get a List of Schemas: DataTable dt = conn.GetSchema("MetaDataCollections") • Get a Specific Schema: DataTable dt = conn.GetSchema("collection-name") • Get Selected Details from a Schema: DataTable dt = conn.GetSchema("collection-name", restriction-array)

  12. Schema Selection GetSchema("collection-name",restrictions[x])

  13. Some Metadata Collections = restriction that can be used for selecting items

  14. DataSet enhancements

  15. DataSet Serialization in .NET 1.1 • DataSets serialize through DiffGram scripts DataSet XML ISerializable SoapFormatter : BinaryFormatter : CompactFormatter Formatter http://www.freewebs.com/compactFormatter/downloads.html

  16. DataSet Serialization in .NET 2.0 • Binary Serialization of Contents • V 1.x DataSet always serialized to XML • good for data interchange, bad for performance • Binary serialization an option in V 2.0 • fast and compact, especially as row counts increase • just set: DataSet.RemotingFormat = SerializationFormat.Binary • Internal Indexing of Rows • Inserts and deletes are log-n • Updates almost constant

  17. Binary vs XML Serialization Up to 80 x faster for large DataSets!

  18. Loading a DataSet • DataAdapter enhancements • DataAdapter.FillLoadOption and AcceptChangesDuringUpdate properties • DataSet.Load method • Load(DataReader [, load-option] [, tables-array]) • optionally can use FillErrorEventHandler event to trap errors

  19. The LoadOption Enumeration • Used by Load and Merge methods • what happens when loading a row with same key • ds.Merge(loadoption, preservechanges) • use preservechanges=True for overwriting original values but not current values • the LoadOption enumeration helps to support other scenarios • e.g. merging data from a different DB or from file

  20. The LoadOption enumeration • PreserveCurrentValues (default) • overwrites original values, keeps current values • good to resync with original DB after a conflict • UpdateCurrentValues • overwrites current values, keeps original ones • good to read data from DB without losing original data loaded in the dataset • OverwriteRow • overwrites both values, sets the row as unchanged

  21. The LoadOption Enumeration

  22. Stand-alone DataTable Instances • Common DataSet operations now also available on DataTable: • ReadXml, ReadXmlSchema, WriteXml, WriteXmlSchema, Clear, Clone, Copy, Merge • DataTable is now auto-serializable: • return a DataTable instance from a Web Service or via Remoting

  23. Loading and Using a DataTable • DataAdapter.Fill(DataTable) • DataAdapter.Fill(DataTable[ ]) • and more, including subsets of rows • DataAdapter.Update(DataTable) • DataTable.Load(DataReader [, load-option] [, FillErrorEventHandler]) • new methods: BeginLoadData, Load, EndLoadData • DataTable.GetDataReader method • stream data from a DataTable

  24. DataSet Load Performance Using LoadDataRow API 3 times as fast for 100,000 rows - and even better with more!

  25. More New Features • RowState values are now updateable • New methods: DataRow.SetAdded and DataRow.SetModified • use Delete and AcceptChanges/RejectChanges for other states • DataTable.GetDataReader method • returns a DataTableReader • you can specify which tables to include • DataView.ToTable method • filtering and sorting, can include only a subset of columns • dt = dv.ToTable(tableName, boolDistinct, columnNames())

  26. Batched Updates • DataSet updates are normally processed one by one • Batching reduces network round-trips • DataAdapter.UpdateBatchSize = batch_size • Works inside transactions • Works with SQL Server 7.0, 2000, 2005 • Also available for OracleClient classes • Minor limitations • max 2000 parameters • maxrows = 2000 / paramsper row • optimal numrows = 100-500 rows

  27. XML Data Types in a DataSet • The DataTable accepts columns of data-type 'xml' • type is System.Data.SqlTypes.SqlXml • in Beta 2 defaults to a String unless DataAdapter.UseProviderSpecificType = true • exposed as an XPathDocument instance • can also be accessed via an XmlReader • makes it easier to work with XML as a document rather than a rowset of values • maintains fidelity of the XML content

  28. User-Defined Types in a DataSet • Populate DataSet with SQL or Stored Procedure • Update with SQL Statement or Stored Procedure • create the Command and Parameters param = da.UpdateCommand.Parameters.Add ("@name",SqlDbType.Udt) param.UdtTypeName = "type-name" param.SourceColumn = "column-name" • or can use a SqlCommandBuilder • use timestamp column for conflict resolution • otherwise UDT conflicts will not be detected

  29. SqlClient enhancements

  30. Asynchronous Commands • Ideal for multiple database queries • Usual Beginxxx and Endxxx model • Supports Polling, Wait and Callback models • Catching asynchronous execution errors • Should not generally be used with MARS • use a separate connection for each Command • Add "async=true" to connection string • Doesn’t work on Win9x and ME clients

  31. Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Synchronous Latency Application Rowset 1 Database1 Latency 3 secs Connection Rowset 2 Database2 Latency 8 secs Connection Rowset 3 Database3 Latency 5 secs Connection Total time until all three displays filled: ~ 16 secs ~ 11 secs ~ 3 secs

  32. Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Data 1 Connection1 Connection2 Connection3 Asynchronous Execution Application Database1 Latency 3 secs Rowset 1 Database2 Latency 8 secs Rowset 2 Rowset 3 Database3 Latency 5 secs Total time until all three displays filled: ~ 8 secs

  33. Asynchronous Polling Model • Start asynchronous command execution: IAsyncResult result = MyCommand.BeginExecuteReader() • Wait until execution is complete: while (! result.IsCompleted) { // execute other code here } • Fetch results: SqlDataReader reader = MyCommand.EndExecuteReader(result )

  34. Asynchronous Wait (All) Model • Start one or more asynchronous commands: IAsyncResult resultx = MyCommand.BeginExecuteReader() • Wait for all commands to complete: WaitHandle.WaitAll(new WaitHandle[] {result1.AsyncWaitHandle, result2.AsyncWaitHandle, result3.AsyncWaitHandle}, timeout-ms, true) • Fetch results: SqlDataReader reader = MyCommand.EndExecuteReader(resultx) • Ideal for ASP.NET Web applications

  35. Asynchronous Wait (Any) Model • Start one or more asynchronous commands as an array of IAsyncResult instances: IAsyncResult resultx = MyCommand.BeginExecuteReader() • Wait for each command to complete: for(int i=0; i < result_array.Length, i++) { index = WaitHandle.WaitAny(result_array, timeout, true); switch(index) { case 0: SqlDataReader reader = MyCommand.EndExecuteReader(resultx); ...etc...

  36. Asynchronous Callback Model • Start execution, specifying callback and passing command as the AsyncState: MyCommand.BeginExecuteReader(new AsyncCallback(MyCallback), cmd) • Provide a callback handler: void MyCallback(IAsyncResult result) { SqlCommand cmd = (SqlCommand) result.AsyncState; SqlDataReader reader = cmd.EndExecuteReader(result); }

  37. Catching Timeouts and Errors • For the WaitOne and WaitAll methods: • use try/catch around each "End" method • For the WaitAny method: • return value is equal to timeout value • When using the Callback model: • use try/catch around "End" method

  38. More SqlClient enhancements • Connection-level statistics • StatisticsEnabled property • RetrieveStatistics method (return IDictionary) • ResetStatistics method • AttachDbFileName in connection string • must be absolute path in ADO.NET 1.x • can be a relative path in ADO.NET 2.0 • great to support SQL Server 2005 Express (MSDE)

  39. Enhancements for SQL Server 2005

  40. Multiple Active ResultSets (MARS) • Opening a second “cursorless” resultset on the same connection • ADO opens a new connection behind the scenes • ADO.NET 1.x throws an exception • Fully supported in ADO.NET 2.0 & SQL Server 2005 • required changes in network libraries • commands on same connection share the same transaction

  41. SqlDependency class • ADO.NET 2.0 SqlDependency class tracks dependencies on query results • Built on SQL Server 2005 Query Notifications • or Notifications Service for SQL 2000 • Query Notifications add-in available • Wraps the low-level SqlNotificationRequest type • Doesn’t require an open connection • Don’t use with many clients • all would refresh data at the same time SqlCommand cmd; cmd = new SqlCommand("SELECT * FROM Authors", conn); SqlDependency dep = new SqlDependency(cmd); dep.OnChanged += new OnChangedEventHandler(OnDepChanged); cmd.ExecuteReader();

  42. ASP.NET Cache Dependency • SqlCacheDependency is ASP.NET-specific wrapper that work with SQL Server 7.0 and 2000 too • invalidates data in the Cache based on table (not query!) changes • Enable notifications for required tables • Create triggers to handle incoming statements • Command-line tool (aspnet_regsql) or methods of SqlCacheDependencyAdmin // SELECT … WHERE country=‘USA’  trigger also for ‘UK’ SqlCacheDependency dep; dep = new SqlCacheDependency(database, table); Cache.Insert("MyData", dataSet, dep);

  43. Notifications & SqlDependency • Cache the data and then be notified when ANYTHING happens that would give a different result if the query was re-executed • Uses SQL Server 2005 Query Notifications • bind SqlDependency to Command and execute it • fully integrated with ASP.NET as well • Notifications Service for SQL 2000 • Query Notifications add-in available

  44. Support for new data types • ADO.NET 2.0 supports SQL Server 2005 XML native data type, user-defined types • SqlXml and SqlUdt in System.Data.SqlTypes • also supported by SqlParameter • DataReader returns UDTs and XML data • GetValue and GetString, respectively • the new SqlMetaData type can return extended properties • XML schema collection for XML type • database name of a UDT

  45. The XML Data Type • SQL Server 2005 provides an XML database and schema repository • XML data-typing and indexing are supported for XML columns • XML data type can be used in T-SQL • when inserting and selecting data • when querying and updating data

  46. Using the XML Data Type • Accessing XML data from ADO.NET • reading XML with a DataReader • updating XML with a SQL statement • XML Data Manipulation Language • XML DML allows updates to the data within XML columns using T-SQL • functions include: modify(), insert(), delete(), replace(), column() and value()

  47. XML DML Queries • Methods of the "xml" data type within SQL Server • Query the data in an XML column to get individual values without retrieving all of it • Manipulate the data in an XML column without retrieving any of it • Example: UPDATE table SET xml-column.modify(' replace value of (/root/element)[index] with "new-value"') WHERE column-name = criteria

  48. Password changing • SQL Server 2005 supports password expiration • requires Windows Server 2003 • the ChangePassword method of the SqlConnection type • you shouldn’t hard-code the password in the connection string (a sound practice anyway) • store in config file in encrypted format

  49. What else?

  50. Promotable Transactions • Automatic promotion of local transactions into distributed ones • Uses TransactionContext • Fully integrated with the classes in System.Transactions namespace • Works with transactions started in SQL Server 2005 CLR code • Context flows even if you start in-proc • Don't promote single RM transactions

More Related