开放源码的对象关系映射工具ORM.NET 读取数据 Retrieve Data using ORM.NET

这篇文章讲解如何使用ORM.NET读取数据。

请看生成的TestApp的代码

static void Main(string[] args)
{
          //
          // TODO: Add code to start application here
          //
          DataManager dm = new DataManager(Config.Dsn);
}

DataManager是data access的接口类型，与ADO.NET中的SqlDataAdapter相似，连接数据库与实体之间的桥梁。
Config是生成的代码中的一个类型，它的源码如下

public class Config
{
    private Config(){}
    /// <summary>
    /// Wraps the 'dsn' entry in the app.config file.
    /// </summary>
    /// <value>
    /// Data source name.
    /// </value>
    public static string Dsn { get { lock( typeof(Config) )
    {
           return ConfigurationSettings.AppSettings["dsn"]; } }
    }

}

目的是读取appSettings中的名值对dsn，它的值如下

<appSettings>
   <add key="dsn" value="Data Source=(LOCAL);Initial Catalog=Northwind;Integrated Security=sspi" />
</appSettings>

原来是从App.config中读取数据库连接字符串。也可以像这样来指定数据库连接字符串

DataManager dm = New DataManager("Data Source=(local);Initial Catalog=Northwind;Integrated Security=sspi")

QueryCriteria.And 和 QueryCriteria.Or

例子1 读取记录 读取名字为Tom的学生记录

SELECT * FROM STUDENT WHERE FirstName = ‘Tom’

ORM.NET写法

// 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 - see Displaying Data for more info
Student student = dm.GetStudent(); 
// Display the retrieved information
Console.WriteLine(student.FirstName + student.LastName);

查询条件的写法，如上面的代码所示，添加到DataManager的QueryCriteria.And()方法中。JoinPath是ORM.NET生成的类型，以引用所有的实体。QueryCriteria.Add的第一个参数是引用Student类型的FirstName列，
第二个是参数值Tom，第三个是匹配的方式，MatchType.Exact表示完全相等(=)。

再复杂一些，如果需要查找学生名字为Tom或是Jack，SQL的语句应该这样写

SELECT * FROM STUDENT WHERE FirstName = ‘Tom’  OR FirstName='Jack'

ORM.NET的在上面的基础上，要再加上下面的句子

dm.QueryCriteria.Or(JoinPath.Student.Columns.FirstName,"Jack",MatchType.Exact);

QueryCriteria.Or表示添加一个OR表达式，QueryCriteria.And表示添加一个AND表达式。

下表列出了QueryCriteria参数的含义

参数定义	解释
JoinPath	枚举所有的数据表，列，和它们的关系
Value	传入的参数文化
MatchType (可选)	前面两个参数的匹配类型，如果是完全匹配(MatchType.Exact)，可以省略

 

QueryCriteria.Clear()

例子2 读取名字是Bill或是姓是Clinton的所有学生的记录

SELECT * FROM Student   WHERE FirstName = 'Bill' OR  LastName = 'Clinton'

ORM.NET的写法如下

// Combine .And and .Or QueryCriteria methods
dm.QueryCriteria.Clear(); // Removes any previous queries from memory
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Bill",MatchType.Exact)
       .Or(JoinPath.Student.Columns.LastName,"Clinton",MatchType.Exact);
// will generate the same query as writing them on separate lines.
dm.QueryCriteria.Clear();      // Removes the previous query from memory
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Bill", MatchType.Exact);
dm.QueryCriteria.Or(JoinPath.Student.Columns.LastName,"Clinton",MatchType.Exact);

上面列举了两种写法，记得在添加条件时，先调用方法QueryCriteria.Clear()清除原来已经存在的条件。

请看下面的ORM.NET写法

DataManager dm = new DataManger(Config.Dsn);
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Smith");
StudentCollection students = dm.GetStudentCollection();
// work with the students data
dm.QueryCriteria.Clear(); // BE SURE TO CALL .Clear() or the Query Builder will try to add the                
// previous QueryCriteria statement to next QueryCriteria statement!
dm.QueryCriteria.And(JoinPath.Contact.Columns.City,"Boulder");
Contact contact = dm.GetContact();
// work with contacts data

上面这句代码片段会生成两条SQL语句，如下

SELECT  * FROM Student WHERE LastName=’Smith’
SELECT * FROM Contract WHERE City=’Boulder’

Root Object and JoinPath Enumeration Object 根对象与JoinPath枚举

例子3 查询主从表数据  查找学生姓为Jennings，所在城市为Boulder的学生

Select *  FROM    Student s, Contact c
   WHERE  s.FKContactId = c.ID and      s.LastName = ‘Jennings’  and  c.City = ‘Boulder’

ORM.NET的写法如下

dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Jennings")
                .And(JoinPath.Student.Contact.Columns.City,"Boulder"); 
Student student = dm.GetStudent();

Contact是Student表的从表，一个Student包含多表联系方式Contact

DataManager.CriteriaGroup  查询条件分组

再复杂一些的查询，查找学生表中FirstName为Bill,LastName是Williams，或是联系方式表Contact中City是Detroit，

邮政编码是87323. SQL写法如下

SELECT *   FROM  Student s, Contact c 
            WHERE s.FKCOntactId = c.ID AND   s.FirstName = 'Bill' AND    s.LastName = 'Williams' 
            OR   (c.City = 'Detriot' AND c.PostalCode = '87323') 

用ORM.NET来设计，写法如下

// Create a CriteriaGroup 
DataManager.CriteriaGroup group1 = dm.QueryCriteria.NewGroup(); 
// Specify the criteria to add for this group 
group1.And( JoinPath.Student.Columns.FirstName, "Bill"); 
group1.And( JoinPath.Student.Columns.LastName, "Williams"); 
// Create another group 
DataManagerBase.CriteriaGroup group2 = dm.QueryCriteria.NewGroup(); 
// Create another group 
group2.And( JoinPath.Student.Contact.Columns.City, "Detriot"); 
group2.And( JoinPath.Student.Contact.Columns.PostalCode,"87323"); 
//OR both groups together as the final criteria to create a single query 
dm.QueryCriteria.Or(group1).Or(group2); 

两者对比一看，CriteriaGroup 相当于SQL语句中的括号，用来把查询条件分组

MatchType

前面我已经提到过，MathType用来匹配查询参数与它的值，如下表所示

MatchType值	SQL 运算符	举例
MatchType.Exact	=	LastName=’Jack’
MatchType.Partial	%value%	Like  ‘%Jack%’
MatchType.StartsWith	value%	Like  ‘Jack%’
MatchType.EndsWith	%value	Like  ‘%Jack’
MatchType.Lesser	<	Age<28
MatchType.Greater	>	Age>28
MatchType.GreaterThanOrEqual	>=	Age>=28
MatchType.LessThanOrEqual	<=	Age<=28
MatchType.Like	LIKE [] [^] _ % *	LIKE 'abc[_]d%'
MatchType.IsNull	IS NULL	Wife IS NULL
MatchType.IsNotNull	IS NOT NULL	Wife IS NOT NULL
MatchType.Not	<> or 'NOT'	FirstName<>’James’
MatchType.NotLike	NOT LIKE [] [^] _ % *	NOT LIKE  'abc[_]d%'
MatchType.NotIn	NOT IN	FirstName NOT IN(‘James’,'ANDY’)
MatchType.In	WHERE Table.ColumnName IN (a,b,c)	FirstName IN(‘James’,'ANDY’)

下面举例说明它的用法

dm.QueryCriteria.Clear(); 
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,oma,MatchType.Partial); 
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,non, MatchType.EndsWith); 

返回 FirstName包含oma和姓是以non结束的学生

dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Room.Columns.Floor,2, MatchType.GreaterOrEqual);
二楼或二楼以上的所有教室

string[] arrLastNames = {"Jennings","Williams"};
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,arrLastNames,MatchType.In);

姓是Jennings或Williams的学生

string[] arrLastNames = {"Jennings","Williams"};
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,arrLastNames,MatchType.NotIn);

姓不是Jennings和Williams的学生

dm.QueryCriteria.And(JoinPath.Teacher.Columns.LastName,"[c-p]arsen",MatchType.Like);
dm.QueryCriteria.And(JoinPath.Teacher.Columns.LastName,"[c-p]arsen",MatchType.NotLike);

老师的姓是以c到p的一个字母开头，后面紧接着是arsen，前一句是符合匹配的记录，后一句是不符合匹配的记录。

dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, null, MatchType.IsNull);
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, null, MatchType.IsNotNull);

老师的状态Status列为null或者不为空，下面这两句的结果也是一样

// Enter "" instead of null
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, "", MatchType.IsNotNull);
// same query without explicitly passing MatchType.IsNull or .NotNull
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, null);