Data Layer Strategy

I'm redesigining the data layer for my current client and am using Microsoft SQL Server Management Studio Express with Visual Studio 2008.

I began by diagramming the relationship of their products and reference information in their annual mail-out catalogs. Then we met to discuss the current product lineup and plans for future products. Following this, I produced some UML diagrams and used note cards with dry erase markers to plot the Unique IDs and Foreign Keys and modularized a RDBM system in preparation of the development process. I also began mocking up some basic UI screens for integration and to assist in the data visualization from their internal team perspective.

The API for their web resolves to two applications, a product inventory and a product photo gallery. Each has a role that defines and supports the other. Using my UML architecture and SQL queries, I developed a schema with the appropriate foundation to begin a DB system.

I started the admin implementation with some models in the .NET IDE. Creating GridViews and binding to the SQL DB, I tested the model with embedded data-specific logic in the presentation layer. The method tested was a combination of ADO.NET code in the ASP.NET code and SqlDataSource controls in the markup. While I was able to populate the data easily, the need to create new markup for every procedure was not an optimal solution.

A better approach for the client, in fact, for any client development project, is the abstraction of the data access logic into a separate layer, called the Data Access Layer, or DAL. Using Visual Studio's DataSet template, you can create predefined SELECT, INSERT, UPDATE and DELETE commands to streamline data binding. It frees up the developer to make better decisions in the implementation process.

Using the DAL allows strongly-typed members in the DataTable, an advantage I'll go into next time. For more information, see Designing Data Tier Components and Passing Data Through Tiers.