Fundamentals of ADO.NET

Microsoft split ADO.NET into two camps: the SqlClient camp and the OleDb camp. The SqlClient camp represents Microsoft SQL Server 7.0 and higher, and the OleDb camp handles everything else, including Microsoft ACCESS, Microsoft SQL Server 6.5, Oracle, UDB, Sybase, Informix, and others. The notion of a camp refers to database providers. In addition to the two-camp model, Microsoft's .NET Framework supports extending ADO.NET by allowing vendors to implement their own provider classes. The result is that any database supplier could create its own custom provider classes for ADO.NET.

ADO.NET in general is made up of the System.Data and System.Xml namespaces. Classes that are general to ADO.NET are typically found in the System.Data namespace. For example, DataSet , a general class shared between providers, is defined in System.Data . Classes that are specific to a provider camp can be found in System.Data.OleDb or System.Data.SqlClient . For classes in either camp to work with the general DataSet class, each camp must support the same basic capabilities. For example, to get data into a DataSet class you need a connection. There is a connection class in the OleDb namespace as well as one in the SqlClient namespace. These namespace-specific classes employ a prefix naming convention: OleDb classes are prefixed with OleDb , and SqlClient classes are prefixed with Sql . Thus the OleDb connection class is suitably named OleDbConnection ; the SqlClient connection class, SqlConnection . For the most part both the OleDb and SqlClient connection classes implement the same members and operations. As a result of the similarities in OleDb and SqlClient classes, if you learn to use one or the other, you can easily use its alternative provider counterpart .

Two other important namespaces related to System.Data are System.Data.Common and System.Data.SqlTypes . The Common namespace defines classes for permissions, table column mappings, and some event argument types. The SqlTypes namespace defines native types for SQL Server.

As a general rule, you will most often use the System.Data and System.Xml namespaces and one or the other of the provider namespaces, OleDb or SqlClient . I will be using the OleDb providers in this chapter because I can reasonably gamble on your having a copy (or being able to get one) of the Northwind database. For informational purposes, it is worth noting that most of the code in this chapter could easily be substituted for classes in the SqlClient namespace, if you are using a newer version of Microsoft SQL Server. However, the OleDb classes represent the classes you will use for the greatest diversity of database providers.