Solved

DataGridView filter - combobox column

Posted on 2006-06-20
11
14,023 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 3

Expert Comment

by:Dimkov
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Closed, 500 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

14 Experts available now in Live!

Get 1:1 Help Now