Ablecommerce7 Data Access: From DB table to the Webpage

This forum is where we'll mirror posts that are of value to the community so they may be more easily found.
Post Reply
User avatar
sohaib
Developer
Developer
Posts: 1079
Joined: Fri Jan 23, 2004 1:38 am

Ablecommerce7 Data Access: From DB table to the Webpage

Post by sohaib » Fri Mar 28, 2008 8:30 am

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

Code: Select all

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.
Here is the code from Affiliate.cs and AffiliateDataSource.cs

Code: Select all

//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: Select all

//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;
        }

    }
}
Here I am posting the code for AffiliateCollection.Generated.cs. It is very simple as it just extends our existing 'PersistantCollection' class.

Code: Select all

 
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

Code: Select all

        /// <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.

Code: Select all

        /// <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

Code: Select all

        /// <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.

Code: Select all

<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>
Last edited by sohaib on Mon Mar 31, 2008 9:14 am, edited 2 times in total.

User avatar
sohaib
Developer
Developer
Posts: 1079
Joined: Fri Jan 23, 2004 1:38 am

Post by sohaib » Fri Mar 28, 2008 8:35 am

This has been moved to the correct forum 'Good Reference Posts'

Thanks Mike!
Last edited by sohaib on Fri Mar 28, 2008 11:28 am, edited 2 times in total.

keats76
Commander (CMDR)
Commander (CMDR)
Posts: 117
Joined: Sat Dec 15, 2007 4:45 pm

Post by keats76 » Fri Mar 28, 2008 10:17 am

Awesome post!

Will you be releasing your DAL generator code as part of the source (when the time comes)?

Thanks!
Mike

User avatar
sohaib
Developer
Developer
Posts: 1079
Joined: Fri Jan 23, 2004 1:38 am

Post by sohaib » Fri Mar 28, 2008 11:04 am

Will you be releasing your DAL generator code as part of the source (when the time comes)?


Firstly I don't think we can commit on that yet. DAL generator was designed for internal use. To be released to public it will need some 'work'.

Secondly I am not sure whether we should ever release it because it will add another support dependency. Once we release something we have to support it. If we don't release the DAL generator we are free to make any changes to it without ever worrying about backward compatibility or support issues.

However Logan is the right person to give you a decisive answer on this.
Last edited by sohaib on Mon Mar 31, 2008 9:00 am, edited 1 time in total.

keats76
Commander (CMDR)
Commander (CMDR)
Posts: 117
Joined: Sat Dec 15, 2007 4:45 pm

Post by keats76 » Fri Mar 28, 2008 11:32 am

That makes sense ... I'll just make sure to send Logan (and yourself) a case of your favorite tasty beverage for an unsupported version :).

User avatar
sohaib
Developer
Developer
Posts: 1079
Joined: Fri Jan 23, 2004 1:38 am

Re: Ablecommerce7 Data Acces: From Database table to the Webpage

Post by sohaib » Mon Mar 31, 2008 5:31 am

I've got some preliminary code in place for the GetColumnNames and LoadDataReader methods etc, but I'm not sure how you pull some of that information in your model (i.e. do you reference a static array of column names that you then prepend the database name to [when passed in], or do you dynamically generate the column names each time etc.)
GetColumnNames and LoadDataReader are generated by the code generator.

They look something like this

GetColumnNames

Code: Select all

        /// <summary>
        /// Returns a coma separated list of column names in this database object.
        /// </summary>
        /// <param name="prefix">Prefix to use with column names. Leave null or empty for no prefix.</param>
        /// <returns>A coman separated list of column names for this database object.</returns>
        public static string GetColumnNames(string prefix)
        {
          if (string.IsNullOrEmpty(prefix)) prefix = string.Empty;
          else prefix = prefix + ".";
          List<string> columnNames = new List<string>();
          columnNames.Add(prefix + "AffiliateId");
          columnNames.Add(prefix + "StoreId");
          columnNames.Add(prefix + "Name");
          columnNames.Add(prefix + "PayeeName");
          columnNames.Add(prefix + "FirstName");
          columnNames.Add(prefix + "LastName");
          columnNames.Add(prefix + "Company");
          columnNames.Add(prefix + "Address1");
          columnNames.Add(prefix + "Address2");
          columnNames.Add(prefix + "City");
          columnNames.Add(prefix + "Province");
          columnNames.Add(prefix + "PostalCode");
          columnNames.Add(prefix + "CountryCode");
          columnNames.Add(prefix + "PhoneNumber");
          columnNames.Add(prefix + "FaxNumber");
          columnNames.Add(prefix + "MobileNumber");
          columnNames.Add(prefix + "WebsiteUrl");
          columnNames.Add(prefix + "Email");
          columnNames.Add(prefix + "CommissionRate");
          columnNames.Add(prefix + "CommissionIsPercent");
          columnNames.Add(prefix + "CommissionOnTotal");
          columnNames.Add(prefix + "ReferralDays");
          return string.Join(",", columnNames.ToArray());
        }


LoadDataReader

Code: Select all

        /// <summary>
        /// Loads the given Affiliate object from the given database data reader.
        /// </summary>
        /// <param name="affiliate">The Affiliate object to load.</param>
        /// <param name="dr">The database data reader to read data from.</param>
        public static void LoadDataReader(Affiliate affiliate, IDataReader dr)
        {
            //SET FIELDS FROM ROW DATA
            affiliate.AffiliateId = dr.GetInt32(0);
            affiliate.StoreId = dr.GetInt32(1);
            affiliate.Name = dr.GetString(2);
            affiliate.PayeeName = NullableData.GetString(dr, 3);
            affiliate.FirstName = NullableData.GetString(dr, 4);
            affiliate.LastName = NullableData.GetString(dr, 5);
            affiliate.Company = NullableData.GetString(dr, 6);
            affiliate.Address1 = NullableData.GetString(dr, 7);
            affiliate.Address2 = NullableData.GetString(dr, 8);
            affiliate.City = NullableData.GetString(dr, 9);
            affiliate.Province = NullableData.GetString(dr, 10);
            affiliate.PostalCode = NullableData.GetString(dr, 11);
            affiliate.CountryCode = NullableData.GetString(dr, 12);
            affiliate.PhoneNumber = NullableData.GetString(dr, 13);
            affiliate.FaxNumber = NullableData.GetString(dr, 14);
            affiliate.MobileNumber = NullableData.GetString(dr, 15);
            affiliate.WebsiteUrl = NullableData.GetString(dr, 16);
            affiliate.Email = NullableData.GetString(dr, 17);
            affiliate.CommissionRate = dr.GetDecimal(18);
            affiliate.CommissionIsPercent = dr.GetBoolean(19);
            affiliate.CommissionOnTotal = dr.GetBoolean(20);
            affiliate.ReferralDays = dr.GetInt16(21);
            affiliate.IsDirty = false;
        }

User avatar
sohaib
Developer
Developer
Posts: 1079
Joined: Fri Jan 23, 2004 1:38 am

Re: Ablecommerce7 Data Access: From DB table to the Webpage

Post by sohaib » Mon Mar 31, 2008 10:17 am

I have been asked about Load, Delete, Save methods. Well it doesn't really matter how you actually program these method in your own objects as long as they work.
Anyway here is the sample code from Affiliates.Generated.cs

Load Method

Code: Select all

        /// <summary>
        /// Load this Affiliate object from the database for the given primary key.
        /// </summary>
        /// <param name="affiliateId">Value of AffiliateId of the object to load.</param>
        /// <returns><b>true</b> if load is successful, <b>false</b> otherwise</returns>
        public virtual bool Load(Int32 affiliateId)
        {
            bool result = false;
            this.AffiliateId = affiliateId;
            //CREATE THE DYNAMIC SQL TO LOAD OBJECT
            StringBuilder selectQuery = new StringBuilder();
            selectQuery.Append("SELECT " + GetColumnNames(string.Empty));
            selectQuery.Append(" FROM ac_Affiliates");
            selectQuery.Append(" WHERE AffiliateId = @affiliateId");
            selectQuery.Append(" AND StoreId = @storeId");
            Database database = Token.Instance.Database;
            DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
            database.AddInParameter(selectCommand, "@affiliateId", System.Data.DbType.Int32, affiliateId);
            database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
            //EXECUTE THE COMMAND
            using (IDataReader dr = database.ExecuteReader(selectCommand))
            {
                if (dr.Read())
                {
                    result = true;
                    LoadDataReader(this, dr);;
                }
                dr.Close();
            }
            return result;
        }

Delete Method

Code: Select all

        /// <summary>
        /// Deletes this Affiliate object from the database.
        /// </summary>
        /// <returns><b>true</b> if delete successful, <b>false</b> otherwise.</returns>
        public virtual bool Delete()
        {
            int recordsAffected = 0;
            StringBuilder deleteQuery = new StringBuilder();
            deleteQuery.Append("DELETE FROM ac_Affiliates");
            deleteQuery.Append(" WHERE AffiliateId = @affiliateId");
            Database database = Token.Instance.Database;
            using (DbCommand deleteCommand = database.GetSqlStringCommand(deleteQuery.ToString()))
            {
                database.AddInParameter(deleteCommand, "@AffiliateId", System.Data.DbType.Int32, this.AffiliateId);
                recordsAffected = database.ExecuteNonQuery(deleteCommand);
            }
            return (recordsAffected > 0);
        }

Save Method

Code: Select all

        /// <summary>
        /// Saves this Affiliate object to the database.
        /// </summary>
        /// <returns><b>SaveResult</b> enumeration that represents the result of the save operation.</returns>
        public virtual SaveResult Save()
        {
            if (this.IsDirty)
            {
                Database database = Token.Instance.Database;
                bool recordExists = true;
                
                //SET EMPTY STOREID TO CURRENT CONTEXT
                if (this.StoreId == 0) this.StoreId = Token.Instance.StoreId;
                if (this.AffiliateId == 0) recordExists = false;

                if (recordExists) {
                    //verify whether record is already present
                    StringBuilder selectQuery = new StringBuilder();
                    selectQuery.Append("SELECT COUNT(*) As RecordCount FROM ac_Affiliates");
                    selectQuery.Append(" WHERE AffiliateId = @affiliateId");
                    using (DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString()))
                    {
                        database.AddInParameter(selectCommand, "@AffiliateId", System.Data.DbType.Int32, this.AffiliateId);
                        if ((int)database.ExecuteScalar(selectCommand) == 0)
                        {
                            recordExists = false;
                        }
                    }
                }

                int result = 0;
                if (recordExists)
                {
                    //UPDATE
                    StringBuilder updateQuery = new StringBuilder();
                    updateQuery.Append("UPDATE ac_Affiliates SET ");
                    updateQuery.Append("StoreId = @StoreId");
                    updateQuery.Append(", Name = @Name");
                    updateQuery.Append(", PayeeName = @PayeeName");
                    updateQuery.Append(", FirstName = @FirstName");
                    updateQuery.Append(", LastName = @LastName");
                    updateQuery.Append(", Company = @Company");
                    updateQuery.Append(", Address1 = @Address1");
                    updateQuery.Append(", Address2 = @Address2");
                    updateQuery.Append(", City = @City");
                    updateQuery.Append(", Province = @Province");
                    updateQuery.Append(", PostalCode = @PostalCode");
                    updateQuery.Append(", CountryCode = @CountryCode");
                    updateQuery.Append(", PhoneNumber = @PhoneNumber");
                    updateQuery.Append(", FaxNumber = @FaxNumber");
                    updateQuery.Append(", MobileNumber = @MobileNumber");
                    updateQuery.Append(", WebsiteUrl = @WebsiteUrl");
                    updateQuery.Append(", Email = @Email");
                    updateQuery.Append(", CommissionRate = @CommissionRate");
                    updateQuery.Append(", CommissionIsPercent = @CommissionIsPercent");
                    updateQuery.Append(", CommissionOnTotal = @CommissionOnTotal");
                    updateQuery.Append(", ReferralDays = @ReferralDays");
                    updateQuery.Append(" WHERE AffiliateId = @AffiliateId");
                    using (DbCommand updateCommand = database.GetSqlStringCommand(updateQuery.ToString()))
                    {
                        database.AddInParameter(updateCommand, "@AffiliateId", System.Data.DbType.Int32, this.AffiliateId);
                        database.AddInParameter(updateCommand, "@StoreId", System.Data.DbType.Int32, this.StoreId);
                        database.AddInParameter(updateCommand, "@Name", System.Data.DbType.String, this.Name);
                        database.AddInParameter(updateCommand, "@PayeeName", System.Data.DbType.String, NullableData.DbNullify(this.PayeeName));
                        database.AddInParameter(updateCommand, "@FirstName", System.Data.DbType.String, NullableData.DbNullify(this.FirstName));
                        database.AddInParameter(updateCommand, "@LastName", System.Data.DbType.String, NullableData.DbNullify(this.LastName));
                        database.AddInParameter(updateCommand, "@Company", System.Data.DbType.String, NullableData.DbNullify(this.Company));
                        database.AddInParameter(updateCommand, "@Address1", System.Data.DbType.String, NullableData.DbNullify(this.Address1));
                        database.AddInParameter(updateCommand, "@Address2", System.Data.DbType.String, NullableData.DbNullify(this.Address2));
                        database.AddInParameter(updateCommand, "@City", System.Data.DbType.String, NullableData.DbNullify(this.City));
                        database.AddInParameter(updateCommand, "@Province", System.Data.DbType.String, NullableData.DbNullify(this.Province));
                        database.AddInParameter(updateCommand, "@PostalCode", System.Data.DbType.String, NullableData.DbNullify(this.PostalCode));
                        database.AddInParameter(updateCommand, "@CountryCode", System.Data.DbType.String, NullableData.DbNullify(this.CountryCode));
                        database.AddInParameter(updateCommand, "@PhoneNumber", System.Data.DbType.String, NullableData.DbNullify(this.PhoneNumber));
                        database.AddInParameter(updateCommand, "@FaxNumber", System.Data.DbType.String, NullableData.DbNullify(this.FaxNumber));
                        database.AddInParameter(updateCommand, "@MobileNumber", System.Data.DbType.String, NullableData.DbNullify(this.MobileNumber));
                        database.AddInParameter(updateCommand, "@WebsiteUrl", System.Data.DbType.String, NullableData.DbNullify(this.WebsiteUrl));
                        database.AddInParameter(updateCommand, "@Email", System.Data.DbType.String, NullableData.DbNullify(this.Email));
                        database.AddInParameter(updateCommand, "@CommissionRate", System.Data.DbType.Decimal, this.CommissionRate);
                        database.AddInParameter(updateCommand, "@CommissionIsPercent", System.Data.DbType.Boolean, this.CommissionIsPercent);
                        database.AddInParameter(updateCommand, "@CommissionOnTotal", System.Data.DbType.Boolean, this.CommissionOnTotal);
                        database.AddInParameter(updateCommand, "@ReferralDays", System.Data.DbType.Int16, this.ReferralDays);
                        //RESULT IS NUMBER OF RECORDS AFFECTED
                        result = database.ExecuteNonQuery(updateCommand);
                    }
                }
                else
                {
                    //INSERT
                    StringBuilder insertQuery = new StringBuilder();
                    insertQuery.Append("INSERT INTO ac_Affiliates (StoreId, Name, PayeeName, FirstName, LastName, Company, Address1, Address2, City, Province, PostalCode, CountryCode, PhoneNumber, FaxNumber, MobileNumber, WebsiteUrl, Email, CommissionRate, CommissionIsPercent, CommissionOnTotal, ReferralDays)");
                    insertQuery.Append(" VALUES (@StoreId, @Name, @PayeeName, @FirstName, @LastName, @Company, @Address1, @Address2, @City, @Province, @PostalCode, @CountryCode, @PhoneNumber, @FaxNumber, @MobileNumber, @WebsiteUrl, @Email, @CommissionRate, @CommissionIsPercent, @CommissionOnTotal, @ReferralDays)");
                    insertQuery.Append("; SELECT @@IDENTITY");
                    using (DbCommand insertCommand = database.GetSqlStringCommand(insertQuery.ToString()))
                    {
                        database.AddInParameter(insertCommand, "@AffiliateId", System.Data.DbType.Int32, this.AffiliateId);
                        database.AddInParameter(insertCommand, "@StoreId", System.Data.DbType.Int32, this.StoreId);
                        database.AddInParameter(insertCommand, "@Name", System.Data.DbType.String, this.Name);
                        database.AddInParameter(insertCommand, "@PayeeName", System.Data.DbType.String, NullableData.DbNullify(this.PayeeName));
                        database.AddInParameter(insertCommand, "@FirstName", System.Data.DbType.String, NullableData.DbNullify(this.FirstName));
                        database.AddInParameter(insertCommand, "@LastName", System.Data.DbType.String, NullableData.DbNullify(this.LastName));
                        database.AddInParameter(insertCommand, "@Company", System.Data.DbType.String, NullableData.DbNullify(this.Company));
                        database.AddInParameter(insertCommand, "@Address1", System.Data.DbType.String, NullableData.DbNullify(this.Address1));
                        database.AddInParameter(insertCommand, "@Address2", System.Data.DbType.String, NullableData.DbNullify(this.Address2));
                        database.AddInParameter(insertCommand, "@City", System.Data.DbType.String, NullableData.DbNullify(this.City));
                        database.AddInParameter(insertCommand, "@Province", System.Data.DbType.String, NullableData.DbNullify(this.Province));
                        database.AddInParameter(insertCommand, "@PostalCode", System.Data.DbType.String, NullableData.DbNullify(this.PostalCode));
                        database.AddInParameter(insertCommand, "@CountryCode", System.Data.DbType.String, NullableData.DbNullify(this.CountryCode));
                        database.AddInParameter(insertCommand, "@PhoneNumber", System.Data.DbType.String, NullableData.DbNullify(this.PhoneNumber));
                        database.AddInParameter(insertCommand, "@FaxNumber", System.Data.DbType.String, NullableData.DbNullify(this.FaxNumber));
                        database.AddInParameter(insertCommand, "@MobileNumber", System.Data.DbType.String, NullableData.DbNullify(this.MobileNumber));
                        database.AddInParameter(insertCommand, "@WebsiteUrl", System.Data.DbType.String, NullableData.DbNullify(this.WebsiteUrl));
                        database.AddInParameter(insertCommand, "@Email", System.Data.DbType.String, NullableData.DbNullify(this.Email));
                        database.AddInParameter(insertCommand, "@CommissionRate", System.Data.DbType.Decimal, this.CommissionRate);
                        database.AddInParameter(insertCommand, "@CommissionIsPercent", System.Data.DbType.Boolean, this.CommissionIsPercent);
                        database.AddInParameter(insertCommand, "@CommissionOnTotal", System.Data.DbType.Boolean, this.CommissionOnTotal);
                        database.AddInParameter(insertCommand, "@ReferralDays", System.Data.DbType.Int16, this.ReferralDays);
                        //RESULT IS NEW IDENTITY;
                        result = AlwaysConvert.ToInt(database.ExecuteScalar(insertCommand));
                        this._AffiliateId = result;
                    }
                }
                this.SaveChildren();

                //OBJECT IS DIRTY IF NO RECORDS WERE UPDATED OR INSERTED
                this.IsDirty = (result == 0);
                if (this.IsDirty) { return SaveResult.Failed; }
                else { return (recordExists ? SaveResult.RecordUpdated : SaveResult.RecordInserted); }
            }

            //SAVE IS SUCCESSFUL IF OBJECT IS NOT DIRTY
            return SaveResult.NotDirty;
        }

Some Other Methods

Code: Select all

        /// <summary>
        /// Ensures that child objects of this Affiliate are properly associated with this Affiliate object.
        /// </summary>
        public virtual void EnsureChildProperties()
        {
            if (this.OrdersLoaded) { foreach (Order order in this.Orders) { order.AffiliateId = this.AffiliateId; } }
            if (this.UsersLoaded) { foreach (User user in this.Users) { user.AffiliateId = this.AffiliateId; } }
        }

        /// <summary>
        /// Saves that child objects associated with this Affiliate object.
        /// </summary>
        public virtual void SaveChildren()
        {
            this.EnsureChildProperties();
            if (this.OrdersLoaded) this.Orders.Save();
            if (this.UsersLoaded) this.Users.Save();
        }

User avatar
Shopping Cart Admin
AbleCommerce Admin
AbleCommerce Admin
Posts: 3055
Joined: Mon Dec 01, 2003 8:41 pm
Location: Vancouver, WA
Contact:

Re: Ablecommerce7 Data Access: From DB table to the Webpage

Post by Shopping Cart Admin » Tue Apr 08, 2008 12:18 pm

Hello All,

This content has been replicated in the WIKI for easier reference:

http://wiki.ablecommerce.com/index.php/ ... cess_Layer
Thanks for your support

Shopping Cart Guru
AbleCommerce.com
Follow us on Facebook

ajdyke
Ensign (ENS)
Ensign (ENS)
Posts: 1
Joined: Mon Jun 16, 2008 9:16 am

Re: Ablecommerce7 Data Access: From DB table to the Webpage

Post by ajdyke » Mon Jun 16, 2008 9:42 am

Hello,

I am considering purchasing multiple AbleCommerce licenses as the main component of several online storefronts and catalogs that my company will be creating. Our primary concern is the ability to customize the tables with additional fields and to easily integrate other custom tables. The presentation and business layers appear to meet our needs, however the data layer is a little ambiguous considering the DAL generator is not being made available.

My question is... What is the typical scenario when integrating additional fields and/or custom tables? Would the generated DAL need to be manually modified, possibly complicating the ability to upgrade or apply bug fixes? Would the custom tables need to use a different data layer (whatever the developer chooses) or be manually coded to match the existing DAL?

I appreciate your help. AbleCommerce really seems to have blown the competition out of the water when it comes to features and customization.

- AJ

User avatar
sohaib
Developer
Developer
Posts: 1079
Joined: Fri Jan 23, 2004 1:38 am

Re: Ablecommerce7 Data Access: From DB table to the Webpage

Post by sohaib » Tue Jun 17, 2008 7:04 am

What is the typical scenario when integrating additional fields and/or custom tables?
If you are going to add additional fields to an existing table then you would probably need source-code version of AC7 so that you can customize the existing Data Layer objects for that table.

If you intend to add new tables you are free to handle them whatever way you like. Whether you follow AC7 DAL style or not doesn't matter. You may chose whatever suits you best.
Would the generated DAL need to be manually modified, possibly complicating the ability to upgrade or apply bug fixes?
Once you start customizing the existing tables the ability to upgrade is complicated in any case. But yes for the generated code if DAL generator is available the upgrade can be simplified. I think the DAL generator will be made available pretty soon but I am giving no deadlines.
Would the custom tables need to use a different data layer (whatever the developer chooses) or be manually coded to match the existing DAL?
As explained above... the developer may chose anything he/she likes..

Post Reply