private static List <Binding> LoadBindingListFromDataReader(OleDbDataReader reader)
{
var bindings = new List <Binding>();
int bindingId = -1;
try
{
while (reader.Read())
{
var binding = new Binding();
binding.Id = reader.GetInt32(reader.GetOrdinal("BindingID"));
bindingId = binding.Id;
binding.Name_EN = reader.GetStringSafe(reader.GetOrdinal("Naam_EN"));
binding.Name_NL = reader.GetStringSafe(reader.GetOrdinal("Naam_NL"));
binding.Name_FR = reader.GetStringSafe(reader.GetOrdinal("Naam_FR"));
binding.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
binding.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
binding.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
bindings.Add(binding);
}
}
catch (Exception exception)
{
log.ErrorFormat("Error in {0} (data record with BindingID={1}, exception={2})", MethodBase.GetCurrentMethod().Name, bindingId, exception);
throw;
}
return(bindings);
}
private static List <Author> LoadAuthorListFromDataReader(OleDbDataReader reader)
{
var authors = new List <Author>();
int authorId = -1;
try
{
while (reader.Read())
{
var author = new Author();
author.Id = reader.GetInt32(reader.GetOrdinal("ComponistID"));
authorId = author.Id;
author.Name = reader.GetStringSafe(reader.GetOrdinal("Naam"));
author.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
author.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
author.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
authors.Add(author);
}
}
catch (Exception exception)
{
log.ErrorFormat("Error in {0} (data record with ComponistID={1}, exception={2})", MethodBase.GetCurrentMethod().Name, authorId, exception);
throw;
}
return(authors);
}
private static List <ProductSeries> LoadProductSeriesListFromDataReader(OleDbDataReader reader)
{
var productSeries = new List <ProductSeries>();
while (reader.Read())
{
var series = new ProductSeries();
series.Id = reader.GetInt32(reader.GetOrdinal("SeriesID"));
series.Name = reader.GetStringSafe(reader.GetOrdinal("Naam"));
series.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
series.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
series.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
productSeries.Add(series);
}
return(productSeries);
}
private static List <Manufacturer> LoadManufacturerListFromDataReader(OleDbDataReader reader)
{
var manufacturers = new List <Manufacturer>();
while (reader.Read())
{
var manufacturer = new Manufacturer();
manufacturer.Id = reader.GetInt32(reader.GetOrdinal("UitgeverID"));
manufacturer.Name = reader.GetStringSafe(reader.GetOrdinal("Naam"));
manufacturer.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
manufacturer.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
manufacturer.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
manufacturers.Add(manufacturer);
}
return(manufacturers);
}
private static List <Supplier> LoadSupplierListFromDataReader(OleDbDataReader reader)
{
var suppliers = new List <Supplier>();
while (reader.Read())
{
var supplier = new Supplier();
supplier.Id = reader.GetInt32(reader.GetOrdinal("VerdelerID"));
supplier.Name = reader.GetStringSafe(reader.GetOrdinal("Naam"));
supplier.MinimumDeliveryDays = reader.GetNullableInt16(reader.GetOrdinal("WS_min_delivery_days"));
supplier.MaximumDeliveryDays = reader.GetNullableInt16(reader.GetOrdinal("WS_max_delivery_days"));
supplier.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
supplier.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
supplier.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
suppliers.Add(supplier);
}
return(suppliers);
}
private static List <Instrument> LoadInstrumentListFromDataReader(OleDbDataReader reader)
{
var instruments = new List <Instrument>();
while (reader.Read())
{
var instrument = new Instrument();
instrument.Id = reader.GetInt32(reader.GetOrdinal("InstrumentID"));
instrument.Name_EN = reader.GetStringSafe(reader.GetOrdinal("Naam_EN"));
instrument.Name_NL = reader.GetStringSafe(reader.GetOrdinal("Naam_NL"));
instrument.Name_FR = reader.GetStringSafe(reader.GetOrdinal("Naam_FR"));
instrument.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
instrument.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
instrument.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
instruments.Add(instrument);
}
return(instruments);
}
private static List <ProductCategory> LoadProductCategoryListFromDataReader(OleDbDataReader reader)
{
var categories = new List <ProductCategory>();
while (reader.Read())
{
var category = new ProductCategory();
category.Id = reader.GetInt32(reader.GetOrdinal("CategoryID"));
category.ParentId = reader.GetNullableInt32(reader.GetOrdinal("ParentCategoryID"));
category.Name = reader.GetStringSafe(reader.GetOrdinal("CategoryName"));
category.SortOrder = reader.GetInt32(reader.GetOrdinal("Sequence"));
category.PictureFilename = reader.GetStringSafe(reader.GetOrdinal("WS_PictureFile"));
category.PictureUpdated = reader.GetBoolean(reader.GetOrdinal("PictureUpdated"));
category.TargetUrl = reader.GetStringSafe(reader.GetOrdinal("TargetURL"));
category.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
category.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
category.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
categories.Add(category);
}
return(categories);
}
/// <summary>
/// Populates a list with customer objects from a data reader object
/// </summary>
/// <param name="reader">Data Reader containing one or more customer records</param>
/// <returns></returns>
private static List <Customer> LoadCustomerListFromDataReader(OleDbDataReader reader)
{
var customers = new List <Customer>();
int customerStoreId = -1;
try
{
while (reader.Read())
{
var customer = new Customer();
customer.StoreId = reader.GetNullableInt32("KlantID");
if (customer.StoreId != null)
{
customerStoreId = (int)customer.StoreId;
}
customer.WebshopId = reader.GetNullableInt32("KlantID_WS");
customer.LastName = reader.GetStringSafe(reader.GetOrdinal("Naam"));
customer.FirstName = reader.GetStringSafe(reader.GetOrdinal("Voornaam"));
customer.ShippingAddressStreet = reader.GetStringSafe(reader.GetOrdinal("Straat"));
customer.ShippingAddressHomeNumber = reader.GetStringSafe(reader.GetOrdinal("Huisnummer"));
customer.ShippingAddressZip = reader.GetStringSafe(reader.GetOrdinal("ZIP"));
customer.ShippingAddressCity = reader.GetStringSafe(reader.GetOrdinal("Stad"));
customer.ShippingAddressState = reader.GetStringSafe(reader.GetOrdinal("Staat"));
customer.ShippingAddressCountry = reader.GetStringSafe(reader.GetOrdinal("Land"));
customer.Phone = reader.GetStringSafe(reader.GetOrdinal("Telefoon"));
customer.Mobile = reader.GetStringSafe(reader.GetOrdinal("GSM"));
customer.Email = reader.GetStringSafe(reader.GetOrdinal("Email"));
customer.BillingName = reader.GetStringSafe(reader.GetOrdinal("FaktuurNaam"));
customer.BillingContact = reader.GetStringSafe(reader.GetOrdinal("FaktuurContact"));
customer.BillingAddress1 = reader.GetStringSafe(reader.GetOrdinal("FaktuurAdres1"));
customer.BillingAddress2 = reader.GetStringSafe(reader.GetOrdinal("FaktuurAdres2"));
customer.BillingAddress3 = reader.GetStringSafe(reader.GetOrdinal("FaktuurAdres3"));
customer.VatNumber = reader.GetStringSafe(reader.GetOrdinal("BTW Nummer"));
customer.WebshopDiscount6 = reader.GetByte(reader.GetOrdinal("WebshopDiscount_6"));
customer.WebshopDiscount21 = reader.GetByte(reader.GetOrdinal("WebshopDiscount_21"));
customer.IsTeacher = reader.GetBoolean(reader.GetOrdinal("IsLeraarOfAcademie"));
customer.IsReseller = reader.GetBoolean(reader.GetOrdinal("IsReseller"));
customer.Institution = reader.GetStringSafe(reader.GetOrdinal("Academie"));
customer.TeachingSubjects = reader.GetStringSafe(reader.GetOrdinal("Discipline"));
customer.TeacherCardNumber = reader.GetStringSafe(reader.GetOrdinal("TeacherCardNumber"));
customer.TeacherCardValidFrom = reader.GetNullableDateTime(reader.GetOrdinal("TeacherCardValidFrom"));
customer.TeacherCardValidTo = reader.GetNullableDateTime(reader.GetOrdinal("TeacherCardValidTo"));
customer.TeacherRegistrationNote = reader.GetStringSafe(reader.GetOrdinal("TeacherRegistrationNote"));
customer.TeacherConfirmed = reader.GetNullableDateTime(reader.GetOrdinal("TeacherConfirmed"));
customer.LastLoginDttm = reader.GetNullableDateTime(reader.GetOrdinal("LastLoginDttm"));
customer.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
customer.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
customer.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
customer.ForcePasswordReset = reader.GetBoolean(reader.GetOrdinal("ForcePasswordReset"));
customer.Test = reader.GetBoolean(reader.GetOrdinal("Test"));
customers.Add(customer);
}
}
catch (Exception exception)
{
log.ErrorFormat("Error in {0} (data record with KlantID={1}, exception={2})", MethodBase.GetCurrentMethod().Name, customerStoreId, exception);
throw;
}
return(customers);
}
public override void ExecuteQuery(CommerceQueryOperation queryOperation, OperationCacheDictionary operationCache, CommerceQueryOperationResponse response)
{
int numToTake = 2000;
lock (_addressLoaderLock) {
for (int i = 0; i < response.CommerceEntities.Count && response.CommerceEntities.Count < numToTake; i += numToTake) // only load addresses if we have less than 2000; any more than that will require a incoming filter
{
List <string> preferredAddressIds = new List <string>();
Dictionary <string, string> addressToParentIdMap = new Dictionary <string, string>();
List <CommerceEntity> currentBatch = response.CommerceEntities.Skip(i).Take(numToTake).ToList();
foreach (var entity in currentBatch)
{
string preferredAddressId = entity.GetPropertyValue("GeneralInfo.preferred_address") as string;
if (!String.IsNullOrEmpty(preferredAddressId) && !addressToParentIdMap.ContainsKey(preferredAddressId))
{
preferredAddressIds.Add("'" + preferredAddressId + "'");
addressToParentIdMap.Add(preferredAddressId, entity.Id);
}
}
// query them out
if (preferredAddressIds.Count == 0)
{
continue;
}
CommerceResourceCollection csResources = SiteHelper.GetCsConfig();
String connStr = csResources["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString();
//ProfileContext pContext = CommerceSiteContexts.Profile[GetSiteName()];
string fields = string.Join(", ", Array.ConvertAll(this.ProfileEntityMappings.PropertyMappings.ToArray(), p => p.Value));
string keys = string.Join(", ", Array.ConvertAll(this.ProfileEntityMappings.PropertyMappings.ToArray(), p => p.Key));
ProfileContext ctxt = CommerceSiteContexts.Profile[SiteHelper.GetSiteName()];
string dataSQLText = "SELECT * FROM [BEK_Commerce_profiles].[dbo].[Addresses] WHERE u_address_id in (" + String.Join(",", preferredAddressIds.ToArray()) + ")";
try {
using (OleDbConnection conn = new OleDbConnection(connStr)) {
conn.Open();
using (OleDbCommand cmdRead = new OleDbCommand(dataSQLText, conn)) {
using (OleDbDataReader dataReader = cmdRead.ExecuteReader()) {
while (dataReader.Read())
{
CommerceEntity entity = new CommerceEntity("Address");
entity.Id = dataReader.GetString("u_address_id");
entity.SetPropertyValue("LastName", dataReader.GetString("u_last_name"));
entity.SetPropertyValue("FirstName", dataReader.GetString("u_first_name"));
entity.SetPropertyValue("AddressName", dataReader.GetString("u_address_name"));
entity.SetPropertyValue("AddressType", dataReader.GetNullableInt("i_address_type"));
entity.SetPropertyValue("Description", dataReader.GetString("u_description"));
entity.SetPropertyValue("Line1", dataReader.GetString("u_address_line1"));
entity.SetPropertyValue("Line2", dataReader.GetString("u_address_line2"));
entity.SetPropertyValue("City", dataReader.GetString("u_city"));
entity.SetPropertyValue("StateProvinceCode", dataReader.GetString("u_region_code"));
entity.SetPropertyValue("StateProvinceName", dataReader.GetString("u_region_name"));
entity.SetPropertyValue("ZipPostalCode", dataReader.GetString("u_postal_code"));
entity.SetPropertyValue("CountryRegionCode", dataReader.GetString("u_country_code"));
entity.SetPropertyValue("CountryRegionName", dataReader.GetString("u_country_name"));
entity.SetPropertyValue("Telephone", dataReader.GetString("u_tel_number"));
entity.SetPropertyValue("TelephoneExtension", dataReader.GetString("u_tel_extension"));
entity.SetPropertyValue("LocaleId", dataReader.GetString("i_locale"));
entity.SetPropertyValue("DateModified", dataReader.GetNullableDateTime("dt_date_last_changed"));
entity.SetPropertyValue("DateCreated", dataReader.GetNullableDateTime("dt_date_created"));
currentBatch.Where(x => x.Id == (addressToParentIdMap[entity.Id])).FirstOrDefault()
.Properties.Add("PreferredAddress", new CommerceRelationship(entity));
}
dataReader.Close();
}
}
}
} catch (Exception ex) {
throw new ApplicationException("Error loading organization addresses", ex);
}
}
}
}
