Monday, February 11, 2013

Custom GridView with AutoGenerateColumns

Problem Statement:
        If you have a requirement to keep AutoGenerateColumns="true" and still mamipulate gridview columns.
 
Solution:
Any <asp:TemplateField> with AutoGenerateColumns="true" should be generic to all rows.
On grid OnRowCreated event iterate over the row type and read item.ContainingField , this is where all the cell values are.
 
rdbSummaryReport.Checked – logic create a multiple common header and keeps the css applied to <th>
 
Below source code is a working example :
 
<asp:GridView ID="grdReconResults" runat="server" AutoGenerateColumns="true"
                    AllowPaging="false" AllowSorting="false" OnRowCreated="grdReconResults_OnRowCreated" >
                    <Columns>
                        <asp:TemplateField> //generates a ‘+’ link button in the first column of the grid
                            <ItemTemplate>
                                <asp:LinkButton ID="lnkExpand" runat="server" OnClientClick="return javascript:alert('clicked');" Text="+" />
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
 
 
 
 
protected void grdReconResults_OnRowCreated(object sender, GridViewRowEventArgs e)
        {
            System.Drawing.Color highlighted = System.Drawing.Color.Yellow;
            if (rdbDetailReport.Checked)
            { //modification for detailed report
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
                    DataRowView drv = (DataRowView)e.Row.DataItem;
                    if (drv != null)
                    {
                        DataRow dataRow = drv.Row;
                        foreach (DataControlFieldCell item in ((GridViewRow)e.Row).Controls)
                        {
                            DataControlField x = item.ContainingField;
                            if (!x.HeaderText.Equals(string.Empty)) //avoid column from markup
                            {
                                if (x.HeaderText.Contains("Diff_")) // || x.HeaderText.Contains("Hierarchy"))
                                {
                                    x.Visible = false;
                                }
                                else
                                {
                                    string[] parts = x.HeaderText.Split(new char[] { '_' }, 2);
                                    string pComputField = string.Format("Diff_{0}", parts[1]);
                                    if (dataRow.Table.Columns.IndexOf(pComputField) >= 0
                                        && ((bool)dataRow[pComputField]))
                                    {
                                        item.BackColor = highlighted;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            if (rdbSummaryReport.Checked)
            { //modification for summary report
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    int colSpanThree = 3;// for comparison field combined header span 3 rows
                    bool isColSpanThreeActive = false;
                    int colSpanOne = Convert.ToInt32(Decimal.One);
                    GridView grd = (GridView)sender;
                    GridViewRow gRow = (GridViewRow)e.Row;
                    GridViewRow grdHeaderRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
                    TableHeaderCell cell = null;
                    List<Control> notRequiredTH = new List<Control>();
                    foreach (DataControlFieldHeaderCell item in gRow.Controls)
                    {
                        DataControlField field = item.ContainingField;
 
                        if (field.HeaderText.Contains("Hierarchy"))
                        {
                            field.Visible = false;
                        }
                        else
                        {
                            if (isColSpanThreeActive)
                            {
                                --colSpanThree;
                                field.HeaderText = field.HeaderText.Substring(0, field.HeaderText.IndexOf('_'));
                                if (colSpanThree == Convert.ToInt32(Decimal.Zero))
                                {
                                    isColSpanThreeActive = false;
                                    colSpanThree = 3;
                                }
                            }
                            else
                            {
                                bool isComparisonCol = selectedMapping.Mappings.Exists(x => x.type == MappingRelation.MappingType.SelectSum
                                    && (field.HeaderText.Contains(x.Y) | field.HeaderText.Contains(x.X)));
                                if (isComparisonCol)
                                {
                                    cell = new TableHeaderCell();
                                    cell.Text = field.HeaderText.Split(new char[] { '_' }, 2)[1];
                                    cell.ColumnSpan = colSpanThree;
                                    cell.HorizontalAlign = HorizontalAlign.Center;
                                    grdHeaderRow.Cells.Add(cell);
                                    field.HeaderText = field.HeaderText.Substring(0, field.HeaderText.IndexOf('_'));
                                    isColSpanThreeActive = true;
                                    --colSpanThree;
                                }
                                else
                                {
                                    cell = new TableHeaderCell();
                                    cell.Text = field.HeaderText;
                                    cell.RowSpan = colSpanOne + colSpanOne;
                                    grdHeaderRow.Cells.Add(cell);
                                    notRequiredTH.Add(item);
                                }
                            }
                        }
                    }
                    notRequiredTH.ForEach(x => gRow.Controls.Remove(x));
                    grdReconResults.Controls[0].Controls.AddAt(gRow.RowIndex, grdHeaderRow);
                }
            }
        }
 
        private string GetHeaderText(string headerText, int headerLevel)
        {
            if (headerLevel == 2)
            {
                return headerText;
            }
            return headerText.Substring(0, headerText.LastIndexOf("."));
        }
 
 
Posted by at No comments:

DataTable based excel like PIVOTing

This is a very detailed source code for creating utility to provide column based summation data
 
Reference used :
 
 
 
 
 
CSV and DataSet Helper Classess
 
#region Using..
using System;
using System.IO;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using System.Data;
Posted by at No comments:

Sunday, February 10, 2013

Functions

Excel Date Difference based on interval type i.e. year, month, day à http://www.cpearson.com/excel/datedif.aspx
 
Compounded Interest = principal (1+ rate of interest per year / times compounded per year) to the power (times compounded per year * number of years)   à http://qrc.depaul.edu/StudyGuide2009/Notes/Savings%20Accounts/Compound%20Interest.htm
 
 
Posted by at No comments:

Wednesday, January 30, 2013

Dynamic LINQ Extension [provided my MS]

 
 
 
Add following code in a “.cs” files and start using
 
//Copyright (C) Microsoft Corporation.  All rights reserved.
 
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Reflection.Emit;
using System.Threading;
using System.Collections;
 
namespace System.Linq.Dynamic
{
    public static class DynamicQueryable
    {
        public static IQueryable Join(this IQueryable outer, IEnumerable inner, string outerSelector, string innerSelector, string resultsSelector, params object[] values)
        {
            if (inner == null) throw new ArgumentNullException("inner");
            if (outerSelector == null) throw new ArgumentNullException("outerSelector");
            if (innerSelector == null) throw new ArgumentNullException("innerSelector");
            if (resultsSelector == null) throw new ArgumentNullException("resultsSelctor");
 
            LambdaExpression outerSelectorLambda = DynamicExpression.ParseLambda(outer.ElementType, null, outerSelector, values);
            LambdaExpression innerSelectorLambda = DynamicExpression.ParseLambda(inner.AsQueryable().ElementType, null, innerSelector, values);
 
            ParameterExpression[] parameters = new ParameterExpression[] {
            Expression.Parameter(outer.ElementType, "outer"), Expression.Parameter(inner.AsQueryable().ElementType, "inner") };
            LambdaExpression resultsSelectorLambda = DynamicExpression.ParseLambda(parameters, null, resultsSelector, values);
 
            return outer.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Join",
                    new Type[] { outer.ElementType, inner.AsQueryable().ElementType, outerSelectorLambda.Body.Type, resultsSelectorLambda.Body.Type },
                    outer.Expression, inner.AsQueryable().Expression, Expression.Quote(outerSelectorLambda), Expression.Quote(innerSelectorLambda), Expression.Quote(resultsSelectorLambda)));
        }
 
        //The generic overload.
        public static IQueryable<T> Join<T>(this IQueryable<T> outer, IEnumerable<T> inner, string outerSelector, string innerSelector, string resultsSelector, params object[] values)
        {
            return (IQueryable<T>)Join((IQueryable)outer, (IEnumerable)inner, outerSelector, innerSelector, resultsSelector, values);
        }
 
        public static IQueryable<T> Where<T>(this IQueryable<T> source, string predicate, params object[] values)
        {
            return (IQueryable<T>)Where((IQueryable)source, predicate, values);
        }
 
        public static IQueryable Where(this IQueryable source, string predicate, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (predicate == null) throw new ArgumentNullException("predicate");
            LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, typeof(bool), predicate, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Where",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Quote(lambda)));
        }
 
        public static IQueryable Select(this IQueryable source, string selector, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (selector == null) throw new ArgumentNullException("selector");
            LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, null, selector, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Select",
                    new Type[] { source.ElementType, lambda.Body.Type },
                    source.Expression, Expression.Quote(lambda)));
        }
 
        public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, params object[] values)
        {
            return (IQueryable<T>)OrderBy((IQueryable)source, ordering, values);
        }
 
        public static IQueryable OrderBy(this IQueryable source, string ordering, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (ordering == null) throw new ArgumentNullException("ordering");
            ParameterExpression[] parameters = new ParameterExpression[] {
                Expression.Parameter(source.ElementType, "") };
            ExpressionParser parser = new ExpressionParser(parameters, ordering, values);
            IEnumerable<DynamicOrdering> orderings = parser.ParseOrdering();
            Expression queryExpr = source.Expression;
            string methodAsc = "OrderBy";
            string methodDesc = "OrderByDescending";
            foreach (DynamicOrdering o in orderings)
            {
                queryExpr = Expression.Call(
                    typeof(Queryable), o.Ascending ? methodAsc : methodDesc,
                    new Type[] { source.ElementType, o.Selector.Type },
                    queryExpr, Expression.Quote(Expression.Lambda(o.Selector, parameters)));
                methodAsc = "ThenBy";
                methodDesc = "ThenByDescending";
            }
            return source.Provider.CreateQuery(queryExpr);
        }
 
        public static IQueryable Take(this IQueryable source, int count)
        {
            if (source == null) throw new ArgumentNullException("source");
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Take",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Constant(count)));
        }
 
        public static IQueryable Skip(this IQueryable source, int count)
        {
            if (source == null) throw new ArgumentNullException("source");
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Skip",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Constant(count)));
        }
 
        public static IQueryable<T> GroupBy<T>(this IQueryable source, string keySelector, string elementSelector, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (keySelector == null) throw new ArgumentNullException("keySelector");
            if (elementSelector == null) throw new ArgumentNullException("elementSelector");
            LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, keySelector, values);
            LambdaExpression elementLambda = DynamicExpression.ParseLambda(source.ElementType, null, elementSelector, values);
            return source.Provider.CreateQuery<T>(
                Expression.Call(
                    typeof(Queryable), "GroupBy",
                    new Type[] { source.ElementType, keyLambda.Body.Type, elementLambda.Body.Type },
                    source.Expression, Expression.Quote(keyLambda), Expression.Quote(elementLambda)));
        }
 
        public static IQueryable GroupBy(this IQueryable source, string keySelector, string elementSelector, params object[] values)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (keySelector == null) throw new ArgumentNullException("keySelector");
            if (elementSelector == null) throw new ArgumentNullException("elementSelector");
            LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, keySelector, values);
            LambdaExpression elementLambda = DynamicExpression.ParseLambda(source.ElementType, null, elementSelector, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "GroupBy",
                    new Type[] { source.ElementType, keyLambda.Body.Type, elementLambda.Body.Type },
                    source.Expression, Expression.Quote(keyLambda), Expression.Quote(elementLambda)));
        }
 
        public static bool Any(this IQueryable source)
        {
            if (source == null) throw new ArgumentNullException("source");
            return (bool)source.Provider.Execute(
                Expression.Call(
                    typeof(Queryable), "Any",
                    new Type[] { source.ElementType }, source.Expression));
        }
 
        public static int Count(this IQueryable source)
        {
            if (source == null) throw new ArgumentNullException("source");
            return (int)source.Provider.Execute(
                Expression.Call(
                    typeof(Queryable), "Count",
                    new Type[] { source.ElementType }, source.Expression));
        }
    }
 
    public abstract class DynamicClass
    {
        public override string ToString()
        {
            PropertyInfo[] props = this.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            StringBuilder sb = new StringBuilder();
            sb.Append("{");
            for (int i = 0; i < props.Length; i++)
            {
                if (i > 0) sb.Append(", ");
                sb.Append(props[i].Name);
                sb.Append("=");
                sb.Append(props[i].GetValue(this, null));
            }
            sb.Append("}");
            return sb.ToString();
        }
    }
 
    public class DynamicProperty
    {
        string name;
        Type type;
 
        public DynamicProperty(string name, Type type)
        {
            if (name == null) throw new ArgumentNullException("name");
            if (type == null) throw new ArgumentNullException("type");
            this.name = name;
            this.type = type;
        }
 
        public string Name
        {
            get { return name; }
        }
 
        public Type Type
        {
            get { return type; }
        }
    }
 
    public static class DynamicExpression
    {
        public static Expression Parse(Type resultType, string expression, params object[] values)
        {
            ExpressionParser parser = new ExpressionParser(null, expression, values);
            return parser.Parse(resultType);
        }
 
        public static LambdaExpression ParseLambda(Type itType, Type resultType, string expression, params object[] values)
        {
            return ParseLambda(new ParameterExpression[] { Expression.Parameter(itType, "") }, resultType, expression, values);
        }
 
        public static LambdaExpression ParseLambda(ParameterExpression[] parameters, Type resultType, string expression, params object[] values)
        {
            ExpressionParser parser = new ExpressionParser(parameters, expression, values);
            return Expression.Lambda(parser.Parse(resultType), parameters);
        }
 
        public static Expression<Func<T, S>> ParseLambda<T, S>(string expression, params object[] values)
        {
            return (Expression<Func<T, S>>)ParseLambda(typeof(T), typeof(S), expression, values);
        }
 
        public static Type CreateClass(params DynamicProperty[] properties)
        {
            return ClassFactory.Instance.GetDynamicClass(properties);
        }
 
        public static Type CreateClass(IEnumerable<DynamicProperty> properties)
        {
            return ClassFactory.Instance.GetDynamicClass(properties);
        }
    }
 
    internal class DynamicOrdering
    {
        public Expression Selector;
        public bool Ascending;
    }
 
    internal class Signature : IEquatable<Signature>
    {
        public DynamicProperty[] properties;
        public int hashCode;
 
        public Signature(IEnumerable<DynamicProperty> properties)
        {
            this.properties = properties.ToArray();
            hashCode = 0;
            foreach (DynamicProperty p in properties)
            {
                hashCode ^= p.Name.GetHashCode() ^ p.Type.GetHashCode();
            }
        }
 
        public override int GetHashCode()
        {
            return hashCode;
        }
 
        public override bool Equals(object obj)
        {
            return obj is Signature ? Equals((Signature)obj) : false;
        }
 
        public bool Equals(Signature other)
        {
            if (properties.Length != other.properties.Length) return false;
            for (int i = 0; i < properties.Length; i++)
            {
                if (properties[i].Name != other.properties[i].Name ||
                    properties[i].Type != other.properties[i].Type) return false;
            }
            return true;
        }
    }
 
    internal class ClassFactory
    {
        public static readonly ClassFactory Instance = new ClassFactory();
 
        static ClassFactory() { }  // Trigger lazy initialization of static fields
 
        ModuleBuilder module;
        Dictionary<Signature, Type> classes;
        int classCount;
        ReaderWriterLock rwLock;
 
        private ClassFactory()
        {
            AssemblyName name = new AssemblyName("DynamicClasses");
            AssemblyBuilder assembly = AppDomain.CurrentDomain.DefineDynamicAssembly(name, AssemblyBuilderAccess.Run);
#if ENABLE_LINQ_PARTIAL_TRUST
            new ReflectionPermission(PermissionState.Unrestricted).Assert();
Posted by at No comments:
Subscribe to: Posts (Atom)