I am using nHibernate in my application, but i just wanted to asked if there was a more effiect way of accessing the data than i have been doing.


Example of what im going:

I have four objects that are utilizing NHibernate Fair, Stand and Exhibitor and User

There are multiple Fairs and each Fair contains multiple Stands and each Stand contains one Exhibitor and each Exhibitor contains one user.

And what i want to do is given a User Id find all the Fairs they have a stand at.

So i have written this piece of code to do it:

IList l = fairdao.getFairs(); //calls a named query that get all fairs
IList attendedFairs = new ArrayList();

foreach(Fair f in l)
{
IList stands = f.Stands;

foreach(Stand s in stands)
{
Exhibitor ex = s.Exhibitor;

if(ex.User.Id==1)
{
attendedFairs.Add(f);
break;

}

}

}


While this does the job, it just appears to me the i could utilize hibernates functionality better. Could you give me afew pointers. Thanks

Perhaps you can use an HQL statement that does this job;

take a look at this:

http://www.hibernate.org/hib_docs/refer ... ryhql.html

so something like this would do it?

<query name="Fair.AttendedFairs">
SELECT Fair FROM Fair,Stand,Exhibitor INNER JOIN Stands ON Fairs.code = Stands.fair_code
INNER JOIN Exhibitor ON Stands.ehibitor_code = Exhibitor.code
WHERE Exhibitor.user_code = ?
</query>


Could u help me on they syntax

It's a bit hard to help you on the exact syntax sinch I have no knowledge of your mapping file... but based on the ealier code this could work :

IQuery q = Session.CreateQuery("select fair from Fair fair where fair.Stands.Exhibitor.User.Id = :userid");
q.SetInt32("userid",1);
IList fairs = q.List();

the IList fairs should then contain the list of fairs that user 1 attends.

If this still fails, perhaps you can drop you mapping / code somewhere on the net and I'll have a closer look.

I tried:
<query name="Fair.GetAttending">from Fair where Fair.Stands.Exhibitor.User.Code = ?</query>

and got this error:
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.

:(

Here are my mappings:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="vcf.code.entites.Fair,vcf" table="vcfFairs">

<id name="Code" column="code" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<bag name="Stands" inverse="true" lazy="true" >
<key column="fair_code" />
<one-to-many class="vcf.code.entites.Stand,vcf" />
</bag>
<property column="starting_date" type="DateTime" name="StartingDate" not-null="true" />
<property column="duration" type="Int32" name="Duration" not-null="true" />
<property column="title" type="String" name="Title" not-null="true" length="50" />
<property column="description" type="String" name="Description" not-null="true" length="1500" />
<property column="sponsor_code" type="Int32" name="SponsorCode" />

</class>

<query name="Fair.GetAll">from Fair order by starting_date</query>

<query name="Fair.GetAttending">from Fair where Fair.Stands.Exhibitor.User.Code = ?</query>


</hibernate-mapping>



<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="vcf.code.entites.Stand,vcf" table="vcfStands">

<id name="Code" column="code" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="Exhibitor" column="exhibitor_code" class="vcf.code.entites.users.Exhibitor,vcf" />
<many-to-one name="Fair" column="fair_code" class="vcf.code.entites.Fair,vcf" />
<property column="stand_number" type="Int32" name="StandNumber" not-null="true" />
<property column="stand_URL" type="String" name="StandUrl" length="100" />
<property column="validated" type="Int32" name="Validated" length="4" not-null="true"/>

</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="vcf.code.entites.users.Exhibitor,vcf" table="vcfExhibitor">

<id name="Id" column="code" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="User" column="user_code" class="vcf.code.entites.users.User,vcf" />
<many-to-one name="Employer" column="emp_code" class="vcf.code.entites.users.Employer,vcf" />
<property column="job_title" type="String" name="JobTitle" length="10" />
<property column="biography" type="String" name="Biography" length="1000" />

</class>
</hibernate-mapping>


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="vcf.code.entites.users.User,vcf" table="vcfUsers">

<id name="Id" column="Code" type="Int32" unsaved-value="0">
<generator class="native" />
</id>

<property column="client_type" type="String" name="ClientType" not-null="true" length="1" />
<property column="first_name" type="String" name="FirstName" not-null="true" length="50" />
<property column="last_name" type="String" name="LastName" not-null="true" length="50" />
<property column="email" type="String" name="Email" not-null="true" length="100" />
<property column="password" type="String" name="Password" not-null="true" length="12" />
<property column="subscription_pref" type="String" name="SubscriptionPref" length="12" />
<property column="photo" type="BinaryBlob" name="Photo" />
</class>
<query name="User.get.authenticate">from User where email = ? and password = ?</query>


</hibernate-mapping>

I had this problem when using the SQL keyword 'user' as a database table, I changed it to something specific like MyAppUser.

Maybe when NHibernate creates the query it does not wrap SQL keywords with square brackets ie:- select * from [user] where [user].id = 123;

hth

Fluxtah

Fluxtah maybe have a very good point there.. have got a log file with the generated sql code?

I went though my whole app and changed User to VcfUser....which is a pain without refactoring....and i got this:

"Exception Details: System.Data.SqlClient.SqlException: The number name 'Fair.Stands.Exhibitor.VcfUser' contains more than the maximum number of prefixes. The maximum is 3."

help

No, i dont have log4net configured...sorry im new to this

That's no prob...

downloaded the file nhibernate-1.0.2.0.zip and look in

src\NHibernate.Examples\App.config

just copy every log4net entry you can find :)

now in your init code add the flowing



this should create an log.txt in you Debug\bin directory that should have some more information.

What abou the "contains more than the maximum number of prefixes. The maximum is 3"...as we could be going on a nasty tangent here, as im new to C#.net developer.

I have this in my web.config(on relevent stuff show):

<configSections>

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
</configSections>

<log4net debug="true">
<!-- Define some output appenders -->
<appender name="trace" type="log4net.Appender.TraceAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>
<appender name="console" type="log4net.Appender.ConsoleAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>
<appender name="rollingFile" type="log4net.Appender.RollingFileAppender,log4net">
<param name="File" value="log.txt" />
<param name="AppendToFile" value="false" />
<param name="RollingStyle" value="Date" />
<param name="DatePattern" value="yyyy.MM.dd" />
<param name="StaticLogFileName" value="true" />
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>
<!-- Setup the root category, add the appenders and set the default priority -->
<root>
<priority value="ALL" />
<appender-ref ref="rollingFile" />
</root>


<logger name="NHibernate.Cache">
<level value="DEBUG" />
</logger>
<logger name="NHibernate.Impl.BatcherImpl">
<level value="WARN" />
</logger>
</log4net>

Which init method would "log4net.Config.XmlConfigurator.Configure();" go in?

I've had some time and took al closer look at your mapping files..
I think this should work;

IQuery q = session.CreateQuery("from Fair fair inner join fair.Stands stands where stands.Exhibitor.User.Id = :userid");
q.SetInt32("userid", 1);
IList fairs = q.List();

You can download the project I've made from :
this contains your mapping files and classes and some basic NH init stuff, It's not a webproject but a winform, makes it a little bit easier to debug for me.

http://www.picassoweb.nl/download/nhtest.zip

I quess this says a lot more that these posts :)

good luck!

don't forget to alter the hibernate.connection.connection_string with your own database and username / password!

Thanks, ill give this a try tomrw

Hi enix

I think we are getting closer, i tried your hql in my named query. But i am getting run time errors when binding the returned Ilist to a repeater.

I debugged it a end something strainge is being returned from the query. Its an ArrayList in which each position has a Fair[], and when expending this array there is a Fair in [0] and Stand in [1]....seem very strainge. :(


Your project wouldnt open, becasue of a version class, im using VS 2003. ill just go and read the docs on setting up log4net

System.Data.SqlClient.SqlException does suggest another SQL generation problem?


Propably Apllication_Start in global.asax