Data Access Layer - AC7
From AbleCommerce Wiki
Questions have been coming regarding the Data Access Layer (DAL) of Ablecommerce7. Sohaib posted a 'brief' overview of how AC7 Data Access works in the forums and it's a terrific addition to the wiki.
Contents
- 1 The Database
- 2 Accessing Database
- 3 Affiliates in Database
- 4 Affiliates in Code
- 5 Code from Affiliate.cs
- 6 Code from AffiliateDataSource.cs
- 7 Code from AffiliateCollection.Generated.cs
- 8 Example of generated property. Accessing Affiliate name
- 9 An example of generated code for associated object
- 10 Loading all affiliates for the current store
- 11 Accessing in ASP.NET
- 12 GetColumnNames
- 13 LoadDataReader
- 14 Load Method
- 15 Delete Method
- 16 Save Method
- 17 Some Other Methods
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. 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
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]
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 from 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.
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;
}
}
Loading all affiliates for the current store
In AffiliateDataSource.Generated.cs various methods are generated that comply with ASP.NET Data Source Object Model. Here is an example.
/// <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>
GetColumnNames
GetColumnNames and LoadDataReader are generated by the code generator.
/// <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
/// <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;
}
Load Method
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
/// <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
/// <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
/// <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
/// <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();
}
