What Is ADO.NET?

for RuBoard

ADO.NET is Microsoft's platform for data access in its new .NET Framework. Out of the box, ADO.NET is scalable, interoperable, and familiar enough to ADO developers to be immediately usable. By design, the ADO.NET object model and many of the ADO.NET code constructs will look very familiar to ADO developers.

At the most basic level, ADO.NET is a set of framework namespaces, specifically :

• System.Data

• System.Data.Common

• System.Data.SqlClient

• System.Data.OleDbClient

• System.Data.SqlTypes

The System.Data namespace contains many of the objects upon which ADO.NET is built. This is where you'll find the DataTable , DataSet , DataRelation , and DataView objects. Additionally, this is where ADO.NET constants are stored. For instance, the System.Data.SqlDbType class shown in Figure 1.2 contains all the Microsoft SQL data types.

Figure 1.2. The System.Data.SqlDbType class contains constant values for all the data types in Microsoft SQL Server version 7.0 and higher.

The System.Data.SqlClient namespace contains objects designed to work with a Microsoft SQL Server database, version 7.0 and higher. This is where you'll find familiar objects such as SqlCommand , SqlConnection , and SqlParameter as well as new faces such as SqlDataAdapter and SqlDataReader . If you're a SQL developer, this namespace will be your sandbox for most of this book. The namespace uses a managed SQL provider to work with the database. By working directly with SQL database APIs, SqlClient bypasses ODBC (Open Database Connectivity) and OLE DB (Object Linking and Embedding for Databases) entirely, offering a very robust and efficient interface.

The System.Data.OleDbClient namespace is designed to work with any valid OLE DB source. This includes data sources as varied as Oracle databases, Microsoft Excel files, standard ASCII comma-delimited text files, and Microsoft Access files, as well as versions of Microsoft SQL Server prior to version 7.0. As mentioned earlier, if you're working with Microsoft SQL Server 7.0 or higher, the System.Data.SqlClient offers a much better way to work with your data.

OLE DB, ODBC, and Managed Providers (Oh My!)

You've probably heard the terms OLE DB, ODBC, and managed provider used when speaking of connecting to databases, but you might not know how to distinguish each from the others.

Back in the dark ages ( roughly 15 years ago), the prospect of retrieving data from a database was much more difficult than it is today. For each separate database type, you would have to learn that system's interfaces to retrieve any data. The interfaces of any given database system could be wildly different than any of the others because there were no standards.

By the late 80s, several vendors (including IBM and Microsoft) realized that it would be a good idea to offer programmers a standardized database interface. By factoring a standard interface from the mire of proprietary APIs used by the database system, programmers only had to learn one API instead. This standard API is known as ODBC.

OLE DB is much like ODBC, but based on COM (Component Object Model). OLE DB offers much better performance than ODBC, but it is only available to Microsoft-based solutions.

ODBC and OLE DB are both layers that exist between application code and the database. As such, they are not as fast as interfacing with the database system directly, in its language. The developers of ADO.NET created a namespace that works with Microsoft SQL Server (versions 7.0 and up) using its native APIs. Because the code connects directly to SQL Server and is managed by the framework, it's known as a managed provider.

Regardless of which method is used to connect to the database, ADO.NET provides a single interface for retrieving data. You don't need to worry about the underlying connection method.

The System.Data.OleDbClient namespace mirrors the System.Data.SqlClient namespace almost precisely. In fact, if you scan the classes in both, you'll notice that the class names differ only by their preface (for example, SqlCommand versus OleDbCommand ). Fortunately, after you've worked with one namespace, you've learned how to use both.

for RuBoard