either as a stand-alone or inside a stored procedure, SQL Server doesn''''t automatically produce a cursor over the set of rows as some databases do. Instead, it uses an optimized method to stream the resultset across the network, on occasions reading from the database buffers directly as the network library pulls the data in packet-size chunks. This is known as "the default resultset of SQL Sever" in SQL Server Boks Oline, or "the cursorless resultset". In versions of SQL Server prior to SQL Server 2005, there could only be a single cursorless resultset active on a single connection at a time. Different database APIs and libraries dealt with the one connection/one cursorless resultset behavior differently. ADO.NET 1.0 and 1.1 throw an error if you attempt to open a second cursorless resultset; ADO "classic" actually opened a new database connection behind the scenes. Opening a new database connection was more convenient, though less "precisely correct" than throwing an error; this convenience feature was inadvertently abused by some programmers and resulted in more database connections than they bargained for. In SQL Server 2005, the database has been enhanced to permit multiple cursorless resultsets to be active on a single connection at a time. This produces the feature acronym "MARS" (multiple active resultsets). There are changes to the network libraries to support this behavior, and both the new network libraries and the new database are needed to enable MARS. In SqlClient code, you multiplex resultsets by having multiple SqlCommand instances use the same connection. Each SqlCommand can accommodate a SqlDataReader produced by calling Command.ExecuteReader, and multiple SqlDataReaders can be used in tandem. In ADO.NET 1.0 and 1.1, you must close one SqlDataReader before obtaining another, even if multiple SqlCommand are used. Note that you cannot multiplex SqlDataReaders produced from multiple ExecuteReader calls on the same SqlCommand instance. Here''''s a short (but not very functionally useful) example: // connection strings should not be hardcoded string connstr = GetConnStringFromConfigFile(); SqlConnection conn = new SqlConnection(connstr); SqlCommand cmd1 = new SqlCommand( "select * from employees", conn) SqlCommand cmd2 = new SqlCommand( "select * from jobs", conn) SqlDataReader rdr1 = cmd1.ExecuteReader(); // next statement causes an error prior to SQL Server 2005 SqlDataReader rdr2 = cmd2.ExecuteReader(); // now you can reader from rdr1 and rdr2 at the same time. This feature is not just about reducing errors or clarifying what used to be ADO library magic. It can be extremely useful in conjunction with asynchronous operations described above. Multiple asynchronous SELECT statements or stored procedure invocations can be executed in tandem, saving database connections and optimizing throughput. Imagine filling 20 drop-down list boxes on a form at the same time, using a single connection. You can also execute non-resultset-returning statements while a resultset is active. Although multiple streams of execution can be active at the same time, all of the execution streams must share the same transaction, if a transaction exists. Transactions are still connection-scoped rather than command-scoped. You associate the SqlTransaction instance with the SqlCommand by setting the SqlCommand Transaction property as in previous versions of ADO.NET. SqlDependency and SqlNotificationRequest It''''s extremely helpful in middle-tier caching situations to be able to refresh the cache based on the fact that someone else has changed a row in the database. Programmers have resorted to a few different techniques to accomplish this, such as writing a trigger that updates a file when the table or view changes, or refreshing the cache every so often whether the database has changed or not. There is no straightforward way the register for database notifications until the SqlClient SqlNotificationRequest and SqlDependency classes. SqlDependency is a high-level class that wraps a SqlNotificationRequest and presents your notification information as a .NET event. With SqlNotificationRequest, there is no event and you must "do the heavy lifting" of registering for the notification and harvesting the information yourself. The great majority of programmers will use SqlDependency. SqlDependency can be used as a stand-alone and its functionality is available directly when using the ASP.NET Cache class. This SQL Server 2005-specific functionality depends on SQL Server Service Broker, a new feature that implements a scalable queuing system. Note that, when using the ASP.NET Cache class, polling the database is used instead of Service Broker to achieve similar functionality. When using Service Broker and SQL Server 2005, you need not maintain a connection to the database in order to be notified. SqlDependency uses your choice of HTTP or TCP protocols and contacts you when the underlying rows change. The notification does not contain any row-specific information: when you are notified, you must fetch the entire set of rows again and re-register for the notification. This functionality is just what you need for a single cache or a limited set of users, but beware when using it with large numbers of users listening at the same time. Each user must refresh the entire rowset in cache when any row changes. With a large number of changes and a large number of users, the SELECT statements used for refresh could be a significant hit on the database. Password Changing SQL Server 2005 provides a mechanism to use SQL logins that are subject to the same expiration as other password policies that integrated logins (Windows logins connecting to SQL Server). This feature requires SQL Server 2005 running on Windows Server 2003. If a SQL login password (like ''''sa'''') is going to expire, you won''''t be able to use the traditional Windows mechanism and password changing APIs to change it. You can only change this password using a SQL Server client that ends up calling the Transact SQL ALTER LOGIN verb. SqlClient accommodates this through the ChangePassword method on the SqlConnection class. Note that this method is only useable if executed against a SQL Server 2005 instance; although you can change a SQL login on older versions of the database, this API uses a network packet type that no other version of SQL Server supports. Another aspect of password changing to consider is that it is no longer possible to hard-code SQL Server login IDs and passwords in connection strings in programs. Unless you are going to produce .NET intermediate language and replace the executable each time the password changes (this is not a viable option), you must store your SQL Server password in a configuration file. Serious SQL Server developers have been using a configuration file to store (hopefully encrypted) passwords for quite a while. Better yet, always use SQL Server integrated security, if possible. System.Transactions Integration The SqlClient provider in ADO.NET 2.0 integrates with the new System.Transactions namespace, enabling a behavior known as promotable transactions. Although Transact SQL can be used to start a local or distributed transaction (BEGIN TRANSACTION and BEGIN DISTRIBUTED TRANSACTION), there are occasions, especially in client-side/middle-tier programming where the programmer may wish to write a component that could be used in one database or multiple database scenarios. These scenarios might include multiple SQL Server instances and SQL Server can automatically detect multi-instance access and "promote" a transaction from local to multi-instance (distributed). This is even possible when multiple database products or multiple connections are used, as long the first database (known as a resource manager in distributed transaction terminology) is SQL Server. Promotable transactions are enabled by default in ADO.NET. Client Failover SQL Server 2005 supports a "hot spare" capability through database mirroring. If a SQL Server instance fails, the work can be shifted over to the backup server automatically. This requires an instance to witness the failover known as (not surprisingly) the "witness instance". Hot spare scenarios require that existing client connections must "know" to fail over (establish a connection with the new server instance), as well. Client connections that produce an error on the next attempted access and must be manually "failed over" by client programming are suboptimal. SqlClient in ADO.NET 2.0 supports client failover without special programming of the application program. Support for New Transaction Isolation Level SQL Server 2005 supports transaction isolation through two methods, locking and versioning. Previous versions of SQL Server supported locking but not versioning. Two types of versioning are supported; these are known as statement-level versioning and transaction-level versioning. The feature is meant to selectively reduce locking in extreme circumstances and to ease conversion of applications that were designed for versioning databases. Applications designed for versioning databases often need significant changes when porting them to a locking database, and vice-versa. The default behavior of a versioning database is almost always statement-level versioning. For more information on the difference, consult A First Look at SQL Server 2005 for Developers by Beauchemin, Berglund, and Sullivan. Both different versioning and different locking behavior equate to starting a transaction using a specific transaction isolation level. There are four transaction isolation levels defined by the
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
