Database Access with ADO.NET

ADO.NET is an interface library that provides access to various kinds of data providers, such as SQL, OLE DB, and ODBC. Although each data provider has its own set of classes, they are conceptually the same. The main classes are as follows:

• Connection: Used to establish a connection to a database; this connection is necessary to execute a command or read data. To connect to an SQL database, you use the SqlConnection class; an OdbcConnection class is used to connect to an ODBC data provider.
• Command: Executes a database command such as SELECT, INSERT, or UPDATE. Requires a database connection.
• DataReader: Exposes the data resulting from the execution of a SELECT command.

Although the classes that establish a database connection and execute commands are specific to the type of the data source, the database commands themselves are all based on SQL and are mostly compatible between the various kinds of data sources. The commands needed for this project are SELECT, INSERT, and UPDATE.

To promote cost efficiency, we store the customer information in a Microsoft Excel sheet, which is specified in the application's configuration file. The ODBC service has an Excel driver that can be used to store the customer records in an XLS file. However, we will encapsulate all database dependencies in an additional layer to allow the replacement of the data provider later based on growth of the business.

Figure 12.3 shows the class diagram for the data access layer. DataTable is a simple .NET Framework class for caching the content of a database table, in our case the customer table. CustomerDB is an abstract class providing higher-level API functions specific to the kind of operations performed by Online Photo Shopnamely, validating login credentials and creating a new user account. CustomerDB also defines an abstract method to create a new DbDataAdapter instance. This method must be implemented by inherited classes because it is specific to the data source type. For now, SqlCustomerDB will not be implemented. It is shown only to visualize how this possible future change can be addressed.

Figure 12.3. Class Diagram for Database Access Utilities