Subsonic queries查询帮助
来源:互联网 发布:政府资助 知乎 编辑:程序博客网 时间:2024/05/02 04:40
目前开发的一个小项目中尝试使用Subsonic的常常会使用到Query,把一些常用的方法收集以备查阅。
下面是来自Subsonic官方网站的一些例子,例子基于Northwind数据库
Simple Select with string columns
1
2
3
4
int
records =
new
Select(
"productID"
).
From(
"Products"
).GetRecordCount();
Assert.IsTrue(records == 77);
Simple Select with typed columns
1
2
3
int
records =
new
Select(Product.ProductIDColumn, Product.ProductNameColumn).
From<Product>().GetRecordCount();
Assert.IsTrue(records == 77);
Returning a Single object(返回简单实体)
1
2
3
Product p =
new
Select().From<Product>().
Where(
"ProductID"
).IsEqualTo(1).ExecuteSingle<Product>();
Assert.IsNotNull(p);
Returning all columns(返回所有列)
1
2
int
records =
new
Select().From(
"Products"
).GetRecordCount();
Assert.IsTrue(records == 77);
Simple Where(简单Where语句)
1
2
3
int
records =
new
Select().From(
"Products"
).
Where(
"categoryID"
).IsEqualTo(5).GetRecordCount();
Assert.AreEqual(7, records);
Simple Where with And (as Collection)(带有And的Where语句,返回集合)
1
2
3
4
5
ProductCollection products =
DB.Select().From(
"Products"
)
.Where(
"categoryID"
).IsEqualTo(5)
.And(
"productid"
).IsGreaterThan(50)
.ExecuteAsCollection<ProductCollection>();
Simple Inner Join(内联接)
1
2
3
SubSonic.SqlQuery q =
new
Select(
"productid"
).From(OrderDetail.Schema)
.InnerJoin(Product.Schema)
.Where(
"CategoryID"
).IsEqualTo(5);
Simple Join With Table Enum
1
2
3
SubSonic.SqlQuery q =
new
Select().From(Tables.OrderDetail)
.InnerJoin(Tables.Product)
.Where(
"CategoryID"
).IsEqualTo(5);
Multiple Joins As Collection(多级联接)
1
2
3
4
5
6
7
CustomerCollection customersByCategory =
new
Select()
.From(Customer.Schema)
.InnerJoin(Order.Schema)
.InnerJoin(OrderDetail.OrderIDColumn, order.OrderIDColumn)
.InnerJoin(Product.ProductIDColumn, orderDetail.ProductIDColumn)
.Where(
"CategoryID"
).IsEqualTo(5)
.ExecuteAsCollection<CustomerCollection>();
Left Outer Join With Generics(左外联接)
1
2
3
SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy(
"CompanyName"
))
.From<Customer>()
.LeftOuterJoin<Order>();
Left Outer Join With Schema()
1
2
3
SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy(
"CompanyName"
))
.From(Customer.Schema)
.LeftOuterJoin(Order.CustomerIDColumn, Customer.CustomerIDColumn);
Left Outer Join With Magic Strings
1
2
3
SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy(
"CompanyName"
))
.From(
"Customers"
)
.LeftOuterJoin(
"Orders"
);
Simple Select With Collection Result
1
2
ProductCollection p = Select.AllColumnsFrom<Product>()
.ExecuteAsCollection<ProductCollection>();
Simple Select With LIKE
1
2
3
4
5
ProductCollection p = DB.Select()
.From(Product.Schema)
.InnerJoin(Category.Schema)
.Where(
"CategoryName"
).Like(
"c%"
)
.ExecuteAsCollection<ProductCollection>();
Using Nested Where/And/Or
1
2
3
4
ProductCollection products = Select.AllColumnsFrom<Product>()
.WhereExpression(
"categoryID"
).IsEqualTo(5).And(
"productid"
).IsGreaterThan(10)
.OrExpression(
"categoryID"
).IsEqualTo(2).And(
"productID"
).IsBetweenAnd(2, 5)
.ExecuteAsCollection<ProductCollection>();
1
2
3
4
ProductCollection products = Select.AllColumnsFrom<Product>()
.WhereExpression(
"categoryID"
).IsEqualTo(5).And(
"productid"
).IsGreaterThan(10)
.Or(
"categoryID"
).IsEqualTo(2).AndExpression(
"productID"
).IsBetweenAnd(2, 5)
.ExecuteAsCollection<ProductCollection>();
Simple Paged Query(分页查询)
1
2
SubSonic.SqlQuery q = Select.AllColumnsFrom<Product>().
Paged(1, 20).Where(
"productid"
).IsLessThan(100);
Paged Query With Join(带联接的分页查询)
1
2
SubSonic.SqlQuery q =
new
Select(
"ProductId"
,
"ProductName"
,
"CategoryName"
).
From(
"Products"
).InnerJoin(Category.Schema).Paged(1, 20);
Paged View
1
SubSonic.SqlQuery q =
new
Select().From(Invoice.Schema).Paged(1, 20);
Simple IN Query(in查询)
1
2
3
4
int
records =
new
Select().From(Product.Schema)
.Where(
"productid"
).In(1, 2, 3, 4, 5)
.GetRecordCount();
Assert.IsTrue(records == 5);
Using IN With Nested Select
1
2
3
4
5
6
7
int
records = Select.AllColumnsFrom<Product>()
.Where(
"productid"
)
.In(
new
Select(
"productid"
).From(Product.Schema)
.Where(
"categoryid"
).IsEqualTo(5)
)
.GetRecordCount();(返回记录数)
Using Multiple INs
1
2
3
4
SubSonic.SqlQuery query =
new
Select()
.From(Product.Schema)
.Where(Product.CategoryIDColumn).In(2)
.And(Product.SupplierIDColumn).In(3);
select * from table where column1 = 1 and (column2 = 2 or column2 = 3)转为Select().From<product>.Where(...).AndExpression(column2).IsEqualTo(2).Or(column2).IsEqualTo(3)想查看更多Subsonic相关的资料可以到http://subsonicproject.com/官方网站。
- Subsonic queries查询帮助
- Subsonic 查询
- subsonic查询语句例子
- SubSonic查询语句例子
- subsonic 级联查询
- Subsonic的复杂查询
- subsonic
- 条件查询(Criteria Queries)
- 条件查询(Criteria Queries)
- 条件查询(Criteria Queries)
- 媒介查询-Media Queries
- Media Queries媒体查询
- MT n95 Queries查询
- MT 195 Queries查询
- subsonic 视频,配置,示例,查询工具,官方网站,学习资料,下载
- Hibernate条件查询(Criteria Queries)
- Hibernate 条件查询(Criteria Queries)
- hibernate 条件查询(Criteria Queries)
- c++ STL Map 用法 小于号重载和仿函数定义
- php 函数嵌套
- arcmap_polygon转坐标文件
- PowerDesigner生成sql语句和从SQL语句生成表的反向工程
- keil中函数变量定位方法
- Subsonic queries查询帮助
- Java synchronized详解
- Xcode如何删除最近打开的工程?
- oealce数据库连接
- Linux的环境变量
- cleartool setview 工具会开启一个子shell
- Ext.grid.CheckboxSelectionModel
- SQL语句操作大全(本
- 服务器内存溢出处理