开放源码的对象关系映射工具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);
