|
Home > Archive > BizTalk Server General > February 2005 > Rule Composer: Verify if value EXISTS in DB column
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Rule Composer: Verify if value EXISTS in DB column
|
|
| Andre Afonso 2005-02-22, 5:50 pm |
| Hello,
I need some guidance building a rule that would behave like a SQL EXISTS
predicate, i.e. that would iterate trought the values of a database column
and stop on the first equal value found.
Now, in Rule Composer there's the "exists" predicate for rule conditions,
but this one works only for XML documents and my data source is a sql
database. So this won't do.
The other option is to use the "equal" predicate. Using this the rule
searches the column for a equal value, but the issue with this predicate is
that it considers every record in the table, what is not intended and
degrades performance. And also it will cause the rule will fire more than
once if more than one equal value is found. I've tried to avoid this by
using the "halt" action, but that ends all rule processing, not just the
given rule, not letting other existing rules to fire.
Is there an easy way to accomplish the behavior of a SQL EXISTS predicate in
the given scenario?
Thank you,
Andre Afonso
| |
| Andre Afonso 2005-02-22, 5:50 pm |
| OK,
I've solved it by creating a class that implements a method that checks if
the value exists issuing an sql query "SET ROWCOUNT 1; SELECT * WHERE column
= value". The method returns the SqlDataReader.HasRows.
Then I call the method in the condition passing it the value to check for
existence.
Don't forget to create a variable of the class type in the "scope" of the
"call rule shape" usign the "orchestration view".
Andre Afonso
"Andre Afonso" <andre.afonso@tekever.com.no.spam> wrote in message
news:ejXcwHQGFHA.4088@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I need some guidance building a rule that would behave like a SQL EXISTS
> predicate, i.e. that would iterate trought the values of a database column
> and stop on the first equal value found.
>
> Now, in Rule Composer there's the "exists" predicate for rule conditions,
> but this one works only for XML documents and my data source is a sql
> database. So this won't do.
>
> The other option is to use the "equal" predicate. Using this the rule
> searches the column for a equal value, but the issue with this predicate
is
> that it considers every record in the table, what is not intended and
> degrades performance. And also it will cause the rule will fire more than
> once if more than one equal value is found. I've tried to avoid this by
> using the "halt" action, but that ends all rule processing, not just the
> given rule, not letting other existing rules to fire.
>
> Is there an easy way to accomplish the behavior of a SQL EXISTS predicate
in
> the given scenario?
>
> Thank you,
>
> Andre Afonso
>
>
|
|
|
|
|