1 / 26

ADO.NET 2.0 y SQL Server 2005

ADO.NET 2.0 y SQL Server 2005. Adolfo Wiernik adolfo@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

remedios-graves
Télécharger la présentation

ADO.NET 2.0 y SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related