Databases Reference
In-Depth Information
important you choose the best provider for your purpose. Given the performance bene-
fits of the server-specific data providers, if you use SQL Server, 99% 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 follow-
ing two things very well:
• Provide access to data with an active connection to the data source.
• Provide data transmission to and from disconnected datasets 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 read-
ers, commands, and data adapters support retrieving data, executing SQL statements, and
reading or writing to datasets 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 see how to work with the three data providers that can be used with SQL Server.
Working with 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 9-2 describes some important classes in the
SqlClient
namespace.
Search WWH ::
Custom Search