ActiveX Data Objects for the .NET Framework
From SwinBrain
The ActiveX Data Object for the .NET Framework, or ADO.NET for short, is a library used by .NET code to connect to databases. ADO.NET provides a number of interfaces and abstract classes that can be implemented by DBMS providers to allow .NET code to interact with their products.
ADO.NET Architecture
The first step of programming with a database is to determine how you are going to connect to the database, with ADO.NET you uses a DbConnection object. This abstract class is implemented by DBMS vendors who then provide the concrete classes used to access their databases. The DBMS specific implementations are called Managed Providers in the ADO.NET model. Developers can choose to use the managed provider directly, or can program against the more generic abstract classes. Examples of managed providers include the providers for SQL Server, Oracle, and Firebird.
Once you have created a DbConnection to the database, you can create a DbCommand object that is used to run SQL commands on the database. When the command object has been created you set its Connection property to the DbConnection object that provides the connection to the database, then set the CommandText property to the SQL text you want to execute. The DbCommand object also includes the ability to run stored procedures, and to accept parameters.
Now that the DbCommand has the SQL text to run and the connection to the database, you can call its ExecuteReader] method. This method executes the command's CommandText on the database and returns a DbDataReader that can be used to read the values from the first result set. Using the DbDataReader your code can read the values from the result set. You can access additional result sets by calling the DbDataReader's NextResult method.
The DbCommand also offers others methods used to execute it on the database. The following list describes the three methods and when they should be used.
- ExecuteReader: used to execute a query, SQL that returns at least one result set. The DbDataReader can then be used to read values from the database.
- ExecuteNonQuery: used to execute any SQL that does not return a result set, such as INSERT, UPDATE, and DELETE commands.
- ExecuteScalar: used when the query will return a single value in the result set. This will execute the query and return the value at the first row, first column of the result set. This is useful if you want to read the value from the SELECT which uses an aggregate function such as SUM, COUNT, etc.
Becoming Provider Independent
ADO.NET provides a number of abstract classes that different providers implement to allow access to databases that use their DBMS. So far we have talked about the main classes that are involved in this, but how can you create them in a provider independent manner? With ADO.NET 2.0 Microsoft provides the answer, the DbProviderFactories class. This class provides a factory method that can be used to fetch a DbProviderFactory, which is itself an abstract factory. Using the DbProviderFactory you can create the DbConnections, DbCommands, and other ADO.NET classes in a provider independent manner. The DbProviderFactories GetFactory method is used to fetch the DbProviderFactory, which then provides the required Create methods. The DbProviderFactories initially contains the managed providers that ship with .NET, though additional providers can be added via configuration files.
Links
SwinBrain links:
- ADO.NET, Getting Started Sample a walk through that illustrates the basic components involved in a ADO.NET 2 solution, source code in C#.
- ADO.NET Sample Solution: This solution implement a simple database component that allows you to fetch and update information using a SqlDataAdapter. Once you download this example you will need to run the create query on a pre-existing database. You will also need to setup the connection string inside of the database component. Once this is done the sample should execute.
External links related to this material.
- The main classes are described in the article Architecture of ADO.NET on MSDN.