StrataFrame Forum

Mapping BO to Views

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

By Tim Dol - 12/19/2006

I created and deployed three views using the DDT.  Everything works fine except the views do not appear in the business object mapper.  I am using the DDT as the datasource.

Is this a bug or do I have to select SQL Server as the datasource when mapping to views.

Thanks

By Trent L. Taylor - 12/19/2006

Is this a bug or do I have to select SQL Server as the datasource when mapping to views.

It is not a bug and is actually behaving as it should.  When mapping to a view you have to use a SQL Server to pull the view.  The reason behind this is two-fold.  First, the DDT does not have a connection to a deployed server from which to pull the details.  Second, if we were to pull this from the view within the DDT, it would require us ultimately creating the full blown RegEx used by SQL Server itself to properly parse the view into the appropriate columns.  So when you are going to associate a BO with a view, just pull it straight from SQL Server.

By LeRoy Jackson, Jr. - 12/19/2006

So I guess my question would be How would you uses views if you were using Oracle?
By Trent L. Taylor - 12/19/2006

It should work the exact same way.  Just replace SQL Server with Oracle in my previous post.
By Tim Dol - 12/19/2006

Thanks, I just wanted to make sure I wasn't missing something because I'm having problems with views...This is the first time I'm trying to use them with StrataFrame.

I created the view and deployed to the server. I Checked 'Bind To Schema' because I want the view to be updateable.

I did not create an index... (I don't know if this is required)

When I go into SQL Server management studio and open the view, everything seems to work okay. I can update and the changes are reflected in the related table.

I created a Business Object  and Mapped it to the view. I did not override the primary key specification.  I set PrimaryKeyIsAutoIncrented to 'False'

When I run my program, the data appears fine, but when I edit and save I get an error.

'Cannot create UPDATE command because the business object does not contain any PrimaryKeyFields'.

I then tried to 'override the primary key definition' so my view had a primary key. This also created errors saving data... 'View or function 'dbo.LicenceModuleView' is not updatable because the modification affects multiple base tables'.

Please let me know what I'm doing wrong...

Much Appreciated...

Tim

By StrataFrame Team - 12/20/2006

I Googled around and found some information for you here: http://forums.microsoft.com/msdn/showpost.aspx?postid=72087&siteid=1  Your problem is that the view is attempting to update multiple tables, and the view can only update multiple tables if it is a partitioned view (in addition to being an indexed view).  I know that Chris Crabtree is using views for his business objects, but he is manually creating the INSERT and UPDATE stored procedures for the views so that he can allow them to modify multiple tables.  So, unless you can somehow make your view a partitioned view, you will need to create INSERT and UPDATE stored procedures for your business objects to use when updating your views.
By Tim Dol - 12/20/2006

Thanks Ben, I didn't want to waste your time with this. I thought it was something I was doing wrong in StrataFrame. 
By StrataFrame Team - 12/21/2006

Nope.  Pure updatable views are quite troublesome sometimes.  However, if you use stored procedures, then you can customize them any way you want.  You just have to write the stored procedures by hand Sad
By Chris Crabtree - 12/29/2006

Just wanted to jump in and say I am successfully using views, basically treating them as tables with custom stored procs for inserts, updates, and deletes. In many cases it is unnecessary, but when you really need to use a basetype/derivedtype database structure (and it seems to happen to me a lot for some reason), I am able to use StrataFrame and all its productivity-enhancing features to work against such a design.

Post to this thread if I can help elaborate in any way...