Grandchild Stored Procedures

Developer
May 28, 2013 at 1:18 AM
Edited May 28, 2013 at 1:51 PM
Why is the stored procedure for Grandchildren (PO/PODetailList/PODetailListItem) generating the ID column twice in the Grandchildren (PODetailListItem) and the two foreign key contsraint columns are declared as output? It's the typical PO/PODetail relationship in which each line of has an an with a foreign key constraint to an item as follows:

Image

Image

The PO.ID column is defined in the value property of PODetailListItem as 'POID' and Item.ID defined in the value property of PODetailListItem as 'ItemID'. Here is the stored procedure which is generated:

``` / Object: StoredProcedure [dbo].[AddPODetailListItem] /
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPODetailListItem]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddPODetailListItem]
GO

CREATE PROCEDURE [dbo].[AddPODetailListItem]
@ID int OUTPUT,
@POID int OUTPUT,
@ItemID int OUTPUT,
@ID int,
@Description nvarchar(255),
@Quantity smallint,
@Cost decimal(19, 4),
@CreateDT datetime2,
@CreateUserID varchar(25),
@ChangeDT datetime2,
@ChangeUserID varchar(25)
AS
BEGIN

    SET NOCOUNT ON

    /* Insert object into dbo.PODetail */
    INSERT INTO [dbo].[PODetail]
    (
        [POID],
        [Description],
        [Quantity],
        [Cost],
        [CreateDT],
        [CreateUserID],
        [ChangeDT],
        [ChangeUserID]
    )
    VALUES
    (
        @ID,
        @Description,
        @Quantity,
        @Cost,
        @CreateDT,
        @CreateUserID,
        @ChangeDT,
        @ChangeUserID
    )

    /* Return new primary key */
    SET @ItemID = SCOPE_IDENTITY()

END
GO
END
GO
END
GO

/ Object: StoredProcedure [dbo].[UpdPODetailListItem] /
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdPODetailListItem]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdPODetailListItem]
GO

CREATE PROCEDURE [dbo].[UpdPODetailListItem]
@ID int,
@POID int,
@ItemID int,
@Description nvarchar(255),
@Quantity smallint,
@Cost decimal(19, 4),
@ChangeDT datetime2,
@ChangeUserID varchar(25)
AS
BEGIN

    SET NOCOUNT ON

    /* Check for object existance */
    IF NOT EXISTS
    (
        SELECT [ID], [ID], [ID] FROM [dbo].[PODetail]
        WHERE
            [ID] = @ID AND
            [ID] = @POID AND
            [ID] = @ItemID
    )
    BEGIN
        RAISERROR ('''dbo.PODetailListItem'' object not found. It was probably removed by another user.', 16, 1)
        RETURN
    END

    /* Update object in dbo.PODetail */
    UPDATE [dbo].[PODetail]
    SET
        [Description] = @Description,
        [Quantity] = @Quantity,
        [Cost] = @Cost,
        [ChangeDT] = @ChangeDT,
        [ChangeUserID] = @ChangeUserID
    WHERE
        [ID] = @ID AND
        [ID] = @POID AND
        [ID] = @ItemID
    /* Update object in dbo.Item */
    UPDATE [dbo].[Item]
    SET
    WHERE
        [ID] = @ID AND
        [ID] = @POID AND
        [ID] = @ItemID
    /* Update object in dbo.PO */
    UPDATE [dbo].[PO]
    SET
    WHERE
        [ID] = @ID AND
        [ID] = @POID AND
        [ID] = @ItemID

END
GO

/ Object: StoredProcedure [dbo].[DelPODetailListItem] /
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DelPODetailListItem]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DelPODetailListItem]
GO

CREATE PROCEDURE [dbo].[DelPODetailListItem]
@ID int,
@POID int,
@ItemID int
AS
BEGIN

    SET NOCOUNT ON

    /* Check for object existance */
    IF NOT EXISTS
    (
        SELECT [ID], [ID], [ID] FROM [dbo].[PODetail]
        WHERE
            [ID] = @ID AND
            [ID] = @POID AND
            [ID] = @ItemID
    )
    BEGIN
        RAISERROR ('''dbo.PODetailListItem'' object not found. It was probably removed by another user.', 16, 1)
        RETURN
    END

    /* Delete PODetailListItem object from PODetail */
    DELETE
    FROM [dbo].[PODetail]
    WHERE
        [dbo].[PODetail].[ID] = @ID AND
        [dbo].[PO].[ID] = @POID AND
        [dbo].[Item].[ID] = @ItemID

END
GO
Coordinator
May 29, 2013 at 6:54 AM
Edited May 29, 2013 at 6:55 AM
Hi Keith,

CslaGenFork doesn't handle correctly your schema because you use ID as a column name everywhere. Can you change Item.ID to Item.ItemID and PO.ID to PO.POID and repeat the creation gestures?

Keep in mind you must have two editable child collections, like this

PO (ER)
PO_Detail_List (ECC all Items of each PO)
PO_Detail_Item (EC item of the above collection)

Item (ER)
Item_Detail_List (ECC all POs of each Item)
Item_Detail_Item (EC items of the above collection)

This is an issue as CslaGenFork should look at the foreign key relationship but in fact looks for similar column names.

Regards,
Tiago Freitas Leal
Developer
May 29, 2013 at 12:11 PM
tiago wrote:
Hi Keith,

CslaGenFork doesn't handle correctly your schema because you use ID as a column name everywhere. Can you change Item.ID to Item.ItemID and PO.ID to PO.POID and repeat the creation gestures?

Keep in mind you must have two editable child collections, like this

PO (ER)
PO_Detail_List (ECC all Items of each PO)
PO_Detail_Item (EC item of the above collection)

Item (ER)
Item_Detail_List (ECC all POs of each Item)
Item_Detail_Item (EC items of the above collection)

This is an issue as CslaGenFork should look at the foreign key relationship but in fact looks for similar column names.

Regards,
Tiago Freitas Leal
<br/>
That explains a LOT. All along I though I was doing something wrong.
Coordinator
May 29, 2013 at 11:18 PM
Hi again Keith,

In fact it matches both name and FK at the same time. The rationale was support for legacy databases that can't be changed and that might miss FK but that have match naming conventions (like the one I proposed in my previous message).
The implementation is wrong as CslaGenFork should use naming match or FK but never use both at the same time.
I'm fixing it right now and 4.5 RC will have this corrected.

Regards,
Tiago Freitas Leal
Coordinator
May 30, 2013 at 6:05 PM
Developer
May 30, 2013 at 10:28 PM
tiago wrote:
Hi Keith,

CslaGenFork doesn't handle correctly your schema because you use ID as a column name everywhere. Can you change Item.ID to Item.ItemID and PO.ID to PO.POID and repeat the creation gestures?


This is an issue as CslaGenFork should look at the foreign key relationship but in fact looks for similar column names.

Regards,
Tiago Freitas Leal
<br/>
I have made the column name changes and all is 100%. The only other change I had to make was to set 04. Child Object Options/Parent to 'InvoiceID/POID' and remove them from the value properties. The generated code "on the money".

I may change the column names back once 4.5 is released. I prefer to use only 'ID'. Since the column is in the context of the table it seems redundant to have Invoice.InvoiceID etc.

Keep up the good work.