Using the WhereStatementCollection

Bill Cunnien — Sat, 12 Jul 2008 12:55:57 GMT

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

RE: Using the WhereStatementCollection

Trent L. Taylor — Sat, 12 Jul 2008 12:55:57 GMT

Looks good :cool: ...and thanks for the compliments!

RE: Using the WhereStatementCollection

Bill Cunnien — Sat, 12 Jul 2008 11:31:27 GMT

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

[codesnippet]
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;
    }
}
[/codesnippet]

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:

[codesnippet]
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();
    }
}
[/codesnippet]

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

RE: Using the WhereStatementCollection

Bill Cunnien — Sat, 12 Jul 2008 09:56:03 GMT

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!

RE: Using the WhereStatementCollection

Trent L. Taylor — Sat, 12 Jul 2008 09:47:37 GMT

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.

StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Using the WhereStatementCollection

Using the WhereStatementCollection

RE: Using the WhereStatementCollection

RE: Using the WhereStatementCollection

RE: Using the WhereStatementCollection

RE: Using the WhereStatementCollection