By Bill Cunnien - 11/3/2009
Good afternoon!
I have the following code working within a browse dialog.
private void WorkOrderBrowseDialog_Searching(object sender, MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogSearchingEventArgs e)
{
ArrayList mPartArray = new ArrayList();
using (PartsBO mPartsBO = new PartsBO())
{
mPartsBO.FillForComboBox(); // fills list for one division
foreach (PartsBO mBO in mPartsBO.GetEnumerable())
mPartArray.Add(mBO.partindex);
}
WhereStatement mWhere = new WhereStatement(new String[] { "partindex" }, mPartArray, WhereStatementTypeOptions.In);
e.RawWhereStatementsCollection.Add(mWhere);
}
The problem is, like always, speed. There are 1250 parts in the mPartsBO--more in the other division. The delay seems to be building that array. Is there any easier, faster, more economical way to reference the partindex field in the BO collection?
Thanks!
Bill
|
By Bill Cunnien - 11/4/2009
(bump)
Table Structure
WorkOrderMaster
woindex (PK)
partindex (FK)
...
PartMaster
partindex (PK)
divisionindex
...
I am trying to limit the browse dialog to only those work orders that have parts in a specific division. The SQL would look like this:
SELECT WorkOrderMaster.* FROM WorkOrderMaster LEFT OUTER JOIN PartMaster ON PartMaster.partindex = WorkOrderMaster.partindex WHERE PartMaster.divisionindex = 1
|
By Bill Cunnien - 11/4/2009
Sorry about the formatting...apparently, the posts get leading spaces stripped?
Table Structure
.....WorkOrderMaster
..........woindex (PK)
..........partindex (FK)
..........etc.
.....PartMaster
..........partindex (PK)
..........divisionindex
..........etc.
|
By Greg McGuffey - 11/4/2009
Bill,
I'm not quite clear on what your doing with the code, but perhaps if you rethought how to do this on the server, it'd go faster. I rarely use an IN clause discreet values if there are going to be lots of values. Rather I'd use a join or a sub query. I.e. rather than a statement like this (the resulting SQL statement):
Where partindex IN(1,2,4,5,....,(1250 of these)...)
to something like:
Where partindex IN(Select partindex From PartMaster Where divsionindex = @divID)
Assuming you've got an index on the divisionindex, this should be blazing fast on SQL side, and since no array is being created, blazing fast in code to.
|
By Charles R Hankey - 11/5/2009
Also, Bill, if you are using SQL 2008 you might take a look at the sample stuff I posted in User Contributed ... regarding passing a datatable as a parameter to a SQL sproc for doing exactly this kind of stuff. Pretty powerful and seems tailor made ( as opposed to Taylor made  ) for this kind of thing.
|
By Bill Cunnien - 11/6/2009
Is it possible to substitute a SQL Stored Procedure for the logic of the Browse Dialog? If so, then my problems are solved.
What I am trying to do involves the Browse Dialog and the use of the WhereStatement.
WhereStatement mWhere = new WhereStatement(new String[] { "partindex" }, mPartArray, WhereStatementTypeOptions.In);
e.RawWhereStatementsCollection.Add(mWhere);
If there is another way to do this (limit the work orders BO by the divisionindex of the Part assigned to the work order), I'd really appreciate some suggestions. I can do all of it in a query on the server--no problem (and, it is indeed fast that way!). What I need, for now, is the proper way to do it within the Browse Dialog.
Thanks!!
Bill
|
By Bill Cunnien - 11/10/2009
Bill Cunnien (11/06/2009)
Is it possible to substitute a SQL Stored Procedure for the logic of the Browse Dialog?
Just bumping...still looking for some help on this.
Thanks,
Bill
|
By Trent L. Taylor - 11/10/2009
Why not use the OverrideSearchTableName and OverrideSearchTableSchema? You can use a view here which would then allow you to get creative on your query within the view and create any type of result set or output you need. If this doesn't work, then you can also use the Searching event. Between these two methods, I have done some very creative stuff and never had the need to do anything outside of it. Also, keep in mind that you can also dynamically change the OverrideSearchTableName at run-time and use multiple queries if that would help also. If you search the forum for the Override properties mentioned, I know that we had a sample out there somewhere and a long discussion to go with it.
|
By Bill Cunnien - 11/11/2009
Cool! Thanks for the tip.
Good to see you back on the boards!!
|
By Trent L. Taylor - 11/11/2009
Sorry for the absence! Had a lot going on lately. The good news is that we are really building a strong internal SF team that will eventually be all hands on deck both in development and forum activity...so the absence has been for a greater long-term purpose!
|
By Bill Cunnien - 11/11/2009
No worries on the absence...you need no excuse. We are all glad to have you back.
I am trying to work out the OverrideSearchTableName and OverrideSearchTableSchema properties on one of my Browse Dialogs. The browse dialog is being stubborn. The OverrideSearchTableName has been set to my newly formed SQL view. The OverrideSearchTableSchema has been set to dbo. All seems well.
The field (divindex) I added to the View is not showing up in my list of SearchFields. Shouldn't it? If I manually enter the field, there is no comforting checkmark icon on the left in the SearchFields dialog like all of the other kids. When I run it, there is no change. It is as if the new view doesn't want to play with the dialog. Not sure who is being stubborn, actually. Probably, me.
Can you shed some additional light on this?
Thanks!!
|
By Bill Cunnien - 11/11/2009
The search item is invisible, so I removed the checkmark on "Ignore initial search value if not visible" and it works. Cool. 
Thanks!!
|
By Trent L. Taylor - 11/12/2009
Good job...glad you got it going! 
|