ADO.NET 2.0 y SQL Server 2005

1. ADO.NET 2.0 y SQL Server 2005

2. Adolfo Wiernikadolfo@wiernik.net • Microsoft Regional Director - http://msdn.microsoft.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@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 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-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 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 • Common Table Expressions • PIVOT – UNPIVOT Operators • APPLY Operator • Exception Handling .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 • Indexing of XML Datatype

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.NET’s 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(CONNSQL2005A)) { 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 security=sspi;database=pubs"); 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.Read(); Console.WriteLine(rdr2[0]);

17. The MARS Solution // MARS is the default with SQL Server 2005 DB SqlConnection conn = new SqlConnection( "server=zmv43;integrated security=sspi;database=pubs"); 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(); rdr2.Read(); rdr.Read(); // both readers on same connection Console.WriteLine(rdr[0]); Console.WriteLine(rdr2[0]);

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( "server=mysvr;integrated security=sspi;database=pubs"); conn.ConnectionString += ";async=true"; 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 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-MX

26. Gracias adolfo@wiernik.net adolfo@solidqualitylearning.com Weblog: www.wiernik.net