ADO.NET Framework Data Providers
Data provider is used to connect to the database, execute commands and retrieve the record. It is lightweight component with better performance. It also allows us to place the data into DataSet to use it further in our application.
The .NET Framework provides the following data providers that we can use in our application.
| .NET Framework data provider | Description |
|---|---|
| .NET Framework Data Provider for SQL Server | It provides data access for Microsoft SQL Server. It requires the System.Data.SqlClient namespace. |
| .NET Framework Data Provider for OLE DB | It is used to connect with OLE DB. It requires the System.Data.OleDb namespace. |
| .NET Framework Data Provider for ODBC | It is used to connect to data sources by using ODBC. It requires the System.Data.Odbc namespace. |
| .NET Framework Data Provider for Oracle | It is used for Oracle data sources. It uses the System.Data.OracleClient namespace. |
| EntityClient Provider | It provides data access for Entity Data Model applications. It requires the System.Data.EntityClient namespace. |
| .NET Framework Data Provider for SQL Server Compact 4.0. | It provides data access for Microsoft SQL Server Compact 4.0. It requires the System.Data.SqlServerCe namespace. |
.NET Framework Data Providers Objects
Following are the core object of Data Providers.
| Object | Description |
|---|---|
| Connection | It is used to establish a connection to a specific data source. |
| Command | It is used to execute queries to perform database operations. |
| DataReader | It is used to read data from data source. The DbDataReader is a base class for all DataReader objects. |
| DataAdapter | It populates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class. |
.NET Framework Data Provider for SQL Server
Data provider for SQL Server is a lightweight component. It provides better performance because it directly access SQL Server without any middle connectivity layer. In early versions, it interacts with ODBC layer before connecting to the SQL Server that created performance issues.
The .NET Framework Data Provider for SQL Server classes is located in the System.Data.SqlClient namespace. We can include this namespace in our C# application by using the following syntax.
This namespace contains the following important classes.
| Class | Description |
|---|---|
| SqlConnection | It is used to create SQL Server connection. This class cannot be inherited. |
| SqlCommand | It is used to execute database queries. This class cannot be inherited. |
| SqlDataAdapter | It represents a set of data commands and a database connection that are used to fill the DataSet. This class cannot be inherited. |
| SqlDataReader | It is used to read rows from a SQL Server database. This class cannot be inherited. |
| SqlException | This class is used to throw SQL exceptions. It throws an exception when an error is occurred. This class cannot be inherited. |
.NET Framework Data Provider for Oracle
It is used to connect with Oracle database through Oracle client. The data provider supports Oracle client software version 8.1.7 or a later version. This data provider supports both local and distributed transactions.
Oracle Data Provider classes are located into System.Data.OracleClient namespace. We must use both System.Data.OracleClient and System.data to connect our application with the Oracle database.
Which .NET Framework Data Provider is better
Selection of data provider is depends on the design and data source of our application. Choice of optimum .NET Framework data provider can improve the performance, capability and integrity of our application. The following table demonstrates advantages and disadvantages of data provider.
| Data Provider | Note |
|---|---|
| .NET Framework Data Provider for SQL Server | It is good for middle-tier applications, single-tier applications that use Microsoft SQL Server. |
| .NET Framework Data Provider for OLE DB | It is good for single-tier applications that use Microsoft Access databases. |
| .NET Framework Data Provider for ODBC | It is good for middle and single-tier applications that use ODBC data sources. |
| .NET Framework Data Provider for Oracle | It is good for middle and single-tier applications that use Oracle data sources. |