Our updates are now working ... 55 ms vs. 25000 ms before, thanks everyone! Now we have run into one other minor question/issue:

We have some small classes in our code which require relatively complex inline SELECT statements. We had been using a basic data abstraction layer that provided the capability to send a SQL statement to any RDBMS and retrieve the data using a datareader or dataset. We don't really care how the data comes out, but would like the ability to retrieve this data via a SQL statement, even if we have to access it like: datareader.fields[5].value or similar.

Can we do this with LLBLGen, or should we maintain our separate DAL for these situations? Alternately, would it be better to put these queries (~50 of them) into stored procs? My team would prefer to keep the SQL inline since they contend it's easier to read and maintain (we deliver our product to hundreds of clients, but rarely change their database).

Thank you, Josh Lindenmuth

You can execute your own sql code using a connection provided by LLBLGen Pro's code. That would help perhaps. But perhaps your queries are re-writable to be formulated in a dynamic list fetch. Or are you using sql functions, case statements in sql and derived tables (SELECT * FROM (SELECT * FROM FOO) AS Foo... ) ?

We're not using any CASE statements, but do have subqueries that we'd prefer not to touch. How can we execute our own SQL code (and retrieve the results) with a connection provided by LLBLGen? I'd prefer not to maintain 2 DALs, even though the other DAL is only a couple hundred lines of code used only for SQL SELECTs.

We really appreciate everyone's quick and informative responses!

Thanks, josh

JoshLindenmuth wrote:

We're not using any CASE statements, but do have subqueries that we'd prefer not to touch.

LLBLGen Pro supports subqueries (FieldCompareSetPredicate), it doesn't support derived tables in FROM clauses, like: SELECT * FROM (SELECT * FROM FOO) As DFoo, where 'DFoo' is a derived table in this sqlstatement.

How can we execute our own SQL code (and retrieve the results) with a connection provided by LLBLGen? I'd prefer not to maintain 2 DALs, even though the other DAL is only a couple hundred lines of code used only for SQL SELECTs.

Well, you can implement an IPredicate class (derive from Predicate in the ORMSupportClasses and override ToQueryText), to directly inject the subquery SQL you want to append to a query into the generated SQL string. Another way is to completely do your own fetching, and either use DbUtils.CreateConnection (in SelfServicing), or derive a class from DataAccessAdapter and override CreateNewPhysicalConnection(), call the base' method and you have a connection object to use in your own code. You can for example add your own select code to the derived DataAccessAdapter class so you have one class with all the persistence code