查询一组数据
返回一组dynamic对象(new in .NET 4.0)
1: List<dynamic> products = Context.Sql("select * from Product").QueryMany<dynamic>();
返回一组强类型对象
1: List<Product> products = Context.Sql("select * from Product").QueryMany<Product>();
返回一个自定义的Collection
1: ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>();
返回单个对象
返回一个dynamic对象
1: dynamic product = Context.Sql(@"select * from Product where ProductId = 1").QuerySingle<dynamic>();
返回一个强类型对象：
1: Product product = Context.Sql(@"select * from Product where ProductId = 1").QuerySingle<Product>();
返回一个DataTable：
1: DataTable products = Context.Sql("select * from Product").QuerySingle<DataTable>();
其实QueryMany<DataTable>和QuerySingle<DataTable>都可以用来返回DataTable，但考虑到QueryMany<DataTable>返回的是List<DataTable>，所以使用QuerySingle<DataTable>来返回DataTable更方便。
查询一个标量值
1: int numberOfProducts = Context.Sql(@"select count(*) from Product").QuerySingle<int>();
返回一组标量值
1: List<int> productIds = Context.Sql(@"select ProductId from Product").QueryMany<int>();
设置查询参数：
索引顺序形式的参数
1: dynamic products = Context.Sql(@"select * from Product where ProductId = @0 or ProductId = @1", 1, 2).QueryMany<dynamic>();
或者
1: dynamic products = Context.Sql(@"select * from Product where ProductId = @0 or ProductId = @1").Parameters(1, 2).QueryMany<dynamic>();
名字形式的参数：
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<string>("ProductName");
7:
8: List of parameters - in operator:
9: List<int> ids = new List<int>() { 1, 2, 3, 4 };
10:
11: dynamic products = Context.Sql(@"select * from Product
12: where ProductId in(@0)", ids).QueryMany<dynamic>();
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<dynamic>();
Query for a list of items
Return a list of dynamic objects (new in .NET 4.0):
List<dynamic> products = Context.Sql("select * from Product").QueryMany<dynamic>();
Return a list of strongly typed objects:
List<Product> products = Context.Sql("select * from Product").QueryMany<Product>();
Return a list of strongly typed objects in a custom collection:
ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>();
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<dynamic>();
Return as a strongly typed object:
Product product = Context.Sql(@"select * from Product
where ProductId = 1").QuerySingle<Product>();
Return as a DataTable:
DataTable products = Context.Sql("select * from Product").QuerySingle<DataTable>();
Both QueryMany<DataTable> and QuerySingle<DataTable> can be called to return a DataTable, but since QueryMany returns a List<DataTable> 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<int>();
Query for a list of scalar values
List<int> productIds = Context.Sql(@"select ProductId
from Product").QueryMany<int>();
Parameters
Indexed parameters:
dynamic products = Context.Sql(@"select * from Product
where ProductId = @0 or ProductId = @1", 1, 2).QueryMany<dynamic>();
or:
dynamic products = Context.Sql(@"select * from Product
where ProductId = @0 or ProductId = @1")
.Parameters(1, 2).QueryMany<dynamic>();
Named parameters:
dynamic products = Context.Sql(@"select * from Product
where ProductId = @ProductId1 or ProductId = @ProductId2")
.Parameter("ProductId1", 1)
.Parameter("ProductId2", 2)
.QueryMany<dynamic>();
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<string>("ProductName");
List of parameters - in operator:
List<int> ids = new List<int>() { 1, 2, 3, 4 };
dynamic products = Context.Sql(@"select * from Product
where ProductId in(@0)", ids).QueryMany<dynamic>();