NHibernate3.2学习笔记-几种查询方式

一、开发环境

数据库：SQLServer2008

编译器：VS2010

.Net版本：.Net Framework 4.0

二、开发过程

1.项目结构

承接上一篇

2.执行sql语句

(1)基本语法

static void ExecuteSql()
{
    string str = "select * from T_Product";
    ISQLQuery query = session.CreateSQLQuery(str).AddEntity(typeof(Product));
    IList<Product> list = query.List<Product>();
    Console.WriteLine(list.Count);
}

3.执行hql语句

(1)基本语法

static void ExecuteHql()
{
    string str = "from Product a where a.Id >:pId";
    IList<Product> list = session.CreateQuery(str).SetInt32("pId", 1).List<Product>();
    Console.WriteLine(list.Count);
}

(2)返回多个不确定类型的值

static void ExecuteHql2()
{
    string str = "select a.Name,count(a.Name)  from Product a group by a.Name";
    //返回的两个不同对象类型，用对象集合保存
    IList<object[]> list = session.CreateQuery(str).List<object[]>();
    foreach (object[] obj in list)
    {
        string msg = string.Format("【" + obj[0] + "】出现了【" + obj[1] + "】次");
        Console.WriteLine(msg);
    }
}

(3)返回确定类型的值

static void ExecuteHql3()
{
    string str = "select distinct a.Name from Product a";
    IList<string> list = session.CreateQuery(str).List<string>();
    Console.WriteLine(list.Count);
}

4.执行Linq语句

(1)基本语法

static void ExecuteLinq()
{
    IList<Product> list = session.QueryOver<Product>().Where(a=>a.Id > 1).List();
    decimal maxUnitPrice = (from s in list select s.UnitPrice).Max();
    Console.WriteLine(maxUnitPrice);
}

5.使用Criteria方法

(1)基本语法

static void ExecuteCriteria()
{
    ICriteria criteria = session.CreateCriteria(typeof(Product));
    IList<Product> list = criteria.List<Product>();
    Console.WriteLine(list.Count);
}

(2)常用方法1

static void ExecuteCriteria2()
{
    ICriteria criteria = session.CreateCriteria(typeof(Product));
    criteria.Add(Expression.Lt("Id", 5));
    criteria.Add(Expression.Like("Name", "Apple", MatchMode.Anywhere));
    IList<Product> list = criteria.List<Product>();
    Console.WriteLine(list.Count);
}

(3)常用方法2

static void ExecuteCriteria3()
{
    ICriteria criteria = session.CreateCriteria(typeof(Product));
    criteria.Add(Expression.Between("UnitPrice", 1, 20));
    criteria.Add(Expression.In("Id", new int[] { 1, 2,3, 4,5 }));
    criteria.Add(Expression.Not(Expression.Eq("Id", 5)));
    IList<Product> list = criteria.List<Product>();
    Console.WriteLine(list.Count);
}

(4)使用类

static void ExecuteCriteria4()
{
    ICriteria criteria = session.CreateCriteria(typeof(Product));
    Product model = new Product();
    model.Id = 2;
    model.Name = "Apple";
    model.UnitPrice = 2.60m;
    criteria.Add(Example.Create(model));
    IList<Product> list = criteria.List<Product>();
    Console.WriteLine(list.Count);
}