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,
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
