c# - Bind gridview when column not exists in datatable but binding at itemtemplate -
i having dataset pivoting , binding grid view. working fine when itemtemplates value present in pivoted table in case if of column not present in pivoted table, @ time of binding gridview , getting error . please me how handle in grid view if of columns not present binding grid view itemtemplate. below grid view code.
<asp:gridview id="gvcoreutilization" runat="server" backcolor="white" bordercolor="#cecfce" borderstyle="solid" borderwidth="1px" cellpadding="4" forecolor="black" onrowcreated="grdpivot3_rowcreated" autogeneratecolumns="false" onrowdatabound="grdcoreutilization_rowdatabound"> <rowstyle backcolor="#f7f7de" /> <footerstyle backcolor="#cccc99" /> <pagerstyle backcolor="#f7f7de" forecolor="black" horizontalalign="right" /> <selectedrowstyle backcolor="#ce5d5a" font-bold="true" forecolor="white" /> <headerstyle backcolor="#6b696b" font-bold="true" forecolor="white" horizontalalign="left" /> <alternatingrowstyle backcolor="white" /> <columns> <asp:templatefield> <itemtemplate> <asp:label id="lblroleid" text='<%#eval("roleid") %>' runat="server" visible="false"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> supervisorname </headertemplate> <itemtemplate> <asp:label id="lblsupervisorname" text='<%#eval("supervisorname") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> userecode </headertemplate> <itemtemplate> <asp:label id="lbluserecode" text='<%#eval("userecode") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> username </headertemplate> <itemtemplate> <asp:label id="lblusername" text='<%#eval("username") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> designation </headertemplate> <itemtemplate> <asp:label id="lbldesignation" text='<%#eval("designation") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> l & d training% </headertemplate> <itemtemplate> <asp:label id="lblldtraining" text='<%#eval("l & d training%") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> non production% </headertemplate> <itemtemplate> <asp:label id="lblnonproduction" text='<%#eval("non production%") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> process support% </headertemplate> <itemtemplate> <asp:label id="lblprocesssupport" text='<%#eval("process support%") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> process training% </headertemplate> <itemtemplate> <asp:label id="lblprocesstraining" text='<%#eval("process training%") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> production% </headertemplate> <itemtemplate> <asp:label id="lblproduction" text='<%#eval("production%") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> system downtime% </headertemplate> <itemtemplate> <asp:label id="lblsystemdowntime" text='<%#eval("system downtime%") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> <asp:templatefield> <headertemplate> grand total% </headertemplate> <itemtemplate> <asp:label id="lblgrandtotal" text='<%#eval("grand total%") %>' runat="server"></asp:label> </itemtemplate> </asp:templatefield> </columns> </asp:gridview> actually "l & d training%","non production%","process support%","process training%","production%","system downtime%","grand total%" pivoted columns binding itemtemplate .for user few of these column not present , while binding grid getting error.
below code of pivot class doing pivot :-
private datatable _sourcetable = new datatable(); private ienumerable<datarow> _source = new list<datarow>(); public pivot(datatable sourcetable) { _sourcetable = sourcetable; _source = sourcetable.rows.cast<datarow>(); } public datatable pivotdata(string datafield, aggregatefunction aggregate, string[] rowfields, string[] columnfields) { datatable dt = new datatable(); string separator = "."; var rowlist = _sourcetable.defaultview.totable(true, rowfields).asenumerable().tolist(); (int index = rowfields.count() - 1; index >= 0; index--) rowlist = rowlist.orderby(x => x.field<object>(rowfields[index])).tolist(); // gets list of columns .(dot) separated. var collist = (from x in _sourcetable.asenumerable() select new { name = columnfields.select(n => x.field<object>(n)) .aggregate((a, b) => += separator + b.tostring()) }) .distinct() .orderby(m => m.name); //dt.columns.add(rowfields); foreach (string s in rowfields) dt.columns.add(s); foreach (var col in collist) dt.columns.add(col.name.tostring()); // cretes result columns.// foreach (var rowname in rowlist) { datarow row = dt.newrow(); string strfilter = string.empty; foreach (string field in rowfields) { row[field] = rowname[field]; strfilter += " , " + field + " = '" + rowname[field].tostring() + "'"; } strfilter = strfilter.substring(5); foreach (var col in collist) { string filter = strfilter; string[] strcolvalues = col.name.tostring().split(separator.tochararray(), stringsplitoptions.none); (int = 0; < columnfields.length; i++) filter += " , " + columnfields[i] + " = '" + strcolvalues[i] + "'"; row[col.name.tostring()] = getdata(filter, datafield, aggregate); } dt.rows.add(row); } return dt; } public datatable pivotallocationdata(string datafield, aggregatefunction aggregate, string[] rowfields, string[] columnfields) { datatable dt = new datatable(); string separator = "."; var rowlist = _sourcetable.defaultview.totable(true, rowfields).asenumerable().tolist(); (int index = rowfields.count() - 1; index >= 0; index--) rowlist = rowlist.orderby(x => x.field<object>(rowfields[index])).tolist(); // gets list of columns .(dot) separated. var collist = (from x in _sourcetable.asenumerable() select new { name = columnfields.select(n => x.field<object>(n)) .aggregate((a, b) => += separator + b.tostring()) }) .distinct() .orderby(m => m.name); //dt.columns.add(rowfields); foreach (string s in rowfields) dt.columns.add(s); foreach (var col in collist) dt.columns.add(col.name.tostring()); // cretes result columns.// foreach (var rowname in rowlist) { datarow row = dt.newrow(); string strfilter = string.empty; foreach (string field in rowfields) { row[field] = rowname[field]; strfilter += " , " + field + " = '" + rowname[field].tostring() + "'"; } strfilter = strfilter.substring(5); foreach (var col in collist) { string filter = strfilter; string[] strcolvalues = col.name.tostring().split(separator.tochararray(), stringsplitoptions.none); (int = 0; < columnfields.length; i++) filter += " , " + columnfields[i] + " = '" + strcolvalues[i] + "'"; row[col.name.tostring()] = getallocationdata(filter, datafield, aggregate); } dt.rows.add(row); } return dt; } /// <summary> /// retrives data matching rowfield value , columnfields values aggregate function applied on them. /// </summary> /// <param name="filter">datatable filter condition string</param> /// <param name="datafield">the column name needs spread out in data part of pivoted table</param> /// <param name="aggregate">enumeration determine function apply aggregate data</param> /// <returns></returns> private object getdata(string filter, string datafield, aggregatefunction aggregate) { try { datarow[] filteredrows = _sourcetable.select(filter); object[] objlist = filteredrows.select(x => x.field<object>(datafield)).toarray(); switch (aggregate) { case aggregatefunction.average: return getaverage(objlist); case aggregatefunction.count: return objlist.count(); case aggregatefunction.exists: return (objlist.count() == 0) ? "false" : "true"; case aggregatefunction.first: return getfirst(objlist); case aggregatefunction.last: return getlast(objlist); case aggregatefunction.max: return getmax(objlist); case aggregatefunction.min: return getmin(objlist); case aggregatefunction.sum: return getsum(objlist); default: return null; } } catch (exception ex) { return "#error"; } } /// <summary> /// retrives data matching rowfield value , columnfields values aggregate function applied on them. /// </summary> /// <param name="filter">datatable filter condition string</param> /// <param name="datafield">the column name needs spread out in data part of pivoted table</param> /// <param name="aggregate">enumeration determine function apply aggregate data</param> /// <returns></returns> private object getallocationdata(string filter, string datafield, aggregatefunction aggregate) { try { datarow[] filteredrows = _sourcetable.select(filter); object[] objlist = filteredrows.select(x => x.field<object>(datafield)).toarray(); switch (aggregate) { case aggregatefunction.average: return getaverage(objlist); case aggregatefunction.count: return objlist.count(); case aggregatefunction.exists: return (objlist.count() == 0) ? "false" : "true"; case aggregatefunction.first: return getfirst(objlist); case aggregatefunction.last: return getlast(objlist); case aggregatefunction.max: return getmax(objlist); case aggregatefunction.min: return getmin(objlist); case aggregatefunction.sum: return getallocationsum(objlist); default: return null; } } catch (exception ex) { return "#error"; } } private object getaverage(object[] objlist) { return objlist.count() == 0 ? null : (object)(convert.todecimal(getsum(objlist)) / objlist.count()); } private object getsum(object[] objlist) { return objlist.count() == 0 ? null : (object)(objlist.aggregate(new decimal(), (x, y) => x += convert.todecimal(y)) + "%"); } private object getallocationsum(object[] objlist) { return objlist.count() == 0 ? null : (object)(objlist.aggregate(new decimal(), (x, y) => x += convert.todecimal(y))); } private object getfirst(object[] objlist) { return (objlist.count() == 0) ? null : objlist.first(); } private object getlast(object[] objlist) { return (objlist.count() == 0) ? null : objlist.last(); } private object getmax(object[] objlist) { return (objlist.count() == 0) ? null : objlist.max(); } private object getmin(object[] objlist) { return (objlist.count() == 0) ? null : objlist.min(); } public enum aggregatefunction { count = 1, sum = 2, first = 3, last = 4, average = 5, max = 6, min = 7, exists = 8 }
since actual database being used has been not provided you. can apply following trick. thing nedd create wrapper class extracting ever data have formating data in defined schema being used grid.
eg. suppose have 3 column in table can contain <>3 , on. since gridview appearence same, create datatable containing rows of gridview. create function extract data , fill datatable data available , data not available filled null or blank values.
this way can mitigate problem of exception being throws. actual implementation of function converting variable length table fixed length datatable depend on actual logic used creating varaible length datatable , how mapped each other.
Comments
Post a Comment