StrataFrame Forum

Saving Data via Stored Procedures

http://forum.strataframe.net/Topic437.aspx

By Clayton Hoyt - 1/17/2006

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

By StrataFrame Team - 1/17/2006

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 Smile)
By Clayton Hoyt - 1/17/2006

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?

By StrataFrame Team - 1/18/2006

I will need to have the parameters mirror the fields in the table both in order and type




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...