ADO'NET 2'0 y SQL Server 2005

1
ADO.NET 2.0 y SQL Server 2005
2
Adolfo Wiernikadolfo_at_wiernik.net

• Microsoft Regional Director - http//msdn.microso
  ft.com/isv/rd
• Mentor Solid Quality Learning -
  http//www.solidqualitylearning.com
• Fundador, Costa Rica User Group .NET -
  http//www.crug.net
• Orador INETA Latinoamérica - http//www.ineta.org/
  latam
• Blog - http//www.wiernik.net

Jose Ricardo Ribeiroricardor_at_microsoft.com

• En Microsoft desde 1998
• Desde el 2003 - Regional Program Manager
• SQL Server Latinoamérica

3
Series de Webcasts

• Introducción a SQL Server 2005 para
  desarrolladoresViernes, 22 de Julio de 2005
  0600 p.m.(GMT)http//msevents.microsoft.com/CUI/
  EventDetail.aspx?EventID1032277969Culturees-MX
• Nuevas características del lenguaje T-SQL en SQL
  Server 2005Lunes, 25 de Julio de 2005 0600
  p.m.(GMT) http//msevents.microsoft.com/CUI/Event
  Detail.aspx?EventID1032277973Culturees-MX   
• Aprovechando XML dentro de la base de datos con
  SQL Server 2005Viernes, 29 de Julio de 2005
  0600 p.m.(GMT) http//msevents.microsoft.com/CUI
  /EventDetail.aspx?EventID1032277975Culturees-MX
  
• Programando SQL Server 2005 con el CLR
  Integración SQL-CLRLunes, 01 de Agosto de 2005
  0600 p.m.(GMT) http//msevents.microsoft.com/CUI
  /EventDetail.aspx?EventID1032277977Culturees-MX
   
• Nuevas características en ADO.NET 2.0Viernes, 05
  de Agosto de 2005 0600 p.m.(GMT)
  http//msevents.microsoft.com/CUI/EventDetail.aspx
  ?EventID1032277978Culturees-MX 

4
Nuevas Características para Desarrollo

• SQL Server Engine
• SQL Service Broker
• HTTP Support (Native HTTP)
• Multiple Active Result Sets (MARS)
• Snapshot Isolation Level
• Reporting Services
• Multiple Output Formats
• Parameters (Static, Dynamic, Hierarchical)
• Bulk Delivery of Personalized Content
• Support Multiple Data Sources
• STS (Web Parts, Doc Libraries)
• Visual Design Tool
• Charting, Sorting, Filtering, Drill-Through
• Scheduling, Caching
• Complete Scripting Engine
• Scale Out architecture
• Open XML Report Definition
• Notification Services
• SQL Server Mobile Edition

• .NET Framework
• Common Language Runtime Integration
• User-defined Aggregates
• User-defined Data Types
• User-defined Functions
• SQL Server .NET Data Provider
• Extended Triggers
• Data Types
• Managed SQL Types
• New XML Datatype
• Varchar (MAX) Varbinary (MAX)
• XML
• XQUERY Support
• XML Data Manipulation Language
• FOR XML Enhancements
• XML Schema (XSD) Support
• MSXML 6.0 (Native)
• .Net XML Framework
• Full-text Search

• MDAC
• SNAC
• Microsoft Installer base setup
• ADO.NET 2.0
• Notification Support
• Object Model enhancements
• SQL Client .NET Data Provider
• Server Cursor Support
• Asynchronous Execution
• System.Transactions
• Security
• Separation of Users and Schema
• Data encryption primitives
• Administration
• SQL Management Objects (SMO)
• Analysis Management Objects (AMO)
• Replication Management Objects (RMO)
• T-SQL
• Recursive Queries

5
Agenda

• SNAC
• DataSet and DataTable improvements
• Transactions and TransactionScope
• Multiple Active Resultsets (MARS)
• Snapshot Isolation support
• Asynchronous Support
• SqlClient Statistics
• Bulk Copy
• Tracing

6
Client-Side Data Access

• ADO.NETs SqlClient contains two types of
  functional changes
• support of new SQL Server 2005 functionality
• added client functionality for all support
  database versions
• Most of these changes mimicked in unmanaged stack
• Additional changes for all providers

7
SQL NAtive Client (SNAC)

• SNAC separates SQL client from MDAC
• MDAC is part of the OS
• SQL Server 2005 uses MDAC 2.8
• MDAC not shipped with SQL Server 2005
• SNAC contains
• new OLE DB provider
• new ODBC driver
• acts differently than old provider/driver
• subtle implementation differences
• not supported on Windows 9x
• not used by System.Data.SqlClient (or
  System.Data.SqlServer)

8
SNAC and new functionality

• SNAC OLE DB provider and ODBC driver support
• snapshot isolation
• MARS
• "max" data type support
• UDT and XML support (not yet)

9
DataSet Improvements

• CreateDataReader
• Similar to streaming data from a DB using
  DataReader
• Returns a DataTableReader with multiple result
  sets
• // Use this to copy tables
• myReader myTable1.CreateDataReader()
• myTable2.Load(myReader)
• Improved Insert/Delete performance
• Internal indexing scheme completely rewritten
  (load API)
• Binary Serialization/Persistence
• ds.RemotingFormat SerializationFormat.Binary
• SerializeDataSet(ds, "ds.dat", SerializationFormat
  .Binary)

10
DataTable Improvements

• DataTables are more independent now
• ReadXML, ReadXMLSchema
• WriteXML, WriteXMLSchema
• Load, Merge
• CreateDataReader
• Returns a DataTableReade

11
Load and Update Improvements

• DataSets don't need DataAdapters to Load
• ds.Load(...)
• DataTables don't need DataSets or DataAdapters
• dt.Load(...)
• Batched Updates
• In ADO.NET 1.x all updates where 1 row at a time
• In ADO.NET 2.0, you set the Adapter's
  UpdateBatchSize
• Defaults to 1 (compatible with 1.x) 0 updates
  all
• adp.UpdateBatchSize 20
• adp.Update(dt)

12
ADO.NET 2.0 Transactions

• Transactions in ADO.NET 1.x
• Transaction object associated with connection
• COMMIT ROLLBACK in a stored procedure
• Enterprise Services (COM)
• Transactions in ADO.NET 2.0
• Easier
• Same code for single DB (simple) or multiple DB
  transactions (complex)
• TransactionScope object
• "Wrap all your commands in a TransactionScope
  object, and it takes care of everything for you"

13
Simple TransactionScope Example

• bool IsConsistent false
• using (System.Transactions.TransactionScope ts
  new System.Transactions.TransactionScope())
• SqlConnection CN newSqlConnection(CONNSTR)
• string SQL "DELETE Products"
• SqlCommand CMD newSqlCommand(SQL, CN)
• cn.Open()
• cmd.ExecuteNonQuery()
• cn.Close()
• ts.Consistent IsConsistent

14
Complex TransactionScope Example

• bool IsConsistent false
• using (TransactionScope ts newTransactionScope()
  )
• using (SqlConnection CN1 newSqlConnection(CONN
  SQL2005A))
• try
• ... Execute SqlCommand 1
• using(SqlConnection CN2
  newSqlConnection(CONNSQL2005B))
• ... Execute SqlCommand 2
• IsConsistent true
• catch (SqlException ex) ...
• cn.Close()
• ts.Consistent IsConsistent

15
MARS

• Before SQL Server 2005, SQL Server had no
  multiplexed connections
• connection supported only a single DataReader
• could be done with multiple server cursors
• ADO classic's answer was to open more connections
• Multiple Active Result Sets adds multiplexed
  connections
• multiple streams and composable transactions
  multiplexed

16
The Problem...
SqlConnection conn new SqlConnection(
"server.integrated securitysspidatabasepubs")
SqlCommand cmd new SqlCommand( "select
from authors",conn) conn.Open() SqlDataReader
rdr cmd.ExecuteReader() Console.WriteLine("got
first reader") // second reader, same
connection This will not work cmd.CommandText
"select from jobs" SqlDataReader rdr2
cmd.ExecuteReader() // attempt to use both
readers, but never get to here rdr.Read() rdr2.Re
ad() Console.WriteLine(rdr20)
17
The MARS Solution
// MARS is the default with SQL Server 2005
DB SqlConnection conn new SqlConnection( "server
zmv43integrated securitysspidatabasepubs")
SqlCommand cmd new SqlCommand("select from
authors",conn) // must use a separate
SqlCommand instance SqlCommand cmd2 new
SqlCommand("select from jobs",conn) conn.Open()
SqlDataReader rdr cmd.ExecuteReader() //
second reader, same connection - THIS DOES
WORK SqlDataReader rdr2 cmd2.ExecuteReader() rd
r2.Read() rdr.Read() // both readers on same
connection Console.WriteLine(rdr0) Console.Writ
eLine(rdr20)
18
Behavior Within a Connection

• MARS allows multiple "execution paths" per
  connection
• transaction is tied to the execution path
• each SqlCommand must be associated with a
  SqlTransaction
• if there is a transaction in progress

19
Snapshot Isolation

• Snapshot isolation is supported in SQL Server
  2005
• just another isolation level in client
• must be enabled on server

SqlConnection conn new SqlConnection(
"connect string") SqlTransaction tx
null SqlCommand cmd new SqlCommand( "update
jobs set job_desc 'New job" where job_id 1",
conn, tx) try conn.Open() tx
conn.BeginTransaction(IsolationLevel.Snapshot)
//...
20
Password change API

• Passwords can expire for SQL users in SQL Server
  2005
• well-known error message returned
• must prompt for new password
• no standard password prompt
• SqlConnection.ChangePassword to change
• needs old and new passwords
• must replace password in config file
• precludes storing connection string in program

21
Asynchronous Execution

• Asynchrony Added at the TDS layer
• Available in SqlClient using async delegate
• BeginInvoke - EndInvoke pair
• can use any common delegate pattern
• Variety of command operations are asynchronous
• Command.BeginExecuteReader
• Command.BeginExecuteNonQuery
• Command.BeginExecuteXmlReader
• IAsyncResult class can be used to harvest results

22
Asynchronous ExecuteReader
// "busywait" example SqlConnection conn new
SqlConnection( "servermysvrintegrated
securitysspidatabasepubs") conn.ConnectionStri
ng "asynctrue" conn.Open() SqlCommand cmd
new SqlCommand("select from authors",
conn) // execute the
command asynchronously IAsyncResult ar
cmd.BeginExecuteReader() // check every 250 ms
for result while (!ar.IsCompleted)
Console.Write(".") Thread.Sleep(250) //
harvest results SqlDataReader rdr
cmd.EndExecuteReader(ar)
23
Bulk Insert

• Managed classes to encapsulate some BCP
  functionality
• can write from DataTable or IDataReader
• uses a connection and BULK INSERT
• works off-host
• import from file and export not supported
• SqlBulkCopy more like IRowsetFastLoad

// bulk copy from a DataReader void
DoBulkCopy(IDataReader reader) SqlBulkCopy
bcp new SqlBulkCopy(connectString)
bcp.DestinationTableName "Customers"
bcp.WriteToServer(reader)
24
Summary

• SNAC for non-.NET clients
• DataSet and DataTable improvements
• SqlClient changes for SQL Server 2005
• SqlClient supports multiple active resultsets
• SqlClient supports snapshot isolation
• password change API for SQL Server logins
• transaction scopes used to implement promotable
  transactions
• SqlClient changes for all SQL Server versions
• asynchronous connection and commands
• transaction scopes simplify distributed
  transactions
• client statistics
• bulk copy support in code

25
Series de Webcasts

• Introducción a SQL Server 2005 para
  desarrolladoresViernes, 22 de Julio de 2005
  0600 p.m.(GMT)http//msevents.microsoft.com/CUI/
  EventDetail.aspx?EventID1032277969Culturees-MX
• Nuevas características del lenguaje T-SQL en SQL
  Server 2005Lunes, 25 de Julio de 2005 0600
  p.m.(GMT) http//msevents.microsoft.com/CUI/Event
  Detail.aspx?EventID1032277973Culturees-MX   
• Aprovechando XML dentro de la base de datos con
  SQL Server 2005Viernes, 29 de Julio de 2005
  0600 p.m.(GMT) http//msevents.microsoft.com/CUI
  /EventDetail.aspx?EventID1032277975Culturees-MX
  
• Programando SQL Server 2005 con el CLR
  Integración SQL-CLRLunes, 01 de Agosto de 2005
  0600 p.m.(GMT) http//msevents.microsoft.com/CUI
  /EventDetail.aspx?EventID1032277977Culturees-MX
   
• Nuevas características en ADO.NET 2.0Viernes, 05
  de Agosto de 2005 0600 p.m.(GMT)
  http//msevents.microsoft.com/CUI/EventDetail.aspx
  ?EventID1032277978Culturees-MX 

26
Gracias

• adolfo_at_wiernik.net
• adolfo_at_solidqualitylearning.com
• Weblog www.wiernik.net