Solved

DataGridView filter - combobox column

Posted on 2006-06-20
11
14,038 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coding C# in Linux 8 70
Sent email using Shared Mailbox from outlook in C# 5 24
Getting error in connectionstring with Excel. 30 35
tableview is not updating 1 10
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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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