请看生成的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(){} ///
}
目的是读取appSettings中的名值对dsn，它的值如下
原来是从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 _disibledevent=>// 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);
最新评论