built-in diagnostics. I''''ll cover tracing in depth in a future
article.
SqlClient Enhancements
The Microsoft flagship database is SQL Server and SqlClient is the SQL Server-specific provider. ADO.NET
2.0 actually ships with four Microsoft providers:
1. SqlClient—the Microsoft provider for SQL Server
2. OracleClient—the Microsoft provider for the Oracle database
3. OleDb—the bridge provider for using OLE DB providers in ADO.NET
4. Odbc—the bridge provider for using ODBC drivers in ADO.NET
In ADO.NET 2.0, all four of these providers have been enhanced to enable their use in partially trusted
environments. By properly configuring .NET code access security (CAS), it is possible to enable more
data-centric mobile code scenarios. In ADO.NET 1.1, only the SqlClient provider supported this feature.
In addition, data providers are written by database companies (Oracle''''s ODP.NET and IBM''''s data
provider for DB2), provider specialists (DataDirect Technologies), and open source projects and
individuals. In addition, Microsoft will ship a DB2 data provider in Host Integration Server 2004 product.
Because SQL Server is an important piece of the software puzzle, there are many enhancements to
SqlClient in ADO.NET 2.0, in addition to the enhancements in all Microsoft-supported providers. Some of
this functionality supports any version of SQL Server, while much of the new functionality is meant to
support the many new features available in SQL Server 2005, which may be more easily recognized by
its codename, "Yukon". SQL Server 2005 supports .NET code running inside the server, and there are
optimizations for data access inside the server using the provider model as well. One big internal change
that is not immediately evident is that the SqlClient data provider in ADO.NET 2.0 does not use the
Microsoft Data Access Components (MDAC). There is also better error handling in the provider, with
clearer error messages for network errors and more granular error messages overall. Here''''s an overview
of the programmer-visible SqlClient-specific functionality.
Connection Pooling Enhancements
ADO.NET 1.0 introduced a new infrastructure for pooling database connections. The Microsoft SqlClient
and OracleClient data providers use this infrastructure; the OleDb and Odbc data providers do not. The
new pooling mechanism provided granular support of connection pooling parameters, including
minimum and maximum pool sizes and the ability for the pool manager to wait for a user-defined amount
of time for a connection to become available in the pool. ADO.NET adds a connection-pooling
enhancement that allows you to programmatically "drain" the connection pool; that is, close all of the
connections currently kept alive by the pooler. You can clear a specific connection pool by using the static
(shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an
appdomain by using the SqlConnection.ClearPools method. Both SqlClient and OracleClient
implement this functionality.
Asynchronous Commands
Sometimes in client or middleware code, you want to do more than one thing at the same time. In
inherently multithreaded middleware code, this is a key factor for increasing throughput. In ADO.NET 2.0,
SqlClient now supports asynchronous command execution.
The .NET paradigm for asynchronous operations is to provide a set of Begin and End methods for an
operation, as well as a method for synchronous operation. Because database command execution can
take a long time, SqlClient now provides built-in SqlCommand methods that provide asynchronous
execution. Methods that support asynchronous execution and their synchronous counterparts are listed
in the table below.
Synchronous Method Asynchronous Methods
ExecuteNonQuery BeginExecuteNonQuery, EndExecuteNonQuery
ExecuteReader BeginExecuteReader, EndExecuteReader
ExecuteXmlReader BeginExecuteXmlReader, EndExecuteXmlReader
Although asynchronous execution can be a nice feature, it should not be used gratuitously; only use it if
you know the command can run for a long time, and also that you have something useful to do at the
same time. The Windows thread scheduler in the Windows NT family of operating systems (the feature
is not available on Windows 9x and Me clients) takes overhead of its own to switch between threads. Also
bear in mind that some .NET libraries are thread-sensitive; using asynchrony, the thread that you use to
start the operation won''''t necessarily be the same thread it finishes on. However, the SQL Server network
library stack has been enhanced to support asynchrony by means of I/O completion ports and this
provides better throughput for asynchronous SQL Server operations. Not only can asynchronous
operation be effective for multiple action statements and stored procedure execution, when used with the
multiple active resultset feature in SQL Server 2005, you can multiplex asynchronous SELECT
statements using a single database connection.
Bulk Import
Many database applications can INSERT rows into SQL Server in large batches, quickly. The canonical
example of this is an application that inserts rows into SQL Server that correspond to readings from a
hardware device, such as a telephone switch or a hospital patient monitor. Although SQL Server comes
with utilities (like bcp) to accommodate this, these typically use a file for their input.
SqlClient contains a new class called SqlBulkCopy. This class is not meant to directly consume input
from files and produce file output like BCP, but to accommodate inserting many rows into the database
from a client quickly and efficiently. SqlBulkCopy can get its input from DataReaders and DataSets. This
means that you can not only stream a series of rows from a provider directly (DataReader), but also fill
DataSets with outside data obtained from a hardware device that is not a provider and update this
directly; in this case, no provider is needed as a source.
// Fill up a DataSet
DataSet ds = new DataSet();
FillDataSetFromHardwareDevice(ds);
// Copy the Data to SqlServer
string connect_string = GetConnectStringFromConfigFile();
SqlBulkCopy bcp = new SqlBulkCopy(connect_string);
bcp.DestinationTableName = "hardware_readings";
bcp.WriteToServer(ds);
Provider Statistics
Some application writers find it useful to do "real-time" monitoring in their application. Although you
could use Windows Performance Monitor, define your own performance classes, and use internal (and
possibly fragile, over time) SQL Server metadata calls to obtain this information, SqlClient now has a
built-in way to provide this information for you. An instance method on the SqlConnection class lets you
harvest per-connection statistics that are similar to those available in the ODBC API. Because storing and
gathering these statistics takes overhead of its own, there is a property that can be used to toggle
statistics gathering. There is also a method to reset the counters. Statistics gathering is turned off by
default, of course, and is also set off when you return a connection to the connection pool by calling
Dispose or Close in a pooling scenario. Here is an example of the statistics produced.
string connect_string = GetConnectStringFromConfigFile();
SqlConnection conn = new SqlConnection(connect_string);
conn.Open();
// Enable
conn.StatisticsEnabled = true;
// do some operations
//
SqlCommand cmd = new SqlCommand("select * from authors", conn);
SqlDataReader rdr = cmd.ExecuteReader();
Hashtable stats = (Hashtable)conn.RetrieveStatistics();
// process stats
IDictionaryEnumerator e = stats.GetEnumerator();
while (e.MoveNext())
Console.WriteLine("{0} : {1}", e.Key, e.Value);
conn.ResetStatistics();
Connection-specific statistics
BuffersReceived : 1
BuffersSent : 1
BytesReceived : 220
BytesSent : 72
ConnectionTime : 149
CursorFetchCount : 0
CursorFetchTime : 0
CursorOpens : 0
CursorUsed : 0
ExecutionTime : 138
IduCount : 0
IduRows : 0
NetworkServerTime : 79
PreparedExecs : 0
Prepares : 0
SelectCount : 0
SelectRows : 0
ServerRoundtrips : 1
SumResultSets : 0
Transactions : 0
UnpreparedExecs : 1
For more information about exactly what these statistics represent, consult the ADO.NET 2.0 or the
ODBC documentation.
AttachDbFileName
The SqlClient data provider supports desktop applications (in which the database is stored on a user''''s
desktop) as well as client-server and middleware-based applications. There is a special version of SQL
Server known as MSDE; the SQL Server 2005 era name for this product is SQL Server 2005 Express
Edition. In desktop applications, the database itself is application-specific and bundled with the
application. The user may even be unaware that SQL Server is being used as the data repository, as the
application setup program will run the SQL Server Express installation.
To facilitate attaching the database files to the SQL Server Express instance inside of an application,
ADO.NET 1.0 provided a connection string parameter, AttachDbFileName. This parameter had to be
specified as a hard-coded pathname, however, making it difficult for users to install the application in a
location other than the default. In ADO.NET 2.0, the AttachDbFileName parameter can be a relative path,
and is used in conjunction with application configuration settings. This makes setting up a desktop
application for SQL Server Express as easy as connecting to a Microsoft Access file-based data store.
SQL Server 2005-Specific Features in SqlClient
MARS
When you select a set of rows using a SQL SELECT statement, 上一页 [1] [2] [3] [4] 下一页 |
