查询
SubSonic2.1版本 – 例出3种查询。
Product product
=
new
Select().From
<
Product
>
()
.Where(Product.ProductIDColumn).IsEqualTo(
4
)
.ExecuteSingle
<
Product
>
();
Product product
=
DB.Select().From
<
Product
>
()
.Where(
"
ProductID
"
).IsEqualTo(
4
)
.ExecuteSingle
<
Product
>
();
Query query
=
new
Query(Product.Schema);
query.WHERE(
"
ProductID=4
"
).ORDER_BY(
"
ProductID asc
"
);
IDataReader dr
=
Product.FetchByQuery(query);
DataSet product
=
new
Query(Product.Schema).WHERE(
"
ProductID=4
"
).
ORDER_BY(
"
Prod uctID asc
"
).ExecuteDataSet();
关联查询
DataSet product
=
new
Select(
Product.ProductNameColumn,
Category.CategoryNameColumn
).From
<
Product
>
()
.InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
.Where(Category.CategoryIDColumn).IsGreaterThan(
4
)
.ExecuteDataSet();
注意. InnerJoin(f2, f1) 的使用方法。
分页查询
List
<
Product
>
products
=
new
Select().From
<
Product
>
()
.Where(Product.ProductIDColumn).IsEqualTo(
4
)
.Paged(
1
,
30
)
.ExecuteTypedList
<
Product
>
();
查询—返回对象
ExecuteReader(); 返回DataReader
ExecuteScalar(); 返回对象
ExecuteScalar
<
string
>
(); 返回泛型对象
ExecuteSingle
<
Product
>
(); 返回表实体对象
ExecuteTypedList
<
Product
>
(); 返回泛型表实休数据集
ExecuteDataSet(); 返回DataSet
ExecuteJoinedDataSet
<
强数型数据集
>
(); 返回关联查询 DataSet
Execute(); 返回执行后,数据更新条数
SQL关键词
IsEqualTo(obj)
//
等于 value
IsBetweenAnd(obj1, obj2)
//
[字段1] BETWEEN 值1 AND 值2
StartsWith
//
LIEK '1%‘
EndsWith
//
LIEK '%1‘
IsGreaterThan
//
[字段1] > 值1
IsGreaterThanOrEqualToIsGreaterThan
//
[字段1] >= 值1
IsLessThan
//
[字段1] < 值1
IsLessThanOrEqualToIsLessThan
//
[字段1] <= 值1
WhereExpression
/
AndExpression
//
Expression 表示括号
C#:
.Where(
"
1
"
).IsGreaterThan(
1
)
.And(
"
2
"
).IsGreaterThanOrEqualTo(
2
)
.AndExpression(
"
3
"
).IsLessThan(
3
)
.AndExpression(
"
4
"
).IsLessThanOrEqualTo(
4
).And(
"
5
"
).StartsWith(
"
5
"
)
.AndExpression(
"
6
"
).EndsWith(
"
6
"
)
.ExecuteSingle
<
Product
>
();
SQL:WHERE
1
>
@1AND
2
>=
@
2
AND (
3
<
@
3
) AND (
4
<=
@
4
AND
5
LIKE @
5
) AND (
6
LIKE @
6
)
修改
Product product
=
new
Product(
4
);
product.ProductName
=
"
amo
"
;
product.Save();
int
i
=
new
Update(Product.Schema)
.Set(Product.ProductNameColumn).EqualTo(
"
Chai -- Amo
"
)
.Where(Product.ProductIdColumn).IsEqualTo(
1
)
.Execute();
添加
Product product
=
new
Product();
product.ProductName
=
"
my xiaoli
"
;
product.SupplierID
=
1
;
product.CategoryID
=
1
;
product.Discontinued
=
true
;
//
product.Save();
int
i
=
new
Insert().Into(Product.Schema,
"
ProductName
"
,
"
SupplierID
"
,
"
CategoryID
"
,
"
Discontinued“
)
.Values(
"
my amo
"
,
1
,
1
,
1
).Execute();
删除
int
i
=
new
Delete ().From
<
Product
>
()
.Where(Product.ProductIDColumn). IsEqualTo(
1
)
.Execute();
事务1
List
<
Insert
>
queries
=
new
List
<
Insert
>
();
queries.Add(
new
Insert().Into(Product.Schema).Values(
"
product1
"
));
queries.Add(
new
Insert().Into(Product.Schema).Values(
"
product2
"
));
queries.Add(
new
Insert().Into(Product.Schema).Values(
"
product3
"
));
queries.Add(
new
Insert().Into(Product.Schema).Values(
"
product4
"
));
queries.Add(
new
Insert().Into(Product.Schema).Values(
"
product5
"
));
queries.Add(
new
Insert().Into(Product.Schema).Values(
"
product6
"
));
queries.Add(
new
Insert().Into(Product.Schema).Values(
"
product7
"
));
//
execute in a transaction
SqlQuery.ExecuteTransaction(queries);
事务2
using
(SharedDbConnectionScope sp
=
new
SharedDbConnectionScope())
{
using
(TransactionScope scope
=
new
TransactionScope())
{
//
coding.
scope.Complete();
}
}
减少对ActiveRecord的依赖
MyProduct product
=
new
Select(
Product.ProductNameColumn,
Category.CategoryNameColumn
).From
<
Product
>
()
.InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
.Where(Category.CategoryIDColumn).IsGreaterThan(
4
)
.ExecuteSingle
<
MyProduct
>
();
string
cname
=
product.CategoryName;
string
pname
=
product.ProductName;
自定义MyProduct实体类
public
class
MyProduct : ActiveRecord
<
MyProduct
>
{
public
string
CategoryName {
get
{
return
GetColumnValue
<
string
>
(Category.Columns.CategoryName); }
set
{ SetColumnValue(Category.Columns.CategoryName, value); }
}
public
string
ProductName {
get
{
return
GetColumnValue
<
string
>
(Product.Columns.ProductName); }
set
{ SetColumnValue(Product.Columns.ProductName, value); }
}
}
转载于:https://www.cnblogs.com/zhdonghu/archive/2009/06/10/1500189.html