Somewhat Dynamic Pivot in C#

I started working on a dynamic pivot to be used in Kendo UI, where the user could select the datapoints to be shown in the graph. Didn't quite finish, but here is some of the code, didn't want to waste/lose it; since, the concept was interesting.

Code:

public class StageDataPoints
    {
        public List<StageDataPoint> dataSeries { get; set; }

        public List<ExpandoObject> pivot()
        {
            var datapoints = dataSeries.Select(n => n.DataPoint).Distinct().ToList();

            var sdpGroup = from d in dataSeries
                           group d by new { d.DateTimeGrainValue, d.Stage, d.SubStage }
                               into grp
                               select new
                               {
                                   Key = grp.Key,
                                   Dict = grp.ToDictionary(n => n.DataPoint, n => n.DecimalValueAvg)
                               };

            List<ExpandoObject> defactoObjs = new List<ExpandoObject>();

            foreach (var row in sdpGroup)
            {
                dynamic r = new ExpandoObject();
                r.DateTime = row.Key.DateTimeGrainValue.ToString("M/d/yy h:mm");
                r.Stage = row.Key.Stage;
                r.SubStage = row.Key.SubStage;

                double value = double.NaN;
                string tempDP = "";

                foreach (var dp in datapoints)
                {
                    tempDP = dp;

                    foreach (var idp in row.Dict)
                    {
                        if (idp.Key == dp)
                        {
                            value = idp.Value;
                            break;
                        }
                    }
                    //Console.Write(dp + " " + value.ToString());
                    ((IDictionary<string, object>)r).Add(dp, value);
                }
                defactoObjs.Add(r);
            }
            return defactoObjs;
        }

        public List<StageDataPointMinify> rpt()
        {
            List<StageDataPointMinify> ret = new List<StageDataPointMinify>();
            foreach (var x in dataSeries)
            {
                ret.Add(new StageDataPointMinify()
                {
                    //Convert datetime to javascript verion
                    DateTimeGrainValueStr = x.DateTimeGrainValue.ToString("M/d/yy H:mm"),
                    DateTimeGrainValue = x.DateTimeGrainValue,
                    DataPoint = x.DataPoint,
                    DecimalValueAvg = x.DecimalValueAvg
                });
            }
            return ret;
        }
    }

    public class StageDataPoint
    {
        //public DateTime CalendarGrainValue { get; set; }
        //public string ClockGrainValue { get; set; }
        public DateTime DateTimeGrainValue { get; set; }
        public string Stage { get; set; }
        public string SubStage { get; set; }
        public string DataPoint { get; set; }
        //public Guid CJobGuid { get; set; }
        public string JobName { get; set; }
        public double DecimalValueAvg { get; set; }
        //public double DecimalValueMin { get; set; }
        //public double DecimalValueMax { get; set; }
    }

 

Some steps when converting Dynamic tsql to Static

Here is a list of things to keep in mind when converting dynamic tsql stored procedures into a static version (for performance enhancement).

1) Remove quotes (carefully). - Going through and eliminated the outside quotes is the easy part. You do have to be careful of the single inner quotes, for example a single quote(s) might be necessary in a varchar statement.

2) Look over the convert statements. - A majority of the convert statements, especially the converting to varchar will need to be looked over and possibly removed in most cases.

3) Replace variables that dealt with parsing. - Some stored procedures might be sending in multiple data into one variable for example @Y = ('1,2,3,4,5') and being accessed like: WHERE x in @Y. This will need to be replaced with either a PATINDEX or CHARINDEX.

4) Remove unnecessary declarations - Some variables are going to end up not being used now, for example the varchar variable that was used to make the dynamic tsql string.



These are the tips, I might remember some more and add them later.

SQL: The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

A good read on Dynamic SQL.