Difference between revisions of "Data Access Layer - AC Gold"

Latest revision as of 05:41, 8 November 2013

The Database

AC Gold database design is probably one of the best among the available shopping cart solutions. If you know a bit of databases, AC Gold database design is very easy to understand and follow. There are a few areas (e.g. Category and Catalog tables) 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

Ablecommerce Gold makes use of NHibernate for data access. For the most part we just have to deal with the 'Objects' in C#. The code that we have written complies with 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. You need to create a Class Library Project and put all your custom classes and HBM files in it. Finally you will reference the library in AbleCommerce website.

Affiliates in Database

Here is how Affiliates are defined in database

CREATE TABLE ac_Affiliates ( 
	AffiliateId INT IDENTITY NOT NULL,
	StoreId INT NOT NULL,
	Name NVARCHAR(100) NOT NULL,
	PayeeName NVARCHAR(100) NULL,
	FirstName NVARCHAR(30) NULL,
	LastName NVARCHAR(50) NULL,
	Company NVARCHAR(50) NULL,
	Address1 NVARCHAR(100) NULL,
	Address2 NVARCHAR(100) NULL,
	City NVARCHAR(50) NULL,
	Province NVARCHAR(50) NULL,
	PostalCode NVARCHAR(15) NULL,
	CountryCode CHAR(2) NULL,
	PhoneNumber NVARCHAR(50) NULL,
	FaxNumber NVARCHAR(50) NULL,
	MobileNumber NVARCHAR(50) NULL,
	WebsiteUrl NVARCHAR(255) NULL,
	Email NVARCHAR(255) NULL,
	CommissionRate DECIMAL(9,4) NOT NULL,
	CommissionIsPercent BIT NOT NULL,
	CommissionOnTotal BIT NOT NULL,
	ReferralPeriodId TINYINT NOT NULL,
	ReferralDays SMALLINT NOT NULL,
	GroupId INT NULL,
	PRIMARY KEY(AffiliateId))

Affiliates in Code

In Ablecommerce Gold .NET code there are a total of 9 C# files related to affiliates. This is almost always the case for any database object represented in AC Gold.

Affiliate.cs
Affiliate.Generated.cs
Affiliate.hbm.xml
IAffiliateRepository.cs
IAffiliateRepository.Generated.cs
AffiliateRepository.cs
AffiliateRepository.Generated.cs
AffiliateDataSource.cs
AffiliateDataSource.Generated.cs

Five of the above files are generated. Four of them are custom coded. Although there are 9 files there are only 3 classes and one interface(thanks to C# partial classes). The 'Affiliate' class that represents an affiliate and 'Affiliate.hbm' represents the nhibernate mappings. The 'IAffiliateRepository', and 'AffiliateRepository' class that provide repository implementation for Affiliate object for example load/save methods. AffiliateDataSource class is simply a wrapper around AffiliateRepository and does have same methods as repository class does but instead of using different logic for the methods it just uses repository class in its methods. The major purpose of these DataSource classes is to provide backward code compatibility plus to comply with ASP.NET Object Data Source model.

Entity

Entity object encapsulates the data for corresponding database table. For example in case of Affiliates Affiliate.cs, Affiliate.Generated.cs and Affiliate.hbm.xml provides the entity object implementations where CS files provide data and navigation properties and HBM file provides nhibernate mappings. All entity objects either extend from CommerceBuilder.DomainModel.Entity or CommerceBuilder.DomainModel.EntityWithTypedId<T> where T can be class representing composite key. These base classes does provide some useful functionality to these Entity objects for example Save() and Delete() methods.

Below are the contents of Affiliate.hbm file or nhibernate mappings for affiliate entity.

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <!-- Copyright (c) 2010 Able Solutions Corporation. All rights reserved. -->
  <class name="CommerceBuilder.Marketing.Affiliate,CommerceBuilder" table="ac_Affiliates" lazy="true" dynamic-update="true">
    <id name="Id" column="AffiliateId" type="int">
      <generator class="native" />
    </id>
    <many-to-one name="Store" column="StoreId" not-null="true" class="CommerceBuilder.Stores.Store,CommerceBuilder" />
    <property name="Name" column="Name" type="string" not-null="true" />
    <property name="PayeeName" column="PayeeName" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="FirstName" column="FirstName" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="LastName" column="LastName" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="Company" column="Company" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="Address1" column="Address1" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="Address2" column="Address2" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="City" column="City" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="Province" column="Province" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="PostalCode" column="PostalCode" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="CountryCode" column="CountryCode" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="PhoneNumber" column="PhoneNumber" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="FaxNumber" column="FaxNumber" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="MobileNumber" column="MobileNumber" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="WebsiteUrl" column="WebsiteUrl" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="Email" column="Email" type="CommerceBuilder.DomainModel.NullSafeString, CommerceBuilder" />
    <property name="CommissionRate" column="CommissionRate" type="Decimal" not-null="true" />
    <property name="CommissionIsPercent" column="CommissionIsPercent" type="Boolean" not-null="true" />
    <property name="CommissionOnTotal" column="CommissionOnTotal" type="Boolean" not-null="true" />
    <property name="ReferralPeriodId" column="ReferralPeriodId" type="byte" not-null="true" />
    <property name="ReferralDays" column="ReferralDays" type="short" not-null="true" />
    <many-to-one name="Group" column="GroupId" class="CommerceBuilder.Users.Group,CommerceBuilder" />
    <bag name="Orders" inverse="true" lazy="true" cascade="save-update" collection-type="CommerceBuilder.DomainModel.PersistableList`1[[CommerceBuilder.Orders.Order,CommerceBuilder]], CommerceBuilder">
      <key column="AffiliateId" />
      <one-to-many class="CommerceBuilder.Orders.Order,CommerceBuilder" />
    </bag>
    <bag name="Users" inverse="true" lazy="true" cascade="save-update" collection-type="CommerceBuilder.DomainModel.PersistableList`1[[CommerceBuilder.Users.User,CommerceBuilder]], CommerceBuilder">
      <key column="AffiliateId" />
      <one-to-many class="CommerceBuilder.Users.User,CommerceBuilder" />
    </bag>
  </class>
</hibernate-mapping>

Below piece of code will save a new affiliate in database.

 Affiliate affiliate = new Affiliate();
 affiliate.Name = "John Smith";
 affiliate.ReferralPeriod = AffiliateReferralPeriod.Persistent;
 affiliate.Save();

Repository

Repository holds the logic related to data save/load to/from database. In case of Affiliates IAffiliateRepository.cs IAffiliateRepository.Generated.cs, AffiliateRepository.Generated.cs and AffiliateRepository.cs hold the repository implementations. Though there are four files but actually we are just dealing with one interface and one class by using partial class to keep the custom and generated code separate from each other. Every repository class implements the corresponding repository interface and extends either from CommerceBuilder.DomainModel.Repository<T> or CommerceBuilder.DomainModel.RepositoryWithTypedId<T,K> where T is entity type, K is composite key type. These class provides some common functionality for example Load, LoadAll, CountAll, Save and Delete methods. Following example code explain how to use repository object.

// USING IOC TO RESOLVE REPOSITORY
IAffiliateRepository repository = AbleContext.Container.Resolve<IAffiliateRepository>();
            
// LOAD SINGLE AFFILIATE BY ID
Affiliate affiliate = repository.Load(1);

// LOAD ALL AFFILIATES
System.Collections.IList<Affiliate> affiliates = repository.LoadAll();

// SAVE AFFILIATE
affiliate.ReferralPeriod = AffiliateReferralPeriod.FirstOrder;
repository.Save(affiliate);

// DELETE AFFILIATE
repository.Delete(affiliate);

Data Source

The main purpose of Data Source classes is to allow to work with ASP.NET Data Source Object Model. In case of Affiliates AffiliateDataSource.cs and AffiliateDataSource.Generated.cs implement data source. This data source can be considered as a wrapper around repository because it does have similar methods as we do on repository but instead of doing any database interaction all it does it to pass the information to underlying repository object. Every data source either extends CommerceBuilder.DomainModel.DataSource<T,R> or CommerceBuilder.DomainModel.DataSourceWithTypedId<T,K,R> where T is entity type, K is composite key type and R is repository type. Following code explain the Data Source object usage.

// LOAD SINGLE AFFILIATE BY ID
Affiliate affiliate = AffiliateDataSource.Load(1);

// LOAD ALL AFFILIATES
System.Collections.IList<Affiliate> affiliates = AffiliateDataSource.LoadAll();

// UPDATE AFFILIATE
affiliate.ReferralPeriod = AffiliateReferralPeriod.FirstOrder;
AffiliateDataSource.Update(affiliate);

// DELETE AFFILIATE
AffiliateDataSource.Delete(affiliate);