StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Saving Data via Stored Procedures

Saving Data via Stored Procedures

Clayton Hoyt — Wed, 18 Jan 2006 08:43:48 GMT

Hi All

Do you have any samples or documentation on using sprocs to do data manipulation through a business object?

I'm using a sproc to fill the datatable and I see options in the business objects for DeleteUsingStoredProceure / DeleteStoredProcedureName etc but I'm not sure how these work.

I've not had a chance to test this but I'm assuming that I'll need to leave dbWriter permissions open to the tables if I'm going to allow the BO to do the data manipulation. Is this correct?

Thanks much

Clay

RE: Saving Data via Stored Procedures

StrataFrame Team — Wed, 18 Jan 2006 08:43:48 GMT

[quote]I will need to have the parameters mirror the fields in the table both in order and type[/quote]

Just name and type... the order is irrelavent. On the parameter names, you can add any prefix to the field name you like. For instance, if your primary key field is UserID, then you can set your parameter name to @p_UserID or just @UserID. Just set the StoredProcedureParameterNamePrefix property to the correct value...

RE: Saving Data via Stored Procedures

Clayton Hoyt — Tue, 17 Jan 2006 17:10:16 GMT

Thanks much...

So for example, when I create an Insert or Update sproc, I will need to have the parameters mirror the fields in the table both in order and type. From there, will the BO take care of the rest?

RE: Saving Data via Stored Procedures

StrataFrame Team — Tue, 17 Jan 2006 16:24:37 GMT

Clay,

Yes, I'm actually writing the documentation on the C.R.U.D. setting right now... how ironic. Yes, whatever login you use to connect to the SQL Server will have to have write permissions on the database to be able to save the records.

I've attached the script file I use to create the stored procedures for our unit testing. Basically, you just have to follow these rules:

1). The UPDATE and DELETE stored procedures need to use the same concurrency type as the business object is set to.
a). If you're using row versioning or timestamp, you need to pass accept the row version or timestamp column and check the value.
2). Any output value (new row version value, assigned identity value, etc.) needs to be returned through an output parameter.
3). Use the field names for the parameters with a common prefix. The StoredProcedureParameterPrefix property allows you to set the prefix that you want to use (defaults to just '@').

Just take a look at these stored procedures and I'm sure you'll get the idea of what you'll need to do. (Until I get you the new CHM with examples :))