Recently I had to build a feature matrix Grid (as shown below), which we do at least once on every project. Well, this time I tried to build the whole matrix in the database (Oracle) and return the core result set. Here is an example on how I wanted my grid to look like:
| | Standard | Professional | |
| Monthly fee | $9.99 | $29.99 | $99.99 |
| Setup fee | $0 | $0 | $0 |
| Support | N/A | 3 issues per month | Unlimited |
| Online Help | Included | Included | Included |
| Customer Service Chat | Included | Included | Included |
| On-site service | N/A | N/A | Included |
Well, no big deal right? How about trying to build this grid dynamically by passing the products and building with all the distinct features of all the products (may be N/A for the ones that don’t have the feature)? A simplified data model might look like this:
Adding to this, let’s throw in few static benefits to the mix that are not saved in the database. Like for example Online Help, Customer Service Chat and On-site service as shown in the grid above. There could be many reasons we don’t want this in the database; may be we want to attach online help to all products or something like that but those 3 are not available in the features table. Say those static benefits are saved in an xml file like this:
<Benefits>
<Feature ProductId="ALL" ID="ONLINE_HELP" Description="Online Help" Value="Included"></Feature>
<Feature ProductId="ALL" ID="CS_CHAT" Description="Customer Service Chat" Value="Included"></Feature>
<Feature ProductId="1234" ID="SITE_SERVICE" Description="On-site service" Value="Included"></Feature>
</Benefits>
Here is how I implemented this; first created a package in Oracle that does build the ref-cursor with the product benefits matrix:
CREATE OR REPLACE PACKAGE myBenefitsMix AS
TYPE ARRAY_PRD IS TABLE OF Products.ProductID%TYPE INDEX BY BINARY_INTEGER;
TYPE BNFTS_CUR IS REF CURSOR;
--Get the Product benefits matrix
PROCEDURE GETMyMix(p_ARRAY_PRD IN ARRAY_PRD,
p_out_BNFTS_CUR OUT myBenefitsMix.BNFTS_CUR);
END myBenefitsMix;
CREATE OR REPLACE PACKAGE BODY myBenefitsMix AS
PROCEDURE GETMyMix(p_ARRAY_PRD IN ARRAY_PRD,
p_out_BNFTS_CUR OUT myBenefitsMix.BNFTS_CUR)
IS
l_SQL LONG;
l_PRDLIST VARCHAR2(1000);
BEGIN
--Build the SQL for the Prod-Benefits
l_SQL := 'SELECT B.FeatureID, B.Description';
--for each product, as a column
FOR pnum IN 1..p_ARRAY_PRD.LAST
l_SQL := l_SQL || ',' ||
'(SELECT PB.FeatureValue' ||
' FROM ProductFeatures_vw PB WHERE PB.ProductID = ''' || p_ARRAY_PRD(pnum) ||
''' AND PB.FeatureID = B.FeatureID AND ROWNUM <> || p_ARRAY_PRD(pnum) || '"';
l_PRDLIST := l_PRDLIST || ',''' || p_ARRAY_PRD(pnum) || '''';
END
l_PRDLIST := SUBSTR(l_PRDLIST, 2);
l_SQL := l_SQL ||
' FROM (SELECT DISTINCT B1.FeatureID FeatureID,B1.Description Description ' ||
' FROM ProductFeatures_vw B1' ||
' WHERE B1.ProductID IN (' || l_PRDLIST || ')) B' ||
' Order By B.FeatureID';
--
OPEN p_out_BNFTS_CUR FOR l_SQL;
END GETMyMix;
END myBenefitsMix;
To consume this, here is my Benefits class would do:
public static DataSet FindForProducts(ArrayList Products)
{
MyLibrary.OracleHelper oh = new MyLibrary.OracleHelper();
try
{
OracleParameter[] op = new OracleParameter[2];
op[0] = new OracleParameter();
op[0].ParameterName = "@p_ARRAY_PRD";
op[0].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
op[0].Direction = ParameterDirection.Input;
op[0].OracleDbType = OracleDbType.Varchar2;
op[0].Value = (string[])Products.ToArray(typeof(String));
op[0].Size = Products.Count;
op[1] = new OracleParameter("@p_out_BNFTS_CUR", OracleDbType.RefCursor, ParameterDirection.Output);
// Execute & Return DataSet:
DataSet dsBenefits = oh.ExecuteDataset("myBenefitsMix.GETMyMix", op, CommandType.StoredProcedure);
#region "Add Static Benefits"
DataSet dsStatic = new DataSet();
//DataTable dtStatic = new DataTable();
dsStatic.ReadXml(HttpContext.Current.Server.MapPath("~/references/StaticBenefits.xml"));
string filterCondition = "ProductId = 'ALL'";
foreach (string prodID in Products)
filterCondition += " OR ProductId = '" + prodID + "'";
dsStatic.Tables[0].DefaultView.RowFilter = filterCondition;
foreach (DataRowView drv in dsStatic.Tables[0].DefaultView)
{
DataRow dr = dsBenefits.Tables[0].NewRow();
foreach (DataColumn dc in dsBenefits.Tables[0].Columns)
{
switch (dc.Ordinal)
{
case 0:
dr[dc] = drv["ID"].ToString();
break;
case 1:
dr[dc] = drv["Description"].ToString();
break;
default:
if (dc.ColumnName == drv["ProductId"].ToString() || drv["ProductId"].ToString() == "ALL")
dr[dc] = drv["Value"].ToString();
break;
}
}
dsBenefits.Tables[0].Rows.Add(dr);
}
#endregion "Add Static Benefits"
return dsBenefits;
}
finally { oh.Dispose(); oh = null; }
}
To summarize, this may not be the best of best but a way of doing it. I build dynamic SQL in my oracle package that adds columns for each product passed. That build the matrix with the benefits data from the database and I populate my dataset from that. To add some static benefits from an xml file, I then add rows to the dataset.
Finally, all I’m trying to show here is couple of points: building dynamic SQL and appending rows to the dataset from an xml file.
Love coding!
Posts
