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上一页 [1] [2] [3] [4] 下一页 |
