StrataFrame Forum / StrataFrame Application Framework / Business Objects and Data Access (How do I?) / Map BO to MS Access view / Latest Posts

RE: Map BO to MS Access view

Ben Chase — Tue, 18 Mar 2008 08:50:07 GMT

[quote]My question was if I could fill a datatable with more columns than properties mapped in the BO mapper.[/quote]

Yes, you can. Each business object wraps a DataTable, and that DataTable can have all sorts of extra columns containing data. By the same token, you can also fill a business object with fewer fields than are mapped to the business object... just don't try to retrieve the data through one of the properties or you'll get an exception :)

Once you have the extra columns in the table, there are a few ways to retrieve the values. You can use the indexer property of the business object:

myBo["columnName"]

or you can use the current row:

myBo.CurrentRow["columnName"]

or you can create a property on the business object that wraps the current row's indexer. Simply copy one of the properties created by the BOMapper and change the names to protect the innocent.

RE: Map BO to MS Access view

Alex Bibiano González — Tue, 18 Mar 2008 04:27:49 GMT

Thanks,

I already knew how to code the fill method. My question was if I could fill a datatable with more columns than properties mapped in the BO mapper.

I have already solved my problem changing my DB backend to SQL Server :D. This project is for me my first test project with Strataframe (I have developed before with Powerbuilder, C# + NHibernate or Datasets) and I'm testing the same project with Oracle, SqlServer an Access.

Thanks a lot for our answers. I think this help forum is great and our support fast and good.

Alex B.

RE: Map BO to MS Access view

Trent L. Taylor — Mon, 17 Mar 2008 13:28:30 GMT

Yup. Edhy is right on the money. You will just create a method within your BO that executes that query for you.

RE: Map BO to MS Access view

Edhy Rijo — Mon, 17 Mar 2008 12:07:47 GMT

[quote][b]Alex Bibiano González (03/17/2008)[/b][hr]Thanks for your replays. I will try your suggestion but I need a little more help.

I have read the link, but don't understand where to put the query in my BO:

[codesnippet]SELECT Staff.*, SchoolTitles.TitleName FROM Staff INNER JOIN SchoolTitles ON Staff.ForeignKey = SchoolTitles.PrimaryKey[/codesnippet]

Can I retrieve in my fill method more columns than the columns I mapped in the BO mapper?

Can I create a BO with all fields as custom properties? (because my BO don't maps directly to a table, and I can't use the BO mapper tool)

Thanks and excuse for my ignorance

Alex B.[/quote]

Hi Alex,

No problem, so here we go.....

In the Solution Explorer, right click your BO file and select View Code
Open the Region "Data Retrieval Methods" and create a method here which you will call from anywhere in your form, so let's named it something like GetStaffView and the code should be something like this in VB:

[codesnippet]

Public Sub GetStaffView()

Me.FillDataTable("SELECT Staff.*, SchoolTitles.TitleName FROM Staff INNER JOIN SchoolTitles ON Staff.ForeignKey = SchoolTitles.PrimaryKey")

End Sub

[/codesnippet]

You will then call the Me.GetStaffView method from your form's New or Load event or from any other place you want the BO to be populated.

I believe you will also need to create a Custom Field Property to manage the SchoolTitles.TitleName, but I am not sure, so if that is the case let me know to give you some code to do that, or look at the help for the Custom Field Property topic, so you can add it in the same BO file.

RE: Map BO to MS Access view

Alex Bibiano González — Mon, 17 Mar 2008 11:46:44 GMT

Thanks for your replays. I will try your suggestion but I need a little more help.

I have read the link, but don't understand where to put the query in my BO:

[codesnippet]SELECT Staff.*, SchoolTitles.TitleName FROM Staff INNER JOIN SchoolTitles ON Staff.ForeignKey = SchoolTitles.PrimaryKey[/codesnippet]

Can I retrieve in my fill method more columns than the columns I mapped in the BO mapper?

Can I create a BO with all fields as custom properties? (because my BO don't maps directly to a table, and I can't use the BO mapper tool)

Thanks and excuse for my ignorance

Alex B.

RE: Map BO to MS Access view

Trent L. Taylor — Mon, 17 Mar 2008 10:47:23 GMT

Alex,

We actually use OLEDB to talk to Access and call the GetSchema to return the views...apparently the Access OLEDB provider doesn't return views as a table. I can add this to a list to look at before the next update, but Edhy is correct in that you could create a query that does this form you.

Access has full ANSI 92 SQL support which means that you can use JOINs, etc, which is all that a view is doing for you anyway...especially as it relates to read-only data. Here is one post that I have recently talked about how to create an INNER JOIN: http://forum.strataframe.net/FindPost14603.aspx . (It is the same thread that Edhy mentioned).

RE: Map BO to MS Access view

Edhy Rijo — Mon, 17 Mar 2008 10:14:31 GMT

Hi Alex,

Take a look at the following post, it may give you an idea of how to JOIN another table in the BO and then create some Custom Field Properties in your BO.

http://forum.strataframe.net/Topic14580-6-1.aspx?Highlight=join

Of course this is in case you will not be able to use your Access view directly in your BO which would be the easiest way for you.

RE: Map BO to MS Access view

Alex Bibiano González — Mon, 17 Mar 2008 09:57:01 GMT

Thanks for replay but I'm not sure how to do this.

I want to populate the BO only for read purpouse, and the view I have in access is a union from 2 tables with some calculated fields (it's not only a talbe with a where filter).

I think it would be more simple to map the view directly to a BO and use this BO to populate my controls (it's a tree list).

Thanks,

Alex B.

RE: Map BO to MS Access view

Edhy Rijo — Mon, 17 Mar 2008 09:06:57 GMT

Hi Alex,

I don't know if you can use MS Access views, but maybe you don't really need the view since you can create as many BO with all the filtering and parameters you want to.

Remember that the BO is basically a container for your data and you have to create the SQL statement to bring the data to work with.

Map BO to MS Access view

Alex Bibiano González — Mon, 17 Mar 2008 09:04:03 GMT

Can I mapp a BO to a MS Access view?

I have the BO, the view in the mdb, but if I call the BO Mapper tool, only tables are displayed.

Is it possible?

Thanks,

Alex B.