StrataFrame Forum

Using the WhereStatementCollection

http://forum.strataframe.net/Topic17815.aspx

By Bill Cunnien - 7/12/2008

What I would like to do with a browse dialog is access the Where statements and use those in my own query of the data.  I have a stored procedure that returns additional fields that I need on the form and a standard browsedialog will not use a stored procedure since it talks directly with the underlying table.  Once I grab the statement, then I would cancel out the browse dialog and run my own data access method on the BO based on the user's selections.  Can this be done?  If so, what would be the easiest way to retrieve the dynamically generated where statement?

Thanks,
Bill

By Trent L. Taylor - 7/12/2008

You will want to manage the Searching event of the BrowseDialog which will give you the full WhereCollection and even allow you to manipulate that collection.  Look there first and if that isn't what you were looking for let me know.  But this event exposes the query and WHERE clauses that will be executed and allows you to either "tweak" these, add new query definitions, etc. or even cache them off for some other purpose.
By Bill Cunnien - 7/12/2008

Excellent!  I was just running through the WhereStatements in the e.RawWhereStatementsCollection.  The Searching method is the place.  I think I can get what I need here.  Thanks a lot!
By Bill Cunnien - 7/12/2008

I modified my stored procedure to receive a where clause as an nvarchar(4000) data type in addition to two other parameters.  The browse dialog's searching method does the following with the filter generated from the user (I previously defined _filter as a private variable of type string):


private void browseDialog1_Searching(object sender, MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogSearchingEventArgs e)
{
    _filter = "";
    int _count = 1;
    foreach (WhereStatement mStmt in e.RawWhereStatementsCollection)
    {
        if (_count == 1)
        {
            _filter = mStmt.Fields[0];
        }
        else
        {
            _filter += " AND ";
            _filter += mStmt.Fields[0];
        }
        switch (mStmt.StatementType)
        {
            case WhereStatementTypeOptions.BeginsWith:
                _filter += " LIKE '" + mStmt.Values[0].ToString() + "%'";
                break;
            case WhereStatementTypeOptions.CompoundPrimaryKey:
                // not sure how to handle this option
                break;
            case WhereStatementTypeOptions.Contains:
                _filter += " LIKE '%" + mStmt.Values[0].ToString() + "%'";
                break;
            case WhereStatementTypeOptions.EndsWith:
                _filter += " LIKE '%" + mStmt.Values[0].ToString() + "'";
                break;
            case WhereStatementTypeOptions.Equals:
                _filter += " = '" + mStmt.Values[0].ToString() + "'";
                break;
            case WhereStatementTypeOptions.GreaterThan:
                _filter += " > '" + mStmt.Values[0].ToString() + "'";
                break;
            case WhereStatementTypeOptions.GreaterThanOrEqual:
                _filter += " >= '" + mStmt.Values[0].ToString() + "'";
                break;
            case WhereStatementTypeOptions.In:
                // not fully implemented since this may involve multiple values
                _filter += " IN '" + mStmt.Values[0].ToString() + "'";
                break;
            case WhereStatementTypeOptions.LessThan:
                _filter += " < '" + mStmt.Values[0].ToString() + "'";
                break;
            case WhereStatementTypeOptions.LessThanOrEqual:
                _filter += " <= '" + mStmt.Values[0].ToString() + "'";
                break;
            case WhereStatementTypeOptions.Like:
                _filter += " LIKE '%" + mStmt.Values[0].ToString() + "%'";
                break;
        }
        _count += 1;
    }
}

Once I have the where statement, now I have the necessary ammunition to volley into my stored procedure.  I do this within the button that starts the whole thing, like this:


private void cmdSearch_Click(object sender, EventArgs e)
{
    Aspire.Model.ADUserBO mADUser = new Aspire.Model.ADUserBO();
    browseDialog1.SearchFields["divisionindex"].InitialValue = mADUser.LocationIndex.ToString();
    if (browseDialog1.ShowDialog(false) == DialogResult.OK)
    {
        waitWindow1.ShowWaitWindow();
        partsBO1.FillForRequirements(_filter);
        GetRequirements();
        waitWindow1.HideWaitWindow();
    }
}

The GetRequirements method takes a couple of BOs on the form, creates a complex dataset which is fed to a DevEx grid.  The grid takes it from there.

If there is an easier way to get the where clause out of the browse dialog, I am open to suggestions.  This is working, and fulfills the need of the moment.  Thanks for the framework (without which the above would have taken many days to do...only several hours were involved, and that was mostly in the stored procedure) and thanks for the help!

Bill

By Trent L. Taylor - 7/12/2008

Looks good Cool ...and thanks for the compliments!