StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Updating project to use Store Procedures for CRUD

Updating project to use Store Procedures for CRUD

Edhy Rijo — Sat, 20 Dec 2008 08:23:31 GMT

I have a project which is I am reviewing for improvements for data speed process and would like to use the DDT to take advantage of using store procedures for Insert/Update/Delete.

I noticed that this is handle in the the DDT Table Properties, so in order for me to upgrade this project, would it be just a matter to turn on those features and choose my Version Field which I have been using? or besides doing that in the DDT I should do some other modifications to my projects?

RE: Updating project to use Store Procedures for CRUD

Trent L. Taylor — Sat, 20 Dec 2008 08:23:31 GMT

Cool :cool: , glad you got it going.

RE: Updating project to use Store Procedures for CRUD

Edhy Rijo — Fri, 19 Dec 2008 23:16:04 GMT

Thanks Trent,

I made the changes to my base BO and like you said, it was straight forward using the default SP names.

RE: Updating project to use Store Procedures for CRUD

Trent L. Taylor — Fri, 19 Dec 2008 21:41:00 GMT

[quote]One question, when using the DDT like I am now, and setting the BOM to create the BO from the DDT metadata would these properties be filled automatically?[/quote]

No, but you don't really need it to. If you are using the standard names that the DDT produces (TableName_Delete, for example) then you do not need to set the SPROC name, just set the property that indicates that the CRUD setting will be true and the default naming schema will be used. So in this case, just set the following properties to True:

DeleteUsingStoredProcedure
InsertUsingStoredProcedure
UpdateUsingStoredProcedure

Then if you are using row version concurrency, set the concurrency properties:

UpdateConcurrencyType = RowVersion
RowVersionOrTimeSTampColumn = "YourFieldName"

Automatically forcing the BO to set the stored procedure flags isn't really something that should be done as there are times you want to turn this off. But as for the names, you don't need to override them, just use the default naming convention and it will already be handled for you.

RE: Updating project to use Store Procedures for CRUD

Edhy Rijo — Fri, 19 Dec 2008 19:18:53 GMT

Well, I did a quick test and found out that the BO is not reflecting the CRUD options entered in the DDT for that table. This could be an Enhancement Request :cool:

Then since I am using a base BO class, I tried to overwrite the CRUD properties so I can have this information added automatically to all my BOs, and guess what? yes, these properties are not marked as Overridable.

Here is the code I tried in my base BO:

[codesnippet][code]

'-- Setting CRUD to use Store Procedures

Private _DeleteUsingStoredProcedure As Boolean = True

Private _DeleteStoredProcedureName As String = Me.TableName.Trim & "_Delete"

<Category(EDITOR_CATEGORY_CRUD), _

DefaultValue(False)> _

Public Overrides Property DeleteStoredProcedureName() As Boolean

Get

Return _DeleteStoredProcedureName

End Get

Set(ByVal value As Boolean)

_DeleteStoredProcedureName = value

End Set

End Property

<Category(EDITOR_CATEGORY_CRUD), _

DefaultValue(False)> _

Public Overrides Property DeleteUsingStoredProcedure() As Boolean

Get

Return _DeleteUsingStoredProcedure

End Get

Set(ByVal value As Boolean)

_DeleteUsingStoredProcedure = value

End Set

End Property

[/code][/codesnippet]

Is there any other way this can be automated?

RE: Updating project to use Store Procedures for CRUD

Edhy Rijo — Fri, 19 Dec 2008 18:29:34 GMT

[quote][b]Greg McGuffey (12/19/2008)[/b][hr]No matter how the sprocs get created, you will need to setup the BOs to use them. In the CRUD section of the BO properties, there are places to configure the use of sprocs for inserts, updates and deletes. I believe once you have both the sprocs deployed and the BO properties setup, you good to go.[/quote]

Thanks Greg.

One question, when using the DDT like I am now, and setting the BOM to create the BO from the DDT metadata would these properties be filled automatically?

I know I can test this, but I rather ask before start finding out with this particular project. :hehe:

RE: Updating project to use Store Procedures for CRUD

Greg McGuffey — Fri, 19 Dec 2008 18:19:00 GMT

The DDT will create the CRUD sprocs (as you setup them up in the table properties). This will create the sprocs and help you get them deployed. Of course you could do all of this manually too, which would be way more fun! :P

No matter how the sprocs get created, you will need to setup the BOs to use them. In the CRUD section of the BO properties, there are places to configure the use of sprocs for inserts, updates and deletes. I believe once you have both the sprocs deployed and the BO properties setup, you good to go.