Basics of the .NET Framework: ADO.NET

You use applications not only to create information but also to recall information you've stored. The part of the .NET Framework that interfaces with databases is called ADO.NET.

Let's review how applications that follow the Microsoft model get data into and out of databases. Microsoft's first multidatabase platform for accessing data was Open Database Connectivity (ODBC), an API that developers writing in C use to interact with relational databases. Microsoft's next step was to extend this access model beyond C-language applications and to provide access to tabled databases through the Common Object Model (COM). Then Microsoft developed a more language-agnostic way of interacting with databases: OLE DB. ODBC didn't go away--OLE DB just provided a means of accessing it from languages other than C.

The catch to OLE DB is that its COM objects aren't available to scripting languages such as VBScript. To let scripts access databases, Microsoft added yet another layer: ActiveX Data Objects (ADO). ADO doesn't replace OLE DB; rather, ADO gives applications that can't use OLE DB a way to interact with databases.

ADO.NET is the .NET version of ADO. ADO.NET adds an extra layer to the data-access cake with the addition of SQL Server and OLE DB managed providers. The data-access path for SQL Server applications goes straight from the managed provider to the database. The data-access path for applications that use OLE DB access goes from OLE DB to ODBC before accessing the database.

The differences between ADO and ADO.NET aren't limited to the path that data takes from the database to the application and back again, however. Here's an overview of how ADO.NET reads and writes data store data.

First, ADO.NET doesn't depend on continuous connections to its databases. Traditionally, client/server application components open a connection to a database and keep it open while the application is open. This approach keeps the data available but strains the database server by requiring it to maintain open connections. If all applications keep open connections, then the number of connections that a database can support limits the number of applications--and instances of applications--that can connect to it. In addition, not all applications are able to maintain live connections to their data stores. Web applications in particular can't rely on a live connection. For these reasons, ADO.NET connects applications to their data stores only long enough to read or write the appropriate data. This connection architecture better supports Web-based applications and makes database-dependent applications scale better.

Second, because ADO.NET uses managed providers, it interacts with its data stores differently than other data-access models do. Applications usually execute SQL statements or stored procedures to read or interact with a database. ADO.NET packages these statements and stored procedures within data commands. For example, if you want to read a set of rows from the database, you create a data command and configure it with the text of a SQL Select statement or the name of a stored procedure that fetches records. The data commands open the connection, execute the SQL statement or stored procedure, and close the connection. If you need to perform more than one operation, you use multiple data commands.

Third, applications that use ADO.NET can cache data in datasets. The idea behind this approach is that applications frequently need not just one record but a set of records. Rather than going back to the database repeatedly for records, one option is to cache the records retrieved from the database and work from this cache. An object in an application can even send its dataset to another part of the application that needs the same data but would have had to query the database itself. To update a database by updating the dataset, an application uses data adapters. A data adapter typically contains four commands, one each to select, insert, update, and delete rows in the database. Datasets aren't specific to a particular database. A dataset can contain information from multiple databases, or one database and one spreadsheet. After data is in the dataset, the data's origin doesn't matter--the application can work with it all in the same way.

Finally, data stored in a dataset is transferred as XML. The ADO.NET data APIs automatically create XML files or streams from information in the dataset and send them to another component, then translate them back as they enter their new dataset. Data isn't stored in the dataset as XML, it's only transferred in XML format to ensure interoperability among applications. In addition, XML is text-based, so it can pass through firewalls that block binary files.

End of Article