海阔天空

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

转:SubSonic基本操作

SubSonic2.1版本 – 例出3种查询。

1
2
3
4
5
6
7
8
9
10
11
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();

关联查询

1
2
3
4
5
6
7
DataSet product = new Select(
  Product.ProductNameColumn,
  Category.CategoryNameColumn
  ).From<Product>()
  .InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
  .Where(Category.CategoryIDColumn).IsGreaterThan(4)
  .ExecuteDataSet();

注意. InnerJoin(f2, f1) 的使用方法。分页查询

1
2
3
4
List<Product> products = new Select().From<Product>()
    .Where(Product.ProductIDColumn).IsEqualTo(4)
    .Paged(1, 30)
    .ExecuteTypedList<Product>();

查询—返回对象

1
2
3
4
5
6
7
8
ExecuteReader();   返回DataReader
ExecuteScalar();   返回对象
ExecuteScalar<string>();  返回泛型对象
ExecuteSingle<Product>(); 返回表实体对象
ExecuteTypedList<Product>();  返回泛型表实休数据集
ExecuteDataSet();  返回DataSet
ExecuteJoinedDataSet<强数型数据集>(); 返回关联查询 DataSet
Execute(); 返回执行后,数据更新条数

SQL关键词

1
2
3
4
5
6
7
8
9
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#:

1
2
3
4
5
6
7
.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) 修改
1
2
3
4
5
6
7
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();

添加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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();

删除

1
2
3
int i = new Delete ().From<Product>()
  .Where(Product.ProductIDColumn). IsEqualTo(1)
  .Execute();

事务1

1
2
3
4
5
6
7
8
9
10
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

1
2
3
4
5
6
7
8
using (SharedDbConnectionScope sp = new SharedDbConnectionScope())
{
    using (TransactionScope scope = new TransactionScope())
    {
        // coding....
        scope.Complete();
    }
}

减少对ActiveRecord的依赖

1
2
3
4
5
6
7
8
9
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实体类

1
2
3
4
5
6
7
8
9
10
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); }
       }
   }

posted on   达芬奇  阅读(819)  评论(0编辑  收藏  举报

(评论功能已被禁用)
点击右上角即可分享
微信分享提示