Solved

DataGridView filter - combobox column

Posted on 2006-06-20
11
14,029 Views
Last Modified: 2012-06-27
Hello,

I am working on implementing a full-text search for a datagridview.  Currently, I just use some backgruond logic to create a rowFilter statement and set this into the bindingSource.Filter property of the dataGridView's datasource.

This method, however, does not account for a DataGridView's combobox columns - since I am filtering the underlying datasource, I am really filtering on the value of the column, not on the text - what I really want to be able to do is filter by the text (hence the "full-text search"...).

My question is the following: Is there a way to filter the DataGridView so that I can incorporate the text of the cells, rather than the values?  

I would like to avoid 3rd party controls if at all possible, so please don't suggest this as a solution...

Thanks!
0
Comment
Question by:TLevin10
11 Comments
 
LVL 10

Expert Comment

by:jagadeesh_motamarri
ID: 16948715
OK...this might be a very good example for you - http://www.codeproject.com/cs/miscctrl/gridextensions.asp

____Jags.
0
 
LVL 3

Expert Comment

by:Dimkov
ID: 16950334
Instead of using bindingSource.Filter option, why don’t you build your SQL String?
there you can put anything you want after "where"

select * from mytable where firstcolumn="8" and secondcolumn="work"

or better
CString temp;
temp.Format("select * from mytable where firstcolumn=%d and secondcolumn='%s'", field1.iValue, field2.sValue);


Hope it helps,
Dimkov
0
 

Author Comment

by:TLevin10
ID: 16950488
hey Jags,

Thanks for the sample - I saw this one already, and it has a very simple way of dealing with combobox columns - it doesn't allow them!  Too bad, I liked the ideas...
0
 

Author Comment

by:TLevin10
ID: 16950506
Hi Dimkov,

Unfortuntely, this is an action I have to avoid - doing a full text search of 50,000 records that are already at the client would be silly to reload them all from the server.  In addition, I would have to do a bunch of inner joins with other tables to ACTUALLY implement the full text search, since your solution still uses the ID to search by, which isn't a full-text search...

sadly, that solution isn't applicable in my situation.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Expert Comment

by:Dimkov
ID: 16957526
If I got it right, you have the recordset on the client, and you want to search through the data in it?

why don't you open another recordset and do an sql query on the first one?
then just swap the recordset beind displayed

I didn't got the part "full text search" - > can you give an example?
0
 

Author Comment

by:TLevin10
ID: 16959186
Well as far as I know its not possible to do a SQL query on a client side dataset in C#.  Once a dataset is pulled from the SQL server, the relationships not intrinsically downloaded are lost - in a sense, the dataset doesn't know whether the "category_id" column in one is related to the "categoryID" primary key of another column.  There is no knowledge of foreign key relationships outside of the SQL server itself, unless one provides it themself.  

The best I can do to describe what a "full text search" is that I convert all datatypes to a string, so a datetime datatype becomes a string representation, as does an int, etc.  Once everythign is a string, we can do a 'LIKE' filter on the client side to see if the strings contain the string we are searching for.  As an example:

ID                 Name                    Date                         Price
1                  Vendor                  11/25/2005               $400.00
2                  Wholesaler            1/07/2006                 $805.25
3                  Retailer                 1/21/2006                 $999.99

When the user views the table, they see string representations of every column.  The price is represented as currency, the ID as an int, and the date a short-date string.  In a full text search, if the user typed 'ven' they would get only row #1, since this is the only row where one of the columns contains the string 'ven'.  However, if they typed '05', they would get records 1 and 2, since the date in ID #1 contains '05' in the 2005, and the price in #2 cotains 05 in $805.25.  This way, the user is visibly searching through the records they see on screen and doens't have to have any knowledge of the data behind it.  

If you want to see a program which uses this principle, look at Apple iTunes - when you type in a "search" criteria, it searches on all visible columns and finds records which visibly match the criteria you are searching for.

I just realized I posted this question in the wrong category (should have been in Programming->C#) so I will see what it takes to get it moved there.
0
 

Author Comment

by:TLevin10
ID: 16965402
Well since I've spent a lot of time explaining my own question, I have finally solved it.  The actual implementation is somewhat tricky, but it involves creating a RowFilter string based upon the datasource of the grid being assumed as filterable, and then assuming that the combobox columns are fed by a dataset.  With this information, one can filter the underlying dataset of a combobox column and return a string of values, which are comma delimited, and include these in an "IN()" clause for the rowfilter.  The rest of the columns are filtered using a combination of "AND" statements and "OR" statements, with any regular text columns being converted to string representations and then filtered.  See the code below, which is used to build the RowFilter string, passed the search text and the datagridview:

----------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;

namespace FilterComboBoxTester
{
    class FullTextSearch
    {
        public static string CreateRowFilter(DataGridView dg, string text)
        {
            string rowfilter = "";
            if (text != "")
            {

                string searchstring = "";
                foreach (DataGridViewColumn d in dg.Columns)
                {
                    string p = "";
                    if (d.GetType() == typeof(DataGridViewComboBoxColumn))
                    {
                        DataGridViewComboBoxColumn cbc = d as DataGridViewComboBoxColumn;
                        DataSet underdata = cbc.DataSource as DataSet;
                       
                        string FilterString = "";
                        if (underdata != null)
                        {
                            string displayColumn = cbc.DisplayMember.Replace(underdata.Tables[0].TableName + ".", "");
                            string valueColumn = cbc.ValueMember.Replace(underdata.Tables[0].TableName + ".", "");
                            FilterString = GetFilterByID(underdata, valueColumn, displayColumn, text);
                        }

                        p = string.Format("{0} IN ({1})", cbc.DataPropertyName, FilterString);
                    }
                    else
                    {
                        if (d.IsDataBound && d.Visible)
                            p = string.Format("(CONVERT({0}, System.String) LIKE '*{{0}}*')", d.DataPropertyName);
                    }

                    if (p != "")
                    {
                        if (searchstring == "")
                            searchstring = p;
                        else
                            searchstring = string.Format("{0} OR {1}", searchstring, p);
                    }
                }

                string[] s = text.Replace("'", "''").Split(' ');
                for (int i = 0; i < s.Length; i++)
                {
                    string x = s[i];
                    string add = string.Format("(" + searchstring + ")", x);
                    if (i > 0)
                        rowfilter = string.Format("{0} AND {1}", rowfilter, add);
                    else
                        rowfilter = add;
                }
            }
            else
                rowfilter = "";


            return rowfilter;
        }

        public static string GetFilterByID(DataSet data, string valueColumn, string displayColumn, string searchString)
        {
            DataView dv = new DataView(data.Tables[0]);
            dv.RowFilter = string.Format("CONVERT({0}, System.String) LIKE '*{1}*'", displayColumn, searchString);
            string returnString = "";
            foreach (DataRowView r in dv)
            {
                if (returnString == "")
                    returnString = r[valueColumn].ToString();
                else
                    returnString = string.Format("{0}, {1}", returnString, r[valueColumn].ToString());
            }

            return returnString;
        }
    }
}
----------------------------------------------------------------------------------------------

The class can be used by calling the following (assuming dataGridBinder is a filterable component, such as the DataBinder or a DataView):

----------------------------------------------------------------------------------------------
string searchText = this.TextBox1.Text;
this.dataGridBinder.Filter = FullTextSearch.CreateRowFilter(this.dataGridView1, text);
----------------------------------------------------------------------------------------------

After that, the data grid is sorted according the text which is displayed, not just the underlying values!  I think this would be helpful for other EE members trying to do the same thing - please let me know if it helps you!

0
 

Author Comment

by:TLevin10
ID: 16985877
Oops - mixed up a quick bit there - call the method by using:

----------------------------------------------------------------------------------------------
string searchText = this.TextBox1.Text;
this.dataGridBinder.Filter = FullTextSearch.CreateRowFilter(this.dataGridView1, searchText);
----------------------------------------------------------------------------------------------
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 17025489
Closed, 500 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ConsoleSql 1 34
ASP.NET Web API or ASP.NET Core MVC? 3 52
How to LOAD up a .NET Framework website with data? 2 40
Memory Usage 2 50
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now