Thread: How To Guide: Create On The Fly Extension Data Parameters

Hey Guys,
Why?: During website navigation, let's say you wanted to store a custom variable in the db for a customer, like a preference, if they'd like to be added to some type of custom list, or if you use Google Website Optimizer and want to keep track of which experiments they were subject to.

All this script does it adds, adjusts, and removes xml data from the extension data field of the entity's table in the db. It's very simple:

Code:

using System;
using System.Collections.Generic;
using System.Xml;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using AspDotNetStorefrontCommon;
/// <summary>
/// ADNSF Extensions
/// </summary>
namespace Custom
{
    /// <summary>
    /// Gives the abillity to add a value to extensiondata and keep track of it on the fly.
    /// </summary>
    public class ExtensionData
    {
        string Entity; int EntityID;

        string orderSelectQuery = "SELECT ExtensionData FROM Order (NOLOCK) WHERE OrderID = @ID";
        string categorySelectQuery = "SELECT ExtensionData FROM Category (NOLOCK) WHERE CategoryID = @ID";
        string productSelectQuery = "SELECT ExtensionData FROM Product (NOLOCK) WHERE ProductID = @ID";
        string variantSelectQuery = "SELECT ExtensionData FROM ProductVariant (NOLOCK) WHERE VariantID = @ID";
        string customerSelectQuery = "SELECT ExtensionData FROM Customer (NOLOCK) WHERE CustomerID = @ID";
        string thisSelectQuery;

        string orderUpdateQuery = "UPDATE Order SET ExtensionData = @ExtensionData WHERE (OrderID = @ID)";
        string categoryUpdateQuery = "UPDATE Category SET ExtensionData = @ExtensionData WHERE (CategoryID = @ID)";
        string productUpdateQuery = "UPDATE Product SET ExtensionData = @ExtensionData WHERE (ProductID = @ID)";
        string variantUpdateQuery = "UPDATE ProductVariant SET ExtensionData = @ExtensionData WHERE (VariantID = @ID)";
        string customerUpdateQuery = "UPDATE Customer SET ExtensionData = @ExtensionData WHERE (CustomerID = @ID)";
        string thisUpdateQuery;

        public string CurrentExtensionData;
        public string OriginalExtensionData = "";

        public ExtensionData(string entity, int entityID)
        {
            Entity = entity;
            EntityID = entityID;
            PopulateQuery();
        }

        private void PopulateQuery()
        {
            switch (Entity)
            {
                case "order":
                    thisSelectQuery = orderSelectQuery;
                    thisUpdateQuery = orderUpdateQuery;
                    break;
                case "category":
                    thisSelectQuery = categorySelectQuery;
                    thisUpdateQuery = categoryUpdateQuery;
                    break;
                case "product":
                    thisSelectQuery = productSelectQuery;
                    thisUpdateQuery = productUpdateQuery;
                    break;
                case "variant":
                    thisSelectQuery = variantSelectQuery;
                    thisUpdateQuery = variantUpdateQuery;
                    break;
                case "customer":
                    thisSelectQuery = customerSelectQuery;
                    thisUpdateQuery = customerUpdateQuery;
                    break;
            }

            CurrentExtensionData = Get();
            OriginalExtensionData = CurrentExtensionData; //Save it if we need to compare it later
        }

        public string Get()
        {
            string result = "";
            SqlConnection connection = new SqlConnection(DB.GetDBConn()); connection.Open(); SqlCommand command = new SqlCommand(thisSelectQuery, connection);
            command.Parameters.Add("ID", SqlDbType.Int).Value = EntityID;
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                result = reader[0].ToString();
            }
            reader.Close(); reader.Dispose(); command.Dispose(); connection.Close(); connection.Dispose();
            return result;
        }

        public void Set(string newExtensionData)
        {
            CurrentExtensionData = newExtensionData; //update stored xml;
            SqlConnection connection = new SqlConnection(DB.GetDBConn()); connection.Open(); SqlCommand command = new SqlCommand(thisUpdateQuery, connection);
            command.Parameters.Add("ID", SqlDbType.Int).Value = EntityID;
            command.Parameters.Add("ExtensionData", SqlDbType.NVarChar).Value = newExtensionData;
            command.ExecuteScalar();
            command.Dispose(); connection.Close(); connection.Dispose();
        }

        public string Value(string tagName)
        {
            return GetNode(CurrentExtensionData, tagName);
        }

        public void Value(string tagName, string newValue)
        {
            string x = SetNode(CurrentExtensionData, tagName, newValue).ToString();
            Set(x);
        }

        public string Value(string tagName, int i)
        {
            return GetNode(CurrentExtensionData, tagName, i);
        }

        public void Value(string tagName, int i, string newValue)
        {
            string x = SetNode(CurrentExtensionData, tagName, i, newValue);
            Set(x);
        }

        public void Remove(string tagName)
        {
            string x = SetNode(CurrentExtensionData, tagName, 0, null);
            Set(x);
        }

        public void Remove(string tagName, int i)
        {
            string x = SetNode(CurrentExtensionData, tagName, i, null);
            Set(x);
        }

        public string GetNode(string rawXML, string tagName)
        {
            return GetNode(rawXML, tagName, 0);
        }

        public string SetNode(string rawXML, string tagName, string innerValue)
        {
            return SetNode(rawXML, tagName, 0, innerValue);
        }

        public string GetNode(string rawXML, string tagName, int i)
        {
            string result = "";
            try
            {
                result = XML(rawXML).GetElementsByTagName(tagName)[i].InnerText;
            }
            catch (Exception e) { }
            return result;
        }

        public string SetNode(string rawXML, string tagName, int i, string value)
        {
            string result = "";
            XmlDocument x = XML(rawXML);
            if (value != null)
            {
                var node = x.GetElementsByTagName(tagName)[i];
                if (node != null) //check if it exists
                {
                    node.InnerText = value;
                }
                else //create node if it doesn't exist
                {
                    var newNode = x.CreateElement(tagName);
                    newNode.InnerText = value;
                    x.GetElementsByTagName("root")[0].AppendChild(newNode);
                }
            }
            else
            {
                var node = x.GetElementsByTagName("root")[0];
                var nodeToRemove = x.GetElementsByTagName(tagName)[i];
                if (node != null && nodeToRemove != null)
                {
                    node.RemoveChild(nodeToRemove);
                }
            }
            result = RawXML(x);
            return result;
        }

        public string RawXML(XmlDocument x)
        {
            var root = x.GetElementsByTagName("root")[0];
            if (root != null)
            {
                return root.InnerXml;
            }
            else
            {
                return "";
            }
        }

        public string RawXML()
        {
            return RawXML(XML());
        }

        public XmlDocument XML()
        {
            return XML(CurrentExtensionData);
        }

        public XmlDocument XML(string rawXML)
        {
            XmlDocument xmlDoc = new XmlDocument();
            if (rawXML != null)
            {
                xmlDoc.LoadXml(string.Format("<root>{0}</root>", rawXML));
            }
            return xmlDoc;
        }
    }
}

Just copy and past that into a file (like Custom.cs) in your "App_Code" directory. If you wanted to add an XML Node of "GoodCustomer" with an inner value of "true" to the current customer:

Code:

var extensionData = new Custom.ExtensionData("customer", ThisCustomer.CustomerID);
extensionData.Value("GoodCustomer", "true");

What's really cool about this is that it can be used with customers, categories, products, variants, and orders (feel free to extend) & it provides a central, non-unique (easy) way of saving data.

Other Examples:

Retrive the value later:

Code:

var extensionData = new Custom.ExtensionData("customer",  ThisCustomer.CustomerID);
string goodCustomer = extensionData.Value("GoodCustomer");

Remove XML Node

Code:

var extensionData = new Custom.ExtensionData("customer", ThisCustomer.CustomerID);
extensionData.Remove("GoodCustomer");

I just thought I'd share it with you guys

Now, let's say you need to run a query to find the value of "GoodCustomer". Run the following:

Code:

SELECT CAST(ExtensionData as XML).value('(/GoodCustomer/.)[1]', 'varchar(4000)') AS ExtensionDataValue, CustomerID
FROM Customer

If you are wondering about the "(/GoodCustomer/.)[1]", it's xquery, and there isn't a whole lot of documentation on it out on the web, but it allows you to run querys on already queried information, AMAZING!

What do the admins think of this little snippet?