fluentdata 访问mysql_FluentData入门（三）--Query

查询一组数据

返回一组dynamic对象(new in .NET 4.0)

1: List products = Context.Sql("select * from Product").QueryMany();

返回一组强类型对象

1: List products = Context.Sql("select * from Product").QueryMany();

返回一个自定义的Collection

1: ProductionCollection products = Context.Sql("select * from Product").QueryMany();

返回单个对象

返回一个dynamic对象

1: dynamic product = Context.Sql(@"select * from Product where ProductId = 1").QuerySingle();

返回一个强类型对象：

1: Product product = Context.Sql(@"select * from Product where ProductId = 1").QuerySingle();

返回一个DataTable：

1: DataTable products = Context.Sql("select * from Product").QuerySingle();

其实QueryMany和QuerySingle都可以用来返回DataTable，但考虑到QueryMany返回的是List，所以使用QuerySingle来返回DataTable更方便。

查询一个标量值

1: int numberOfProducts = Context.Sql(@"select count(*) from Product").QuerySingle();

返回一组标量值

1: List productIds = Context.Sql(@"select ProductId from Product").QueryMany();

设置查询参数：

索引顺序形式的参数

1: dynamic products = Context.Sql(@"select * from Product where ProductId = @0 or ProductId = @1", 1, 2).QueryMany();

或者

1: dynamic products = Context.Sql(@"select * from Product where ProductId = @0 or ProductId = @1").Parameters(1, 2).QueryMany();

名字形式的参数：

1: var command = Context.Sql(@"select @ProductName = Name from Product

2: where ProductId=1")

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

4: command.Execute();

5:

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

7:

8: List of parameters - in operator:

9: List ids = new List() { 1, 2, 3, 4 };

10:

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

12: where ProductId in(@0)", ids).QueryMany();

Output 参数：

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

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

3: .Parameter("ProductId1", 1)

4: .Parameter("ProductId2", 2)

5: .QueryMany();

Query for a list of items

Return a list of dynamic objects (new in .NET 4.0):

List products = Context.Sql("select * from Product").QueryMany();

Return a list of strongly typed objects:

List products = Context.Sql("select * from Product").QueryMany();

Return a list of strongly typed objects in a custom collection:

ProductionCollection products = Context.Sql("select * from Product").QueryMany();

Return a DataTable:

See Query for a single item.

Query for a single item

Return as a dynamic object:

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

where ProductId = 1").QuerySingle();

Return as a strongly typed object:

Product product = Context.Sql(@"select * from Product

where ProductId = 1").QuerySingle();

Return as a DataTable:

DataTable products = Context.Sql("select * from Product").QuerySingle();

Both QueryMany and QuerySingle can be called to return a DataTable, but since QueryMany returns a List then it's more convenient to call QuerySingle which returns just DataTable. Eventhough the method is called QuerySingle then multiple rows will still be returned as part of the DataTable.

Query for a scalar value

int numberOfProducts = Context.Sql(@"select count(*)

from Product").QuerySingle();

Query for a list of scalar values

List productIds = Context.Sql(@"select ProductId

from Product").QueryMany();

Parameters

Indexed parameters:

dynamic 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();

Named parameters:

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

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

.Parameter("ProductId1", 1)

.Parameter("ProductId2", 2)

.QueryMany();

Output parameter:

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 of parameters - in operator:

List ids = new List() { 1, 2, 3, 4 };

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

where ProductId in(@0)", ids).QueryMany();