ADO'NET 2'0 y SQL Server 2005 - PowerPoint PPT Presentation

1 / 26
About This Presentation
Title:

ADO'NET 2'0 y SQL Server 2005

Description:

Microsoft Regional Director - http://msdn.microsoft.com/isv/rd ... could be done with multiple server cursors. ADO classic's answer was to open more connections ... – PowerPoint PPT presentation

Number of Views:52
Avg rating:3.0/5.0
Slides: 27
Provided by: WPic2
Category:
Tags: ado | net | sql | cursers | server

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com