Sorry for the long subject. Here is the actual question.

I purchased LLBLGen Pro some time ago and haven't used it. It's version **1.0.2005.1 **Final (March 31st, 2006). At last, I got to it and started generating some code. Here is what I noticed:

All of the stored procedures wrappers got generated as static functions in the namespace RetrievalProcedures (or something similar). They all return DataTable objects.

What I was hoping to see is a type-safe return type. For instance, if my stored procedure returns a set of customers, then maybe its wrapper would return something like: List<CustomersEntity> or CustomersEntityCollection...

1. Is that a customizable behavior?
2. Is that something available in my version of LLBLGen?
3. If driven by temlpates, how trivial is it customize templates to implement such behavior?

I guess I can see that the tool would need to parse the stored procedure code to identify exactly what it returns... but I guess it can be done.

I am trying to avoid constantly implementing mappers from DataTables into entities. I haven't looked into whether entities' factories have parameters that would take tables and generate entities from them...

Am I asking for something unreasonable? If this feature is not possible, is there a workaround or a different approach all together?

Thank you, VR

For version 1.0.2005.1, you will need to manually map the resultset.

Starting from v.2.0, a powerful data-projection framework was added, which can project any entitycollection or resultset retrieved from a datareader onto any datastructure of any type using generic code. Hint: you can fetch a Retrieval Stored Procedure as an IDataReader, then you can project it into an entityCollection.

Following is an adapter example:

 // C#
EntityCollection customers = new EntityCollection( new CustomerEntityFactory() );
EntityCollection orders = new EntityCollection( new OrderEntityFactory() );
using(IRetrievalQuery query = RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery( "Germany" ))
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        using(IDataReader reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
        {
            // first resultset: Customers.
            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
            // project value on index 0 in resultset row onto CustomerId
            valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CustomerId.ToString(), 0, typeof( string ) ) );
            // project value on index 1 in resultset row onto CompanyName
            valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CompanyName.ToString(), 1, typeof( string ) ) );
            // resultset contains more rows, we just project those 2. The rest is trivial.
            DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2( customers );
            adapter.FetchProjection( valueProjectors, projector, reader );

            // second resultset: Orders. 
            valueProjectors = new ArrayList();
            valueProjectors.Add( new DataValueProjector( OrderFieldIndex.OrderId.ToString(), 0, typeof( int ) ) );
            valueProjectors.Add( new DataValueProjector( OrderFieldIndex.CustomerId.ToString(), 1, typeof( string ) ) );
            valueProjectors.Add( new DataValueProjector( OrderFieldIndex.OrderDate.ToString(), 3, typeof( DateTime ) ) );
            // switch to the next resultset in the datareader
            reader.NextResult();
            projector = new DataProjectorToIEntityCollection2( orders );
            adapter.FetchProjection( valueProjectors, projector, reader );
            reader.Close();
        }
    }
}

Please note that in general we disencourage the usage of SPs as the main channel of entity fetching.

Walaa,

Thank you for your response.

Walaa wrote:

Starting from v.2.0, a powerful data-projection framework was added, which can project any entitycollection or resultset retrieved from a datareader onto any datastructure of any type using generic code. Hint: you can fetch a Retrieval Stored Procedure as an IDataReader, then you can project it into an entityCollection.

So, I understand that the sample you provided is the 2.0 version. Is that correct?

Walaa wrote:

Hint: you can fetch a Retrieval Stored Procedure as an IDataReader, then you can project it into an entityCollection.

Could you please explain that? I was always under the impression that stored procedures were the best approach to provide database level abstraction to data manipulation, while providing the ability to add roles -- better security than just SQL queries from the code.. Am I misunderstanding something?

Thanks, VR

So, I understand that the sample you provided is the 2.0 version. Is that correct?

Yes, 2.x

Could you please explain that? I was always under the impression that stored procedures were the best approach to provide database level abstraction to data manipulation, while providing the ability to add roles -- better security than just SQL queries from the code.. Am I misunderstanding something?

This have been debated before here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8452

Also the following is copied from a helpdesk(private) thread:

Frans wrote:

Fetching into entities can be done through projections, though it requires more code than normally fetching entities. Saving entities isn't doable through procs, you've to manually pull the values from the entities and save them using procs.

The things against mapping onto procs is that it's too brittle and not really maintainable: a change to a proc can have deep effects into the application which are hard to track down. Also, all the features you would take for granted using dynamic SQL are gone: no prefetch paths, no auto-id syncing from the db, no flexible updates, no flexible filtering etc.

If you really have to keep the procs, you could use iBatis.net instead. It's an open source toolkit which maps entity classes to procs. It's very simple but does the trick. Though dropping the requirement for procs is IMHO a better choice, IF it's a choice you can make of course. Often these restrictions are purely artificial and based on myths and false information that procs would be faster and more secure. I mean, if I use an application which calls get_Customers @customerID, I can get the info from any customer. if I have to call del_Customer @customerID to delete a customer, and the app I'm using can do that, why can't I do that, as a user? So where's the security?

LLBLGen Pro comes with authorization support inside the entity. THis means that you have 1 application, 1 codebase, and you can decide which user can see which entities, which columns etc. controlled through an authorizer object you write.