Lgen version: 2.6 final (v2.0.50727 runtime)

.NET 3.5, Adapter, General2008, Oracle 9i

I am trying to avoid using stored procedures for our basic CRUD stuff that is simple and not likely to change much. However there are quite a few procedures in various Oracle packages that we need to use...

My question surrounds the best approach to get data returned from procedures back into entities. I need to do this in a manner that is as easy as possible for client use of this data. Specifically I am looking for something that is somewhat generic, rather foolproof (i.e. client callers not worrying about ADO.NET connections etc.), simple to use, and requires the least amount of code possible for typical cases.

I would like to automate the process of creating and filling a given entity by matching up fields that exist in both the DB cursor and the entity. I am not quite sure how to go from DB field name to entity field name as the DB field name in the catalog does not appear to make it back to the generated model code best I can tell? Currently I have an ugly temporary naming convention workaround.

What are some of the differences between just returning a DataSet from an LGEN invoked stored procedure and manually creating an entity per table via reflection or entity field info vs going the projection route? I like the projection idea but I cannot see "genericizing" that w/the datareader and all. Trying to get a handle on what I'd be losing or gaining going different routes of getting stored proc data back into entities.

My initial quick and dirty test attempt looks like below. I would appreciate any input. Thanks

using System;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using CRMA.Model.DatabaseSpecific;
using CRMA.Model.EntityClasses;
using CRMA.Model.HelperClasses;
using MbUnit.Framework;

namespace CRMA.Model.Tests
{
    [TestFixture()]
    public class ChartComponentStoredProcTests
    {
        [Test()]
        public void GetChartCompentsForReviewViaDataSetToEntityFill()
        {
            DateTime startTime = DateTime.Now;
            const int CHART_REVIEW_ID = 32;
            //RetrievalProcedures.GetChartComponentsPkgGetChartComponentsCallAsQuery(CHART_REVIEW_ID);
            DataSet ds = RetrievalProcedures.ChartComponentsPkgGetChartComponents(CHART_REVIEW_ID);
            Assert.AreEqual(1, ds.Tables.Count);

            //either by index or cursor name (O_CURSOR etc.)            
            var collection = TempUtility.FillCollection<ChartComponentsEntity>(ds.Tables["O_CURSOR"]);
            Assert.GreaterEqualThan(collection.Count, 1);

            ChartComponentsEntity entity = collection[0];
            Assert.AreEqual(CHART_REVIEW_ID, entity.ChartReviewId);

            TimeSpan ts = DateTime.Now - startTime;
            Debug.WriteLine(string.Format("seconds: {0}", ts.TotalSeconds));
        }       
    }

    public class TempUtility
    {
        public static EntityCollection<TEntity> FillCollection<TEntity>(
            DataTable table)
            where TEntity : CommonEntityBase, new()
        {
            EntityCollection<TEntity> collection = new EntityCollection<TEntity>();

            if (null == table)
                throw new ArgumentNullException("table cannot be null; verify table name");
            
            foreach (DataRow row in table.Rows)
            {
                TEntity entity = new TEntity();             

                foreach (DataColumn column in table.Columns)
                {
                    if (!row.IsNull(column))
                    {
                        //TODO: how to get field name from field db target name?
                        // no "Target" or "FieldDbName" on IEntityField2
                        string fieldName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase( 
                            column.ColumnName.ToLower() ).Replace( "_", "" ); 
                        entity.Fields[fieldName].CurrentValue = row[column];
                    }
                }

                entity.IsNew = false;
                collection.Add(entity);
            }           

            return collection;
        }       
    }
}

Hi Geoff,

Have you checked Projecting Stored Procedure resultset onto entity collection already?

Yeah the below help topics are what got me rolling with this: 1) Generated code - Calling a stored procedure, Adapter 2) Generated code - Fetching DataReaders and projections, Adapter

However:

A) That does not tell me what differences there might be between doing a projection vs just newing off an entity and filling it myself manually (as in posted code for example).

B) Those are examples specific to calling one given SP. It would be rather painful to do something like that each time if you had to call hundreds of procedures. I'm looking for ways to automate as much of that as possible. The datareader / projection route appears much harder to attempt to automate.

C) In attempt to accomplish (B), trying to more directly map SP params to entities. Thought the Designer might have that ability. I'm missing pieces of info for doing this mapping, like the DB field names that correspond with the entity field names.

A) That does not tell me what differences there might be between doing a projection vs just newing off an entity and filling it myself manually (as in posted code for example).

IMHO projection is another easier approach and simple to code that doing it yourself as in the FillCollection() method.

B) Those are examples specific to calling one given SP. It would be rather painful to do something like that each time if you had to call hundreds of procedures. I'm looking for ways to automate as much of that as possible. The datareader / projection route appears much harder to attempt to automate.

Earlier you said

I am trying to avoid using stored procedures for our basic CRUD stuff that is simple and not likely to change much

And that's exactly what we recommend. Also you said:

However there are quite a few procedures in various Oracle packages that we need to use

DataReader Projection from SPs was introduced for these rare cases. It wasn't intended to be the highway of producing entities.

C) In attempt to accomplish (B), trying to more directly map SP params to entities. Thought the Designer might have that ability. I'm missing pieces of info for doing this mapping, like the DB field names that correspond with the entity field names.

Are you trying to develop a plug-in, with some templates to generate the projection code for you?

In v2.x there's no way to map CRUD procedures on entity actions, the runtime simply produces Dynamic SQL for a save action.

You could of course drive some templates with custom properties, but the runtime also has to be altered, i.e.: when a query is generated, you should intercept that and return a command which contains the proc and parameters setup instead. This isn't that trivial.

I'm not trying to develop a plug-in or mess with templates / code generation. I am simply trying to write a helper method that can match up an output cursor's fields to an entity's fields so it can automatically prefill an entity with all the fields that exist in both. Some fields might have to be manually set for differences between the data SELECTed and the entity and that's fine. I am only talking about reading/loading/fetching data here, no modifications.

We are coming from a heavy DataSet and oracle procedure paradigm and I am trying to ease the transition by ensuring we can still load entities from stored procs relatively easily. We will still need to use several oracle procedures.

With a dataset it is obviously much simpler to do:

Customers.Load(reader)

Than to do the below...

List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CustomerId.ToString(), 0, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CompanyName.ToString(), 1, typeof( string ) ) );
DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2( customers );
adapter.FetchProjection( valueProjectors, projector, reader );

... when there might be 30 fields or so per entity, and 3 cursors / entities being loaded in one stored proc call. I'd like to avoid writing 100 lines of entity loading code for such a stored proc when we could do it before in 3 lines of code. We know we are matching a given cursor to a given entity, know that most fields match the targets of the LGEN entity fields, and we know the types match... so it seems redundant to have to specify name, index, type etc. for each of the 90 fields. Multiply that by the various procedures and its a lot of code.

I am not opposed to helper methods that dynamically create projections to get the cursor data into entities. If there are benefits to that over me inflating entities myself that's cool; I'm just not sure what the pros and cons would be to each approach. For example (A) would the entity be in a different state using projections vs manual filling (IsNew... etc. etc.)? Or (B) are projections handling any scenarios that might be missed doing it manually? Or (C) is performance slower w/one method over the other?

Either way it seems like I need a way to get at the catalog metadata that LGEN has to go backwards from the DB field name to what the field is named on the entity. With the DataReader/Projection approach, I do not like the idea of specifying the index / ordinal position of the SP params... the order could get shifted easily. I could use reader.GetOrdinal but I would need to the DB field name each given entity field maps to.

thnk2wn wrote:

I'm not trying to develop a plug-in or mess with templates / code generation. I am simply trying to write a helper method that can match up an output cursor's fields to an entity's fields so it can automatically prefill an entity with all the fields that exist in both. Some fields might have to be manually set for differences between the data SELECTed and the entity and that's fine. I am only talking about reading/loading/fetching data here, no modifications.

Then you should look into the projection feature. If you for example have 20 fields in your entity, and you fill 10 through method x, you can project a proc's result onto the entities to fill the other 10. This might require you to write your own projector class, based on the existing one, though this is a simple job as you can just modify the class in the runtime lib sourcecode.

We are coming from a heavy DataSet and oracle procedure paradigm and I am trying to ease the transition by ensuring we can still load entities from stored procs relatively easily. We will still need to use several oracle procedures.

You can do so, please see the projection documentation in the manual which has an example of fetching entities from a resultset produced by a proc. You can wrap this in generic code with some factories perhaps, though it is of course specific per proc which fields it returns under which names. That's also why you should look into moving to dyn. sql pretty quickly and use the projection stuff for procs which are hard to convert to the query system (or if you use linq, to linq) and migrate them over when time permits.

With a dataset it is obviously much simpler to do:

Customers.Load(reader)

Than to do the below...

List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CustomerId.ToString(), 0, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CompanyName.ToString(), 1, typeof( string ) ) );
DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2( customers );
adapter.FetchProjection( valueProjectors, projector, reader );

... when there might be 30 fields or so per entity, and 3 cursors / entities being loaded in one stored proc call. I'd like to avoid writing 100 lines of entity loading code for such a stored proc when we could do it before in 3 lines of code. We know we are matching a given cursor to a given entity, know that most fields match the targets of the LGEN entity fields, and we know the types match... so it seems redundant to have to specify name, index, type etc. for each of the 90 fields. Multiply that by the various procedures and its a lot of code.

That's a lot of code indeed, but keep in mind that the resultset of the proc is unknown, so it will be work done by hand anyway.

I am not opposed to helper methods that dynamically create projections to get the cursor data into entities. If there are benefits to that over me inflating entities myself that's cool; I'm just not sure what the pros and cons would be to each approach. For example (A) would the entity be in a different state using projections vs manual filling (IsNew... etc. etc.)? Or (B) are projections handling any scenarios that might be missed doing it manually? Or (C) is performance slower w/one method over the other?

They're not marked as fetched. If you want that, please create your own projector class. Please have a look at ORMSupportClasses\Projection\DataProjectorToEntityCollection.cs (contains 2 classes, one for selfservicing and one for adapter). It's really straightforward, so making a copy of that code and alter it to your liking (e.g. set the IsNew flag to false, the IsDirty flag to false and the entity.Fields.State enum to Fetched) is easy to add. To perform the projection, you then simply use your class as projector instead of the default one.

Either way it seems like I need a way to get at the catalog metadata that LGEN has to go backwards from the DB field name to what the field is named on the entity. With the DataReader/Projection approach, I do not like the idea of specifying the index / ordinal position of the SP params... the order could get shifted easily. I could use reader.GetOrdinal but I would need to the DB field name each given entity field maps to.

Projections don't directly work on the datareader. The reader's row is first processed if duplicates have to be filtered out. Also if type converters have to be performed (e.g. NUMBER(1,0) to bool) they're performed. Then for each projector at index i, the ProjectValue is called which gets the complete read row of values as an object array, the result is then placed at index i. After that, the row is passed to the projector class for conversion to an object, e.g. an entity. (so that projector is just doing simply value copy, no more processing necessary).

The routine in the projector which adds the result to the projector container gets besides the row of values, also the projectors used.

Though, I think you don't want all that, you just want to pump values from a proc into an entity through a reader. This somehow still requires a mapping between entity field - resultset column of the proc. That mapping one way or the other has to be defined somewhere. That kind of mapping can be consumed by a routine which builds the projector lists per entity for you.