StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » BOM and SQL2005 Computed Columns

RE: BOM and SQL2005 Computed Columns

Trent L. Taylor — Wed, 10 Oct 2007 13:24:24 GMT

Just double-click BO in the Solution Explorer to bring up the BO in the component designer, then go to the property sheet and you will find these properties.

These are just properties on the BO, so you could set this in code also if you had the need.

BOM and SQL2005 Computed Columns

Sarosh — Wed, 10 Oct 2007 13:24:24 GMT

Hi!

I am having a problem while saving a BO that I created using the SF BOM (Business Object Mapper) which is based on a SQL2005 table that has a computed column in it.

I made that BO column/property ReadOnly in the BOM but if I make changes to any other column and then try to save that BO I get an error that I cannot update a computed column. This is not a calculated/computed column defined at the BO level it is defined as a computed column at the SQL table level. Which brings up two questions:

1] How do I handle SQL2005 computed columns defined at the table level in a BO.

2] How do I selectively pick columns from a table for a BO.

e.g. I have a few text columns with lots of data in them and so I don't want them to be a part of the BO.

It seems that right now the BOM only allows me to pick a table and not specific columns from within that table.

Thanks

Sarosh

RE: BOM and SQL2005 Computed Columns

Ross L. Rooker, Sr. — Wed, 10 Oct 2007 11:27:53 GMT

exactly how is this specied in the BO:

FieldsToExcludeFromUpdate
FieldsToExcludeFromInsert

I have computed columns and have looked everywhere for these properties and cannot find them.

RE: BOM and SQL2005 Computed Columns

Sarosh — Wed, 13 Jun 2007 10:27:33 GMT

Hi!

Ben:

Will it be possible to add "ExcludeFromInsert" and "ExcludeFromUpdate" after the ReadOnly column in the BOM which in turn would fill the FieldsToExcludeFromUpdate and FieldsToExcludeFromInsert internally?

What about adding "IncludeInSelect" as well to handle the Selectively picking columns?

Thanks

Sarosh

RE: BOM and SQL2005 Computed Columns

StrataFrame Team — Wed, 13 Jun 2007 08:38:33 GMT

Greg is correct on the FieldsToExclude* properties.

As for your questions about the null value options, those are set on a per-field level within the BOMapper. You select the field you want to customize, and within the customization option, you use the null value options in the upper right of the customization dialog to set the options. Most data returned from the database is placed into .NET value types, which cannot have null values, so the BOMapper is forced to do some translations between those DBNull.Value values that get returned and the actual value that can be cast as a System.Int32 for example. Also, if you ever need to set a null value explicitly, and you don't have the null value options set on your BO field, you can do so using BO.CurrentRow("FieldName") = DBNull.Value.

If you want to set the null value options on more than one field at a time, you can use the customization wizard within the BOMapper.

RE: BOM and SQL2005 Computed Columns

Sarosh — Wed, 13 Jun 2007 07:51:59 GMT

Hi!

Yes this help a lot, thanks!

Based on what you have mentioned I will be able to take care of both my issues but I still think that letting you selectively pick columns in the BOM will be cleaner or maybe adding two extra columns in the BOM when we select a BO.

By adding "ExcludeFromInsert" and "ExcludeFromUpdate" (after the ReadOnly column in the BOM) which we could set as "yes" or "no" will handle my issue 1] of Excluding fields in a cleaner way (which in turn could fill the FieldsToExcludeFromUpdate and FieldsToExcludeFromInsert automatically for us)

and maybe even adding a third column "IncludeInSelect" or something like that which will handle my issue 2] of Selectively picking columns.

One more question how do you handle NULLS in the BOM? We have many columns in our tables that allow nulls, shouldn't the BOM just have a "Allow NULLS" as a NullType and set the appropriate value internally?

Sarosh

RE: BOM and SQL2005 Computed Columns

Greg McGuffey — Tue, 12 Jun 2007 23:10:41 GMT

Sarosh,

Based on what I've learned/done, I can offer the following insights:

1. Excluding fields
There are two properties that you can set for a BO (at the BO level, not when dropped on a form):

FieldsToExcludeFromUpdate
FieldsToExcludeFromInsert

I believe you enter the names of the fields, one per line, to exclude from inserts/updates. You'd enter your calculated columns here. I'm pretty sure this works if you are using sprocs as well.

2. Selectively picking columns
You can't selectively pick columns in the mapper. It will map all of the fields and create strongly typed properties for them. However, there is no rule that you have to actually [i]fill[/i] all of those columns. That is completely under your control when you fill the BO with data. I.e. lets say you have a table with three columns: id, name, comment. Comment is a varchar(max) field and can be quite huge. You don't have to fill it. I.e. you could have a fill method something like:

[codesnippet]Public Sub FillIdNameOnly()
Using cmd As New SqlCommand()
cmd.CommandText = "Select id, name From yourTable"
Me.FillDataTable(cmd)
End Using
End Sub[/codesnippet]

After calling this command, the ID and name property will work as expected, but the comment property will likely throw an exception, as there will be no column in the datatable. But that's OK, since you'd only call this if you didn't want the comment anyway.

Hope this helps!