The Data Provider Classes in ADO.NET

On their own, the data classes can’t accomplish much. Technically, you could create data objects by hand, build tables and rows in your code, and fill them with information. But in most cases, the information you need is located in a data source such as a relational database. To access this information, extract it, and insert it into the appropriate data objects, you need the data provider classes described in this section. Remember, each one of these classes has a database-specific implementation. That means you use a different, but essentially equivalent, object depending on whether you’re interacting with SQL Server, Oracle, or any other ADO.NET provider.

Regardless of which provider you use, your code will look almost the same. Often the only differences will be the namespace that’s used and the name of the ADO.NET data access classes.

Each provider designates its own prefix for naming classes. Thus, the SQL Server provider includes SqlConnection and SqlCommand classes, and the Oracle provider includes OracleConnection and OracleCommand classes. Internally, these classes work quite differently, because they need to connect to different databases using different low-level protocols. Externally, however, these classes look quite similar and provide an identical set of basic methods because they implement the same common interfaces. This means your application is shielded from the complexity of different standards and can use the SQL Server provider in the same way the Oracle provider uses it. In fact, you can often translate a block of code for interacting with a SQL Server database into a block of Oracle-specific code just by editing the class names in your code.

SQL Server Data Provider

Connection - SqlConnection 

Command - SqlCommand 

DataReader - SqlDataReader 

DataAdapter - SqlDataAdapter 

OLE DB Data Provider

Connection - OleDbConnection 

Command - OleDbCommand 

DataReader - OleDbDataReader 

DataAdapter - OleDbDataAdapter 

Oracle Data Provider

Connection - OracleConnection 

Command - OracleCommand 

DataReader - OracleDataReader 

DataAdapter - OracleDataAdapter 

ODBC Data Provider

Connection - OdbcConnection

Command - OdbcCommand

DataReader - OdbcDataReader

DataAdapter - OdbcDataAdapter

Remember, though the underlying technical details differ, the classes are almost identical. The only real differences are as follows:

• The names of the Connection, Command, DataReader, and DataAdapter classes are different in order to help you distinguish them.

• The connection string (the information you use to connect to the database) differs depending on what data source you’re using, where it’s located, and what type of security you’re using.

• Occasionally, a provider may choose to add features, such as methods for specific features or classes to represent specific data types. For example, the SQL Server Command class includes a method for executing XML queries that aren’t part of the SQL standard.