Learn NHibernate Lesson Part 2 - More Query Methods and Syntaxes

disclaimer - at the moment, this post is in note format from a workshop I did, I need to it clean up, and add grammar

2 - Part 2 More Query Methods and Syntaxes

first a refactor.. up until now we have been getting a session in every dataaccess layer method. we dont want to be doing this as each time we get teh session we are connecting to the db, and wiring up anything else needed.. a waste.. so, we'll start by making session a private member on the dataaccess layer.[csharp]

public class DataAccessProvider
{
private ISession session;

//we now call the getsession in the constructor to the class..

public DataAccessProvider ()
{
session = GetSession();
}

//now methods will look like the following:

public IList<Customer> GetCustomerByFirstName(string firstname)
{
return session.CreateQuery("select from Customer c whre c.Firsname='" + firstname + "'").List<Customer>();
}

[/csharp]

getting distinct in HQL[csharp]

public IList<string> GetDistinctCustomerFirstnames()
{
return session.CreateQuery"select distinct c.Firstname from Customer c").List<string>();
}

[test]
public void CanGetDistinctCustomerFirstnames()
{
IList<string> firstnames;
provider.GetDistinctCustomerFirstnames();

IList<string> foundfirstnames = new List<string>();

foreach (string fn in firstnames)
{
if(foundFirstnames.Count != 0)
Assert.IsFalse(foundFirstNames.Contains(fn));

foundFirstNames.Add(fn);
}
}

[/csharp]

refactor test to use lambda expressions..[csharp]

[test]
public void CanGetDistinctCustomerFirstnames()
{
IList<string> firstnames;
provider.GetDistinctCustomerFirstnames();

foreach (string fn in firstnames)
{
Assert.AreEqual(1, firstnames.Count<string>(x => x == fn));
}
}

//GetDistinctCustomerFirstnames using criteria
public IList<string> GetDistinctCustomerFirstnames()
{
return session.CreateCrtieria<Customer>
.SetProjection(Projections.Distinct(Projections.Property("Firstname")))
.List<string>();
}

[/csharp]

getting ordered names through HQL[csharp]

public IList<string> GetOrderedCustomerFirstnames()
{
return session.CreateQuery<Customer>("select from Customer c order by c.Firstname").List<Customer>();
.List<string>();
}

[/csharp]


getting ordered names through Criteria[csharp]

public IList<string> GetOrderedCustomerFirstnames()
{
return session.CreateCritera<Customer>
.AddOrder(new Order("Firstname", true))
.List<string>();
}

[/csharp]

getting counted names through HQL[csharp]

public IList<object[]> GetCountedCustomerFirstnames()
{
return session.CreateQuery<Customer>("select c.Firstname, count(c.Firstname) from Customer c group by c.Firstname").List<object[]>();
.List<string>();
}

[test]
public void CanGetCountedCustomerFirstnames()
{
IList<object[]> firstnameCounts = provider.GetCountedCustomerFirstnames();

Dictionary<String, int> expectedCounts = new Dictionary<string, int>();
expectedCounts.Add("steve", 3);
expectedCounts.Add("mike", 1);

foreach(object[] item in firstnameCounts)
{
Assert.AreEqual(expectedCounts[item[0].ToString()], item[1]);
}
}

[/csharp]