StrataFrame Forum » StrataFrame Application Framework - V1 » WebForms (How do I?) » Customise Stored Procedure Updates

Customise Stored Procedure Updates

Paul Gwynne — Tue, 15 Aug 2006 09:34:26 GMT

Hi,

I have just downloaded the trail version and like the idea of being able to use stored procedures to handle the updates as this is our company policy. However looking at the documentation it seems that these stored procedures are being generated by the framework. We have have special requirements for the parameters passed E.G. Orginal values,update field flags and session information for logging who made the change.

In essence the parameters look something like this

create procedure dbo.smEXPSMASTER_ACD
     @I_ACTION  char(1) ='',
     @I_IDKEY  IDKey =0,
     @I_TSTAMP  timestamp =null,
     -- Input Data Fields
     @I_USERCODE   UserCode  = '',
     @I_NAME    varchar(35)  = '',
     @I_NOMCODE   UserCode  = '',
     -- Update Flags
     @I_UPD_USERCODE   bit  = 0,
     @I_UPD_NAME   bit  = 0,
     @I_UPD_NOMCODE   bit  = 0,
     -- Original Values
     @I_ORG_USERCODE   UserCode  = '',
     @I_ORG_NAME    varchar(35)  = '',
     @I_ORG_NOMCODE   UserCode  = '',
     -- Progress Parameters
     @I_PROG_SPID  int  =0,
     -- Session Parameters
     @I_SESS_USERID  IDKey  =0,
     @I_SESS_USERNAME UserCode ='',
     -- Audit Parameters
     @I_NOAUDIT_LOG  bit  =0,
     @I_AUDIT_MESG  varchar(255) ='',
     @I_AUDIT_SOURCE  varchar(1) ='L',
     -- Output Parameters
     @O_IDKEY  int  =0 output,
     @O_ERROR_TABLE  varchar(30) ='' output,
     @O_ERROR_USERCODE UserCode ='' output,
     @O_RETURN  int  =0 output,
     @O_MESG   varchar(100) = '' output

The question is how far can you go in customizing the CRUD stored procedures to allow me to call something like the above.

Thanks

Paul

RE: Customise Stored Procedure Updates

StrataFrame Team — Tue, 15 Aug 2006 09:34:26 GMT

Yes, the ability to allow additional parameters to be passed to the executing stored procedures is on our long term list of feature improvements. By long term, I mean most likely it will be included with the .NET 3.0 release when we can support DLinq within StrataFrame.

The easiest way to provide the functionality right now would be to create your own DbDataSourceItem implementation and when the UpdateRow method is called, raise an event that allows you to add the additional parameters to the command that is being executed. You could even use the SqlDataSourceItem as a base class since you only need to override the UpdateRow method.

RE: Customise Stored Procedure Updates

Paul Gwynne — Tue, 15 Aug 2006 09:25:11 GMT

Thanks for the reply. This might be an issue as we are doing a bit more than just pure auditing.Not having access to the source code, what about a suggested change for the next release where you provide the ability to decorate the SQLCommand object with additional parameters via a call to a custom decorating class which is written outside of the framework.

By this I mean
1. Presistance of object is called.
2. If saving via stored proc then do all of the work to prepare the proc with all the parameters you expect.
3. Check if a custom interface/provider/decorator has been injected into the framework and call it to modify the SQLCommand object with additional parameters etc..
4. Fire the SQLCommand object to save the data.

This then gives greater flexibility to us without compromising the framework.

Also if I buy the product.I see it says it comes with source code. To what extent could I modify the framework to allow such customisations. I can see it would not be a great idea given the amount of work you guys must have put in, but maybe that is another angle to look at.

Thanks

RE: Customise Stored Procedure Updates

StrataFrame Team — Tue, 15 Aug 2006 08:30:04 GMT

You can customize the CRUD stored procedures to the extent of being able to choose the parameter prefix for the names, however, when a business object saves a record, it is expecting to be able to determine the parameters necessary for the UPDATE/INSERT from the fields within the business object. We have no method you to pass extra parameters to a stored procedure.

We do have some functionality to support much of the information you're trying to save off through our security module, which supports built-in auditing for INSERTs/UPDATEs/DELETEs and application level events. It stores of the information so you can see what user did what within the application. Like I said, though, we don't have the functionality for you to pass the extra parameters into the stored procedures.

[quote]Apologies for putting this in the wrong forum section.[/quote]

Awe, no problem. I almost never check what section people post a question to; I just get an email letting me know that a new post exists :)

RE: Customise Stored Procedure Updates

Paul Gwynne — Tue, 15 Aug 2006 05:39:08 GMT

Apologies for putting this in the wrong forum section.