StrataFrame Forum

Best way to show fields from multiple tables on one grid

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

By lastcanary - 3/15/2010

I have two tables:



exchange(exc_code,exc_desc) (Exchange code and exchange description)



exchange_rate(exr_code, exr_valu, exr_date) (Exchange code, excange rate value, exchange rate date)





I want to display on one grid the following information: (exr_code is a foreign key to exc_code)



exr_code,exc_desc,exr_valu,exr_date





What is the best way to achive this? I tried to read the forms and manages to create a new custom field on a BO but still it cannot be displayed in the grid.



Could you please give me a simple example?



Best Regards
By Peter Jones - 3/15/2010

Hi,



The easiest way is have the grid use a view as its datasource then you can join as many tables as you like.



Cheers, Peter
By lastcanary - 3/15/2010

Thanks for the quick reply. Can we do it without using views?
By Trent L. Taylor - 3/15/2010

Well, you don't have to use a view, but if you plan to show fields from more than one table, you will either have to call a SPROC, view, or call a SELECT statement that populates the BO with all of the data. So anyway that you elect to do this if fine. So first, get your data into the BO.



Next, you will need to either create custom field properties (which there are a lot of samples on how to do this), or you can take another approach if there will be a lot. If you have the DDT (Database Deployment Toolkit) you can go in and create a structure that will be used ONLY for mapping a BO structure. You never have to deploy the DDT package, it is used solely to create all of the custom field properties for you. You can then populate the BO with the data that would match this schema and you don't ever have to manually code the custom fields. This is an approach that I used from time to time for this very purpose.
By lastcanary - 3/17/2010

I am trying to add a custom field to my custom BO which is created in custom BBS wizard. I checked the strataflix example and tried to use the same technique but it seems I am missing some point.



In strataflix example in "People Profile Report" there is PeopleBO, in this BO to add custom fields:



In region " Private Fields " the following line is added:

private static MovieCastBBS _MovieCast = new MovieCastBBS();



In region " Protected Methods " the following line is added:

r.Add(new ReflectionPropertyDescriptor("MovieCast", typeof(PeopleBO)));



In region " Public Properties " the following lines are added:

public MovieCastBBS MovieCast

{

   get

   {

   //-- Make sure that records exists prior to filter

      if (this.Count > 0)

      {

         //-- Filter out the movie cast records for the current person

         _MovieCast.SourceBO.Filter = "mc_pl_pk = " + this.pl_pk.ToString();

      }



                  

   return _MovieCast;

}

}



I added the similar lines to my example but I am receiving the following error:



Error   2   Inconsistent accessibility: property type 'deneme.test.dovizBBS' is less accessible than property 'deneme.test.kurlar.deneme'   E:\DENEME\DENEME\Multi Table Grid\deneme\kurlarBO.cs   68   25   deneme



Could you please help?
By lastcanary - 3/24/2010

I used your style to display fields from another BO in the same grid. Here is the style as far as I understood:



-Create a custom BBS and custom BO for each table that will be displayed

-Set the BBS class as public

-Add the custom field in the custom BO code

-Fill SourceBO and connected SourceBO



deneme.reporting.KurlarBBS MyCustomBBS = new deneme.reporting.KurlarBBS();

MyCustomBBS.SourceBO.FillDataTable("SELECT * FROM kurlar");

MyCustomBBS.SourceBO.Doviz.SourceBO.FillDataTable("SELECT * FROM doviz");



It is working perfectly with our test database but when I tried to use it in our application which has 370000 rows in the table that contains the PK and 1700000 rows in the table that contains the FK then I received the error SystemOutOfMemory.



We are going to use this system in our reporting tool so there will be many tables joining and it seems that it is not efficient to pump all the data to BO.



How did you solve this problem in your applications?
By Edhy Rijo - 3/25/2010

lastcanary (03/25/2010)


MyCustomBBS.SourceBO.FillDataTable("SELECT * FROM kurlar");

MyCustomBBS.SourceBO.Doviz.SourceBO.FillDataTable("SELECT * FROM doviz");



It is working perfectly with our test database but when I tried to use it in our application which has 370000 rows in the table that contains the PK and 1700000 rows in the table that contains the FK then I received the error SystemOutOfMemory.




How realistic would it be for you to use a "SELECT *" in a table with millions of records?



One of my projects have the need to import millions of records from CSV files and at some point I did reach a couple of SystemOutOfMemory exceptions you can look it up in the forums and had to changed my design not to fill a data table with that many records if is not really needed. I believe that is a limitation of .NET data tables, even though there are some settings you can make to the project to make the .EXE handle more available memory and it worked a bit in my case but I had to abandon my original design to show the data in a datagridview.



Of course you may have a legitimate reason to show all the data to the users, but if that can be avoided it will be best for you and the reliability of your application.
By lastcanary - 3/25/2010

Should we write the complete SQL sentence to retreive only the required rows?



How do you solve this problem when you write your queries for reports? We are planning to support many databases and if we write the complete SQL query we should write it depending on the database.
By Edhy Rijo - 3/25/2010

lastcanary (03/25/2010)
Should we write the complete SQL sentence to retreive only the required rows?


Yes, report data should be limited by any parameter. You can do a simple test with a plain .Net project not SF and see if you will get the same out of memory error with a data table and your "SELECT *" statement. Of course the BO & BSS classes should add some overhead on the memory since they do more stuff than the plain data table, but I believe the most rececent SF release address several memory leaks in this regard.



How do you solve this problem when you write your queries for reports?


Using a parameter form dialog to capture End User parameter and pass that to the report engine. I have been using the SF BrowseDialog in the same fashion as the StrataFlix project but I have also asked SF team to create a dialog for reporting purpose generic enough like the BrowseDialog.



We are planning to support many databases and if we write the complete SQL query we should write it depending on the database.


I agree it could be tricky, but I think that if SF produce a BrowseDialog for reporting purpose this issue may be covered. You can see the discussion here