开放源码的对象关系映射工具ORM.NET 查看和显示数据 View and Display data using ORM.NET

这一节讲解如何使用ORM.NET提供的接口，显示数据，包括分组，排序

DataManager.Get[Object]  Get[Object]Collection Methods

这是获取数据的主要方法，从名字中可以看出，GetObject是获取一个实体，GetObjectColletion是读取几行记录，以获取一个实体集合。使用模式如下

Object object = DataManager.Get[Object]

或

ObjectCollection object = DataManager.Get[Object]Collection

读取名字叫Tom的学生

// Create a new DataManager object with database connection string
DataManager dm = new DataManager(Config.Dsn); 
// Create the query to retrieve the desired information
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Tom",MatchType.Exact);
// Copy the resulting dataset from DataManger to a new Student object 
Student student = dm.GetStudent(); 
// Display the retrieved information
Console.WriteLine(student.FirstName + " " + student.LastName);

如果这样写, 会有编译错误，因为GetStudent返回的是Student。

Contact contact = dm.GetStudent();

如果把根对象(root)写错了，像这样，也会报错

// Create the query to retrieve the desired information
dm.QueryCriteria.And(JoinPath.Contact.Columns.Address1,"123 Main Street",MatchType.Exact);
// INCORRECT - Cannot create and copy the root object Contact from above into a Student object!!
Student student = dm.GetStudent(); 

获取姓中包含Jones的所有学生

//Create a new DataManager object with DSN information
DataManager dm = new DataManager(Config.Dsn);                               
// Clear any previous queries
dm.QueryCriteria.Clear();       
// Create the query
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, "Jones", MatchType.Exact);
//Copy the result dataset from DataManager as a Collection of Student objects instead  of a single object 
StudentCollection students = dm.GetStudentCollection();
// Loop through each student in the collection.
foreach (Student s in students)
     Console.WriteLine("FullName: {0}  {1}", s.FirstName, s.LastName);

在开始查询之前，也可以不指定QueryCriteria的值，像下面这样，它会查询所有的数据行记录

// Create a new DataManager object with DSN information
DataManager dm = new DataManager(Config.Dsn);
// Return ALL Students from the Student Table. Copy the result dataset from DataManager as a Collection of Student objects instead  of // a single object 
StudentCollection students = dm.GetStudentCollection();
// Loop through each student in the collection.
foreach (Student s in students)
     Console.WriteLine("FullName: {0}  {1}", s.FirstName, s.LastName);

DataManager.LastQueryText

当调用DataManager的方法Get[Object] or Get[Object]Collection后，可以读取属性LastQueryText以返回最后一个SQL查询语句,像下面这样的写法

StudentCollection students = dm.GetStudentCollection();
Console.WriteLine("OUTPUT SQL: " + dm.LastQueryText);

Collection methods generated for each column Property

对于返回的Collection集合，有如下的几种方示来对它进行过滤，排序

FindBy[Property](value)	搜索集合，返回一个对象
FilterBy[Property](value)	根据传入的参数，返回过滤后的集合
SortBy[Property](SortDirection)	对集合进行排序，枚举值是 SortDirection.Ascending, SortDirection.Descending

从所有学生中找名字是Karen的学生

// Retrieve a single object from the cached collection where the first //name is ‘Karen'
Student student = dm.GetStudentCollection().FindByFirstName("Karen");
Console.WriteLine("Student Name :" + student.FirstName + " " + student.LastName);
// or you can access the same information directly
Console.WriteLine("Test: " + students.FindByFirstName("Karen").LastName);

查找创建于2002/11/06的所有课程

//Filter the CourseCollection to find courses created on 11/06/2002
CourseCollection = courses.FilterByDateCreated(DateTime.Parse("11/06/2002"));

查找所有学生，并以名字升序排列

StudentCollection students = dm.GetStudentCollection().SortByFirstName(SortDirection.Ascending); 

CompareType Enumeration

还记得在QueryCriteria中可以使用MatchType枚举来匹配需要的记录，在Collection中，则是用CompareType来匹配

下面的代码片段，返回所有学生中，入学日期大于2002/11/06的学生

StudentCollection students = dm.GetStudentCollection(); // return ALL students from the table
// Use FilterBy to return only students which have a DateCreated value greater than 11/06/2002
StudentCollection newStudents = 
students.FilterByDateCreated(DateTime.Parse("11/06/2002"),CompareType.Greater);

ComareType的枚举值如下表所示

CompareType.Contains	Will search the database column that Property maps to and return all rows where value is contained anywhere within the column's value.
CompareType.EndsWith	Will search the database column that Property maps to and return all rows where value is at the end of the column's value.
CompareType.Exact	Default - Will search the database column that Property maps to and return all rows where the column's value exactly matches value.
CompareType.Greater	Will search the database column that Property maps to and return all rows where value is greater than the column's value.
CompareType.GreaterOrEqual	Will search the database column that Property maps to and return all rows where value is greater than or equal to the column's value.
CompareType.Lesser	Will search the database column that Property maps to and return all rows where value is less than the column's value.
CompareType.LesserOrEqual	Will search the database column that Property maps to and return all rows where value is less than or equal to the column's value.
CompareType.Not	Will search the database column that Property maps to and return all rows where value does NOT exactly match the column's value.
CompareType.NotContain	Will search the database column that Property maps to and return all rows where value does NOT exactly match the column's value.
CompareType.StartsWith	Will search the database column that Property maps to and return all rows where value is at the beginning of the column's value.