Communicating with OLEDB Data Sources Using ADO.NET

Communicating with OLEDB Data Sources Using ADO.NET

Every application that needs to retrieve data from a database needs to establish a connection to the database. In ADO, this was achieved using the Connection object. InADO.NET, the classes to be used for establishing the connection depend on the data source being used. For instance, to connect to SQL Server databases, the classes in the System.Data.SqlClient namespace are used. To connect to OLE DB data sources, you need to use classes in the System. Data.OleDb namespace. It must be noted that both the SQL Server and OLE DB providers are managed providers. These providers act as a thin layer that connects the application to the database without adding any unnecessary overhead, such as converting from OLE DB -compatible data types to native SQL Server data types and vice versa when communicating between the client and the server. The SQL Server data provider, for example, does not depend on OLE DB/ODBC. Instead, it uses the Tabular Data Stream (TDS) protocol of SQL Server to natively communicate with the SQL Server. The use of the TDS provides a tremendous performance boost to applications. They have same types of private connection mode:

§                                       1.       OledbConnection class

2.      OledbDataReader class

3.      OledbCommand class

4.      OledbDataAdapter class

1. OleDbConnection class

This class encapsulates the connection to an OLE DB data source. Applications that need to use an OLE DB provider to connect to the data source should use this class, because not all data sources will have managed .NET data providers. When the .NET Framework is shipped by Microsoft, it provides managed data providers for some of the popular DBMSs like MS Access and SQL Server. But, the data providers for the otherDBMSs will be developed by the respective vendors. To avoid waiting for the availability of managed data providers, ADO.NET has the option of connecting to any OLE DBcompliant data sources. The OLE DB provider makes it easier for the application to be migrated to ADO.NET. All the features of ADO.NET can be readily used in an application without having to depend on the availability of a managed .NET data provider.

                          

2. OleDbCommand class

This class encapsulates the commands that need to be sent to the OLE DB data source. Applications use the OleDbCommand class to create select, insert, update, and delete commands that need to be sent to the data source. Also, this class can be used to execute stored procedures besides sending input parameters to the stored procedure and retrieving output parameters from the stored procedure.

3. OleDbDataReader class

This class is equivalent to a forward-only, read-only RecordSet object in classic ADO. This class is very useful to all applications that want to retrieve data returned from a query to the database and want to process one record at a time. A classic example for this would be to populate a list box with values retrieved from, say, and a master table. The OleDbDataReader is independent of the OLE DB data source from which the data is retrieved. The process of reading data using the OleDbDataReader object is similar to reading data from any stream.

4. OleDbDataAdapter class

The data adapter acts as the conduit between the client application and the database connection, command objects. The data adapter represents the command and connection that are used to populate the client dataset. In case of a disconnected client, the data adapter has the responsibility of firing the appropriate insert, update, or delete commands onto the database to synchronize the changes that are recorded in the client dataset. The OleDbDataAdapter class has three command properties that are used to update the database:-

InsertCommand: Represents the query or stored procedure that is used to insert new records into the data source.                                                            .

SelectCommand: Represents a SQL statement used to select records in the data source.

DeleteCommand: Represents a SQL statement for deleting records from the data set.

https://www.youtube.com/channel/UCKLRUr6U5OFeu7FLOpQ-FSw/videos