Whats New in System'Data

1
Whats 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)
DbBase 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.GetFactoryClass
  es()
• DbProviderFactory factory DbProviderFactories.Ge
  tFactory(dt.Rowsx)
• ... or ...
• DbProviderFactory factory DbProviderFactories.Ge
  tFactory( dt.Select("InvariantName'System.Data.Sq
  lClient'") 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", restrictionsx)
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 AcceptChangesDuring
  Update 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 preservechangesTrue 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
  ("_at_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 "asynctrue" to connection string
• Doesnt work on Win9x and ME clients

31
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
3 secs
11 secs
32
Asynchronous Execution
Application
Database1 Latency 3 secs
Rowset 1
Database2 Latency 8 secs
Rowset 2
Rowset 3
Database3 Latency 5 secs
8 secs
Total time until all three displays filled
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.BeginExecuteReade
  r()
• 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.BeginExecuteReade
  r()
• Wait for each command to complete
• for(int i0 i lt 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
• Doesnt require an open connection
• Dont 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.ExecuteRe
ader()
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 countryUSA ? 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 shouldnt 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

51
Auto-promoting a Transaction

• Initialize the transaction scope
• TransactionScope scope
• new TransactionScope
• (TransactionScopeOptions.RequiresNew)
• Create a connection and do work
• Do not enrol - uses a local transaction
• Create second connection and do work
• transaction auto-promoted to distributed
• Vote to commit or rollback
• scope.Consistent truefalse
• Dispose of transaction when complete
• scope.Dispose()

52
Tracing

• OLEDB lacks a standard trace mode
• makes it harder to solve OLEDB and ADO problems
• ADO.NET 2.0 traces every API call
• similar to ODBC tracing
• all Microsoft providers are instrumented
• open standard for 3rd party provider writers
• DataSet has built-in diagnostics too

53
Support for untrusted apps

• in ADO.NET 1.1 only the SQL Server provider can
  be used in partially-trusted apps
• in ADO.NET 2.0 all four Microsoft providers are
  supported in this scenario
• necessary for ClickOnce deployment
• Must be enabled from .NET configuration panel

54
Other ADO.NET 2.0 enhancements

• Doesnt depend on MDAC
• Better error handling and clearer error messages
• Better control on connection pooling (SqlClient
  and OracleClient)
• SqlConnection.ClearPool clears a specific pool
• SqlConnection.ClearPools clears all the pools in
  an appdomain

55
Even more for SQL Server 2005

• Support for snapshot isolation level
• Transparent support for client failover
• when the a SQL Server instance fails and work is
  shifted to the backup (witness) instance

56
Summary

• Understood how to use the Common Programming
  Model to write provider-independent code
• Explored the use of the Schema Discovery API to
  examine the schema and structure of databases
• Reviewed the enhancements to the DataSet class
  that provide better performance and increased
  usability

57
Microsoft