ADO.NET divides its world into two hemispheres: providers and the DataSet. Imagine your kitchen as the world of ADO.NET, with your refrigerator representing the provider, and the oven/stove as the DataSet. The provider "provides" access to some content, such as food, or an Oracle database (which normally appears in the meat-and-cheese drawer). It's a long-term storage facility, and content that goes in there usually stays in there for quite a while. If something is removed, it's because it is no longer valid, or has become corrupted.
To use your oven, you select content from the long-term storage of the refrigerator, update it by mixing, heating, and rearranging the content, and eventually return it to the refrigerator where it will again sit in storage. This analogy isn't perfect; in fact, something just doesn't smell right about it. But it conveys the basic idea: Providers give you access to stored data, some of which can be moved into and processed through an application and its DataSet on a short-term basis.
Large database systems, such as SQL Server and Oracle, are stand-alone "servers" (hence the "SQL Server" name) that interact with client tools and applications only indirectly. These systems generally accept network connections from clients through a TCP/IP port or similar connection. Once authenticated, the client makes all of its requests through this connection before disconnecting from the system.
Back in the early 1990s, Microsoft implemented ODBC (based on other existing standards) as a common system through which clients would connect to database servers, as well as other simpler data sources. Clients no longer had to worry about all of the networking protocols needed to talk with a database; all of that code was included in the ODBC driver.
Microsoft later released a similar data connection system called OLE DB, based on ActiveX technology. OLE DB drivers for common systems soon appeared, although you could still get to ODBC resources through a generic ODBC driver built in OLE DB.
In .NET, both ODBC and OLE DB are replaced by providers, libraries of code that provide all of the communication between the database and your application. Providers are an integral part of ADO.NET, and you will have to use them to get to your databases. Fortunately, providers exist for the main database systems, and an "OleDb provider" exists for systems without their own providers.
Four primary objects make up the programmer's view of the provider.
Using these objects is a little involved, but not hard to understand. To connect to a typical relational database, such as SQL Server, and process data, follow these steps:
Although the .NET Framework includes data providers for a few different data systems, the remainder of this chapter's discussion focuses only on the SQL Server provider, exposed through the System.Data.SqlClient namespace.
SQL Server 2005 includes support for a new feature called "User Instances," for use with SQL Server 2005 Express Edition databases. This feature allows a low-privilege user to access a specific SQL Server Express database file without the need for an administrator to establish SQL Server security settings for that user. This feature is useful in environments where the related software was installed through the new ClickOnce deployment method (discussed in Chapter 24, "Deployment,") without administrator involvement. It also requires specific reconfiguration of the SQL Server Express installation before use. For more information on this feature, reference the "Working with User Instances" article in the ADO.NET portion of the MSDN documentation supplied with your Visual Studio installation.
If you are going to do more than just quickly scan the data that comes back from a DataReader query, you will probably use a DataSet to store, manage, and optionally update your data. Each DataSet provides a generic disconnected view of data, whether it is data from a provider, or data that you build through code. While each provider is tied to a specific database platform (such as Oracle) or communication standard (such as OLE DB), the objects in the DataSet realm are generic, and can interact with any of the platform-specific providers.
Three main objects make up the world of DataSets.
Although data sets are most often used with providers, you can use them independently to build your own in-memory collection of tables and relationships. This is similar to the "client-side record sets" that you could build with pre-.NET ADO objects, although the features included with ADO.NET make the DataSet much more powerful than the older Recordset.
Visual Basic 2005 includes "Typed DataSets," a feature used to integrate a DataSet with a specific data or record format. You may find them useful in your applications, but I won't be discussing them in this book.