BenEskew.com Just another web developer's personal weblog.

Display MSRP in product.SimpleProduct.xml XMLPackage in AspDotNetStorefront

ASPDNSF Version: 9 (C# + MS SQL)

By default in AspDotNetStorefront the MSRP for products is not displayed or used within the product.SimpleProduct.xml XMLPackage, so if you would like to use or display it you will need to make some fine-tuned adjustments.

The Quick and Dirty Way (not as efficient):

In your product.SimpleProduct.xml XMLPackage find the lines where database queries are being made, like:

...
<query name="Products" rowElementName="Product" runif="edit">
<sql>
<![CDATA[
exec dbo.aspdnsf_EditOrderProduct @ShoppingCartRecID, @CustomerLevelID
]]>
</sql>
<queryparam paramname="@ShoppingCartRecID" paramtype="request" requestparamname="CartRecID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
<queryparam paramname="@CustomerLevelID" paramtype="system" requestparamname="CustomerLevelID" sqlDataType="int" defvalue="0" validationpattern="" />
</query>
...


and add a new database query by adding the following after the last query:

<query name="Variant" rowElementName="Price" runif="showproduct">
<sql>
<![CDATA[
select MSRP from dbo.ProductVariant with(NOLOCK) where ProductID=@ProductID and IsDefault=1
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
</query>


ex.

...
<query name="Products" rowElementName="Product" runif="edit">
<sql>
<![CDATA[
exec dbo.aspdnsf_EditOrderProduct @ShoppingCartRecID, @CustomerLevelID
]]>
</sql>
<queryparam paramname="@ShoppingCartRecID" paramtype="request" requestparamname="CartRecID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
<queryparam paramname="@CustomerLevelID" paramtype="system" requestparamname="CustomerLevelID" sqlDataType="int" defvalue="0" validationpattern="" />
</query>
<query name="Variant" rowElementName="Price" runif="showproduct">
<sql>
<![CDATA[
select MSRP from dbo.ProductVariant with(NOLOCK) where ProductID=@ProductID and IsDefault=1
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
</query>
...


Now, in your XMLPackage wherever you'd like to display the MSRP, simply add the following:

<xsl:value-of select="aspdnsf:FormatCurrency(/root/Variant/Price/MSRP)" disable-output-escaping="yes" />


The Efficient Way (MS SQL):

Using SQL Management Studio, open up the stored procedure and locate near the bottom where it reads:

...
SELECT
p.*,
pv.VariantID, pv.name VariantName, pv.Price, pv.Description VariantDescription, isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix, pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, pv.Inventory, pv.ImageFilenameOverride VariantImageFilenameOverride, pv.isdefault, pv.CustomerEntersPrice, isnull(pv.colors, '') Colors, isnull(pv.sizes, '') Sizes,
...

simply add the MSRP table call to the select statement, like:

...
SELECT
p.*,
pv.VariantID, pv.name VariantName, pv.Price, pv.MSRP, pv.Description VariantDescription, isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix, pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, pv.Inventory, pv.ImageFilenameOverride VariantImageFilenameOverride, pv.isdefault, pv.CustomerEntersPrice, isnull(pv.colors, '') Colors, isnull(pv.sizes, '') Sizes,
...

Now, execute the statement.
Finally, in your XMLPackage where you'd like to display the MSRP simply add the following:

<xsl:value-of select="aspdnsf:FormatCurrency(MSRP)" disable-output-escaping="yes" />

Or, if you're calling it from outside of the Product template, use the full xpath, like:

<xsl:value-of select="aspdnsf:FormatCurrency(/root/Products/Product/MSRP)" disable-output-escaping="yes" />