Sunday, December 2, 2007

Faster access to SQL Server using the SqlClient data library

.Net supports data access through ADO.Net which provides a generic mechanism to access data. Unlike previous versions of data connector technology Microsoft decided to break with tradition and provide a customized library for SQL Server meant to improve performance by bypassing the high level OleDb API to access the database engine. The SqlClient namespace provides access to the SQL Server specific versions of objects for connecting and accessing the database while the OleDb namespace houses the same functionality for the generic OleDb driver access. You can use either for accessing SQL Server, but the SqlClient objects will be somewhat faster and specifically tuned for SQL Server.

In practice this means if you want to use the Sql Server specific classes you will need to bracket your code if you also want to support other data sources because the object names vary for each of the class libraries. All Sql Server objects start with Sql like SqlConnection, SqlCommand and SqlDataAdapter, while the OleDb versions start with OleDb.

The interfaces for the driver objects is open and extendable through .Net so you can expect to see other database vendors to build .Net classes that are optimized for their data engines. In fact building a new provider is relatively painless and involves override and implementing a handful of methods in the various data access classes (Connection, DataAdapter, Command), so it's possible to use straight .Net code to create a provider with much less effort than previously required through the OleDb interfaces.

No comments: