Link to home
Start Free TrialLog in
Avatar of cefranklin
cefranklinFlag for United States of America

asked on

C# Filtering With DataGridView And/Or BindingSource

I am writing an app and I am at the stage where I want to implement some filters.  I am using a DataGridView with a BindingSource bound to an Entity.

From what I have read, I cannot use binsingsource.filter since this is an entity framework so,
this is what I have resorted to:  writing filtering code for every scenario.  What a pain!  Is there an easier way to do this?

One of the problems I am running into is that these filters semi-work the way I want them to but, the text filter runs on all of the tickets.  I want it to run on the tickets that are currently displayed. So, on my drop down, I have "Opened Tickets", "Closed Tickets", My Tickets" and "Past Due Tickets".  When I do a search for something, I want it to take into account which "view" I am on.

And for other reasons, I can't have my cake and eat it too, as in, I have to use LINQ to select the tickets apparently. If I do not, the DataGridView just displays the type name of any related entities...  I could do this if I did not have to use LINQ.

As you can see below, it will get pretty tedious having to write these select statements for everything...  but, I don't know how else to get the related entities to display like firstname and last name instead of "TicketLib.Person" in the GridView. If that was solved, I'd revert back to what I had that was working... Here is the question to that, if you are interested in another 500 points. https://www.experts-exchange.com/questions/27929773/c-DataGridView-Column-Displaying-Class-Name.html

Here is my code:

private void BuildFilter()
        {
            
            switch (ticketFilterComboBox.SelectedItem.ToString())
            {
                case "Opened Tickets":
                    ticketList = from tickets in context.Tickets
                                 where tickets.DateClosed == null
                                 select new
                                 {
                                     Id = tickets.Id,
                                     Title = tickets.Title,
                                     DateOpened = tickets.DateOpened,
                                     DateClosed = tickets.DateClosed,
                                     OpenedBy = tickets.OpenedBy.FirstName + " " + tickets.OpenedBy.LastName,
                                     Store = tickets.Store.FirstName + tickets.Store.LastName,
                                     DueBy = tickets.DueBy,
                                     TicketNotes = tickets.TicketNotes.Count
                                 };

                    break;

                case "Closed Tickets":
                    ticketList = from tickets in context.Tickets
                                 where tickets.DateClosed != null
                                 select new
                                 {
                                     Id = tickets.Id,
                                     Title = tickets.Title,
                                     DateOpened = tickets.DateOpened,
                                     DateClosed = tickets.DateClosed,
                                     OpenedBy = tickets.OpenedBy.FirstName + " " + tickets.OpenedBy.LastName,
                                     Store = tickets.Store.FirstName + tickets.Store.LastName,
                                     DueBy = tickets.DueBy,
                                     TicketNotes = tickets.TicketNotes.Count
                                 };
                    break;

                case "Past Due Tickets":
                    ticketList = from tickets in context.Tickets
                                 where tickets.DueBy <= DateTime.Now
                                 select new
                                 {
                                     Id = tickets.Id,
                                     Title = tickets.Title,
                                     DateOpened = tickets.DateOpened,
                                     DateClosed = tickets.DateClosed,
                                     OpenedBy = tickets.OpenedBy.FirstName + " " + tickets.OpenedBy.LastName,
                                     Store = tickets.Store.FirstName + tickets.Store.LastName,
                                     DueBy = tickets.DueBy,
                                     TicketNotes = tickets.TicketNotes.Count
                                 };
                    break;

                case "My Tickets":
                    ticketList = from tickets in context.Tickets
                                 where tickets.OpenedBy.FirstName + " " + tickets.OpenedBy.LastName == _myName
                                 select new
                                 {
                                     Id = tickets.Id,
                                     Title = tickets.Title,
                                     DateOpened = tickets.DateOpened,
                                     DateClosed = tickets.DateClosed,
                                     OpenedBy = tickets.OpenedBy.FirstName + " " + tickets.OpenedBy.LastName,
                                     Store = tickets.Store.FirstName + tickets.Store.LastName,
                                     DueBy = tickets.DueBy,
                                     TicketNotes = tickets.TicketNotes.Count
                                 };
                    break;
                default:
                    break;
            }
            if (searchBox.Text == "Search For..." || String.IsNullOrWhiteSpace(searchBox.Text) || String.IsNullOrEmpty(searchBox.Text))
                goto End;

            ticketList = from tickets in context.Tickets
                         where tickets.Title.Contains(searchBox.Text) ||
                                tickets.Description.Contains(searchBox.Text) ||
                                tickets.OpenedBy.FirstName.Contains(searchBox.Text) ||
                                tickets.OpenedBy.LastName.Contains(searchBox.Text) ||
                                tickets.OpenedBy.PrimaryEmail.Contains(searchBox.Text) ||
                                tickets.OpenedBy.SecondaryEmail.Contains(searchBox.Text) ||
                                tickets.OpenedBy.TelephoneNumber.Contains(searchBox.Text) ||
                                tickets.OpenedBy.CellphoneNumber.Contains(searchBox.Text) ||
                                tickets.Store.CellphoneNumber.Contains(searchBox.Text) ||
                                tickets.Store.PrimaryEmail.Contains(searchBox.Text) ||
                                tickets.Store.SecondaryEmail.Contains(searchBox.Text) ||
                                tickets.Store.TelephoneNumber.Contains(searchBox.Text) ||
                                (tickets.Store.FirstName + tickets.Store.LastName).Contains(searchBox.Text)
                         select new
                         {
                             Id = tickets.Id,
                             Title = tickets.Title,
                             DateOpened = tickets.DateOpened,
                             DateClosed = tickets.DateClosed,
                             OpenedBy = tickets.OpenedBy.FirstName + " " + tickets.OpenedBy.LastName,
                             Store = tickets.Store.FirstName + tickets.Store.LastName,
                             DueBy = tickets.DueBy,
                             TicketNotes = tickets.TicketNotes.Count
                         };
        End:
            ticketBindingSource.DataSource = ticketList;
        toolStripLabelNumberOfTickets.Text = "Number Of Tickets: " + dataGridView1.RowCount.ToString();
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            Thread.GetDomain().SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);
            WindowsPrincipal principal = (WindowsPrincipal)Thread.CurrentPrincipal;
            using (PrincipalContext pc = new PrincipalContext(ContextType.Domain))
            {
                UserPrincipal up = UserPrincipal.FindByIdentity(pc, principal.Identity.Name);
                _myName = up.GivenName + " " + up.Surname;
            }
            context = new TicketLib.TicketDataModelContainer();
            ticketList = from tickets in context.Tickets
                         select new
                         {
                             Id = tickets.Id,
                             Title = tickets.Title,
                             DateOpened = tickets.DateOpened,
                             DateClosed = tickets.DateClosed,
                             OpenedBy = tickets.OpenedBy.FirstName + " " + tickets.OpenedBy.LastName,
                             Store = tickets.Store.FirstName + tickets.Store.LastName,
                             DueBy = tickets.DueBy,
                             TicketNotes = tickets.TicketNotes.Count
                         };

            ticketBindingSource.DataSource = ticketList;
            ticketFilterComboBox.SelectedItem = "All Tickets";
            //dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
            dataGridView1.AllowUserToResizeColumns = true;
            toolStripLabelNumberOfTickets.Text = "Number Of Tickets: " + dataGridView1.RowCount.ToString();
        }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cefranklin

ASKER

I had looked at this originally and was hoping for something I could code myself but, it works. Thanks.