映射
自动映射 – 在数据库对象和.Net object自动进行1:1匹配
1: List<Product> products = Context.Sql(@"select *
2: from Product")
3: .QueryMany<Product>();
自动映射到一个自定义的Collection：
1: ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>();
如果数据库字段和POCO类属性名不一致，使用SQL别名语法AS：
1: List<Product> products = Context.Sql(@"select p.*,
2: c.CategoryId as Category_CategoryId,
3: c.Name as Category_Name
4: from Product p
5: inner join Category c on p.CategoryId = c.CategoryId")
6: .QueryMany<Product>();
在这里p.*中的ProductId和ProductName会自动映射到Prodoct.ProductId和Product.ProductName，而Category_CategoryId和Category_Name 将映射到 Product.Category.CategoryId 和 Product.Category.Name.
使用dynamic自定义映射规则
1: List<Product> products = Context.Sql(@"select * from Product")
2: .QueryMany<Product>(Custom_mapper_using_dynamic);
3:
4: public void Custom_mapper_using_dynamic(Product product, dynamic row)
5: {
6: product.ProductId = row.ProductId;
7: product.Name = row.Name;
8: }
使用datareader进行自定义映射:
1: List<Product> products = Context.Sql(@"select * from Product")
2: .QueryMany<Product>(Custom_mapper_using_datareader);
3:
4: public void Custom_mapper_using_datareader(Product product, IDataReader row)
5: {
6: product.ProductId = row.GetInt32("ProductId");
7: product.Name = row.GetString("Name");
8: }
或者，当你需要映射到一个复合类型时，可以使用QueryComplexMany或者QueryComplexSingle。
1: var products = new List<Product>();
2: Context.Sql("select * from Product").QueryComplexMany<Product>(products, MapComplexProduct);
3:
4: private void MapComplexProduct(IList<Product> products, IDataReader reader)
5: {
6: var product = new Product();
7: product.ProductId = reader.GetInt32("ProductId");
8: product.Name = reader.GetString("Name");
9: products.Add(product);
10: }
多结果集
FluentData支持多结果集。也就是说，可以在一次数据库查询中返回多个查询结果。使用该特性的时候，记得使用类似下面的语句对查询语句进行包装。需要在查询结束后把连接关闭。
1: using (var command = Context.MultiResultSql)
2: {
3: List<Category> categories = command.Sql(
4: @"select * from Category;
5: select * from Product;").QueryMany<Category>();
6:
7: List<Product> products = command.QueryMany<Product>();
8: }
执行第一个查询时，会从数据库取回数据，执行第二个查询的时候，FluentData可以判断出这是一个多结果集查询，所以会直接从第一个查询里获取需要的数据。
分页
1: List<Product> products = Context.Select<Product>("p.*, c.Name as Category_Name")
2: .From(@"Product p
3: inner join Category c on c.CategoryId = p.CategoryId")
4: .Where("p.ProductId > 0 and p.Name is not null")
5: .OrderBy("p.Name")
6: .Paging(1, 10).QueryMany();
调用 Paging(1, 10)，会返回最先检索到的10个Product。
Mapping
Automapping - 1:1 match between the database and the .NET object:
List<Product> products = Context.Sql(@"select *
from Product")
.QueryMany<Product>();
Automap to a custom collection:
ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>();
Automapping - Mismatch between the database and the .NET object, use the alias keyword in SQL:
Weakly typed:
List<Product> products = Context.Sql(@"select p.*,
c.CategoryId as Category_CategoryId,
c.Name as Category_Name
from Product p
inner join Category c on p.CategoryId = c.CategoryId")
.QueryMany<Product>();
Here the p.* which is ProductId and Name would be automapped to the properties Product.Name and Product.ProductId, and Category_CategoryId and Category_Name would be automapped to Product.Category.CategoryId and Product.Category.Name.
Custom mapping using dynamic:
List<Product> products = Context.Sql(@"select * from Product")
.QueryMany<Product>(Custom_mapper_using_dynamic);
public void Custom_mapper_using_dynamic(Product product, dynamic row)
{
product.ProductId = row.ProductId;
product.Name = row.Name;
}
Custom mapping using a datareader:
List<Product> products = Context.Sql(@"select * from Product")
.QueryMany<Product>(Custom_mapper_using_datareader);
public void Custom_mapper_using_datareader(Product product, IDataReader row)
{
product.ProductId = row.GetInt32("ProductId");
product.Name = row.GetString("Name");
}
Or if you have a complex entity type where you need to control how it is created then the QueryComplexMany/QueryComplexSingle can be used:
var products = new List<Product>();
Context.Sql("select * from Product").QueryComplexMany<Product>(products, MapComplexProduct);
private void MapComplexProduct(IList<Product> products, IDataReader reader)
{
var product = new Product();
product.ProductId = reader.GetInt32("ProductId");
product.Name = reader.GetString("Name");
products.Add(product);
}
Multiple result sets
FluentData supports multiple resultsets. This allows you to do multiple queries in a single database call. When this feature is used it's important to wrap the code inside a using statement as shown below in order to make sure that the database connection is closed.
using (var command = Context.MultiResultSql)
{
List<Category> categories = command.Sql(
@"select * from Category;
select * from Product;").QueryMany<Category>();
List<Product> products = command.QueryMany<Product>();
}
The first time the Query method is called it does a single query against the database. The second time the Query is called, FluentData already knows that it's running in a multiple result set mode, so it reuses the data retrieved from the first query.
Select data and Paging
A select builder exists to make selecting data and paging easy:
List<Product> products = Context.Select<Product>("p.*, c.Name as Category_Name")
.From(@"Product p
inner join Category c on c.CategoryId = p.CategoryId")
.Where("p.ProductId > 0 and p.Name is not null")
.OrderBy("p.Name")
.Paging(1, 10).QueryMany();
By calling Paging(1, 10) then the first 10 products will be returned.