By Ger Cannoll - 2/9/2012
I am having a problem displaying fields in a Devex Grid. The only fields that will diplay, are the Original Fields from my Main Table BO
I want to select out different fields from a few joined Tables and display them in Grid. For this purpose , I have set up a Dummy BO, with just two fields, and I use this as a sort of Repository to hold the fields I select out. So say my Dummy Table (And BuisnessObject) has just 2 fields D01_pk and D01_Val1.
The only purpose of the Dummy Table is to be able to join the fields and end up with one Table, but the only fields I can get to display are the fields from the original Source BO (In my case just 2 fields). All other fields just show blanks, even though the Business Object populated with the correct count of rows.
I have the following code in the Load of the form (I cannot assign the BBS to a business object on the form parameters as I have not yet created the BusinessObject, this is why I am setting the BusinessObject to the BBS in the Form Load)
Kernel_BOLibrary.XBO myBO = new XBO(); // My Dummy Business Object whcih I use to JOin other Tbales into
SqlCommand comm = new SqlCommand();
comm.CommandText = "Select 1 as D01_PK,'XXX' as D01_Val1, MyField1,MyField2,MyField3 from TABL1 join TABL2 on X=Y ";
myBO.Clear();
myBO.FillDataTable(comm);
bbsPost.BusinessObject = myBO;
gridPost.DataSource = bbsPost;
So in the Grid, DO1_Pk and D01_Val1 display fine, but I cannot get the other fields, which I have joined (MyField1,MyField2,Myfield3) to display
|
By Edhy Rijo - 2/9/2012
Hi Ger,
This would be easier to understand if you can provide a quick sample to work with, but I will try to understand the problem...
Currently at design time, you mapped a BBS to the grid with a BO with fields D01_PK and D01_VAL1 correct?
Then when loading the form, you will load your BO with original 2 fields above plus more columns MyField1, MyField2, etc. right?
If my assertion is correct, does your Kernel_BOLibrary.XBO has the extra field properties for MyField1, MyField2, etc. ?
if it does not, then that may be the problem, the DevExpress grid is binded to a BBS and this to a BO which does not have the columns as properties of the BO to show up in the grid.
Please let me know if I understand the issue before continue?
|
By Ger Cannoll - 2/9/2012
Hi Edhy. Thanks for replying. Yes, you have the requirement spot on.
I just dropped a BBS onto the form, and did not map it to a BO, as I am creating the BO on the Load, to take in the additional fields. I am instantiating just a General Purpose Business Object, with 2 fields, so that I can use the BO.FillDataTable to 'Create' an entity which has fields from many tables. This is something I will need to do an awful lot, i.e. create an entity whcih has fields, other than are in any of the Business Objects (i.e. are the result of a Join from Many Tables)
I dont know any other way to do this other than , picking any Business Object (In my case I use a 'Dummy' Business Object with just two fields  and then using the MyBO.FillDataTable("Select and join a few Tables") to pouplulate the BO with 'ALL' the fields I require. I am happy that the BO is being correctly populated with all the fields ok, but then I need to display them in the Grid.
If this will not work, maybe there's a better/alternative way of doing it
|
By Edhy Rijo - 2/9/2012
You are welcome Ger.
First, let me explain how the BO works. Basically the BO has a DataTable (DT), then when you use the Business Object Mapper (BOM) it will create custom field properties for each field in your table.
When you use any Fill method in your BO the internal data table will have all the fields included in the SELECT statement, but the BBS will only present the BO fields, so in your case, it will not show the fields in the DT.
Since you will be using grid with generic fields (columns), then instead of using a BBS as the source of the grid, try using a data table and then assign the BO.CurrentDataTable as the source.
I don't have any sample since all my DevExpress grid use the BBS with the correct fields, but it should work.
|
By Ger Cannoll - 2/9/2012
Hi Edhy.
I changed datasource to a DataTable and it now displays fine... not sure though if I run up up and down the Grid , will I keep in sync with the BusinessObject ??
I am assuming that if I change anything in the grid though, that the underlying BO will also change(Yet to test)
Also, for any lurkers, initially I was not able to get some of the fields to display
In My Select statement, I had something like: Select Field1,Field2,Filed3 from MyTable
In the DevExpress Columns, I had FIELD1, FIELD2, FIELD3...and nothing displayed, but when I changed the Case, everything worked fine, so even though in the Select, Case does not matter, in Populating the Grid Fields, the Field names MUST match with whats in the Select Statement..... no error is given , just the columns dont display
|
By Trent L. Taylor - 2/10/2012
If you wrap your BO in a business binding source, then attach the grid to the business binding source, the records will be kept in sync when someone changes the row in the grid. You can do a search out here on the forum for more information about the BusinessBindingSource, but this will be the ticket when dealing with a grid.
|
By Edhy Rijo - 2/10/2012
Hi Gen,
So as you said, if by changing the casing of the field names in the grid, it will show the columns using a BBS, then use the BBS so it will give you the CurrentRowIndex sync with your BO, otherwise, if you have to go with the data table solution, you may need to do your own synchronization when moving up/down the grid.
Also, if your BO does not have the custom field properties for each table column, then you would have to access those using the BO's Items collection.
|
By Ger Cannoll - 2/10/2012
Hi Trent. Thanks for replying.
My initial route was to go with the BBS, but I found that Selecting out and joining other tables and ending up with Columns in the Instantiated BO (that are not in the Original BO) meant that these columns were not visible in the Grid. This is the reason I went with the DataTable as the dataSource of the Devex Grid,, where I Could see all the columns . Is this the expected behavior or am I doing something wrong
|
By Ger Cannoll - 2/10/2012
Hi Edhy
.....So as you said, if by changing the casing of the field names in the grid, it will show the columns using a BBS, then use the BBS so it will give you the CurrentRowIndex sync with your BO
Sorry I gave the wrong impression.... still cant see the columns if coming from a BBS, but can see them if DataSource is a DataTbale, but with a DataTable, column names have to be the same case as the select statement
Regards,
Gerard
|
By Edhy Rijo - 2/10/2012
Hi Ger,
No problem I understand now.
Well, since your need is to have some sort of generic solution, if you want to have it all working together and look natural, you can do the following:
- Unless you are using SELECT * in your fill method.
- Create an SQL View with all the fields expected by the grid.
- Create a BO and map it with BOM to the SQL Database View, this will give you the data schema expected.
- Add a BBS and your BO to your form, assign the BO based on the view to the BBS and use the BBS as the grid source. This will give you all the columns in your view where you can setup captions and other properties.
If you really don't know the column names of your SELECT statement, then you would need to go with the data table route and use code to loop through all the columns of the data table and create columns with captions.
|
By Ger Cannoll - 2/10/2012
HI Edhy.
I thought of doing something like this, but what I want to do is come up with a 'Strategy' of being able to 'Join' Tables, and present them in the Grid. I dont know at this stage what tables I will be joining, but I think there are going to be a lot of them, different joins in different screens.
The whole system has maybe 50 different tables, so I think I would probably need to create a view with every field from every table, otherwise I suspect I will be going back amending the view all the time.....I am not sure if this is the right way to go now, so I'll go with the DataTable approach.
Being able to select out ANY field into a BO is great, but a bit restrictive if I cant then use the fields in a Grid
|
By Edhy Rijo - 2/10/2012
Ger Cannoll (2/10/2012)
Being able to select out ANY field into a BO is great, but a bit restrictive if I cant then use the fields in a Grid
The problem is that you should be aware of the purpose of the BO, it is basically a wrapper of your data table columns and using the BOM you can control its properties, but the internal data table will have all the columns returned by the SELECT statement, when using a BBS, the SF BBS class is wired to recognized all the fields in the BO not in the data table, so if you need to create virtual fields to show up in the BO then you create Custom Field Properties (CFP), but the situation here is that you don't know what the columns would show up, even though, you must know then since there are hard coded in the SELECT, then the idea of create a view with all columns you would have in the SELECT is the way to go to keep things simpler and easier to maintain.
I don't know the details of your application or how this "unknown columns" fit within it, but unless you allow the user to create their own JOIN SELECT you must know ahead to hard code it, and if that is the case, then just create a view, if the same grid would use many different JOIN SELECT combination, then a programmatic approach would be needed to build this generic solution which involves looping the data table columns and create them on the fly into the grid and managing your own record pointer movement, you will still be able to use method of that BO to save, delete records as long as you defined the PK, but some other validations would be hard code since there would be no field properties for those unknown columns at runtime.
|
By Ger Cannoll - 2/10/2012
Hi Edhy, many thanks for your comments, and if you have the time, I would like to flesh this out a little more, as I want to end up doing things in a standard way.
Say I have 50 screens in my app (in the process of being designed right now) , and each screen will access possibly 10 tables.
Altogether, the App would use say 30 tables, whcih translates into 30 BusinessObjects. For a lot of my Forms, they are not really maintenance Forms, but Processing Type forms, where I want to display data from a number of joined tables, and if say a particular Row is ticked, do something
So, I would have the following Scenario:
Form1: Select * from Table1 join Table2 join Table3
Form2: Select * from Table4 join Table5 Join Table6
Form3: Select * from Table7 Join Table8 Join Table9
etc for my 50 screens, so yes, I do know the fields in advance ,and I will want each field from myselect to end up on a Grid in the Form
Do I then have to set up 50 different Views ? I have read in a few places that setting up views may not be the most elegant way of doing things, partcicularly when the ONLY purpose of the view is to get over the limitiation that I cannot place all the fields from a BBS onto a Grid.
(Obviously for my day to day maintenance and Data Entry Forms, I use bog standard Business Objects and thet work well)
|
By Edhy Rijo - 2/10/2012
Ger Cannoll (2/10/2012)
Hi Edhy, many thanks for your comments, and if you have the time, I would like to flesh this out a little more, as I want to end up doing things in a standard way.
You are welcome Ger.
I am currently working on final touches of an update, but there is always some time for the SF community. I sent you a Private Message with some info, after that you can post your final decision here.
|
By Ger Cannoll - 2/13/2012
I have this working now satisfactorily, as follows:
1. In the Load Event , and a 'Refresh' button, I have a Call to code that Creates a BO (Which includes a Few Joins to other Tables). I am using a Base Generic type Bo, which I will use for all instances like this where I need to join a few tables
2. I have a Devexpress grid set up, and in Code, set the DataSource of the Grid to the Underlying DataTable in the BO. Doing this, I can see all the fields in the DataTable
3. I then can use the BO.GetEnumeraable to Process the data from the Grid (Any changes done in the Grid are rfelected in the underlying DataTable, and also on the BO)
4. I am not using a BBS, but this has not caused any particular issues as all Grid Changes are reflected in the BO anyway.
Many thanks for all assistance
|
By Trevor Hendricks - 4/22/2012
PMFJI,
" I have a Call to code that Creates a BO "
could you elaborate on the code that does this.
Thanks,
Trevor
|
By Ger Cannoll - 5/2/2012
Hi Trevor.
The approcah I use here is to have a 'Dummy' BO, with say just one field in it, that comes form a 'Dummy' table, so it might have one column .
Then if I need a BO populated with fields from Joined Tables, I just run a Join and indicate the Fields I need in the Select String. This ends up with lots of fields that are not in the Original BO, but unless you are writing back to tables, this does not seem to matter. (I tend to use these for enquiry type screens)
The pont I was making in the thread is that Devexpress (which I now use for all my grids) does not seem to like Fields like this that are not taken from the originating table (if a BusinessBindingSource is the Source) .
If however, the Undrlying Datatable is specified as the Source of the Grid, these fields are recognised ok by Devexpress
|
|