DataPortal_Insert() Return value

smark posted on Friday, February 02, 2007

    What would be some of the ways you could get return values from an insert database operation in the DataPortal_Insert() method and bubble it up to the calling UI. Basically, i need to grab a couple of output parameters after the insert operation. Thanks.

Michael Hildner replied on Friday, February 02, 2007

There's an example in the book in chapter 8. You can search for ParameterDirection.Output.

You mark your parameters as output params, e.g.:

cm.Parameters.AddWithValue("@AddressPK", this._pk);
cm.Parameters["@AddressPK"].Direction = ParameterDirection.Output;

Then after the insert, grab the new value, e.g.:

_pk = (Guid)cm.Parameters["@AddressPK"].Value;

Of course your sproc need to specify that it's an output:

ALTER PROCEDURE [dbo].[InsertAddress]

@AddressLine1 varchar(200),

...

@AddressPK uniqueidentifier OUTPUT

Hope that's what you're looking for.

smark replied on Friday, February 02, 2007

Thanks Michael,

I am actually looking to get an output parameter that is not part of an object's state - at least not directly. Okay, I have at least three object properties other than the ID pk, which the SP checks to see if these properties already exsit in the database. For example, if a username already exists in the database, the sproc returns this information as a result of the insert operation without inserting any data. I would like to bubble up this information to the UI. Thanks.

RockfordLhotka replied on Friday, February 02, 2007

Here are two ideas.

First, in reality any data flowing to and from the database as part of your use case should fit naturally into your object model. If it doesn't, then your object model is suspect. That's what you are facing here, and the right answer is to alter your object model to better match your use case requirements.

Second, there's a workaround. If you want to keep your object model as-is, but still technically resolve the issue, you can use a Command object. Don't insert your object directly, but rather wrap it in a Command object, which can orchestrate the work. This Command object can return the "out-of-band" data. This may feel clumsy, and I think it is, because the first answer is the right one , but it can work.

smark replied on Friday, February 02, 2007

    I knew the object model question would invariably show up! Oh the joys of learning! Somebody from Magenic mentioned "Dumb UI" in CSLA context a while ago; I think "Dumb Database" would be more appropriate in this context.
The business logic is that there are three properties that are unique to this object: (1) the combination of First & Last Name; (2) UserName/LoginID (string); and (3) Email. The fourth is of course the object's ID (GUID).

It has been so easy to put  this logic in the  insert  sproc and let the RDBMS (MySQL in this particular case) figure out if those values already exists in a ten thousand row table. How would you code this logic in the business object? May be I could wrap this in the EXISTS' method as in the project tracker sample? Does that make it too chatty -- first talking to the database to see if the properties exists and then another call to insert the data?
Thanks Rocky.

swegele replied on Friday, February 02, 2007

Certainly would agree with Rocky's point about altering your BO to match the behavior you want...sounds like you need a wrapper object that has the user object as one of it's members.

Another ugly workaround would be to stick your sproc output variables into ApplicationContext and check it on the return side of the portal...yuck...but it might work and I take no pride in even thinking of it as a possibility ;-)

Sean 

smark replied on Saturday, February 03, 2007

Sean, please elaborate a bit if you can about the wrapper object part.

I think what is happening here is something that needs to be addressed as part of the validation mechanism of the object. My object is invalid if three of the properties I mentioned are already in the database. And there is no way of knowing this without making a call to the database: that this is properly done in the CheckValidationRules would be more appeasing to the OOP deity than making a call through the command object to get the return value of the insert sproc.

I still thinks this makes chatty network callls and I have no solution for this within the CSLA framework.

RockfordLhotka replied on Saturday, February 03, 2007

smark replied on Saturday, February 03, 2007

Rocky, this is an "Add User" use case.

On a side note, I am re-reading the Validation namespace in the 2005 C# book; I bought the new supplemental e-book yesterday and boom - it starts off with ValidationRules!

I think I am beginning to see this as part of the User object's state and what has really helped - just in the last couple of hours - is to see this as a validation issue: the User object is invalid if three of its properties are already in the database. I guess I will need to add a method in the CommonRules class and make a database call from that method to check duplicate values.

Thanks Rocky,

Shailesh.

RockfordLhotka replied on Saturday, February 03, 2007

smark replied on Saturday, February 03, 2007

Thanks,

I was going to put it in the CommonRules class because I will require this functionality in a few other use cases -- for most other root objects in fact, about 30. That way I could get some code reuse.

RockfordLhotka replied on Saturday, February 03, 2007