[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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

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
dyarosh
Asked:
dyarosh
  • 5
  • 5
1 Solution
 
Ioannis ParaskevopoulosCommented:
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
 
dyaroshAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dyaroshAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
dyaroshAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
dyaroshAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
dyaroshAuthor Commented:
Thank you so much.  I kept trying different combinations and couldn't come up with the right one!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now