Hi,

we have an existing application (old program in VB6) that has the functionality for users to define a search/report that exports to Excel.

For example: SELECT firstname, lastname FROM customer WHERE company = "something"

We have also defined some fields criteria the user can use to search on. For example Department.

This means that since we defined that Department is searchable we create a form for the customer to set values so we can make a filter and sort for department.

This means that the user defines that department = 100 and the SQL code will be generated to:

SELECT firstname, lastname FROM customer WHERE company = "something" AND department = 100

Now are we using LLBLGen in our new application and I was thinking that this is exactly what the PredicateExpression filter does and SortExpression does.

So what I am hoping is that we can use the SQL code that has been predefined for the report and use LLBLGen to add the restrictions from the user and run it using LLBLGen connection and retrieving it in a Datatable.

Is there anyway to do this? If so, can you help me out a little by telling me how?

Gr.,

G.I.

The best thing I can think of is to re-write your code using LLBLGen Pro classes and objects.

If only lif ewas that simple ...

I can't do that, since customers use our application and have created their own reports and I have to create a web application that is able to use those predefined queries and just add the where clauses and sort options.

And since I want to use the LLBLGen as much as possible, I was wondering ....

G.I. wrote:

This means that the user defines that department = 100 and the SQL code will be generated to:

SELECT firstname, lastname FROM customer WHERE company = "something" AND department = 100

Now are we using LLBLGen in our new application and I was thinking that this is exactly what the PredicateExpression filter does and SortExpression does.

So what I am hoping is that we can use the SQL code that has been predefined for the report and use LLBLGen to add the restrictions from the user and run it using LLBLGen connection and retrieving it in a Datatable.

Funny, I haven't tired this yet. But, I was thinking of something similar for a feature in our app.

The idea being to create a typedlist that would return a result set with the schema needed. But, then not use the typed list.

The thought was to build a predicate expression and use LLBLGen's query generator to build the SQL statement, and rather than have LBLGen populate the datatable/set just have it return the SQL.

So, is there a method in and LLBLGen Entity object that will let you say, here's a PredicateExpression, build me the SQL and return it to me. I almost doubt it because I am pretty sure that LLBLGen builds a parameterized query. ???

The second thought was, is there a method that I can pass a predicate expression to LLBLGen and it will just return me a DataReader.

BOb

Is it out of reach to build a web service to back end your VB6 report engine? The .NET web service can then take full advantage of LLBLGen.

Hi,

I have to start programming this next week now. And I still have no idea if I am gonna use LLBLGen or not. Since the reports of all customer are created SQL Queries which just have to be extended somehow by the user, I at least have to use an own adapter to execute RAW SQL Code.

The only question I have now is can I use LLBLGen to create where clauses and sort clauses?

I mean: The user selects a field, and operator and a value and a sort type, order.

Which means for example if he wants a list of orders of a specific customer, and the SQL report is already defined, the user will be shown options for specifying the scope. So the user Selects an operator, for example '=' and enter a value, f.e. 'MyCompany' then I know that I have to create an LLBLGen Predicatexpression saying Customer.CustomerName == "MyCompany".

And maybe add a sorting expression also. Now that is easy programming. I was now wondering if there's a way to get the SQL this predicate expression generates, so I will get the SQL code " WHERE CUSTOMER.CUSTOMER_NAME = 'MyCompany' " which I can easily add to the SQL Report SQL Code the customer has made.

I have tried to use the filter.ToQueryText(ref UniqueMarker) function, but then I need a creator, and even if I create a new one, I still get a reference not set error. So I don't know if it's possible at all, and if so how?

Gr.,

G.I.

The actual queries can't be produced with llblgen pro code, so you need the where clauses to be generated and then be added to a fixed query elsewhere?

In Adapter, it's key the predicates have persistence info set. Add a method to a derived class of DataAccessAdapter which calls the base class' InsertPersistenceInfoObjects(predicateexpression).

Then, to get the sql, you do: SqlServerSpecificCreator creator = new SqlServerSpecificCreator(); myPredicateExpression.DatabaseSpecificCreator = creator; int uniqueMarker = 0; string whereClause = myPredicateExpression.ToQueryText(ref uniqueMarker);

you'll get a string without 'WHERE'. The parameters are in myPredicateExpression.Parameters.

Otis,

thanks this is working great! Now I only have two more questions:

Is it possible for me to do this also with SortExpressions. I saw it doesn't have the ToQueryText function, so I really don't know how to do this.

Another thing: Is it somehow possible for me to determine by Table Name and Column Name to get the LLBLGen field?

For example: I have a table CUSTOMER and a column CUSTOMER_NAME. Is it somehow possible for me do get the correct field for this? In this case CustomerFields.CustomerName?

Gr.,

Robin