Background I am evaluating LLBLGen for use alongside Microsoft Dynamics AX. This is an ERP system based around an SQL database (MS or Oracle). Around the database sits the "application", which consists of a large amount of logic written in a language called X++. The application basically contains all the business logic and integrity logic, and you must use it to update the database - it is callable via COM. For example, to create a sales order I can call a method on a class and specify one field (the customer ID) and this will result in a record being written to the SALESTABLE with about 50 fields filled in from defaults in various places.

Currently I have built an ORM-type system for DAX in .Net 2 using DataSets and extensive WinForms databinding, but I don't really like using DataSets, they are a pain for non-trivial use and I am looking for something better. I find it difficult to maintain DataSet definitions using the VS editor (leave my SQL formatting alone please!), difficult to extend DataSets with custom code and inheritance, difficult to use in code because of the nesting of classes, and difficult to debug them especially in complex data binding situations.

I have known about LLBLGen for a while and I have done some initial evaluation for DAX use and it seems OK for the basics (load records, extend via partial classes) but DAX is a very different environment to what I suspect most of you are used to. Apologies for all the questions but I need to be fairly sure before I switch data access technologies that I am not exchanging one set of problems and annoyances for another set of the same size :-)

I still have quite a bit of work to do on understanding inheritance modelling, which is potentially very useful to us, though I'm not sure exactly how it is going to work given that the DAX database is dynamic (fields and indeed entire tables can be added or removed depending on your customisations and licence).

Questions 1. (The biggie) In DAX it is OK for me to issue SELECTs against the database to read objects into RAM. However, all INSERT/UPDATE/DELETE statements are strictly prohibited. I need to intercept all such attempts that the framework makes and insert my own logic (basically I will call an X++ method via COM). IsDirty, IsNew flags etc must be properly updated after I have finished. There is no need for transaction management, that is handled in X++. It really is absolutely imperative that I never issue a I/U/D statement against the database; can the code be removed, or have it throw an exception?

1. I would like most SELECTS to be issued WITH (NOLOCK) to improve concurrency. How can I do this?

2. Not all DAX tables have physical primary keys defined (in fact many are heaps!). Can I define my own logical PK in LLBLGen?

3. I have run into situations where it is handy to run some special logic just before/after save and load. How can I do this?

4. DAX sometimes pads identifies with spaces, eg an employee ID might be " PHIL". In some circumstances, such as form display, I need to use the trimmed identifier, but if using it in a query I need to use the untrimmed form. With DataSets I trim everything using RTRIM(LTRIM(x)) when I load it, and then pad it back out as needed. What is the best way to do this in LLBLGen? Similarly, multi-line strings sometimes need twiddling to deal with CR/LF issues.

5. In my current system it has been necessary for me to de-normalize some entities on load so that I can present them in a friendlier, simplified manner. For example, an ITEM has 3 prices, stored as 3 records in the ITEMPRICES table. On my form I present the ITEM and 3 fields labelled "Sales Price", "Purchase Price" and "Inventory Price". My DataSet solution for this is a horrible hack, and I am wondering what the best solution for doing this sort of thing in LLBLGen is. Create a SQL view in the database to do the de-normalization and then create an entity based on the view? (Actually that might have been a better solution for DataSets too....dang!).

6. I need strongly-typed collection classes (to represent Employees, Manufacturers etc). Is this a good argument for use self-servicing model? My initial testing has used Adapter which doesn't generate these although I can create them in one line

   public partial class Employees : EntityCollection<EmplTableEntity>

7. Drawbacks to DataSets part 1. I have sometimes run into situations in complex data binding situations whereby displaying an object(s) in a form leads to events firing and the object thinks it has been changed when it really hasn't. I have found this really tricky to debug. Is there some way in LLBLGen objects (for example) of setting a breakpoint somewhere to find out which field is changing?

8. Drawbacks to DataSets part 2. I sometimes get a dreaded "failed to enable constraints" error. These are a pain to track down, especially if you don't have access to the customer's database. Can I turn on logging or something in LLBLGen to help me find out what is failing?

9. Connection string management. I need to have complete programmatic control over the connection string. I need to be able to change it in code before LLBLGen makes any connection attempts.

10. Entity lifetime management. When displaying entities in WinForms, which is a rich environment, I often need to "Delete" an entity from the screen when the user presses a button, but not serialize the delete until the user presses OK (if they press CANCEL nothing should change). I know of at least 2 ways of doing this a) clone the real object into a copy before displaying it and work on the copy, b) mark an object "deleted" and only commit that change later. Both have drawbacks. What approach works well with LLBLGen?

OK, I just answered number 6 myself. Looks like a typed list or view is the way to go!

Answers: 1. You can either use validation logic to stop Inserts/Updates/Deletes (Refer to the LLBLGen Pro docs: "Use the generated code -> Validation per field or per entity"), or you can just handle/Override some of the various events available in the DataAccessAdapter or in the Entity classes themselves (SelfServicing) Please refer to the LLBLGen Pro Reference manual.

1. LLBLGen Pro doesn't use database locks on table rows to prevent some sort of concurrency control; every entity object is disconnected and can move around in the application freely. The application should be aware of the fact that when data is altered in an entity object, it has to propagate those changes a.s.a.p. back to the database.

2. You can use LLBLGen Pro to define your PK Fields.

3. Please refer to the LLBLGen Pro docs "Using the generated code - Tapping into actions on entities and collections"

4. You can either handle this in the web/windows controls and leave the retrieved value as it is, or you can trim the retrieved values, and set the IsChanged property of the field to false, so it doesn't get saved in the database when you save your entity, or trim the retrieved values and when saving use the originaly fetched value to be saved again. The originally fetched value will be stored in the DBValue property of your EntityField.

5. Many options here: a) A database View mapped into either an entity or a Typed View. b) Use a typed List c) Use a Dynamic List d) Use Fields Mapped on related Fields (extends the properties of your entity)

6. Either use SlefServicing for those classes to be generated outof the box. Or you can use Generics as you have stated: EntityCollection<MyEntity> myEntityCollection = new EntityCollection<MyEntity>();

7. Entity: OnFieldValueChanged

8. Refer to the LLBLGen Pro docs: "Using the generated code - Troubleshooting and debugging"

9. a) Adapter: The DataAccessAdapter reads the connection string automatically from the *.config file available, however it also accepts a connection string if you supply one. This means that you can target different databases under different users on a per-call basis

b) Slef-Servicing: The DbUtils class lets you set the global connection string to use for every connection to the database. This setting overrides the connection string read from the appSettings section in the .config file. Once the setting is set, every connection to the database uses the set connection string. You set the connection string to use at runtime using the following code:

1. Use a UnitOfWork object, please refer to the LLBLGen Pro docs: "Using the generated code -> Adapter/SelfServicing-> Unit of work and field data versioning"

Some extra info to what Walaa already provided: ad 1: you could look into creating a derived class of DataAccessAdapter and override the save* methods and delete* methods, the ones which are virtual, and instead of calling the base, do your own thing, like calling the external methods or throw an exception.

Thanks guys, looks like LLBLGen can be a viable solution. I do like the option of deriving my own subclass (saw that recommended in some other threads I was browsing too).