Hello,

Is it possible to create an entity of a Stored Procedure? Just like you can create an entity of a view I would like the same for a Stored Procedure.

In my situation I would like to fetch data from multiple tables and save them in a single entity. With a view this is possible, but you can't pass parameters to a view so that is why I need a Stored Procedure.

If this is not possible is there another way to create a custom entity (with data from multiple tables) and save my Stored Procedure results into that?

Since I've had no reaction yet I created my stored procedure and fetched it using RetrievalProcedures. This can return a Datatable, Dataset or IRetrievalQuery. I would like to save this data in an entitycollection or project it to a custom class. Currently I've got the following code:

UmbracoProductCollection umbracoProductCollection = new UmbracoProductCollection();
            using (IRetrievalQuery query = RetrievalProcedures.GetSpDigiGetRecursiveUmbracoProductsCallAsQuery(productGroupCode))
            {
                TypedListDAO dao = new TypedListDAO();
                using (IDataReader reader = dao.GetAsDataReader(null, query, CommandBehavior.CloseConnection))
                {
                    List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
                    valueProjectors.Add(new DataValueProjector(UmbracoProductFieldIndex.UmbracoProductId.ToString(), 0, typeof(Guid)));
                    valueProjectors.Add(new DataValueProjector(UmbracoProductFieldIndex.FirstProductName.ToString(), 1, typeof(string)));
                    DataProjectorToIEntityCollection projector = new DataProjectorToIEntityCollection(umbracoProductCollection);
                    dao.GetAsProjection(valueProjectors, projector, reader);
                }
            }

In this code I need to define each property myself. Isn't there a more easy to do this like:

UmbracoProductCollection umbracoProductCollection = new UmbracoProductCollection();
IRetrievalQuery query = RetrievalProcedures.GetSpDigiGetRecursiveUmbracoProductsCallAsQuery(productGroupCode)
umbracoProductCollection.Load(query)

That way I don't need to define the properties every time. Is something like this possible?

I still don't understand why you cannot use entities based on views - you can filter them in the code instead of needing to pass parameters to stored procedures.

Can you elaborate more on why you think this approach will not work for you ?

Matt

jbreuer wrote:

That way I don't need to define the properties every time. Is something like this possible?

you also can do something like:

List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>(entityFields.Count);
foreach (EntityField2 entityField in entityFields)
{
    valueProjectors.Add(new DataValueProjector(entityField.Alias, entityField.FieldIndex, entityField.DataType));
}
DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2(entityCollection);

MTrinder wrote:

I still don't understand why you cannot use entities based on views - you can filter them in the code instead of needing to pass parameters to stored procedures.

Can you elaborate more on why you think this approach will not work for you ?

Matt

Perhaps it's more clear if I show my Stored Procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Jeroen Breuer (Digibiz)
-- Create date: 2009-12-03
-- =============================================
ALTER PROCEDURE [dbo].[spDIGI_GetRecursiveUmbracoProducts] 
    -- Add the parameters for the stored procedure here
    @productGroupCode nvarchar(255)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @UmbracoProductsQuery nvarchar(MAX)
    DECLARE @Results nvarchar(MAX)
    DECLARE @Count int

    DECLARE my_cursor CURSOR FOR

    WITH UmbracoResults (code, parentCode, description, nivo) AS
    (
       -- Base case
       SELECT
          code,
          parentCode,
          description,
          nivo
       FROM productGroup
       WHERE code = @productGroupCode

       UNION ALL

       -- Recursive step
       SELECT
          e.code,
          e.parentCode,
          e.description,
          e.nivo
       FROM productGroup e
        INNER JOIN UmbracoResults -- Note the reference to CTE table name
          ON e.parentCode = UmbracoResults.code
    )
        
    SELECT UmbracoResults.code FROM UmbracoResults

    SET @UmbracoProductsQuery = 'SELECT     umbracoProduct.*
                                 FROM       umbracoProduct INNER JOIN
                                                umbracoProduct2productGroup ON umbracoProduct.umbracoProductId = umbracoProduct2productGroup.umbracoProductId'
    SET @Count = 0
    OPEN my_cursor
    
    FETCH NEXT FROM my_cursor
    INTO @Results

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Count = 0
        BEGIN
            SET @UmbracoProductsQuery = @UmbracoProductsQuery + ' WHERE umbracoProduct2productGroup.Code = ''' + @Results + ''''
        END
        ELSE
            SET @UmbracoProductsQuery = @UmbracoProductsQuery + ' OR umbracoProduct2productGroup.Code = ''' + @Results + ''''

        SET @Count = @Count + 1
        
        FETCH NEXT FROM my_cursor
        INTO @Results
    END

    CLOSE my_cursor
    DEALLOCATE my_cursor

    IF @Count > 0
    BEGIN
        EXECUTE sp_executesql @UmbracoProductsQuery
    END 
END

As you can see I'm using a recursive query in which the parameter is passed. I don't think this is possible I if create a view and filter it in the code unless I'm wrong and someone can explain to me how I should do this .

If this is not possible is there another way to create a custom entity (with data from multiple tables) and save my Stored Procedure results into that?

The only route I can think of is: Create a table to match the SP resultSet, either let the SP write inot that table, so you can later fetch your entity from that table, or Use projection at runtime, so the SP call resultSet can be projected into an entity mapped to that table.

Walaa wrote:

The only route I can think of is: Create a table to match the SP resultSet, either let the SP write inot that table, so you can later fetch your entity from that table, or Use projection at runtime, so the SP call resultSet can be projected into an entity mapped to that table.

Well if that's the only to go guess I'll have to.

Still the following would be much easier if it was possible:

UmbracoProductCollection umbracoProductCollection = new UmbracoProductCollection();
IRetrievalQuery query = RetrievalProcedures.GetSpDigiGetRecursiveUmbracoProductsCallAsQuery(productGroupCode)
umbracoProductCollection.Load(query)

Hope you guys implement it with Linq to LLBLGen someday.

daelmo wrote:

you also can do something like:

List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>(entityFields.Count);
foreach (EntityField2 entityField in entityFields)
{
    valueProjectors.Add(new DataValueProjector(entityField.Alias, entityField.FieldIndex, entityField.DataType));
}
DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2(entityCollection);

Hmm I'm new to LLBLGen. How can you loop through all the entityFields? I've got a entityFields object, but it only has static properties and no way to loop through them (or get a count like in your example). The name of my enity is UmbracoProductEntity and the name of my entityFields is UmbracoProductFields.

Thank you for this example!

Use the Fields property of the entity.

Walaa wrote:

Use the Fields property of the entity.

Already tried that, but my entity doesn't have a Fields property. Do I need to add an extra namespace or did I do something wrong generating the entities?

Are you sure the Designer shows some fields inside your entities? Did you try to access it as a static variable like so: CustomerEntity.Fields ? Coz it's not. YOu should do: var customer = new CustomerEntity(); var x = customer.Fields.Count;

Walaa wrote:

Are you sure the Designer shows some fields inside your entities? Did you try to access it as a static variable like so: CustomerEntity.Fields ? Coz it's not. YOu should do: var customer = new CustomerEntity(); var x = customer.Fields.Count;

Thank you that solved the problem! Thought I could acces them by calling it as a static variable. Why isn't it accesible as a static variable like the EntityFields object?

Thanks everybody for all your help! I've been able to create a generic method to convert a Stored Procedure to a collection:

/// <summary>
        /// Gets the resultset of a Stored Procedure and returns it as a Collection.
        /// </summary>
        /// <typeparam name="EntityObject">Entities which the collection will be filled with.</typeparam>
        /// <typeparam name="EntityCollection">The collection which must be returned.</typeparam>
        /// <param name="query">The Stored Procedure as an IRetrievalQuery.</param>
        /// <param name="transactionManager">If the sp is part of a transaction, put the Transaction here.</param>
        /// <returns>A filled collection.</returns>
        public EntityCollection ConvertStoredProcedureToCollection<EntityObject, EntityCollection>(IRetrievalQuery query, Transaction transactionManager)
            where EntityObject : EntityBase, new()
            where EntityCollection : EntityCollectionBase<EntityObject>, new()
        {
            //Create new instances of the EntityObject and EntityCollection.
            EntityObject entityObject = new EntityObject();
            EntityCollection entityCollection = new EntityCollection();

            //Get all the Fields of the EntityObject. These fields are necessary for fetching all the data from the resultset.
            IEntityFields entityFields = entityObject.Fields;

            using (query)
            {
                //Get the resultset of the Stored Procedure as an IDataReader.
                TypedListDAO dao = new TypedListDAO();
                using (IDataReader reader = dao.GetAsDataReader(transactionManager, query, CommandBehavior.CloseConnection))
                {
                    //Create a new instance of List<IDataValueProjector>. In this the values which will be fetched will be stored.
                    List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>(entityFields.Count);
                    foreach (EntityField entityField in entityFields)
                    {
                        //Put each entityField in List<IDataValueProjector>.
                        valueProjectors.Add(new DataValueProjector(entityField.Alias, entityField.FieldIndex, entityField.DataType));
                    }

                    //Create the projector and fill the collection.
                    DataProjectorToIEntityCollection projector = new DataProjectorToIEntityCollection(entityCollection);
                    dao.GetAsProjection(valueProjectors, projector, reader);

                    reader.Close();
                }
            }

            return entityCollection;
        }

Now if you want to convert a Stored Procedure to a collection all you have to do is:

UmbracoProductCollection umbracoProductCollection = ConvertStoredProcedureToCollection<UmbracoProductEntity, UmbracoProductCollection>(RetrievalProcedures.GetSpDigiGetRecursiveUmbracoProductsCallAsQuery(productGroupCode), null);

If people like this code it can be added to DbUtils class of LLBLGen.

Glad to know it worked finally. Thanks for sharing.