Communicating OLEDB 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. In ADO.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.

System.Data.OleDb.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 other DBMSs 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.

System.Data.OleDb.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.

System.Data.OleDb.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, 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.

System.Data.OleDb.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.

System.Data.DataSet,system.Data.DataTable,System.Data.DataRow,and System.Data.DataColumn classes

The DataSet is a generic class provided by the .NET Framework. This class is very useful on the client side to store data in a manner that is much more functional and powerful than the ADO Recordset object. Moreover, the data in a DataSet is in XML format, and therefore is readily accessible and manageable. The XML format makes it very well suited to Web applications, and makes cross-platform access possible.

The DataSet in memory is quite similar to a full-blown, in-memory DBMS in that it has the ability to store data from multiple tables and the relationships between them. The tables are stored in DataTable objects, and DataRelation objects represent the relationship between tables. The rows and columns in a table are stored in DataRow and DataColumn objects, respectively.

related post

CREATING ASSEMBLIES WITH ASP.NET

BUILDING HANDLERS IN ASP.NET

INTERFACES AND CLASSES CREATION IN ASP.NET

CACHING IN ASP.NET

CACHING IN ASP.NET PART TWO

WIRE LESS APPLICATION WITH ASP.NET

SECURITY IN ASP.NET PART ONE

SECURITY IN ASP.NET PART TWO

LOCALIZING ASP.NET APPLICATIONS

DEPLOYING ASP.NET APPLICATIONS

ASP.NET CONFIGURATION SYSTEM

WEB SERVICES IN ASP.NET

WEB SERVICES PART TWO

WEB SERVICE INFRASTRUCTURE

WEB SERVICE INFRASTRUCTURE PART TWO

EXCHANGING MESSAGES IN ASP.NET

MICROSOFT HAIL STORM

SOAP AND DOT NET PART ONE

SOAP AND DOT NET PART TWO


No comments:

Post a Comment