Database Reference
In-Depth Information
Figure 10-2.
SQL Server and OLE DB data provider differences
If your application connects to an older version of SQL Server (6.5 or older) or to more than one kind
of database server at the same time (for example, an Access and an Oracle database connected
simultaneously), only then should you choose to use the OLE DB data provider.
No hard-and-fast rules exist; you can use both the OLE DB data provider for SQL Server and the
Oracle data provider (
System.Data.OracleClient
) if you want, but it's important you choose the best
provider for your purpose. Given the performance benefits of the server-specific data providers, if you
use SQL Server, 99 percent of the time you should be using the
System.Data.SqlClient
classes.
Before we look at what each kind of data provider does and how it's used, you need to be clear on
their core functionality. Each .NET data provider is designed to do the following two things very well:
•
Provide access to data with an active connection to the data source
•
Provide data transmission to and from disconnected data sets and data tables
Database connections are established by using the data provider's
Connection
class (for example,
System.Data.SqlClient.SqlConnection
). Other components such as data readers, commands, and data
adapters support retrieving data, executing SQL statements, and reading or writing to data sets or data
tables, respectively.
As you've seen, each data provider is prefixed with the type of data source it connects to (for
instance, the SQL Server data provider is prefixed with
Sql
), so its connection class is named
SqlConnection
. The OLE DB data provider's connection class is named
OleDbConnection
.
Let's understand the three data providers that can be used with SQL Server.
Understanding the SQL Server Data Provider
The .NET data provider for SQL Server is in the
System.Data.SqlClient
namespace. Although you can
use
System.Data.OleDb
to connect with SQL Server, Microsoft has specifically designed the
System.Data.SqlClient
namespace to be used with SQL Server, and it works in a more efficient and
optimized way than
System.Data.OleDb
. The reason for this efficiency and optimized approach is that
this data provider communicates directly with the server using its native network protocol instead of
through multiple layers.
Table 10-2 describes some important classes in the
SqlClient
namespace.
