Problems with ParameterBuilder and .BeginGroup

Hi all,

For the life of me i can't get the .BeginGroup method working correctly. Where ever it is placed while i build my parameter collection, it simply inserts empty brackets and causes sql exceptions. Here is an example of my code where i'm having the issue.

---------- 

Dim pb As ConsigneeParameterBuilder = New ConsigneeParameterBuilder()

pb.AppendIn(String.Empty, ConsigneeColumn.ExporterId, CStr(ExporterId.Value))

'Check by name only if the original consignee name is different from the edited name.

If (consigneeName <> hdnConsigneeOriginalName.Value) Then

pb.AppendEquals("AND", ConsigneeColumn.ConsName, consigneeName)

End If

pb.AppendEquals("OR", ConsigneeColumn.Address1, address1)

pb.AppendEquals("AND", ConsigneeColumn.MarketCode, marketCode)

'Add telephone and email parameters only if they aren't empty/null

If (Not String.IsNullOrEmpty(email) Or Not String.IsNullOrEmpty(telephone)) Then

pb.BeginGroup("OR")

If (Not String.IsNullOrEmpty(telephone)) Then

pb.AppendEquals(String.Empty, ConsigneeColumn.Telephone, telephone)

End If

If (Not String.IsNullOrEmpty(email)) Then

pb.AppendEquals("OR", ConsigneeColumn.Email, email)

End If

pb.EndGroup()

pb.AppendIn("AND", ConsigneeColumn.ExporterId, CStr(ExporterId.Value))

End If

'Search for any consignees with matching criteria

Dim consignees As TList(Of Consignee) = DataRepository.ConsigneeProvider.Find(pb)

-------------------

The above code yields the following sql:

(ExporterId IN (@Param0)) AND (ConsName = @Param1) OR (Address1 = @Param2) AND (MarketCode = @Param3)OR ()  (Telephone = @Param4) OR (Email = @Param5) AND (ExporterId IN (@Param6))

You can see that the OR has been inserted with empty brackets. The right bracket should appear at the end of @param5. I've been able to fudge it using IFs etc, but it would be much cleaner if i could build this parameter list using the begin and endgroup methods.

 thanks for any help you may provide.....

Just wondering if anyone can help, this problem is really infuriating. I've also tried to build a where clause with string builder (of which the content works fine if copied into query analyzer) but when used in the .Find() method, gives me an error saying:

"Unable to use this part of the where clause in this version of Find: (address1 = '34 john st'"

 I can't for the life of me figure out why the above statement causes an error using the find method. It works fine when copied into query analyzer....

 xazos,

with find method you have to use parameterbuilder class, which seem you're doing in your first post, and also invoke GetParameters() method to dynamically generate a paramaterized SQL statement

Dim consignees As TList(Of Consignee) = DataRepository.ConsigneeProvider.Find(pb.GetParameters())

Hi Mike,

I did try the getparameters method and received the same results so i just left it as passing the entire builder object.

 X

 I got the same project.

every kind of using .BeginGroup() produces  SqlSyntaxError

 

There is a fairly simple work around for this issue.  After issuing a BeginGroup call, you just need to set the Junction to an empty string.  Something like the following:

 Hello,

 very thanks for than hint with that .Junction = "".

But just using it didn't run at first.

 

So i got to make an annotation.      --->> SETTING the .Junction after first .Append back to   .Junction = "AND" 

 

I create my query to runtime. 

The parameter are filled in GUI to a DataTable. 

That Table I transform to a suitable format, a List<QueryRow> ( each DataTable means such a QueryRow) 

 A QueryRow is a class containing two properties : the logical operator as string  and a list<SingleQuery>.

At runtime I organize every queryRow in a single Group. 

And SingleQuery (two properties: string columnName,string  cellValue) has the content for  a .Append*(...) clause

 

now creating the query:

foreach ( QueryRow in  queryRowList)

{

    //greating the group

  .BeginGroup("") oder ähnlich --> depends on how to organize the runtime code.

  .Junction = "";

 

    foreach (SingleRow in queryList.SingleRowList)

    {

.          Append... ; 

           .Junction = "AND" ;    -->> that is the point that wasn't in your hint  --> setting the junction after the first append clause

    } 

    .EndGroup(); 

 }

 

 VERY THANKS 

 

Actually, this workaround only works if your criteria are static. With dynamic criteria, the SqlParameterBuilder is driving me insane. For example:

 

MyParameterBuilder pb = new MyParameterBuilder();

if (somethingValueSomewhere == 1) 

  pb.AppendEquals(MyColumn.Something, "ABC");

if (someFlag)

  pb.AppendEquals(MyColumn.OtherThing, "DEF");

i f (someOtherFlag)

   pb.AppendEquals(MyColumn.YetAnotherThing, "GHI");

 

In the above, all the conditions might be true, none of them might be true, or any combination might be true.

It's almost impossible to get the junctions working right without painstakingly keeping track of whether or not you've previously added any criteria. Otherwise, the builder winds up putting the junction text before the first parameter and you wind up with stuff like WHERE AND Something='ABC'. I really shouldn't have to do that. The SqlParameterBuilder should know not to put a junction before the first item in a group.

-Bryan

P.S. How are you pasting code with syntax highlighting like you did above?? 

of course you have to check. (like you have to chekc for possible exceptions)

 

that's normal

watch my last post --> very dynamical creating of query

 

what you can do.

check the last char in query:

query.ToString().TrimEnd.EndsWith(...)

if end with '(' --> .Append("",...,...)

id end with ')' --> Append("AND",...,...) 

I completely disagree that it's "normal" in any way. It seems very simple to me for the SqlParameterBuilder to do this for me. Whether or not the criterion I am adding is at the beginning of a group is a matter of object state. Objects are supposed to keep track of their OWN state. I shouldn't have to do it for them.

you're right.

 

but don't forget: .netTiers is open source 

feel free to help or to pay for someone who patch your need..

 I intend to fix it myself, I just haven't gotten around to it  But I want to do it in such a way that it doesn't break people's existing code.