Title: ADO'NET 2'0 y SQL Server 2005
1ADO.NET 2.0 y SQL Server 2005
2Adolfo 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
3Series 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
4Nuevas 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
5Agenda
- SNAC
- DataSet and DataTable improvements
- Transactions and TransactionScope
- Multiple Active Resultsets (MARS)
- Snapshot Isolation support
- Asynchronous Support
- SqlClient Statistics
- Bulk Copy
- Tracing
6Client-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
7SQL 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)
8SNAC and new functionality
- SNAC OLE DB provider and ODBC driver support
- snapshot isolation
- MARS
- "max" data type support
- UDT and XML support (not yet)
9DataSet 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)
10DataTable Improvements
- DataTables are more independent now
- ReadXML, ReadXMLSchema
- WriteXML, WriteXMLSchema
- Load, Merge
- CreateDataReader
- Returns a DataTableReade
11Load 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)
12ADO.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"
13Simple 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
14Complex 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
15MARS
- 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
16The 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)
17The 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)
18Behavior 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
19Snapshot 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)
//...
20Password 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
21Asynchronous 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
22Asynchronous 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)
23Bulk 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)
24Summary
- 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
25Series 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
26Gracias
- adolfo_at_wiernik.net
- adolfo_at_solidqualitylearning.com
- Weblog www.wiernik.net