Count and result

Papy214 · **Joined:** Fri Feb 29, 2008 9:36 am **Posts:** 40

Hi,

Sorry in advance for my newbie question ...
I work with NHibernate and C# 2008.

I don't understand how to get the count result from a table.
Actually, I create a List and I get list.count.

Example:

select * from patients where enabled = 0

But if want to get the column value, how get the result for the count result ?

select count(Id) from patients where enabled = 0

Code:

            
int result = 0;
NHibernate.ISession session = null;
try
{
    session = this.CubeSessionFactory.OpenSession();
    IList q = session.CreateQuery("select count(Id) as c from patients where enabled <> 0").List();
    if (q != null)
    {
        result = q[0].?????????????;
    }
}
finally
{
    if (session != null)
        session.Close();
}

Thanks for your help

rolandz · **Posted:** Mon Jun 02, 2008 5:06 am

Code:

Use this approach:

int result = 0;
NHibernate.ISession session = null;
try
{
    session = this.CubeSessionFactory.OpenSession();
    result = session.CreateQuery("select count(Id) from patients where enabled <> 0").UniqueResult<patients>();
}
finally
{
    if (session != null)
        session.Close();
}

Only I am not sure now whether result shuold not be of Int64 type...

rolandz · **Posted:** Mon Jun 02, 2008 5:06 am

Code:

Use this approach:

int result = 0;
NHibernate.ISession session = null;
try
{
    session = this.CubeSessionFactory.OpenSession();
    result = session.CreateQuery("select count(Id) from patients where enabled <> 0").UniqueResult<patients>();
}
finally
{
    if (session != null)
        session.Close();
}

Only I am not sure now whether result shuold not be of Int64 type...

rolandz · **Posted:** Mon Jun 02, 2008 5:15 am

rolandz wrote:

Oops, correcting:

Code:

    result = session.CreateQuery("select count(Id) from patients where enabled <> 0").UniqueResult<int>();

I provided wrong template argument...

Papy214 · **Joined:** Fri Feb 29, 2008 9:36 am **Posts:** 40

Ok, thanks ! It work for one column !

But if my query return more than one column with one for the count result ?

Example:

SELECT Patients.gender, Count(Patients.gender) AS CountOfgender
FROM Patients
GROUP BY Patients.gender;

How to get CountOfgender ? ... it's not a Patients property ...

rolandz · **Posted:** Mon Jun 02, 2008 6:11 am

Papy214 wrote:

But if my query return more than one column with one for the count result ?

Example:

SELECT Patients.gender, Count(Patients.gender) AS CountOfgender
FROM Patients
GROUP BY Patients.gender;

How to get CountOfgender ? ... it's not a Patients property ...

I see. There are two ways. Create your own type like following:

Code:

class MyType
{
  public MyType() {}

  public MyType(string gender, int count) { /* Put code here... */ }
}

Then in your query you do this:

Code:

select new MyType(Patients.gender, Count(Patiens.gender)) from Patiens...

I am not sure about the query - wanted to show just an idea I've seen already...

Another approach is a derivative from my former message but if you have two collumns then you just need to use an UniqueResult<object[]> and as a result you get an array of objects, where each collumn is stored in separate array element so youo do this:

Code:

object[] result = q.UniqueResult<object[]>();
string gender = result[0];
int count = (int)result[1];

Indexing follows your column order in the query. I still did not check the int type... Note that it seems that the second approach is easier (thought less clear in comlex architectures...) and here I would choose it.

Papy214 · **Joined:** Fri Feb 29, 2008 9:36 am **Posts:** 40

Thanks a lot ! I'll make that ! :-)