Data Access Layer - AC7
From AbleCommerce Wiki
Questions have been coming regarding the Data Access Layer (DAL) of Ablecommerce7. 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.
Contents
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]
Affiliates in Code
In Ablecommerce7 .NET code there are a total of 5 C# files related to affiliates. This is almost always the case for any database object represented in AC7.
Affiliate.cs
AffiliateDataSource.cs
Affiliate.Generated.cs
AffiliateCollection.Generated.cs
AffiliateDataSource.Generated.cs
Three of the above files are generated. Two of them are custom coded. Although there are 5 files there are only 3 classes (thanks to C# partial classes). The 'Affiliate' class that represents an affiliate, 'AffiliateCollection' class that represents a collection of Affiliate objects and AffiliateDataSource class implements DataSource methods for Affiliate objects. Usually customization is not needed for collection classes but if it is needed one can create another file AffiliateCollection.cs.
Because almost all of the useful code is already generated the custom code files have very little code.
Code from Affiliate.cs
//Affiliate.cs
using System;
using CommerceBuilder.Common;
namespace CommerceBuilder.Marketing
{
/// <summary>
/// This class represents a Affiliate object in the database.
/// </summary>
public partial class Affiliate
{
/// <summary>
/// Calculates commission for this affiliate
/// </summary>
/// <param name="orderCount">Number of orders</param>
/// <param name="productSubtotal">Sub total of the products</param>
/// <param name="orderTotal">Total value of orders</param>
/// <returns>The calculated commission</returns>
public LSDecimal CalculateCommission(int orderCount, LSDecimal productSubtotal, LSDecimal orderTotal)
{
if (this.CommissionIsPercent)
{
if (this.CommissionOnTotal) return Math.Round((((Decimal)orderTotal * (Decimal)this.CommissionRate) / 100), 2);
return Math.Round((((Decimal)productSubtotal * (Decimal)this.CommissionRate) / 100), 2);
}
else
{
return (orderCount * this.CommissionRate);
}
}
}
}
Code from AffiliateDataSource.cs
//AffiliateDataSource.cs
using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Practices.EnterpriseLibrary.Data;
using CommerceBuilder.Common;
using CommerceBuilder.Users;
using CommerceBuilder.Utility;
using System.ComponentModel;
using System.Web.Security;
namespace CommerceBuilder.Marketing
{
[DataObject(true)]
public partial class AffiliateDataSource
{
/// <summary>
/// Gets a count of users who were referred by the affiliate during the given timeframe.
/// </summary>
/// <param name="affiliateId">Id of the affiliate to count users for.</param>
/// <param name="startDate">Inclusive start date of timeframe to count.</param>
/// <param name="endDate">Inclusive end date of timeframe to count.</param>
/// <returns>The number of new visitors referred during the given timeframe.</returns>
public static int GetReferralCount(int affiliateId, DateTime startDate, DateTime endDate)
{
Database database = Token.Instance.Database;
StringBuilder sql = new StringBuilder();
sql.Append("SELECT COUNT(*) AS TotalRecords FROM ac_Users WHERE ReferringAffiliateId = @affiliateId");
if (startDate > DateTime.MinValue) sql.Append(" AND CreateDate >= @startDate");
if (endDate > DateTime.MinValue) sql.Append(" AND CreateDate <= @endDate");
DbCommand selectCommand = database.GetSqlStringCommand(sql.ToString());
database.AddInParameter(selectCommand, "@affiliateId", System.Data.DbType.Int32, affiliateId);
if (startDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@startDate", System.Data.DbType.DateTime, startDate);
if (endDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@endDate", System.Data.DbType.DateTime, endDate);
return CommerceBuilder.Utility.AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
}
/// <summary>
/// Calculates the percentage of referred users who made purchases for a given timeframe.
/// </summary>
/// <param name="affiliateId">Id of the affiliate to calculate conversion rate for.</param>
/// <param name="startDate">Inclusive start date of timeframe to calculate.</param>
/// <param name="endDate">Inclusive end date of timeframe to calculate.</param>
/// <param name="totalReferrals">The number of referrals for the timeframe.</param>
/// <returns>The conversion rate for the affiliate for the given timeframe.</returns>
public static LSDecimal GetConversionRate(int affiliateId, DateTime startDate, DateTime endDate, int totalReferrals)
{
if (totalReferrals == 0) return 0;
Database database = Token.Instance.Database;
StringBuilder sql = new StringBuilder();
sql.Append("SELECT COUNT(DISTINCT ac_Users.UserId) AS TotalRecords");
sql.Append(" FROM ac_Users INNER JOIN ac_Orders ON ac_Users.UserId = ac_Orders.UserId");
sql.Append(" WHERE ac_Users.ReferringAffiliateId = @affiliateId");
if (startDate > DateTime.MinValue) sql.Append(" AND ac_Users.CreateDate >= @startDate");
if (endDate > DateTime.MinValue) sql.Append(" AND ac_Users.CreateDate <= @endDate");
DbCommand selectCommand = database.GetSqlStringCommand(sql.ToString());
database.AddInParameter(selectCommand, "@affiliateId", System.Data.DbType.Int32, affiliateId);
if (startDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@startDate", System.Data.DbType.DateTime, startDate);
if (endDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@endDate", System.Data.DbType.DateTime, endDate);
int convertedCustomers = CommerceBuilder.Utility.AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
LSDecimal percentage = (LSDecimal)convertedCustomers / (LSDecimal)totalReferrals;
percentage = percentage * 100;
percentage = Math.Round((Decimal)percentage, 2);
return percentage;
}
}
}
Code for AffiliateCollection.Generated.cs
It is very simple as it just extends our existing 'PersistantCollection' class.
namespace CommerceBuilder.Marketing
{
using System;
using CommerceBuilder.Common;
/// <summary>
/// This class implements a PersistentCollection of Affiliate objects.
/// </summary>
public partial class AffiliateCollection : PersistentCollection<Affiliate>
{
/// <summary>
/// Gets the index of the CatalogNode object in this collection whose primary key
/// matches the given value.
/// </summary>
/// <param name="affiliateId">Value of AffiliateId of the required object.</param>
/// <returns>Index of the required object.</returns>
public int IndexOf(Int32 affiliateId)
{
for (int i = 0; i < this.Count; i++)
{
if (affiliateId == this[i].AffiliateId) return i;
}
return -1;
}
}
}
The generated code in Affiliate.Generated.cs and AffiliateDataSource.Generated.cs is quite long. I will just provide a summary of what is generated in these files.
In Affiliate.Generated.cs the database access code for Affiliate objects is generated. Table fields are accessible as properties. Methods for Loading, Saving and Deleting an affiliate object are generated. Methods/Properties to access associated objects and/or child objects are generated.
An example of generated property. Accessing Affiliate name
/// <summary>
/// Name
/// </summary>
public String Name
{
get { return this._Name; }
set
{
if (this._Name != value)
{
this._Name = value;
this.IsDirty = true;
}
}
}
An example of generated code for associated object.
/// <summary>
/// A collection of Order objects associated with this Affiliate object.
/// </summary>
public OrderCollection Orders
{
get
{
if (!this.OrdersLoaded)
{
this._Orders = OrderDataSource.LoadForAffiliate(this.AffiliateId);
}
return this._Orders;
}
}
In AffiliateDataSource.Generated.cs various methods are generated that comply with ASP.NET Data Source Object Model. Here is an example.
Loading all affiliates for the current store
/// <summary>
/// Counts the number of Affiliate objects for the current store.
/// </summary>
/// <returns>The Number of Affiliate objects in the current store.</returns>
public static int CountForStore()
{
int storeId = Token.Instance.StoreId;
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand("SELECT COUNT(*) AS TotalRecords FROM ac_Affiliates WHERE StoreId = @storeId");
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, storeId);
return CommerceBuilder.Utility.AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
}
/// <summary>
/// Loads a collection of Affiliate objects for the current store from the database
/// </summary>
/// <returns>A collection of Affiliate objects</returns>
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
public static AffiliateCollection LoadForStore()
{
return LoadForStore(0, 0, string.Empty);
}
/// <summary>
/// Loads a collection of Affiliate objects for the current store from the database. Sorts using the given sort exrpression.
/// </summary>
/// <param name="sortExpression">The sort expression to use for sorting the loaded objects.</param>
/// <returns>A collection of Affiliate objects</returns>
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
public static AffiliateCollection LoadForStore(string sortExpression)
{
return LoadForStore(0, 0, sortExpression);
}
/// <summary>
/// Loads a collection of Affiliate objects for the current store from the database.
/// </summary>
/// <param name="maximumRows">Maximum number of rows to retrieve.</param>
/// <param name="startRowIndex">Starting index from where to start retrieving.</param>
/// <returns>A collection of Affiliate objects</returns>
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
public static AffiliateCollection LoadForStore(int maximumRows, int startRowIndex)
{
return LoadForStore(maximumRows, startRowIndex, string.Empty);
}
/// <summary>
/// Loads a collection of Affiliate objects for the current store from the database. Sorts using the given sort exrpression.
/// </summary>
/// <param name="maximumRows">Maximum number of rows to retrieve.</param>
/// <param name="startRowIndex">Starting index from where to start retrieving.</param>
/// <param name="sortExpression">The sort expression to use for sorting the loaded objects.</param>
/// <returns>A collection of Affiliate objects</returns>
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
public static AffiliateCollection LoadForStore(int maximumRows, int startRowIndex, string sortExpression)
{
int storeId = Token.Instance.StoreId;
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0) selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
selectQuery.Append(" " + Affiliate.GetColumnNames(string.Empty));
selectQuery.Append(" FROM ac_Affiliates");
selectQuery.Append(" WHERE StoreId = @storeId");
if (!string.IsNullOrEmpty(sortExpression)) selectQuery.Append(" ORDER BY " + sortExpression);
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, storeId);
//EXECUTE THE COMMAND
AffiliateCollection results = new AffiliateCollection();
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
Affiliate affiliate = new Affiliate();
Affiliate.LoadDataReader(affiliate, dr);
results.Add(affiliate);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return results;
}
Accessing in ASP.NET:
Here is a code form Admin/Marketting/Affiliates/Default.aspx that accesses affiliates. Note how it is using DataSource method LoadForStore.
<cb:SortedGridView ID="AffiliateGrid" runat="server" AllowPaging="true" AllowSorting="true" PageSize="20"
AutoGenerateColumns="False" DataKeyNames="AffiliateId" DataSourceID="AffiliateDs"
ShowFooter="False" DefaultSortExpression="Name" SkinID="PagedList" Width="400">
.....
.....
.....
.....
.....
<asp:ObjectDataSource ID="AffiliateDs" runat="server" OldValuesParameterFormatString="original_{0}"
SelectMethod="LoadForStore" TypeName="CommerceBuilder.Marketing.AffiliateDataSource"
SelectCountMethod="CountForStore" SortParameterName="sortExpression" DataObjectTypeName="CommerceBuilder.Marketing.Affiliate"
DeleteMethod="Delete" UpdateMethod="Update">
</asp:ObjectDataSource>
