Could DAC code generation include OUTPUT (SQL)/RETURN (Oracle) clause to return auto generated primary keys?

Jan 24, 2012 at 2:36 AM
Edited Jan 24, 2012 at 3:05 AM

While working with the generated DACs, I noticed that the Create method purports to return an updated business entity, for example:

 

        /// <summary>
        /// Inserts a new row in the _CitizenshipTypes table.
        /// </summary>
        /// <param name="_CitizenshipType">A _CitizenshipType object.</param>
        /// <returns>An updated _CitizenshipType object.</returns>
        public _CitizenshipType Create(_CitizenshipType _CitizenshipType)
        {
            const string SQL_STATEMENT =
                "INSERT INTO _CitizenshipTypes (CitizenshipTypeID, Citizenship, Country) " +
                "VALUES (@CitizenshipTypeID, @Citizenship, @Country);  ";

            // Connect to database.
            Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);
            using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
            {
                // Set parameter values.
                db.AddInParameter(cmd, "@CitizenshipTypeID", DbType.Guid, _CitizenshipType.CitizenshipTypeID);
                db.AddInParameter(cmd, "@Citizenship", DbType.AnsiString, _CitizenshipType.Citizenship);
                db.AddInParameter(cmd, "@Country", DbType.AnsiString, _CitizenshipType.Country);
                
                db.ExecuteNonQuery(cmd);
            }

            return _CitizenshipType;
        }

While this code requires that the CitizenshipTypeID field be set in the object and then inserted into the database, if the data model instead defined a default for this field like SQLServer's NewSequentialID() a small modification would allow this to be returned from the insert and populated into _CitizenshipType before being returned:

 

        public _CitizenshipType Create(_CitizenshipType _CitizenshipType)
        {
            const string SQL_STATEMENT =
                "INSERT INTO _CitizenshipTypes (Citizenship, Country) " +
                "OUTPUT inserted.CitizenshipTypeID VALUES (@Citizenship, @Country);  ";

            // Connect to database.
            Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);
            using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
            {
                // Set parameter values.
                db.AddInParameter(cmd, "@Citizenship", DbType.AnsiString, _CitizenshipType.Citizenship);
                db.AddInParameter(cmd, "@Country", DbType.AnsiString, _CitizenshipType.Country);
                
                _CitizenshipType.CitizenshipTypeID = (Guid)db.ExecuteScalar(cmd);
            }

            return _CitizenshipType;
        }

 

I've read that a similar feature, RETURN, exists for Oracle with slightly different syntax.

For the Method Type 'Insert', Momentum could include a check box for a column to be the target of OUTPUT, and then adjust the format of the generated code accordingly...

In fact, I would be willing to implement this as it would save me from having to manually modify the insert code for some 150 DACs I've generated with Momentum which need this feature :).  I feel this is a very valuable feature considering how common auto generated primary keys are and the myriad scenarios in which one would want to retrieve the id, such as when managing foreign key relationships.  With the _CitizenshipType example above, an application with a Customers table with a reference to Citizenship would be able to manage the foreign key relationship with a newly created citizenship in a manner similar to:



_CitizenshipTypeDAC.Create(_citizenshipType);
customer.CitizenshipTypeID = _citizenshipType.CitizenshipTypeID;
_CustomerDAC.Create(customer);

 

 

Jan 28, 2012 at 2:54 AM
Edited Jan 28, 2012 at 7:46 AM

Hi,

I have taken your suggestion and implemented the feature. It will be available in 1.0.0.6. Thank you for your suggestion.

Btw, you generated 150 DACs? That's awesome! :)

Hugs,
Serena

Jan 31, 2012 at 6:55 PM

YaY!  Thanks Serena!!  And it turns out to be 165 DACs, but who's counting? Well, I guess I just did :)

Jan 31, 2012 at 6:59 PM

While I have your ear :) I added an issue a while back to the issue tracker regarding the mapping of SQL Server 'real' type in the Entity generator.  The generator generates a 'single' when it needs to be 'Single'.  Can you slip that into the next update :)?

Feb 1, 2012 at 4:52 AM

Just to make sure you are doing it correctly, you should NOT have 165 BCs and 165 services mapping to those DACs :) A BC can call multiple DACs.

Yeah, I fixed the "single" bug. Thanks for pointing it out.

Hugs,
Serena 

Feb 2, 2012 at 9:07 AM

Indeed, my BCs and Services map more closely to units of business functionality.  One, uses about 50 of those tables all by its self.  You were kind enough to put those words of wisdom in one of your how-tos and I've taken it to heart.

Still gets a little crazy in the solution explorer, though.  Since I'm really good at suggesting features ;) I'll go ahead and put my vote in for an additional namespace field in the generators which map to a folder in the project  so I could have Business.Entities.Preferences, for example, in the Preferences folder of the Business.Entities project

Alternatively, I'll be completely content with the wonderful work you've already done, hehe

Feb 3, 2012 at 1:26 AM
erikest wrote:

... I'll go ahead and put my vote in for an additional namespace field in the generators which map to a folder in the project  so I could have Business.Entities.Preferences, for example, in the Preferences folder of the Business.Entities project

Alternatively, I'll be completely content with the wonderful work you've already done, hehe

Generating code files to specific project folders are not supported. :p