这篇文章讲解如何使用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);