DgvRowFinder class

from the Artful MySQL Tips List


Most custom apps need custom Find dialogs. Many also need generic Find capability.
This is a simple default column search dialog for an underlying DataGridView, which may be databound or running in VirtualMode. It accepts a user value as a column value to look for (with or without wildcards), looks up the column type in information_schema, forms and executes a query, and repositions the underlying grid if the search was successful.The DgvInfo class referenced in this class is the same as that referenced in the just-in-time DataGridView.


  public class DgvRowFinder {



    Form theForm;

    DataGridView dgv;

    private int colno;

    private long rowno = -1;

    private string userValue;



    public DgvRowFinder( Form _theForm, DataGridView _dgv, int _colno, int _rowno ) {

      theForm = _theForm;

      dgv = _dgv;

      colno = _colno;

      DataGridViewColumn col = dgv.Columns[colno];

      DgvInfo info = ( DgvInfo ) dgv.Tag;

      Rectangle rect = dgv.GetCellDisplayRectangle( _colno, _rowno, true );

      FindDlg findDlg = new FindDlg( col.Name );

      findDlg.Location = dgv.PointToScreen( new Point( rect.X, rect.Y + rect.Height ) );

      findDlg.ShowDialog();

      userValue = findDlg.UserFindSpec;

      if ( userValue.Length > 0 ) {

        string quote;

        Type t = col.ValueType;

        if ( t == null ) // IN DGV VIRTUALMODE WE MUST FETCH TYPE INFO FROM THE DB

          quote = ( theUsual.MustQuoteValue( info.schema, info.table, col.Name )) ? "'" : "";

        else

          quote = ( t.Name.StartsWith( "String" ) || t.Name.StartsWith( "Date" ) ) ? "'" : "";

        string arg = findDlg.UserFindSpec;

        string orderby = " ORDER BY " + info.order;

        string colArgs = theUsual.IdName( col.Name );

        if( col.Name != info.order ) colArgs += "," + theUsual.IdName( info.order );

        string criterion = theUsual.IdName( col.Name ) + 

                           ( arg.Contains( "%" ) ? " LIKE " : "=" ) + quote + arg + quote;

        // if ( info.key.Length > 0 ) colArgs += "," + theUsual.IdName( info.key ); 

        // IS THERE A MATCHING ROW?

        string sql = theUsual.CachedQuery( info.database, theUsual.IdName(info.schema), theUsual.IdName(info.table), 

                                           colArgs, " WHERE " + criterion, orderby, 0, 1 );

        DataTable dt = theUsual.QueryResult( sql );

        if ( dt.Rows.Count > 0 ) {

          string compop = ( dgv.SortOrder == SortOrder.Descending ) ? " > " : " < ";

          string oquote;

          string ordercolValue = dt.Rows[0][info.order].ToString();

          Type t1 = dgv.Columns[info.order].ValueType;

          if( t1 == null )

            oquote = ( theUsual.MustQuoteValue( info.schema, info.table, info.order ) ) ? "'" : "";

          else

            oquote = ( t1.Name.StartsWith( "String" ) || t1.Name.StartsWith( "Date" ) ) ? "'" : "";

          // GET THE ROWCOUNT UP TO THE FIRST MATCHING ORDERING COLUMN VALUE

          rowno = theUsual.TableRows( info.schema, info.table,

                                      " WHERE " + theUsual.IdName( info.order ) + compop + oquote + ordercolValue + oquote );

          if ( rowno > 0 && info.order != info.key ) {

            // IF THE ORDERING COLUMN IS NOT A PK, FIND THE FIRST MATCHING ROW HAVING A MATCHING ORDERING COL VALUE

            string target = findDlg.UserFindSpec;

            sql = theUsual.WorkingQuery( info.database, info.schema, info.table, colArgs, 

                                         " WHERE " + theUsual.IdName( info.order ) + "=" + 

                                         oquote + ordercolValue + oquote, orderby );

            dt.Reset();

            dt = theUsual.QueryResult( sql );

            if( dt.Rows.Count > 1 ) {

              criterion = arg.Contains("%" ) ? arg.Substring( 0, arg.IndexOf("%")) : arg;

              for( int i = 0; i < dt.Rows.Count; i++ ) {

                if ( dt.Rows[i].ItemArray[col.Index].ToString().StartsWith( criterion )) {

                  rowno += i;

                  break;

                }

              }

            }

          }

        }

      }

    }



    public string UserValue {

      get { return userValue; }

    }

    

    public long RowFound {

      get { return rowno; }

    }

  }




Return to the Artful MySQL Tips page