StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » How Do I Handle Foreign Keys Through List Controls?

How Do I Handle Foreign Keys Through List Controls?

John Kujur — Tue, 24 Oct 2006 07:53:59 GMT

This has got to be really simple, as I have been doing this throughout my coding years, but I can't make it happen using SF.

I have an Organization BO, a Group BO, and a GroupOrganization BO. An Organization can belong to many groups and a Group can have many organizations. I want to use the GroupOrganization BO to handle this. (The GroupOrganization table just has two fields -- GroupId, and OrganizationId which are foreign keys referencing the primary keys in the respective tables).

On the GroupOrganization BO, I just want to have a list control displaying all available Organizations, and one more list control displaying all available Groups. I want to be able to choose a Group, then choose (multiple) Organizations and click 'Save', resulting in proper records which look like (GroupId, OrganizationId) where both of these are composite primary keys.

I need some guidance how to go about achieving this through SF.

Thanks, and let me know if I can be more descriptive.

RE: How Do I Handle Foreign Keys Through List Controls?

Trent L. Taylor — Tue, 24 Oct 2006 07:53:59 GMT

[quote]I was just curious about whether other SF users should also have these new DLLs? Is this going into an update/release?[/quote]

Only a few. These DLLs contain the changes that will be released on Friday. But it is stable as we are using this ourselves and anyone that is having specific problems we are letting have these DLLs.

RE: How Do I Handle Foreign Keys Through List Controls?

John Kujur — Tue, 24 Oct 2006 00:53:33 GMT

Thanks Trent, Ben And Team.

Yes My PKs are TinyInts as the number of records will never cross two digits here; and I have them auto-incrementing as it is a simple solution. It is just a little application I'm doing to test out SF. So yes, since these are converted into Bytes it all makes sense now.

I haven't gone through the example yet, I'll be doing that now and will let you know.

I was just curious about whether other SF users should also have these new DLLs? Is this going into an update/release?

Thanks Again.

RE: How Do I Handle Foreign Keys Through List Controls?

Trent L. Taylor — Mon, 23 Oct 2006 10:06:10 GMT

John,

Attached are two things....some updated StrataFrame DLLs and a Link Manager sample. I ran into the problem you were encountering and I will explain it is just a minute. First let me explain how to update your DLLs:

Updating SF DLLs:

Extract the SF DLLs to a temp folder
Drag the DLLs into the c:\windows\assembly folder (GAC)
Copy those same files to C:\Program Files\Common Files\MicroFour\StrataFrame
Restart all Visual Studio Instances

This will update your SF DLLs.

The Problem

Your entire problem surrounds the TinyInt as your primary key. Your PKs were also autoincrementing which caused some additional side-effects. A TinyInt is converted to a Byte in .NET (you can find this in the SQL Server Books Online). The problem with this is that when a PK is auto-incrementing the framework will us negative numbers until the record is committed...obviously this won't work with a Byte since you cannot go below zero. There was an additional problem that we found while testing your scenario that is the reason we gave you the new DLLs. It has to do with a new record being added through a grid when there are already records populated within the Link Table in the BusinessLayerLinkManager while using TinyInt PKs. This issue has been resolved with these DLLs.

The Solution

You will notice that the business objects within the sample attached here have an alternate value returned on NULL. Since the framework cannot create the negative PK place holders for a TinyInt and DbNull value is returned which will cause a number of errors. To get around this, we set a Return Alternate Value on Null setting on each of the PKs to zero. See the customized field property value through the BO Mapper below:

Note: This needs to be done on each of the TinyInt PKs.

Since your PKs aure auto-incrementing, they will properly return the new PK once saved, but this just prevents the DbNull error from happening (since the PK could not be set to a negative value).

Past that I think the sample is pretty self-explanitory. Let me know if you have any other questions regarding this.

RE: How Do I Handle Foreign Keys Through List Controls?

Trent L. Taylor — Mon, 23 Oct 2006 08:39:52 GMT

I will check this out....just curious, I noticed that your primary keys are TinyInt's...so I assume that you will never have more than 250 records in this table or recreate a record 250 times?

RE: How Do I Handle Foreign Keys Through List Controls?

John Kujur — Mon, 23 Oct 2006 00:23:30 GMT

Thanks, I'm attaching the package file.

RE: How Do I Handle Foreign Keys Through List Controls?

Trent L. Taylor — Sat, 21 Oct 2006 09:50:55 GMT

John,

Send us the structure of these 3 tables and I will create you a sample for this exact scenario. The easiest way is to use the DDT (Database Deployment Toolkit) to create the structures cause all you will need to send us is the package file that gets created. But any other method will work also. You can even give us an MDF and we will just mount the database if that is the easiest.

I thought this would be easier than going back and forth and it will not take long to create you this sample.

RE: How Do I Handle Foreign Keys Through List Controls?

John Kujur — Sat, 21 Oct 2006 06:03:13 GMT

Can someone point me in the right direction please? Maybe some kind of sample or documentation?

Thanks.

RE: How Do I Handle Foreign Keys Through List Controls?

John Kujur — Tue, 17 Oct 2006 08:47:57 GMT

Thanks for the tips.

Here's what I did:

1. I made the GroupOrganizationBO the PrimaryBusinessObject on an SF Maintenance Form.
2. I added a GroupBO and an OrganizationBO to the form.
3. I called a "FillAll" method for all three BOs in their respective ParentFormLoading events.
4. I dropped a combobox and set its BusinessObject to GroupOrganizationBO and its bindingField to GroupId. I set this to populate through GroupBO1 on formload.
5. I dropped another combobox and set its BusinessObject to GroupOrganizationBO and its bindingField to OrganizationId. I set this to populate through OrganizationBO1 on formload.
6. I dropped a BusinessLayerLinkManager and set its Table1 to GroupBO1, Table2 to OrganizationBO1, TableLink to GroupOrganizationBO1, as well as proper values for ForeignKeyToTable1, ForeignKeyToTable2, PrimaryKeyOnTable1, and PrimaryKeyOnTable2.

I get the following exception in Visual Studio when I run this:
-------------------------------------------------------------------------------
MicroFour.StrataFrame.Business.BusinessLayerException was unhandled by user code
Message="The CurrentRow could not be evaluated because the CurrentRowIndex is out of range. Business object record count: 0. CurrentRowIndex: -1."
Source="MicroFour StrataFrame Business"
StackTrace:
       at MicroFour.StrataFrame.Business.BusinessLayer.get_CurrentRow()
       at SalaryCSSF2.GroupOrganizationBO.get_GroupId() in D:\SalaryCSSF2\SalaryCSSF2\BusinessObjects\GroupOrganizationBO.Designer.vb:line 239
       at SalaryCSSF2.GroupOrganizationBO.Field_GroupId_Descriptor.GetValue(Object component) in D:\SalaryCSSF2\SalaryCSSF2\BusinessObjects\GroupOrganizationBO.Designer.vb:line 598
       at MicroFour.StrataFrame.Business.BusinessLayerLinkManager.HandleTableLinkNavigated(NavigatedEventArgs e)
------------------------------------------------------------------

When I go along with the exceptions, I can see the form. On clicking the "New" button, I get an error (pls see attached image).

Could you please let me know whether the steps I am following are okay? I have three or four records in both Group and Organization tables, so I don't really know why I'm getting the CurrentRowIndex = -1 message.

Are there two separate issues here?

Thanks.

RE: How Do I Handle Foreign Keys Through List Controls?

StrataFrame Team — Mon, 16 Oct 2006 08:25:40 GMT

No more description needed :)

This is accomplished using the BusinessLayerLinkManager. Essentially, you drop a BusinessLayerLinkManager on the form and set the Table1, Table2, and TableLink properties. Then on the BLLM, you set the primary key and foreign key fields on the appropriate properties and it will manage the link for you. When you add a new record to the link table, it will retrieve the primary key values from the two outside business objects and maintain the relationships between them.

As for a control, we have a CheckedTreeView control that you can use or you can use a CheckedListBox control. Either way, in the CheckedChanged event (NodeStateChanged in the CheckedTreeView), you will need to just add or delete the record from the link table. Call Save() when you're done and that's it.