fluentdata 访问mysql_Fluentdata详解

返回 dynamic类型的List 集合:List products = Context.Sql("select * from Product").QueryMany();

返回强类型的List 集合List products = Context.Sql("select * from Product").QueryMany();

返回一个自定义类型的集合类型:ProductionCollection products = Context.Sql("select * from Product").QueryMany();

返回单个动态对象数据表dynamic product = Context.Sql(@"select * from Product

where ProductId = 1").QuerySingle();

返回一个强类型对象Product product = Context.Sql(@"select * from Product

where ProductId = 1").QuerySingle();

返回一个DataTable对象，QueryMany< DataTable > and QuerySingle 都可以返回DataTable, 只不过QuerMany返回的是 List< DataTable >DataTable products = Context.Sql("select * from Product").QuerySingle();

返回一个 int 类型int numberOfProducts = Context.Sql(@"select count(*)

from Product").QuerySingle();

返回一个List< int >List productIds = Context.Sql(@"select ProductId

from Product").QueryMany();

索引传参SQLdynamic products = Context.Sql(@"select * from Product

where ProductId = @0 or ProductId = @1", 1, 2).QueryMany();

or:

dynamic products = Context.Sql(@"select * from Product

where ProductId = @0 or ProductId = @1")

.Parameters(1, 2).QueryMany();

参数名传参dynamic products = Context.Sql(@"select * from Product

where ProductId = @ProductId1 or ProductId = @ProductId2")

.Parameter("ProductId1", 1)

.Parameter("ProductId2", 2)

.QueryMany();

输出参数var command = Context.Sql(@"select @ProductName = Name from Product

where ProductId=1")

.ParameterOut("ProductName", DataTypes.String, 100);

command.Execute();

string productName = command.ParameterValue("ProductName");

List 类型参数,请注意，不要在(…)语法中留下任何空格List ids = new List() { 1, 2, 3, 4 };

dynamic products = Context.Sql(@"select * from Product where ProductId in(@0)", ids).QueryMany();

like 模糊查询:string cens = "%abc%";

Context.Sql("select * from Product where ProductName like @0",cens);

实体集合自动映射List products = Context.Sql(@"select *

from Product")

.QueryMany();

自定义映射对象ProductionCollection products = Context.Sql("select * from Product").QueryMany();

也可以将链表查询结果集映射到自定义对象集合List 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();

使用动态的自定义映射List products = Context.Sql(@"select * from Product")

.QueryMany(Custom_mapper_using_dynamic);

public void Custom_mapper_using_dynamic(Product product, dynamic row)

{

product.ProductId = row.ProductId;

product.Name = row.Name;

}

基于 datareader 的动态的自定义映射List products = Context.Sql(@"select * from Product")

.QueryMany(Custom_mapper_using_datareader);

public void Custom_mapper_using_datareader(Product product, IDataReader row)

{

product.ProductId = row.GetInt32("ProductId");

product.Name = row.GetString("Name");

}

如果你有一个复杂的实体类型需要控制它的创建方式，那么可以使用 QueryComplexMany/QueryComplexSinglevar products = new List();

Context.Sql("select * from Product").QueryComplexMany(products, MapComplexProduct);

private void MapComplexProduct(IList products, IDataReader reader)

{

var product = new Product();

product.ProductId = reader.GetInt32("ProductId");

product.Name = reader.GetString("Name");

products.Add(product);

}

支持多个查询结果表映射成一个实体对象集，且单次链接中执行多次查询using (var command = Context.MultiResultSql)

{

List categories = command.Sql(

@"select * from Category;

select * from Product;").QueryMany();

List products = command.QueryMany();

}

链表查询并支持分页List products = Context.Select("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();

插入数据，返回自增IDint productId = Context.Sql(@"insert into Product(Name, CategoryId)

values(@0, @1);")

.Parameters("The Warren Buffet Way", 1)

.ExecuteReturnLastId();

int productId = Context.Insert("Product")

.Column("Name", "The Warren Buffet Way")

.Column("CategoryId", 1)

.ExecuteReturnLastId();

使用自动应用的生成器Product product = new Product();

product.Name = "The Warren Buffet Way";

product.CategoryId = 1;

product.ProductId = Context.Insert("Product", product)

.AutoMap(x => x.ProductId)

.ExecuteReturnLastId();

更新操作，返回受影响行数int rowsAffected = Context.Sql(@"update Product set Name = @0

where ProductId = @1")

.Parameters("The Warren Buffet Way", 1)

.Execute();

int rowsAffected = Context.Update("Product")

.Column("Name", "The Warren Buffet Way")

.Where("ProductId", 1)

.Execute();

使用自定义映射来更新实体Product product = Context.Sql(@"select * from Product

where ProductId = 1")

.QuerySingle();

product.Name = "The Warren Buffet Way";

int rowsAffected = Context.Update("Product", product)

.AutoMap(x => x.ProductId)

.Where(x => x.ProductId)

.Execute();

自定义插入或更新列操作var product = new Product();

product.Name = "The Warren Buffet Way";

product.CategoryId = 1;

var insertBuilder = Context.Insert("Product", product).Fill(FillBuilder);

var updateBuilder = Context.Update("Product", product).Fill(FillBuilder);

public void FillBuilder(IInsertUpdateBuilder builder)

{

builder.Column(x => x.Name);

builder.Column(x => x.CategoryId);

}

删除操作int rowsAffected = Context.Sql(@"delete from Product

where ProductId = 1")

.Execute();

int rowsAffected = Context.Delete("Product")

.Where("ProductId", 1)

.Execute();

执行存储过程var rowsAffected = Context.Sql("ProductUpdate")

.CommandType(DbCommandTypes.StoredProcedure)

.Parameter("ProductId", 1)

.Parameter("Name", "The Warren Buffet Way")

.Execute();

var rowsAffected = Context.StoredProcedure("ProductUpdate")

.Parameter("Name", "The Warren Buffet Way")

.Parameter("ProductId", 1).Execute();

骚操作1var product = Context.Sql("select * from Product where ProductId = 1")

.QuerySingle();

product.Name = "The Warren Buffet Way";

var rowsAffected = Context.StoredProcedure("ProductUpdate", product)

.AutoMap(x => x.CategoryId).Execute();

骚操作2var product = Context.Sql("select * from Product where ProductId = 1")

.QuerySingle();

product.Name = "The Warren Buffet Way";

var rowsAffected = Context.StoredProcedure("ProductUpdate", product)

.Parameter(x => x.ProductId)

.Parameter(x => x.Name).Execute();

事务使用using (var context = Context.UseTransaction(true))

{

context.Sql("update Product set Name = @0 where ProductId = @1")

.Parameters("The Warren Buffet Way", 1)

.Execute();

context.Sql("update Product set Name = @0 where ProductId = @1")

.Parameters("Bill Gates Bio", 2)

.Execute();

context.Commit();

}

在查询一个实体对象集时如果需要再创建实体时就进行一些特殊的操作可以自定义实体工厂来满足你的需求List products = Context.EntityFactory(new CustomEntityFactory())

.Sql("select * from Product")

.QueryMany();

public class CustomEntityFactory : IEntityFactory

{

public virtual object Resolve(Type type)

{

return Activator.CreateInstance(type);

}

}