Monday, August 31, 2009

Calling a custom database function in WHERE part of your entity

I have encountered a need to call a custom database function in WHERE part of the entity using entityspaces(2008.x.x) which does not support Raw SQL Injection Everywhere.

I have searched everywhere and posted inquiries in the friendly ES forum but theres no luck.

This task can be done easily using stored procedure but I wanted to use ES for I have other filters, join and grouping, all of this are already in place except for the part where I need to call the function.

Now LastQuery to the rescue, this property contains nothing until you make the query, so the idea is to call the query making sure that only one or zero record will be return then use the value of LastQuery to construct a new SQL Query/Statement with the datase function in it to be use in our entity custom load. I am not sure if this is a good practice but I works for me. See the code below.


' Make sure we only have one record for this load
Dim esqUsers As New UserCollectionQuery("uv")
esqUsers.es.Top = 1
esqUsers.es.PageSize = 10
esqUsers.es.PageNumber = _PageNumber

' this part will give us an entry point for insertion of call to our custom database function it will generate this string "uv.[UserID] IN ('-2')"
esqUsers.Where(esqUsers.UserID.OP(esWhereOperand.In, "-2".Split("!"c)))

' more filters, join and grouping here.

' Make the call so we can generate the LastQuery
esUsers = New UserCollection
esUsers.Load(esqUsers)

' Get last query
Dim strLastQuery As String = esqUsers.es.LastQuery
' Insert the call to the function
strLastQuery = strLastQuery.Replace("uv.[UserID] IN ('-2')", "database function & params")

' Make sure we have the data
strLastQuery = strLastQuery.Replace("TOP 1", "")

' You also need to update some parameters if needed.

' Now we call our custom load
esUsers.CustomLoad(strLastQuery)


The custom load function is very simple, see below:

Public Function CustomLoad(ByVal sSQL As String) As UserCollection
MyBase.Load(esQueryType.Text, sSQL)
Return Me
End Function


HTH




No comments:

Post a Comment