How to display entities in a DataGrid

adin05_ · **Posted:** Mon May 16, 2005 1:24 am

I have a cross join query between to persistent classes. For example:

Code:

query="FROM Member, Question"

Member has a one-to-many relationship to Question. Respectively for Question, it has a many-to-one relationship to Member.

The query works fine ... but I can't view the result directly from a DataGrid.

Here's a pseudocode:

Code:

IList lst = session.Find(query);

DataGrid1.DataSource = lst;
DataGrid1.DataBind();

The DataGrid only shows properties of the lst. It doesn't show any result of the query.

sergey · **Posted:** Mon May 16, 2005 3:04 am

That's because the query result is a list of arrays of two elements, the first one is a Member, the second one a question. You probably need to select only one of the items and use some form of join between Member and Question.

adin05 · **Posted:** Mon May 16, 2005 3:30 am

Thanks for the reply.

I think I get your point. When I debug my application, I can see the list of array of two elements. I've tried selecting only one of the items and it worked fine. But I need more than one item. For example, I need to see who's the member who post which question. So I tried to select more than one item, but it's back to square one. The DataGrid, once again, only shows the property of the list.

Ah, and another thing, any clue about how to fix a QueryException when using JOIN? The message tells me something regarding "path expression".

Thx in advance.

-Adin

adin05 · **Posted:** Mon May 16, 2005 5:27 am

About my previous post, let me post the queries that I've tested.

Code:

query = "SELECT q.Question_Text FROM Member m, Question q WHERE m.User_ID = q.Asker_ID"

Code:

query = "SELECT q.ID, q.Question_Text FROM Member m, Question q WHERE m.User_ID = q.Asker_ID"

Member relates to Question in a one-to-many relationship. I've mention this in my first post.

Here's what my code looks like:

Code:

IList lst = session.Find(query);

DataGrid1.DataSource = lst;
DataGrid1.DataBind();

When I use the first query, DataGrid1 shows the result of the query. But when I use the second query, DataGrid1 shows me something that looks pretty much like the properties of lst.

About the QueryException ...

I've modified the first query, which is successful into this:

Code:

query = "SELECT q.ID, q.Question_Text FROM Member m INNER JOIN Question q WHERE m.User_ID = q.Asker_ID"

This is where the QueryException occured. Here's the message:

Quote:

outer or full join must be followed by path expression [SELECT q.ID, q.Question_Text FROM Ametis.Lexica.Objects.Member m INNER JOIN Question q WHERE m.User_ID = q.Asker_ID]

I've tried the Open Discussion but came out with nothing (or maybe I'm not looking it right :D ). I've also tried the SDK, still no clue.

Anyone?

-Adin

KPixel · **Posted:** Mon May 16, 2005 7:51 am

Why are you adding "Member m" ? As you select nothing in this entity, I don't think that it is usefull (idem for "WHERE m.User_ID = q.Asker_ID").

I don't know how DataGrid bind collections, but IMO, you need to convert the IList you received from NHibernate (from an array of array to a 2-dimensional array).

sergey · **Posted:** Mon May 16, 2005 8:25 am

Usually you don't write joins in HQL explicitly (a inner join b where a.id = b.id), but use HQL join syntax, which would be:

Code:

from Question q join q.Member m where something

or

Code:

from Member m join m.Questions q where something

You can also use outer joins and "join fetch", Hibernate documentation has all the details (http://www.hibernate.org/hib_docs/reference/en/html/).

devonl · **Posted:** Mon May 16, 2005 7:04 pm

i'm assuming you are trying to get a list of question objects for a particular member. since you already have the m-1 relationship mapped you could use the CreateCritera API:

Code:

public class MemberDAO {

...

  public IList GetAskedQuestions(Member asker) {
    return session.CreateCriteria(typeof(Question))
      .Add(Expression.Eq("Member", asker))
      .List();
  }
}

Then, in your asp.net app you might do something like:

Code:

MemberDAO memberDAO = new MemberDAO(session);

MyDataGrid.DataSource = memberDAO.GetAskedQuestions(member);
MyDataGrid.DataBind();

and Viola!, your DataGrid is bound to an IList of Question objects for the member you pass to it.

I hope this is what you were trying to do...

-devon

adin05 · **Posted:** Tue May 17, 2005 11:46 pm

KPixel wrote:

Why are you adding "Member m" ? As you select nothing in this entity, I don't think that it is usefull (idem for "WHERE m.User_ID = q.Asker_ID").

I don't know how DataGrid bind collections, but IMO, you need to convert the IList you received from NHibernate (from an array of array to a 2-dimensional array).

Thx for your reply.

Actually here's what I needed to get:

Code:

"SELECT q.ID, q.Question_Text, m.User_ID FROM Member m, Question q WHERE m.User_ID = q.Asker_ID"

So, I needed to join Member and Question.

I agree with your opinion, I need to convert the IList. I noticed it when I debug my application. It's just that I don't know how to do the converting. Can you help me on this? I've tried a work around like creating a new ArrayList then add the elements from the IList one-by-one.

Still stuck here.

adin05 · **Posted:** Wed May 18, 2005 1:05 am

sergey wrote:

Usually you don't write joins in HQL explicitly (a inner join b where a.id = b.id), but use HQL join syntax, which would be:

Code:

from Question q join q.Member m where something

or

Code:

from Member m join m.Questions q where something

You can also use outer joins and "join fetch", Hibernate documentation has all the details (http://www.hibernate.org/hib_docs/reference/en/html/).

Thx for the help ... I guess I misread the documentation. I've tried this query:

Code:

SELECT q.Question_Text FROM Question q join q.Asker_ID m WHERE m.User_ID = 1

The above query returns the appropriate result.

Thx again for noticing this.

adin05 · **Posted:** Wed May 18, 2005 1:19 am

Thx for all the reply. I really appreciate the time and effort for those who're trying to help me, especially to sergey, KPixel, and devonl. Your help will not come to waste :D . Hope you all never got bored of helping :D .

I have another thing concerning the same subject. Here's a simple query that I've tried:

Code:

query="SELECT q, m FROM Question q join q.Asker_ID m"

The query returns Questions with corresponding Member who post the Question. It returns the right results.

But eventually, the IList resulted from the query is still an array of array containing Question and Member. Just like what KPixel wrote earlier in this thread.

I still can't find a way to convert that array of array into a 2-dimensional array like KPixel suggested. I'm still trying a work around on this. A clue might become useful.

Thx.

sergey · **Posted:** Wed May 18, 2005 2:01 am

I suggest you only select the questions in your query, without the members, like this:

Code:

select q from Question q join fetch q.Asker_ID m

Notice I added the "fetch" keyword, to retrieve and initialize the q's Asker but not put it in the result list. What you will get is a list of Question objects, and in ASP.NET it should be easy to not only show properties of each Question, but also properties of its member.

KPixel · **Posted:** Wed May 18, 2005 8:45 am

There are two type of select :
* The one you used ("SELECT q.ID, q.Question_Text, m.User_ID [...]") return an array of arrays of the items you selected
* The one suggested by Sergey ("select q from Question q join fetch q.Asker_ID m") return an array of entities (as classes) with their collections and <many-to-one> (depending on your lazy-loading settings)

The first is used mostly for searching and listing (=> in a Grid).
The second is required when you want to modify an entity and save the changes.

adin05 · **Posted:** Thu May 19, 2005 12:49 am

Hello again ...

I've tried what sergey suggested. But maybe I should agree to what KPixel said that what sergey suggested would be best used if I want to modify the entities.

KPixel guessed right that what I needed is a list of Questions and related Members. So in a Grid, I'd like to see rows of Question and who post them.

IMO, What sergey suggested obviously returned a 2-dimensional array. I can clearly see the result of the sergey's query, that is:

Code:

select q from Question q join fetch q.Asker_ID m

But when I set the Grid's dataSource to the result of the query, I still can't find a way to show the properties of Member.

Still stuck ... :)

O yea, many thanks for the replies.

devonl · **Posted:** Thu May 19, 2005 1:21 am

Hi Adin,

If you end up using the method I mentioned, you will end up with a List of Quesiton objects. In this instance, you will have to set outer-join="true" on the many-to-one collection back to the "asker" so that the Member object is instantiated when you load that question object.

Then, assuming you set the datasource as I described earlier you can set up a datagrid in the aspx file like so:

Code:

<asp:DataGrid ID="myDataGrid" AutoGenerateColumns="false" Runat="server">
  <Columns>
    <asp:TemplateColumn HeaderText="Question Text">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "Question_Text") %>
      </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Asker">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "Asker.Name") %>
      </ItemTemplate>
    </asp:TemplateColumn>
  </Columns>
</asp:DataGrid>

This will produce a DataGrid with two columns one containing the Question, and the other containing the Asker's Name. The fields I maby be using may not be correct, but I haven't seen your mapping. The point is, if you use the CreateCriteria API to get your list, you'll end up with the Question->Asker association that your hbm.xml files specify. Then using the DataBinder.Eval method you can access any of the fields anywhere in the object graph.

I respectfully disagree with KPixel in that you do not have to write direct SQP queries to create a dataset that the datagrid will use. If you are concerned about placing domain logic within the ASPX page, you could build the control in the cs codebehind file, or even create a ServerControl.

adin05 · **Posted:** Thu May 19, 2005 3:19 am

Problem solved ... many thanks to devonl, KPixel, and sergey for all the their time and effort in helping me with the problem.

Problem arose when I need a listing of Questions asked and Members who asked them. I'm planning to put that listing inside a datagrid.

One the O/R mapping, Member has a one-to-many relationship with Questions (i.e. each Member can asked more than one Question, but each Question can only be asked by one specific Member).

Here's a part of my mapping file:

Code:

Member.hbm.xml
<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="Ametis.Lexica.Objects.Member, Ametis.Lexica.Objects" table="Member">
      <id name="User_ID" column="User_ID" type="Int32" unsaved-value="0">
         <generator class="native" />
      </id>
      <property name="Last_Logon" column="Last_Logon" type="DateTime" />
      <property name="Points" column="Points" type="Int32" />
      <set name="Questions" inverse="true" cascade="all">
         <key column="User_ID" />
         <one-to-many class="Ametis.Lexica.Objects.Question, Ametis.Lexica.Objects" />
      </set>
   </class>
</hibernate-mapping>

Code:

Question
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="Ametis.Lexica.Objects.Question, Ametis.Lexica.Objects" table="Question">
      <id name="ID" column="Question_ID" type="Int32" unsaved-value="0">
         <generator class="native" />
      </id>
      <property name="Question_Subject" column="Question_Subject" type="String" />
      <property name="Question_Text" column="Question_Text" type="String" />
      <property name="Points" column="Points" type="Int32" />
      <property name="Post_Date" column="Post_Date" type="DateTime" />
      <property name="Closed" column="Closed" type="Boolean" />
      <many-to-one name="Asker_ID" column="User_ID" class="Ametis.Lexica.Objects.Member, Ametis.Lexica.Objects" />
   </class>
</hibernate-mapping>

Here's my query:

Code:

SELECT q FROM Question q join fetch q.Asker_ID m

I've clarified the result of the query when Debugging my application.

Here's my DataGrid:

Code:

         <asp:DataGrid id="dgTest" runat="server">
            <Columns>
               <asp:TemplateColumn HeaderText="Penanya">
                  <ItemTemplate>
                     <%# DataBinder.Eval(Container.DataItem, "Asker_ID.User_ID") %>
                  </ItemTemplate>
               </asp:TemplateColumn>
            </Columns>
         </asp:DataGrid>

In the end, I manage to (almost) achieve what I need. My DataGrid shows me a listing of all the Question's properties and the corresponding Member's User_ID.

Alright ... 8)

Again, thanks to devonl, especially about using the DataBinder. Thanks to KPixel for reminding about the array conversion. Thanks to sergey for noticing my mistake on the join.

Thanks a bunch.

Case closed? :D