By Terry Bottorff - 9/13/2009
If I have a Course table and a Student table that are related by Student PK such as (Simplified):
Course1 table which has fields
PK
Course#
StudentPK
TestScore
QuizScore
DailyScore
and Student table has fields
PK
Name
I have a stored procedure that gets the following information using a parameter to get students in Algebra1:
course#,Name and TestScore
I want to put the the above info in a devexpress grid. I can get this far from the help I already got from the forum (I use a dummy table with the correct field names) put the info into a BO and then I use a BBS to attach it to the devgrid. But how does the test scores get back to the course table?
I have simplified the layout of the tables but if we can make this work then I can apply it too the project. TIA
|
By Peter Jones - 9/13/2009
Hi Terry,
We do this all the time. The approach we take is:
1) Create a BO on the Course1 table.
2) Create a BO on the Student table.
3) Configure each BO with a proc that extracts all the data in each table.
4) Point 3) can, of course, get more complicated but lets assume this is good enough for now.
5) In BO.Student:
> Set ChileAutoFilterOption to MatchCurrentRow (this will ensure that onlt Students in the current Course are displayed.
> Set ParentRelationship so that Student is related to Course by it PK
6) Create a form and drop a grid on to it and set its datasource to BBS.Course1. This becomes the forms PrimaryBusinessObject.
7) Drop a second grid onto the from (we normally use a split container, one panel for each grid) and set its datasource to BBS.Student.
8) In BO.Student (once its been dropped on your form) go into Properties and set ParentBusinessObject to BO.Course which is already on the form.
And you're done. A nice parent child form. The same can be done for parent/child/grandchild etc.
Cheers, Peter
|
By Terry Bottorff - 9/13/2009
Oh wow. Your SP does not bring the data together it just makes sure you have the correct data from each table which then ends up in a BO and then the BBS for the grid? Correct?
1. So what do you do if some data from both tables are to be displayed(the first grid)?
2. What do you do if you have a link table(I think that is what they are called)? Like this.
studentpk, name <-----> studentpk,coursepk <---------->coursepk,coursename,testscore,quizscore,dailyscore?
I hope #2 makes sense. It is just 3 tables related thru the middle table.
Again, thank you Peter for such a timely response and a clear response.
|
By Peter Jones - 9/13/2009
Hi Terry,
In that case you will three tables:
Parent: Course
Child: StudentCourse
GrandChild: Student
Grid1 would be populated with Course
Grid2 would be populated with StudentCourse
There would be no need for a Student to be included as a seperate grid. The data column Student in Grid2 would be a simple DevEx lookup edit populated from the Student table and the Course data column in StudentCouse table would not even be displayed in Grid2. Straraframe will take care of the relationships and keep the Course column (in StudentCourse) correctly populated with the Course PK.
Cheers, Peter
|
By Peter Jones - 9/13/2009
Hi Terry,
Oops, a bit early in the morning. I've just reread my post and your question and I see I'm off the track a little bit.
In your example "testscore, quizscore, dailyscore" would have to become part of the StudentCourse table because that's what the data refers to, i.e. it isn't Course specific its StudentCourse specific.
So one grid containing the expanded StudentCourse table with the data columns Student and Course being lookup edits referencing those two tables.
Cheers, Peter
|
By Terry Bottorff - 9/13/2009
Ok I thought about that but the teachers are so accustomed to putting their cursor in a column say test column and putting in a score and hitting enter which takes them to the next student. The grid is in the same order as their grade book. Don't think I can change that thought pattern.
How about this? Create a View that will get ALL Possible Data. Then I believe I can create a BO from that View and I then I think I could use a SP that uses the View to just get the data needed at any given time with parameters. Then I could fill the BO with the SP, then use a BBS for the gird.
If that would work to get the data, would it then update the testscores?????? Or is my thoughts completely off base?
TIA.
|
By Peter Jones - 9/13/2009
Hi Terry,
You can create a BO from a view but not a stored procedure (as you seem to know) however SQL Server doesn't support updatable views.
I guess you could use a view and have an unbound grid and handle all the database updates manually as you move from row to row. That being said your database does need to be structured in the way I outlined.
Cheers, Peter
|
By Terry Bottorff - 9/13/2009
oh views are not updateable. ug........  so much for my idea..... The data structure is in place, we are being allowed to do a few more things with it, I hope.
I am going to have to think about this multiple grid idea. If you scroll down the course grid to enter scores how will I be able to keep it in sink with the Student Table so the names will be correct?
TIA
|
By Peter Jones - 9/13/2009
Hi Terry,
Don't really understand the question but what I do need to know is what tables/columns will you be working with?
Cheers, Peter
|
By Terry Bottorff - 9/13/2009
The data structure is in place from a already purchased piece of software. The company is going to let us access that data and we want to update it outside of their program because of some issues.
I will get the outline of the files and fields together and get back to you. Thanks
|
By Terry Bottorff - 9/13/2009
Here is the layout:
Course
coursepk
coursename
period
LinkTable
linkpk
coursepk
studentpk
period
The above table has multiple records for each course per period.
Student
studentpk
name
grades
gradepk
studentpk
coursepk
period
positioninperiod
testscore
The grid(s) would have (but is fexible):
coursename,studentname,period,testscore
The only updateable field is the testscore and the data appears in positioninperiod order.
|
By Peter Jones - 9/13/2009
Hi Terry,
A couple of questions:
1) Its not clear what what "Period" means, e.g. if LinkTable.Period is the same as Course.Period then one is redundent.
2) Grades.Period seems very suspect. Period has now appeared in three different tables and, it seems, they can all have independent values. Is this correct?
That being said I take it that all tables have existing values and all you want to do it update the Grades.TestScore column. If this is so I would:
.
a) Have one Grid using a BO based on the Grades table. Populate with a stored procedure that sorts on Course.CourseName, Student.Name, Grade.Period and Grade.PositionInPeriod. Show these columns:
Grades.CoursePK - is a DevEx lookup edit to table Course
Grades.StudentPK - is a DevEx lookup edit to table Student
Grades.Period - is whatever is in that column
Grades.PositionInPeriod - is whatever is in that column
Grades.TestScore - is a maintainable column.
b) Probably be best to show the GroupHeader box in the grid and put Grades.CoursePK in there (which will be the Course.Name, not the PK of course).
Cheers, Peter
|
By Terry Bottorff - 9/14/2009
OK I will work on this. Thanks.
I did not design the data structure, I just have to use what is there.
Period is like 8-9am is period 1 and 10-11am is period 3 which might be Algebra classes for a particular teacher.
After some more work on this I will probably come back to this when I get some actual code and forms.....
|
By Trent L. Taylor - 9/14/2009
Taking on existing data structures can be frustrating, and I have just scanned through this post and have seen some good advice from Peter. One thing that could change a some of these answers might be if you are free to add SPROCs or updatable views. If you cannot touch the database at all, then obviously you are 100% stuck with these structures, sprocs, and views. But if you can add some to meet your specific needs, then it would be easier to work with.
At any rate, there was a lot of good advice given here so I will just let you go with what was given for now, just thought I would throw that out there. 
|
By Peter Jones - 9/14/2009
Hi Terry,
"Period is like 8-9am is period 1 and 10-11am is period 3 which might be Algebra classes for a particular teacher"
Ok, but I think you can see that having three seemingly independant Period values doesn't seem to be correct but, as I said, if your only interest is in updating a single value is the Grades table then it doesn't make much difference.
I also notice you have also introduced "Teacher" who doesn't appear in any of the tables you have mentioned.
It's sometimes very tough working with an existing database....
Cheers, Peter
|
By Terry Bottorff - 9/14/2009
I just introduced teacher to explain what a period was. I'm starting to attack this problem and will be back for help I am sure.
In one of your responses you mentioned a lookup edit, am I correct if I want the grid to simply look like this:
Course# Period StudentName TestScore
11 1 Buffie 85
11 1 Sally 90
11 1 Billy 88
...
and so forth where we are only putting in the score that lookup edit is not what I want to use?
TIA.
I could not get the columns to line up...... sorry
|
By Peter Jones - 9/14/2009
Hi Terry,
I only mentioned lookup edits so you can display the names associated with the Course and Student rather than the PK values. I didn't mean to imply that you would actually edit the data.
Another approach (maybe better in this case) would be to extract these names and include them in your stored procedure that extracts the data for the Grades BO. After you have created the BO from the Grades table, add these extra fields as Custom Fields in your BO. Once you have added the Custom Fields and recompiled the BO they will be visible in the grids data source.
In case your are not familiar here's an example of adding a couple of custom fields to a BO. Note "boPUNType" referred to below is the name of my BO.
Custom Fields are very useful. You can base a BO on a table and add any amount of other info into the BO from other sources however Custom Fields are not updatable. The following example is just a couple of fields but, in this particular BO, we have about 15 Custom Fields.
'''
''' LocationInStore
'''
'''
BusinessFieldDisplayInEditor(), _
Description("LocationInStore"), _
DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _
Public ReadOnly Property [LocationInStore]() As System.String
Get
Dim loValue As Object
If Me.CurrentDataTable.Columns.Contains("LocationInStore") Then
loValue = Me.CurrentRow.Item("LocationInStore")
Else
loValue = DBNull.Value
End If
If loValue Is DBNull.Value Then
Return String.Empty
Else
Return CType(loValue, System.String)
End If
End Get
End Property
'''
''' Quarantined
'''
'''
BusinessFieldDisplayInEditor(), _
Description("Quarantined"), _
DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _
Public ReadOnly Property [Quarantined]() As Nullable(Of System.Boolean)
Get
Dim loValue As Object
If Me.CurrentDataTable.Columns.Contains("Quarantined") Then
loValue = Me.CurrentRow.Item("Quarantined")
Else
loValue = DBNull.Value
End If
If loValue Is DBNull.Value Then
Return CType(Nothing, Nullable(Of System.Boolean))
Else
Return New Nullable(Of System.Boolean)(CType(loValue, System.Boolean))
End If
End Get
End Property
Protected Overrides Function GetCustomBindablePropertyDescriptors() As MicroFour.StrataFrame.Business.FieldPropertyDescriptor()
Dim boPUNType As System.Type = Me.GetType()
Return New MicroFour.StrataFrame.Business.FieldPropertyDescriptor() { _
New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
"LocationInStore", boPUNType), _
New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
"Quarantined", boPUNType)}
End Function
Cheers, Peter
|
By Terry Bottorff - 9/14/2009
I think I just had the AH HA Moment. The Lookup Edit is not used to look up a student--the grid uses it to lookup the student name that corresponds to that record in the grid. Is that correct?
|
By Peter Jones - 9/14/2009
Hi Terry,
That's it. Just a way of presenting useful info to the user.
Cheers, Peter
|