Data Access Layer - AC7

Questions have been coming regarding the Data Access Layer (DAL) of Ablecommerce7. In this thread I will post a brief overview of how AC7 Data Access works.

The Database: AC7 database design is probably one of the best among the available shopping cart solutions. If you know a bit of databases, AC7 database design is very easy to understand and follow. There are a few areas (e.g. catalog management) that are slightly complex but in general you can just look at the database tables/fields and get a fair idea of what they are meant for.

Accessing Database Ablecommerce7 has a standard pattern of accessing the database tables. We have a custom code generator that generates most of the database access code. For the most part we just have to deal with the 'Objects' in C#. The code generated by our code generator and the other customized code that we write complies the ASP.NET Data Source Object Model. This helps a great deal in using these objects directly in ASP.NET.

Here I will take the example of Affiliates and explain how they are represented/accessed in the database, in the C# code and in the ASP.NET code.

Affiliates in Database: Here is how Affiliates are defined in database

CREATE TABLE [dbo].[ac_Affiliates](
   [AffiliateId] [int] IDENTITY(1,1) NOT NULL,
   [StoreId] [int] NOT NULL,
   [Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [PayeeName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [FirstName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Company] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Address1] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Address2] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Province] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [CountryCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [PhoneNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [FaxNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [MobileNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [WebsiteUrl] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Email] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [CommissionRate] [decimal](9, 4) NOT NULL,
   [CommissionIsPercent] [bit] NOT NULL,
   [CommissionOnTotal] [bit] NOT NULL,
   [ReferralDays] [smallint] NOT NULL,
CONSTRAINT [ac_Affiliates_PK] PRIMARY KEY CLUSTERED
(
   [AffiliateId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ac_Affiliates]  WITH CHECK ADD  CONSTRAINT [ac_Stores_ac_Affiliates_FK1] FOREIGN KEY([StoreId])
REFERENCES [dbo].[ac_Stores] ([StoreId])
GO
ALTER TABLE [dbo].[ac_Affiliates] CHECK CONSTRAINT [ac_Stores_ac_Affiliates_FK1]