A Clean Database Connection and Command Architecture for C#.Net

The Darie and Watson architecture for database connection and command building as featured in their book ASP.NET 2.0 E-commerce in C# is a fairly clean methodology for establishing configuration properties, building a generic connection and command builder and querying data using stored procedures.  It involves three separate class files:  a Configuration.cs deigned to hold and return the values of the connectionString and dbProvider,  a GenericDataAccess.cs designed to build the command, the database agnostic DB Factory, receive the stored procedure name,open and close the connection, returning a datatable, and finally, the specific data access file I call DatabaseCustomerService.cs.

Configuration.cs

By placing the program’s configuration settings in a separate file, it is easier to make future changes.  In the specific case of our program’s database connection and querying architecture, the Configuration file simply holds the values of the database connection string and db provider and provides a simple return method.

GenericDataAccess.cs

The Generic Data Access class simply calls the connection string and db provider values from the Configuration.cs file, sets up the database agnostic factory (allowing the developer to use db connections and commands without database specifity, except when the factory is initialized), builds the command., opens the connection, quickly performs its work returning a datatable in this case and quickly closes the connection.

The class and its methods are generic enough that it can be used by multiple objects that need database connection, command and stored procedure execution.  GenericDataAccess accepts a command object as a parameter which holds the stored procedure designation in its CommandText property.   

DatabaseCustomerService.cs

This class serves as the “front counter” to the database and is used to create the command object using a specific store procedure name. It calls GenericDataAccess to build a command and query the database. 

It calls GenericDataAccess to build the command which again uses the database agnostic factory to build the command, it obtains the provider name and connection string from the configuration file and stores them as fields and sets them to the properties of the connection string and command object,  setting the ComandType property of the command object to a stored procedure as well.  The method finishes by setting the CommandText property of the command object to the specific stored procedure name  (comm.CommandText = “Get Customers”) for instance and calls the GenericDataAccess.ExecuteSelectCommand(comm), which returns a datatable … return GenericDataAccess.ExecuteSelectCommand(comm);.