Introduction to ADO.NET
This article is an introduction to ADO.NET. It introduces primary ADO.NET concepts and objects that you will learn about in later articles.
Introduction
ADO.NET is an object-oriented managed set of libraries that allows you to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spread sheet, or an XML file. For the purposes of this tutorial, we will look at ADO.NET as a way to interact with a database.
As you are probably aware, there are many different types of databases available. For example, there is Microsoft SQL Server, Microsoft Access, Oracle, Sybase, and IBM DB2. We would use MS SQL Server in our article as database.
ADO.NET Architecture:
Data Providers:
ADO.NET allows .net applications to interact with different types of data sources and different types of databases. Since different data sources expose different protocols, we need a way to communicate with the right data source using the right protocol. Hence different data providers are available.
The following table outlines the four core objects that make up a .NET Framework data provider.
|
Object
|
Description
|
|
Connection
|
Establishes a connection to a specific data source.
|
|
Command
|
Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection.
|
|
DataReader
|
Reads a forward-only, read-only stream of data from a data source.
|
|
DataAdapter
|
Populates a DataSet and resolves updates with the data source.
|
Some older data sources use the ODBC protocol, many newer data sources use the OLE DB protocol, and there are more data sources every day that allow you to communicate with them directly through .NET ADO.NET class libraries.
ADO.NET provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can talk to a data source. These libraries are called Data Providers and are usually named for the protocol or data source type they allow you to interact with. Below table lists some well-known data providers, the API prefix they use, and the type of data source they allow you to interact with.
ADO.NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols. The library APIs has prefixes that indicate which provider they support.
|
Provider Name
|
API prefix
|
Data Source Description
|
|
ODBC Data Provider
|
ODBC
|
Data Sources with an ODBC interface. Normally older databases such as Sybase.
|
|
OleDb Data Provider
|
OLE DB
|
Data Sources that expose an OLE DB interface, i.e. Access or Excel. Its called universal data provider also because we can connect almost all kind of data source with it.
|
|
Oracle Data Provider
|
Oracle
|
For Oracle Database. It is optimized to connect with oracle only, so it is the best way if we have to use oracle database in .NET application.
|
|
Borland Data Provider
|
Bdp
|
Generic access to many databases such as SQL Server, IBM DB2, and Oracle.
|
|
SQL Data Provider
|
Sql
|
For interacting with Microsoft SQL Server. It is optimized to connect with MS SQL Server only, so it is the best way if we have to use MS SQL Server database in .NET application.
|
Comparison of the .NET Framework Data Provider for SQL Server and the .NET Framework Data Provider for OLE DB
The Connection Object:
The connection object provides to make physical connection with the database. It is similar to the network connection in client server. Connection object is used by Command object and Adapter object.
The Command Object
Command object is used to send SQL statement to the data source. There are four type of action can be taken Select, Delete, Update, and insert. A command object uses a connection object to figure out which database is connected.
The DataReader Object
The data reader object allows you to obtain the results of a SELECT statement from a command object. It is read only and forward only. For performance reasons, the data returned from a data reader is a fast forward-only stream of data. This means that you can only pull the data from the stream in a sequential manner. This is good for speed, but if you need to manipulate data, then a DataSet is a better object to work with.
The DataSet Object
DataSet objects are disconnected in-memory representations of data source. They contain multiple Data table objects, which contain columns and rows, just like normal data base tables. We can even define relations between tables to create parent-child relationships. The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.
The DataAdapter Object
The DataAdapter serves as a bridge between a DataSet and a data source for retrieving and saving data. It can be send to retrieve. Manipulate and update the data source.
Summary
ADO.NET is the .NET technology for interacting with data sources. Several Data Providers are available to communication with different data sources. Regardless, of which Data Provider used, you'll use a similar set of objects to interact with a data source. The Connection object lets you manage a connection to a data source. Command object used sending SQL statements to data source. To have fast forward only, read access to data, use the DataReader. To work with disconnected data source, use a DataSet and implement reading and writing to/from the data source with a DataAdapter.