Power Query Question

[KSoft]

Hey Mike, I'm trying to save myself some time here but I want to makes sure I'm being safe about it.

I'm doing a basic search form here and I have a combo box with a list of field names the user can choose, then a list of conditions (equal, not equal, etc) and finally a search text field.

I would like to be able to add the where param right to a d00dad for the load but I can't seem to figure out how to get the string field name to work.

Something like :

Dim MyWhereParam As New MyGeneration.dOOdads.WhereParameter(cmbSearchFields.Value, )

Seems to be the way to go, but I don't know what to put as the next argument to WhereParameter()

I want to use the d00dad partially because the grid is already set up to handle it and partially because I want the safety of the parameter based query so the user can't inject anything into the database.

If there is a way I can do this without a gigantic Select Case I'd love to do it!

Thanks!

[mike.griffin]

You know, I checked with Santa, he said you'd been a good boy and perhaps he might have overlooked you a little this year. He asked if I'd bounce this your way.

Code: Select all

Employees emp = new Employees();
WhereParameter wp = emp.GetWhereParameter(\"EmployeeID\");

Code: Select all

using System.Reflection;

public class Employees : _Employees
{
   public WhereParameter GetWhereParameter(string columnName)
   {
      Type t = this.Where.GetType();
      PropertyInfo prop = t.GetProperty(columnName);
      WhereParameter wp = prop.GetGetMethod(true).Invoke(this.Where, null) as WhereParameter;
      return wp;
   }
}

[KSoft]

I love that Santa guy! Thanks Mike!

[KSoft]

Ooooh Santa

I converted it to VB.Net (correctly, I hope).

Code: Select all

Dim MyType As Type = Me.Where.GetType

Dim PropInfo As PropertyInfo = MyType.GetProperty(TheColumn)

Dim MyWhere As MyGeneration.dOOdads.WhereParameter = CType(PropInfo.GetGetMethod(True).Invoke(Me.Where, Nothing), MyGeneration.dOOdads.WhereParameter)



I get a NullRef Exception here :

Code: Select all

Dim MyWhere As MyGeneration.dOOdads.WhereParameter = CType(PropInfo.GetGetMethod(True).Invoke(Me.Where, Nothing), MyGeneration.dOOdads.WhereParameter)


I'm not entirely sure what is (or isn't) null, though.

[mike.griffin]

The Call

Code: Select all

Dim em As New Employees
Dim wp As WhereParameter
wp = em.GetWhereParameter(\"EmployeeID\")

The Code

Code: Select all

Imports System.Reflection
Imports MyGeneration.dOOdads

Public Class Employees
   Inherits _Employees

   Public Function GetWhereParameter(ByVal columnName As String) As WhereParameter

      Dim MyType As Type = Me.Where.GetType
      Dim PropInfo As PropertyInfo = MyType.GetProperty(columnName)
      Dim MyWhere As WhereParameter = CType(PropInfo.GetGetMethod(True).Invoke(Me.Where, Nothing), WhereParameter)
      Return MyWhere

   End Function

End Class

[KSoft]

Yes, but VB isn't that picky. I modified it just in case and no dice.

On the line where the error pops up :

Code: Select all

Dim MyWhere As MyGeneration.dOOdads.WhereParameter = CType(PropInfo.GetGetMethod(True).Invoke(Me.Where, Nothing), MyGeneration.dOOdads.WhereParameter)

Is C#'s Null the same as VB's Nothing? I assumed so, but perhaps that's what I get for asssuming!

Is there anything I would have to call to set up state or anything within the BusinessEntity object before calling all that to get the where param?

[mike.griffin]

See my VB code above, it works

[KSoft]

Oops, I didn't see your second reply until after I posted mine.

Your code is identical to what I have except for the column variable name.

Code: Select all

    Public Function GetWhereParameter(ByVal TheColumn As String) As MyGeneration.dOOdads.WhereParameter

        Dim MyType As Type = Me.Where.GetType()

        Dim PropInfo As PropertyInfo = MyType.GetProperty(TheColumn)

        Dim MyWhere As MyGeneration.dOOdads.WhereParameter = CType(PropInfo.GetGetMethod(True).Invoke(Me.Where, Nothing), MyGeneration.dOOdads.WhereParameter)

        Return MyWhere

    End Function

Unless I'm missing something?

[KSoft]

I just copy/pasted your code and still get the NullRef Exception on :

Code: Select all

Dim MyWhere As WhereParameter = CType(PropInfo.GetGetMethod(True).Invoke(Me.Where, Nothing), WhereParameter)

So I'm obviously not doing something right!

I am calling AddResultColumn before GetWhereParameter - would that matter?

[mike.griffin]

Is your method in your concrete class? It has to be. Try copying my code.

[KSoft]

Oh yes, the method is in the concrete class. I'm using your exact code, copy and pasted.

Calling it like this :

Code: Select all

Dim MyWhere1 As MyGeneration.dOOdads.WhereParameter = _TheInvoiceList.GetWhereParameter(WhereParam1)

WhereParam1 is a string representing the column name.

The only thing I can think is that the column name is somehow wrong. Would the string be exactly how it is listed in the database? I've tried every variation I can think of and still can't get past without a null ref exception.

[mike.griffin]

The column passed in need to match the property name not the physical column name.

[KSoft]

That was it! It was an issue of case.

Thanks Mike, awesome support as always!

[mike.griffin]

You're welcome

[KSoft]

Ok, one more question then I promise to leave you alone

Do I add the newly created where parameter like this : ?

Code: Select all

_TheInvoiceList.Query.AddWhereParemeter(MyWhere1)

Because this is the SQL it's generating and I'm getting a \"parameter not found\" error, which makes sense as it is putting invoice_number in there twice. invoice_number is what the column is passed to the GetWhereParameter() function.

SELECT <omitted> FROM [invoice] WHERE [invoice_number] = @invoice_number1 AND [invoice_number] = @invoice_number2 ORDER BY [status] ASC, [created_on] ASC

I think I'm adding the where wrong somehow...

[mike.griffin]

You don't need to call AddWhereParameter. All the code is doing is calling this:

Code: Select all

Employees emp = new Employees();

// This code
WhereParameter wp = emp.GetWhereParameter(\"EmployeeID\");

// Equals this code
WhereParameter wp = emp.Where.EmployeeID;

Which means:

Code: Select all

// This code
emps.Where.LastName.Value = \"%A%\";
emps.Where.LastName.Operator = WhereParameter.Operand.Like;

// Equals this code
emps.Where.GetWhereParameter(\"LastName\").Value = \"%A%\";
emps.Where.GetWhereParameter(\"LastName\").Operator  = WhereParameter.Operand.Like;

If you need multiple WhereParameters on the same column I recommend not using the DynamicQuery and instead using LoadFromRawSql.

[KSoft]

Mike, obviously you have some insight as you've already partially answered my newest question..

I just realized that users will want to search on the same field more than once (and they would for certain since I've thought about not adding that). You suggested using LoadFromRawSQL. Is that because it isn't possible to do it using DynamicQuery, or because there is some other caveat?

If it is possible to use DynamicQuery I would like to do so for simplicity's sake, can you point me in the right direction?

[mike.griffin]

Yes, you can use the DynamicQuery, however, you only get one free WhereParameter, after that you need to use the TearOff API, however if you're going to need to group things via AND's and OR's go with LoadFromRawSql

[KSoft]

Yea, you're right. I'll just re-do the thing and use LoadFromRawSQL instead. More powerful in the long run!

Thanks Mike!

[sbc]

It is possible using DynamicQuery, just not that simple. I have managed to do quite complicated SQL using it (could have done it with a stored procedure, but as it was very dynamic, it would have meant more work).

Code: Select all

Dim FilterOut As String = \"City 1|City 2|City 3\"
Dim filters As String[] = FilterOut.Split('|')
emps.Query.AddConjunction(WhereParameter.Conj.And)
emps.Query.OpenParenthesis()
emps.Where.City.Value = \"%\" + filters[0] + \"%\"
emps.Where.City.Operator = WhereParameter.Operand.Like
If (filters.Length > 1) Then
   WhereParameter wp = emps.Where.TearOff.City
   For i As Integer = 0 To filters.Length
      wp = emps.Where.TearOff.City
      wp.Value = \"%\" + filters[i] + \"%\"
      wp.Operator = WhereParameter.Operand.Like
      If (i <> filters.Length) Then
         wp.Conjuction = WhereParameter.Conj.Or
      End If
   Next i
End If
emps.Query.CloseParenthesis()

This would generate:

Code: Select all

AND (City LIKE '%City 1%' OR City LIKE '%City 2%' OR City LIKE '%City 3%')