Would it be an approach that we could consider? I am a fun of pure ADO.Net and SQL. I seem to have a lot of issues with ORM implementations due to the fact that is most real scenarios they do not perform and the object query syntax seems to be complicated and limiting. I want to hear thoughts about autogeneration of classes and methods that hide ADO.Net code, object graph and query mappings that I can edit, complile and run with a mouse click.

As far as I am concerned, icons/graphics are limiting and prone to vagueness, while languages are pricise and rich, I highly doubt it is even possible to map out complicated queries for real life scenarios.

Take Access queries for example, it is very confusing and complicated trying to work out a only sligtly complex query, and often times you have to throw in where conditions in writng.

 OK, if you have the time take a look at http://www.orasissoftware.com. Download it and it has a one month free trial. I am using it and all the code it generates is pure ADO.Net with no dependencies. I was able to map into 10 levels of nested objects graphically and I put in extremely complicated queries either by building them with their designer or just typing them in.  Let me know what you think. I would like to see the pros and cons. There are some things that I do not like for example they embed the database connection string in the DAL classes. They claim that you can inject it using any factory or injection pattern. Please let me know what you think. I loved tha mappings that I can maintain in teh IDE as well as being able to test every method with a click of a button.

 OK, I will try.

Seems that you already have an excellent tool to do what you want, then what is the real question for this post then?

 It may seem a good tool to me but it may be lucking functionality that I may not have encountred yet. The only way to find out is by real developers like you putting it in use. Consideriding the fact that it generates open non proprietary code you can always throw it and just keep the code. I am actually looking for other people's experiences with it and feedback.

 So you can try it as well and tell me what you think? How else do we know that products are good unless we really use them right?

The last one Orasis  handled was this one. As real as it gets... Complicated enough you think? Never doubt unless you try...

With BOM_cte(ProductAssemblyID, ComponentID, ComponentDesc, PerAssemblyQty,

StandardCost, ListPrice, BOMLevel, RecursionLevel) As (Select

b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty,

p.StandardCost, p.ListPrice, b.BOMLevel, 0

From Production.BillOfMaterials b Inner Join

Production.Product p On b.ComponentID = p.ProductID

Where b.ProductAssemblyID = @StartProductID And @CheckDate >= b.StartDate

And @CheckDate <= ISNULL(b.EndDate, @CheckDate)

Union All

Select b.ProductAssemblyID, b.ComponentID, p.Name, b.PerAssemblyQty,

p.StandardCost, p.ListPrice, b.BOMLevel, RecursionLevel + 1

From BOM_cte cte Inner Join

Production.BillOfMaterials b On b.ProductAssemblyID = cte.ComponentID

Inner Join

Production.Product p On b.ComponentID = p.ProductID

Where @CheckDate >= b.StartDate And @CheckDate <= ISNULL(b.EndDate,

@CheckDate))

Select b.ProductAssemblyID, b.ComponentID, b.ComponentDesc,

Sum(b.PerAssemblyQty) As TotalQuantity, b.StandardCost, b.ListPrice,

b.BOMLevel, b.RecursionLevel

From BOM_cte b

Group By b.ProductAssemblyID, b.ComponentID, b.ComponentDesc, b.StandardCost,

b.ListPrice, b.BOMLevel, b.RecursionLevel

Order By b.BOMLevel, b.ProductAssemblyID, b.ComponentID