Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> ad hoc where clause
 

Pages: 1
LLBLGen Pro Runtime Framework
ad hoc where clause
Page:1/1 

  Print all messages in this thread  
Poster Message
mkamoski
User



Location:
ZULU-5
Joined on:
06-Dec-2005 22:51:12
Posted:
116 posts
# Posted on: 29-Jun-2006 20:55:43.  
How can one write a truly ad hoc where clause and then fetch an entity collection based on it?

That is, pass something like this...

" where CompanyName='SomeCompanyName' "


...to FetchEntityCollection?

Is that possible?

If so, how?

(I know this is not "best practices"; but, note that right now it is just an experiment.)

Thoughts?

Please advise.

Thank you.

-- Mark Kamoski
http://www.WebLogicArts.com   Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37314 posts
# Posted on: 29-Jun-2006 21:39:47.  
Create a derived class from Predicate and emit your SQL in an override of ToQueryText Regular Smiley

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
mkamoski
User



Location:
ZULU-5
Joined on:
06-Dec-2005 22:51:12
Posted:
116 posts
# Posted on: 29-Jun-2006 21:46:25.  
Otis wrote:
Create a derived class from Predicate and emit your SQL in an override of ToQueryText Regular Smiley


Ah ha.

To cast an old adage, one can say...

"Where there is an Otis, there is a way."


Thank you.

-- Mark Kamoski
http://www.WebLogicArts.com   Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37314 posts
# Posted on: 29-Jun-2006 22:31:34.  
haha Laugh

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Rob
User



Location:
Gold Coast, Queensland, Australia
Joined on:
17-Sep-2004 13:51:52
Posted:
50 posts
# Posted on: 09-Jun-2007 08:04:23.  
Hi!

Maybe I am just dreaming. But can this be a way to use your own Sql-statement but within the LLBLGen framework?

I sometimes have queries that either are too hard to convert to predicates or (from reading the forums) sometimes are not even possible to convert. That is unless I resort to sprocs or views, but I am not willing to do that since I am working on an enterprise level product with nearly 200 tables. If I had to add a sproc or view everytime I could not solve something with predicates it would be very messy indeed. To me manageability is everything and wading through sprocs and views in order to work out what a small little getXXXX function returns is simply not manageble.

I can obviously use .NET's own SQLClient capabilities to return DataTables. But I have not yet been able to work out how I can invoke a LLBLGen Transaction into it. If somebody can give me a pointer towards how that can be done I would probably simply just do that.

If however, that is not possible, then I wonder if deriving a class from Predicate would give me the freedom to come up with any SQL-query and be able to attach my LLBGen transaction to it. That would possibly be even better. Since then I could perhaps use the GetMulti to return Entity Collections, and perhaps use GetScalar for simple getXXX functions.

If it is possible to use a derived Predicate class for this I would be very grateful for some example code. That is, what the class would look like and how it would be used.

Hopefully,

!Rob
  Top
jbb
User



Location:

Joined on:
29-Nov-2005 13:38:48
Posted:
267 posts
# Posted on: 11-Jun-2007 16:27:19.  
Hello,

if you derive a class from Predicat, you will create a custom predicate but that is only a predicate and not a complete request. Btw you can create as complicated where clause as you want by deriving the Predicate. What sort of query you cannot do directly in llbl, did it cames from a predicate or a custom function?


  Top
Rob
User



Location:
Gold Coast, Queensland, Australia
Joined on:
17-Sep-2004 13:51:52
Posted:
50 posts
# Posted on: 12-Jun-2007 01:46:08.  
Hi!

I realize that predicates are only a part of the story and that I probably cannot use them for this purpose.

I have two main problems.

1. Sometimes the SQL is complex enough so that I cannot translate it to llbl, I will give you two examples below.

2. I am writing an enterprise application. I don't want to hard code views to data in the user interface, so I have tables in the database that store SQL-queries and column mappings and such.

In this application I sometimes refer to SQL stored in database during transactions. Like for instance I write share certificates. Different clients have different layouts so I store the locations on a pdf-template and SQL to retrieve the data in database. Sometimes I write multiple certificates and therefore write back to the database which ones have been written. This means that I have a transaction going while calling the database with normal SQL-queries. This means that potentially I might be requesting data from a table that is locked by my transaction.

So to solve my problem number 2 I would like to know if there is a way to attach the llbl transaction (or its' underlying db transaction) to a normal data-adapter SQL request?


Here are two examples of seemingly simple SQL-queries that I don't know how to do in llbl.

Code:

SELECT
MAX(AccountNumber)
FROM
Account
WHERE
ProductID = " + productID.ToString() + "
AND
LEN(AccountNumber) =
(
SELECT
    LEN(GenerateAccountNumberPattern)
FROM
    Product
WHERE
    ProductID = " + productID.ToString() + ")


Code:

SELECT
Sum(P.Price * C.Quantity)    
FROM
Certificate C
INNER JOIN
Investment P ON P.InvestmentID = C.InvestmentID
WHERE
C.CertificateStatusID = " + Constants.CertificateStatus___Issued.ToString() +
AND    
C.ProductID = " + this.ProductID
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.