Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need to delete a column in my gridview that is bound to a List object

Posted on 2014-02-10
10
335 Views
Last Modified: 2014-02-11
I am new to MVC and created a model using a class that contains data that I want to display in a gridview.  I retrieve the data from my database and put it in a List of the type of my model class (i.e. List<EmployeeExtract> employeeList = new List<EmployeeExtract>()).  The List is then bound to my gridview and displays the data in my list.  All fields are displayed except the last 3 columns since these column were only used for loading the data.

I have a link on my View to download the gridview to an excel spreadsheet.  The link calls a method that takes the gridview and calls the RenderControl method to download the file.  Everything works great except the downloaded file contains the last 3 columns.  This downloaded file is used as input into another system and requires a predefined format.  The last 3 columns are not part of that format.

How do I remove the last 3 columns before creating the excel spreadsheet?  Since the DataSource is a list, how do I remove the last 3 properties in each row of the list?  I see a list.Remove which removes an entire item (i.e. row) but can't find something that would remove just a property (i.e. column) in the list.  

Here is my model definition:
namespace ENL.Models
{
    public partial class EmployeeExtract
    {
        public string action { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string businessphone { get; set; }
        public string businessphoneext { get; set; }
        public string homephone { get; set; }
        public string mobilephone { get; set; }
        public string smsdevice1 { get; set; }
        public string businessphone2 { get; set; }
        public string businessmobilephone { get; set; }
        public string mobilephone2provider { get; set; }
        public string emailaddress { get; set; }
        public string emailaddress2 { get; set; }
        public string group1 { get; set; }
        public string group2 { get; set; }
        public string group3 { get; set; }
        public string group4 { get; set; }
        public string group5 { get; set; }
        public string group6 { get; set; }
        public string group7 { get; set; }
        public string group8 { get; set; }
        public string end { get; set; }
        public int profileid { get; set; }
        public int locationid { get; set; }
        public string locationdesc { get; set; }
    }

    public class EmployeeExtractDbContext : DbContext
    {
        public DbSet<EmployeeExtract> Employees { get; set; }
    }

}

Open in new window


Here is my code for creating the List (getEmployeeExtract):
            string SQL = "SELECT * " +
                            "FROM (SELECT '' AS Action, e.firstname, e.lastname,  '' AS businessphone, '' AS businessphoneext, " +
                            "MAX(CASE WHEN phonetypeid = 5 THEN p.phonenumber END) homephone, " +
                            "MAX(CASE WHEN phonetypeid = 6 THEN p.phonenumber END) mobilephone, " +
                            "'' AS SMSDevice1, '' AS businessphone2, " +
                            "MAX(CASE WHEN phonetypeid = 4 THEN p.phonenumber END) businessmobilephone, " +
                            "'' AS mobilephone2provider, " +
                            "e.emailaddress, '' as emailaddress2, " +
                            "'' as group1, '' as group2, '' as group3, '' as group4, '' as group5, '' as group6, '' as group7, '' as group8, 'END' as end, " +
                            "e.employeeid, e.locationid, l.locationdesc " +
                            "FROM emp_owner.emp_employee e " +
                            "INNER JOIN emp_owner.emp_location l ON l.locationid = e.locationid " +
                            "INNER JOIN emp_owner.emp_phonenumbers p ON p.employeeid = e.employeeid AND p.phonetypeid IN (4, 5, 6) AND p.emergencynotificationsystem = 'Y' " +
                            "WHERE terminationdate IS NULL " +
                            "GROUP BY e.employeeid, e.firstname, e.lastname, e.locationid, l.locationdesc, e.emailaddress) " +
                            "WHERE COALESCE(homephone, mobilephone, businessmobilephone) IS NOT NULL " +
                            "ORDER BY lastname, firstname ";
            DataTable dt = EMPData.EmployeeTBL.GenericGet(EMPData.connection, SQL);

            // Convert DataTable to List
            if (dt != null)
            {
                var convertedList = (from rw in dt.AsEnumerable()
                                     select new EmployeeExtract()
                                     {
                                         action = "",
                                         businessphone = "",
                                         businessphoneext = "",
                                         smsdevice1 = "",
                                         businessphone2 = "",
                                         mobilephone2provider = "",
                                         emailaddress2 = "",
                                         end = "END",
                                         profileid = Convert.ToInt32(rw["employeeid"]),
                                         firstname = rw["firstname"].ToString(),
                                         lastname = rw["lastname"].ToString(),
                                         locationid = Convert.ToInt32(rw["locationid"]),
                                         locationdesc = rw["locationdesc"].ToString(),
                                         emailaddress = rw["emailaddress"].ToString(),
                                         homephone = formatPhone.AddPhoneFormatting(rw["homephone"].ToString()),
                                         mobilephone = formatPhone.AddPhoneFormatting(rw["mobilephone"].ToString()),
                                         businessmobilephone = formatPhone.AddPhoneFormatting(rw["businessmobilephone"].ToString()),
                                         group1 = GetGroupNameByEMPLocationID(Convert.ToInt32(rw["locationid"]))
                                     }).ToList();
                // Get all Employees in ENL_MultiGroup_Contacts that are Active
                var contacts = db.ENL_MultiGroup_Contacts.ToList();

                // For each employee in contacts, find the employee in the convertedList and update the groups
                EmployeeExtract clItem;
                string groupName = "";
                foreach (var c in contacts)
                {
                    // Find EmployeeID in convertedList
                    clItem = convertedList.Find(a => a.profileid == c.EmployeeProfileID);
                    if (clItem != null)
                    {
                        // Get the groupName
                        groupName = GetGroupNameByID(c.ENLGroupNameID);

                        // Find the first group that is available 
                        if (String.IsNullOrEmpty(clItem.group1))
                            clItem.group1 = groupName;
                        else if (String.IsNullOrEmpty(clItem.group2))
                            clItem.group2 = groupName;
                        else if (String.IsNullOrEmpty(clItem.group3))
                            clItem.group3 = groupName;
                        else if (String.IsNullOrEmpty(clItem.group4))
                            clItem.group4 = groupName;
                        else if (String.IsNullOrEmpty(clItem.group5))
                            clItem.group5 = groupName;
                        else if (String.IsNullOrEmpty(clItem.group6))
                            clItem.group6 = groupName;
                        else if (String.IsNullOrEmpty(clItem.group7))
                            clItem.group7 = groupName;
                        else if (String.IsNullOrEmpty(clItem.group8))
                            clItem.group8 = groupName;
                    }
                }

                // Loop through convertedList and remove any employees that don't have a group in group1
                int i = 0;
                while (i < convertedList.Count)
                {
                    clItem = convertedList.ElementAt(i);
                    if (String.IsNullOrEmpty(clItem.group1))
                    {
                        convertedList.Remove(clItem);
                    }
                    else
                    {
                        i++;
                    }
                }
                return convertedList;

Open in new window


profileid and locationid are needed for populating the group2 - 8 fields.  locationdesc is no longer needed and can be removed but hasn't been yet.

Here is the code where the list is bound to the gridview:

                Profile_ENL_Methods methods = new Profile_ENL_Methods();
                employeeList = methods.getEmployeeExtract();

                // Export list
                var grid = new GridView();
                grid.DataSource = employeeList;
                grid.DataBind();
                Session["ENLExtract"] = grid;

Open in new window


Any help is greatly appreciated!  I need to get this done and would prefer that the user not have to edit the excel file created to remove the unwanted columns.
0
Comment
Question by:dyarosh
  • 5
  • 5
10 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39849487
Hi,

One way to go is your getEmployeeExtract to return only the fields you require.

So instead of :

return convertedList;

Open in new window


Use:

return convertedList.Select
               (
                   x=>new
                   {
                       x.action,
                       x.businessphone,
                       x.businessphoneext,
                       x.smsdevice1,
                       x.businessphone2,
                       x.mobilephone2provider,
                       x.emailaddress2,
                       x.end,
                       x.profileid,
                       x.firstname,
                       x.lastname,
                       x.locationid,
                       x.locationdesc,
                       x.emailaddress,
                       x.homephone,
                       x.mobilephone,
                       x.businessmobilephone,
                       x.group1
                   }
               );

Open in new window


This will return a list of only the desired data. Of course feel free to modify the exact columns you need to return as i do not know exactly which columns you want.

For this to work you also need to modify your code for binding to the grid:

Profile_ENL_Methods methods = new Profile_ENL_Methods();
var EmployeeExtract = methods.getEmployeeExtract();

// Export list
var grid = new GridView();
grid.DataSource = EmployeeExtract ;
grid.DataBind();
Session["ENLExtract"] = grid;

Open in new window


Giannis
0
 

Author Comment

by:dyarosh
ID: 39849966
I followed your suggestion and changed my return convertedList to

                return convertedList.Select
                    (
                        x => new
                        {
                            x.action,
                            x.businessphone,
                            x.businessphoneext,
                            x.smsdevice1,
                            x.businessphone2,
                            x.mobilephone2provider,
                            x.emailaddress2,
                            x.end,
                            x.firstname,
                            x.lastname,
                            x.emailaddress,
                            x.homephone,
                            x.mobilephone,
                            x.businessmobilephone,
                            x.group1,
                            x.group2,
                            x.group3,
                            x.group4,
                            x.group5,
                            x.group6,
                            x.group7,
                            x.group8
                        }
                    );

but now I am getting the following error:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.List<ENL.Models.EmployeeExtract>'. An explicit conversion exists (are you missing a cast?)

What should I cast?
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39849989
I guess you are getting this error because you have defined your function as:


public List<EmployeeExtract> getEmployeeExtract(args...)

Open in new window


Try adding a a class in your Models to represent the returned type:

    public partial class EmployeeExtractNew
    {
        public string action { get; set; }
        public string businessphone { get; set; }
        public string businessphoneext { get; set; }
        public string smsdevice1 { get; set; }
        public string businessphone2 { get; set; }
        public string mobilephone2provider { get; set; }
        public string emailaddress2 { get; set; }
        public string end { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string emailaddress { get; set; }
        public string homephone { get; set; }
        public string mobilephone { get; set; }
        public string businessmobilephone { get; set; }
        public string group1 { get; set; }
        public string group2 { get; set; }
        public string group3 { get; set; }
        public string group4 { get; set; }
        public string group5 { get; set; }
        public string group6 { get; set; }
        public string group7 { get; set; }
        public string group8 { get; set; }
    }

Open in new window


And then your function to be:

public List<EmployeeExtractNew> getEmployeeExtract(args...)

Open in new window


Of course add a suitable name instead of EmployeeExtractNew

Giannis
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:dyarosh
ID: 39850078
I did as you suggested and created another class with just the fields I need:

    public partial class ExcelExtract
    {
        public string action { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string businessphone { get; set; }
        public string businessphoneext { get; set; }
        public string homephone { get; set; }
        public string mobilephone { get; set; }
        public string smsdevice1 { get; set; }
        public string businessphone2 { get; set; }
        public string businessmobilephone { get; set; }
        public string mobilephone2provider { get; set; }
        public string emailaddress { get; set; }
        public string emailaddress2 { get; set; }
        public string group1 { get; set; }
        public string group2 { get; set; }
        public string group3 { get; set; }
        public string group4 { get; set; }
        public string group5 { get; set; }
        public string group6 { get; set; }
        public string group7 { get; set; }
        public string group8 { get; set; }
        public string end { get; set; }
    }

Open in new window


I changed my method to return the new class:          public List<ExcelExtract> getEmployeeExtract()

In my method I do the following which compiles without any errors:
                List<ExcelExtract> extractList = new List<ExcelExtract>();
                extractList = (List < ExcelExtract >) convertedList.Select
                    (
                        x => new 
                        {
                            x.action,
                            x.businessphone,
                            x.businessphoneext,
                            x.smsdevice1,
                            x.businessphone2,
                            x.mobilephone2provider,
                            x.emailaddress2,
                            x.end,
                            x.firstname,
                            x.lastname,
                            x.emailaddress,
                            x.homephone,
                            x.mobilephone,
                            x.businessmobilephone,
                            x.group1,
                            x.group2,
                            x.group3,
                            x.group4,
                            x.group5,
                            x.group6,
                            x.group7,
                            x.group8
                        }
                    );
                return extractList;

Open in new window


However, when I run the program I get the following error when trying to select from the list:

Unable to cast object of type 'WhereSelectListIterator`2[ENL.Models.EmployeeExtract,<>f__AnonymousType1`22[System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String]]' to type 'System.Collections.Generic.List`1[ENL.Models.ExcelExtract]'.

Open in new window


Any ideas?
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39850183
Getting closer....

Replace

x => new 

Open in new window


with

x => new ExcelExtract 

Open in new window


on line 4 of the second code extract on your last post.

Giannis
0
 

Author Comment

by:dyarosh
ID: 39850210
I had already tried that but get the following error:

Cannot initialize type 'ENL.Models.ExcelExtract' with a collection initializer because it does not implement 'System.Collections.IEnumerable'
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39850238
Try

                List<ExcelExtract> extractList = new List<ExcelExtract>();
                extractList = (List < ExcelExtract >) convertedList.Select
                    (
                        x => new ExcelExtract 
                        {
                            action = x.action,
                            businessphone = x.businessphone,
                            businessphoneex = x.businessphoneext,
                            smsdevice1 = x.smsdevice1,
                            businessphone2 = x.businessphone2,
                            mobilephone2provider = x.mobilephone2provider,
                            emailaddress2 = x.emailaddress2,
                            end = x.end,
                            firstname = x.firstname,
                            lastname = x.lastname,
                            emailaddress = x.emailaddress,
                            homephone = x.homephone,
                            mobilephone = x.mobilephone,
                            businessmobilephone = x.businessmobilephone,
                            group1 = x.group1,
                            group2 = x.group2,
                            group3 = x.group3,
                            group4 = x.group4,
                            group5 = x.group5,
                            group6 = x.group6,
                            group7 = x.group7,
                            group8 = x.group8
                        }
                    );
                return extractList;
                                            

Open in new window

0
 

Author Comment

by:dyarosh
ID: 39850340
I still get the following error:

Unable to cast object of type 'WhereSelectListIterator`2[ENL.Models.EmployeeExtract,ENL.Models.ExcelExtract]' to type 'System.Collections.Generic.List`1[ENL.Models.ExcelExtract]'.
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 39850368
Hmmm... just noticed that:

You are casting the convertedList to List < ExcelExtract >. This is wrong. You do not need to cast.

                List<ExcelExtract> extractList = new List<ExcelExtract>();
                extractList = convertedList.Select
                    (
                        x => new ExcelExtract 
                        {
                            action = x.action,
                            businessphone = x.businessphone,
                            businessphoneex = x.businessphoneext,
                            smsdevice1 = x.smsdevice1,
                            businessphone2 = x.businessphone2,
                            mobilephone2provider = x.mobilephone2provider,
                            emailaddress2 = x.emailaddress2,
                            end = x.end,
                            firstname = x.firstname,
                            lastname = x.lastname,
                            emailaddress = x.emailaddress,
                            homephone = x.homephone,
                            mobilephone = x.mobilephone,
                            businessmobilephone = x.businessmobilephone,
                            group1 = x.group1,
                            group2 = x.group2,
                            group3 = x.group3,
                            group4 = x.group4,
                            group5 = x.group5,
                            group6 = x.group6,
                            group7 = x.group7,
                            group8 = x.group8
                        }
                    ).ToList<ExcelExtract>();
                return extractList;

Open in new window


Giannis
0
 

Author Closing Comment

by:dyarosh
ID: 39850491
Thank you so much.  I kept trying different combinations and couldn't come up with the right one!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question