DataGridView filter - combobox column

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!
TLevin10Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
GhostModConnect With a Mentor Commented:
Closed, 500 points refunded.

GhostMod
Community Support Moderator
0
 
jagadeesh_motamarriCommented:
OK...this might be a very good example for you - http://www.codeproject.com/cs/miscctrl/gridextensions.asp

____Jags.
0
 
DimkovCommented:
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
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.

 
TLevin10Author Commented:
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
 
TLevin10Author Commented:
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
 
DimkovCommented:
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
 
TLevin10Author Commented:
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
 
TLevin10Author Commented:
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
 
TLevin10Author Commented:
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
All Courses

From novice to tech pro — start learning today.