Geeks With Blogs
A Point To Share A journey through Microsoft Commerce Server and other .NET platforms

For many customers the ROI on software licences is one of the key factors when purchasing software, so when deploying an e-Commerce solution the level of return for frameworks, and CMS platforms such as Commerce Server and MOSS are quite justifiable, however it is the extra features – such as the Store Locator that is difficult spending licensing money on. The current store locator within CS 2009 is based on a commercial Microsoft Virtual Earth web-service, which is provided for non-developer used at extra cost above your Commerce Server licensing. Although I have no problems with the VE licensing model when deploying GIS and Geographical based application, it is a difficult cost to justify when all you want to do is a simply app like a store locator.

A requirement of one of my recent projects however was to deploy a store locator-style application without the extra licensing cost but still using Commerce Server profile objects so a custom can be associated back to a specific store location profile. So here's how I went about doing so:

Firstly I extended the existing Store Profile object which comes out-of-the-box in the standard Commerce Server 2009 install and added some extra attributes. The key attributes here are longitude, and latitude – the exact geographical location of that store. For the purposes of this tutorial I have assigned them to the store object, however technically these are properties of an address profile and not the store itself.

These properties are mapped to two columns added as an extension of the StoreObject table, both being nullable floats.

Now you have somewhere to keep the geographical location of your stores, how do you get the values? Well the answer is more where. For simplicities sakes you can batch "Geocode" [the process of plotting your stores on a map to find the latitude and longitude co-ordinated], and upload the data when you initially upload your stores. For my example I simply had a list of addresses in a CSV file and used a Google Maps Geocodes (http://mapsapi.googlepages.com/batchgeo.htm). Alternately both Google (http://code.google.com/apis/maps/documentation/services.html) and Bing (http://msdn.microsoft.com/en-us/library/cc966793.aspx) offer free XML web service based API's which allow you to geocode in real time. These are easy to implement and well documented – so this is the route I would strongly recommend.

Having a list of stores in a database, with geographical co-ordinates accessible via the Commerce Server profile system is all well and good, however it provides no end-user benefit. So to implement a functionality we want to deploy we head back to our SQL database. A few years ago, when we discovered the world was actually a sphere, it didn't take much longer for mathematicians to formulate distances using the universal grid system we have just discovered – latitude and longitude. So it is possible using relatively straightforward geometry to calculate the distance in Kilometres objects are from a known location, so in our example calculate the distance our stores are from the uses given address. To do this dynamically we are going to write a SQL function, which will return a temporary table containing the id of our stores, and the distance from our known location. The function looks something similar to this:

Create FUNCTION [dbo].[fnNearestStoreByCoord]

(

    @lat AS float,

    @long AS float

)

RETURNS @stores TABLE (idStore INT, storeName varchar(100), km FLOAT)

AS

BEGIN

    IF NOT (@lat IS NULL OR @long IS NULL) BEGIN

        -- Find the nearest store

        INSERT INTO @stores

        SELECT

            dbo.StoreObject.u_store_id,

            dbo.StoreObject.u_store_name,

            6378.7 * acos(sin(RADIANS(u_latitude)) * sin(RADIANS(@lat)) + cos(RADIANS(u_latitude)) * cos(RADIANS(@lat)) * cos(RADIANS(@long) - RADIANS(u_longitude))) AS km

    FROM

            StoreObject

        ORDER BY

            km

    END

    RETURN

END

You will notice that we pass in two parameters – which are our known longitude and latitude and we get a return of our store ID, name, and the distance from our provided location in kilometres. We can know call this query direct and get an accurate response – however for the sake of best practice lets implement a store procedure that can do a bit of house keeping for us:

ALTER PROCEDURE [dbo].[FindNearestStore]

(

    @lat float,

    @long float

)

AS

SELECT Top 4

idStore,

    storeName,    

    km

FROM

    fnNearestStoreByCoord(@lat, @long)

 

ORDER BY

    km

 

As you can see our stored procedure is calling our function, as you would a normal SQL table and passing its required parameters. However in this instance we are limiting the amount of rows being returned to 5, and ordering by the closest – so we don't inundate our end user with stores that are no longer close to them.

So now we have our store locator logic in-place. It is in the data layer, so it is fast and tied in to our Commerce Server profile which is what we wanted – but at the moment we cannot access it via our managed .NET code.

So know we hit the Commerce Server API to try and get access to newly developed database functionality – so we can deploy it to our site. The new Commerce Server API allows us to have a standard interface through entities to all of our Commerce Server objects. So first thing we need to do is extend the Store Profile entity, and its ORM with the MetadataDefintions.xml file.

If you have done this correctly you should end up with extra code lines similar to this within your StoreProfile class:

/// <summary>

/// The geocoded latitude value

/// </summary>

public decimal Latitude

{

get

{

return Convert.ToDecimal(this._commerceEntity.GetPropertyValue(PropertyName.Latitude));

}

 

set

{

this._commerceEntity.SetPropertyValue(PropertyName.Latitude, value);

}

}

 

/// <summary>

/// The geocoded longitude value

/// </summary>

public decimal Longitude

{

get

{

return Convert.ToDecimal(this._commerceEntity.GetPropertyValue(PropertyName.Longitude));

}

 

set

{

this._commerceEntity.SetPropertyValue(PropertyName.Longitude, value);

}

}

And in you MetadataDefinitions.xml file;

<PropertyMappings>

<PropertyMapping property="Id" csProperty="GeneralInfo.store_id" />

<PropertyMapping property="DateCreated"        csProperty="ProfileSystem.date_created" />

<PropertyMapping property="DateModified"    csProperty="ProfileSystem.date_last_changed" />

<PropertyMapping property="ModifiedBy"        csProperty="ProfileSystem.store_id_changed_by" />

<PropertyMapping property="Name"        csProperty="GeneralInfo.store_name" />

<PropertyMapping property="Latitude"    csProperty="GeneralInfo.store_latitude" />

<PropertyMapping property="Longitude"            csProperty="GeneralInfo.store_longitude" />

</PropertyMappings>

Now we have access to the properties we created in step 1, but as of yet not the stored procedure. To call our SQL functionality we need to implement another new concept for Commerce Server 2009 – a sequential component. To assist with backwards compatibility and extensibility the new Commerce Server API is simply an abstracted layer mapped back to the existing framework from Commerce Server 2007. When you call a commerce entity you object originates from the 2007 context, and is passed through a sequence of Commerce Server components that implements, validates and then casts the legacy object into the new standardised API. These layers provide an extensible place to inject extra levels of validation, and access to external systems. So this is where we will access our new store procedure.

So the first step is to create a new class which extends the ProfileLoaderBase class. This abstract class is responsible for loading all profile data from the 2007 context objects. Typically if you tried to call a StoreObject, you request would be routed to the StoreProfileLoader class which limits you to only returning an object by one its primary keys. This is due to the limitation of the underlying 2007 profile context, so to extend this limitation we need to override the default ExecuteQuery method. So we are going to replace it with something that looks like this:

 

public override void ExecuteQuery(CommerceQueryOperation queryOperation, Microsoft.Commerce.Broker.OperationCacheDictionary operationCache, CommerceQueryOperationResponse response)

{

int? count;

List<Profile> matches = new List<Profile>();

ParameterChecker.CheckForNull(queryOperation, "queryOperation");

ParameterChecker.CheckForNull(operationCache, "operationCache");

ParameterChecker.CheckForNull(response, "response");

CommerceModelSearch searchCriteria = queryOperation.SearchCriteria as CommerceModelSearch;

if (searchCriteria.Model.Properties["Latitude"] == null && searchCriteria.Model.Properties["Longitude"] == null)

{

matches = base.GetMatches(searchCriteria,out count);

}

else

matches = GetClosestStores((decimal)searchCriteria.Model.Properties["Latitude"], (decimal)searchCriteria.Model.Properties["Longitude"], out count);

operationCache.SetProfileOperationTargets(base.ProfileEntityMappings.CommerceServerDefinitionName, matches);

operationCache.SetProfileTargetsTotalItemCount(base.ProfileEntityMappings.CommerceServerDefinitionName, count);

 

 

}

If you have used the 2009 SDK (which I hope you would have if you have managed to follow me this far) then you will now that when you create a Commerce Query object you define which properties of that particular model you wish to search on. The code we have implemented is 95% the original ExecuteQuery method from the base class, except we want to intercept the method before it hits the GetMatches call. This the GetMatches method is simply calling the ProfileContext and returning a profile based on its ID. Instead we want to be able to call our own method if we have provided the longitude and latitude parameters, and if we haven't – well then simply allows the class to call its method as normal.

Our method GetClosestStores is even simpler still – all we are going to do is call the stored procedure, return the list of closest ID's and then grab those profiles out of the database. The only difficult part is because the Profile system is not necessarily SQL based you can't call a connection string directly – instead you have to go through the legacy OLEDB components to access the underlying data stores. So our GetClosestStores method will end up looking something like this :

 

private List<Profile> GetClosestStores(decimal lati, decimal longi, out int? totalCount)

{

List<Profile> matches = new List<Profile>();

int i = 0;

try

{

 

CommerceResourceCollection rscol = new CommerceResourceCollection(CommerceContext.Current.SiteName);

string bizDataConnStr = rscol["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString();

OleDbConnection conn = new OleDbConnection(bizDataConnStr);

List<int> profiles = new List<int>();

 

conn.Open();

 

try

{

 

OleDbCommand command = new OleDbCommand("FindNearestStore", conn);

OleDbParameter latitude = command.Parameters.Add("@lat", OleDbType.Decimal);

latitude.Direction = ParameterDirection.Input;

OleDbParameter longitude = command.Parameters.Add("@long", OleDbType.Decimal);

longitude.Direction = ParameterDirection.Input;

 

latitude.Value = lati;

longitude.Value = longi;

 

command.CommandType = CommandType.StoredProcedure;

 

OleDbDataReader reader = command.ExecuteReader();

 

while (reader.Read())

{

profiles.Add((int)reader[0]);

i++;

}

reader.Close();

command.Dispose();

}

catch(Exception ex) { }

finally

{

conn.Close();

}

 

foreach (var p in profiles)

{

ProfileContext context = CommerceSiteContexts.Profile[OperationContext.CurrentInstance.SiteName];

matches.Add(context.GetProfile(p, base.ProfileEntityMappings.CommerceServerDefinitionName));

}

 

 

 

}

 

catch (Exception ex) { }

 

totalCount = i;

return matches;

 

}

As you can see – it's pretty straight forward, with the logic simply retrieving a list of ids from the database stored procedure call (using our known longitude, and latitude) and then looping through those ID's to retrieve a list of profile objects. That's the only modifications we need to make. The underlying profile base classes have been designed to accept a collection of profiles, and will return them as strongly typed commerce entities when requested. So now we have our sequence component we need to get Commerce Server to use ours instead of the default one. We done this by editing the ChannelConfiguration.config file. This file defines the sequential components for the whole site (channel) so we simply need to find the default StoreProfileLoader component and replace it with what we have just created, which looks something like this:

<!--<Component name="Store Profile Loader" type="Microsoft.Commerce.Providers.Components.StoreProfileLoader, Microsoft.Commerce.Providers, Version=1.0.0.0, Culture=neutral,PublicKeyToken=31bf3856ad364e35"/>-->

<Component name="Store Profile Loader" type="Sample.Web.Logic.Components.StoreProfileSequenceComponent, Sample.Web.Logic, Version=1.0.0.0, Culture=neutral, PublicKeyToken=sampleKey"/>

 

That's it – we are up and ready, and can now make a query. Following the same convention as the 2009 SDK we can simply deploy this method in a static controller class, in a manner similar to this:

 

public static List<StoreProfile> GetStoresByLongLat(decimal latitude, decimal longitude )

{

List<StoreProfile> stores = new List<StoreProfile>();

 

CommerceQueryRelatedItem<Address> address = new CommerceQueryRelatedItem<Address>(StoreProfile.RelationshipName.Addresses);

CommerceQuery<StoreProfile> storeQuery = new CommerceQuery<StoreProfile>();

storeQuery.SearchCriteria.Model.Latitude = latitude;

storeQuery.SearchCriteria.Model.Longitude = longitude;

storeQuery.RelatedOperations.Add(address);

CommerceResponse response = SiteContext.ProcessRequest(storeQuery.ToRequest());

CommerceQueryOperationResponse queryResponse = response.OperationResponses[0] as CommerceQueryOperationResponse;

 

foreach (var store in queryResponse.CommerceEntities)

{

stores.Add(new StoreProfile(store));

}

 

return stores;

 

}

and then we can implement this on our front end in an ASP.NET AJAX enabled web service:

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.ComponentModel.ToolboxItem(false)]

[System.Web.Script.Services.ScriptService]

[PresenterHost(typeof(StoreLocatorPresenter), typeof(IStoreLocatorView))]

public class StoreLocator : WebServiceBase<StoreLocatorModel>, IStoreLocatorView

{

 

[WebMethod]

public List<StoreProfile> FindNearestStoreByLongLat(decimal longitude, decimal latitude)

{

return GetStoresByLongLat(longitude, latitude);

}

And there you have it – a web service, exactly the same as the Virtual Earth FindMyNearest service built in-house at no extra cost. You can now use either Google or Bing maps, to Geocode your customers locations, post the longitude and latitude to your local services, and plot the response on a map. Using a library such as JQuery – this is possible in about 15 lines of code. Your end result – something flexible to deploy, easy to use, and you get a better understanding of the Commerce Server 2009 SDK.

As per my last post I am setting up a sample ASP.NET solution – so once I have finished work on that, I will endeavour to package up my source code and some sample JavaScript and link to it of my blog. So stay tuned for updates – but in the meantime if you have questions please feel free to ask!

NB: This is my first technical tutorial.. so constructive criticism is welcome!

 

Posted on Monday, September 14, 2009 8:49 PM | Back to top


Comments on this post: Creating a Custom Store Locator in Commerce Server 2009

# re: Creating a Custom Store Locator in Commerce Server 2009
Requesting Gravatar...
Hi Lewis ,
Great article to get started , each operation sequence component already registered in the Config file all are written in this way , meant Opening connection according to data provider and if there is any SP to be called then attaching that. Please clarify me.
Thanks,
Ramkumar
Left by Ramkumar on Dec 21, 2009 10:04 PM

Your comment:
 (will show your gravatar)
 


Copyright © Lewis Benge | Powered by: GeeksWithBlogs.net | Join free