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

Last updated 16 Aug 2024