SubSonic2.1版本 – 例出3种查询。
01 Product product = newSelect().From<Product>()
02 .Where(Product.ProductIDColumn).IsEqualTo(4)
03 .ExecuteSingle<Product>();
04 Product product = DB.Select().From<Product>()
05 .Where("ProductID").IsEqualTo(4)
06 .ExecuteSingle<Product>();
07 Query query = newQuery(Product.Schema);
08 query.WHERE("ProductID=4").ORDER_BY("ProductID asc");
09 IDataReader dr = Product.FetchByQuery(query);
10 DataSet product = new Query(Product.Schema).WHERE("ProductID=4").
11 ORDER_BY("Prod uctID asc").ExecuteDataSet();
关联查询
1 DataSet product = newSelect(
2 Product.ProductNameColumn,
3 Category.CategoryNameColumn
4 ).From<Product>()
5 .InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
6 .Where(Category.CategoryIDColumn).IsGreaterThan(4)
7 .ExecuteDataSet();
注意. InnerJoin(f2, f1) 的使用方法。分页查询
1 List<Product> products = newSelect().From<Product>()
2 .Where(Product.ProductIDColumn).IsEqualTo(4)
3 .Paged(1, 30)
4 .ExecuteTypedList<Product>();
查询—返回对象
view sourceprint?
1 ExecuteReader(); 返回DataReader
2 ExecuteScalar(); 返回对象
3 ExecuteScalar<string>(); 返回泛型对象
4 ExecuteSingle<Product>(); 返回表实体对象
5 ExecuteTypedList<Product>(); 返回泛型表实休数据集
6 ExecuteDataSet(); 返回DataSet
7 ExecuteJoinedDataSet<强数型数据集>(); 返回关联查询 DataSet
8 Execute(); 返回执行后,数据更新条数
SQL关键词
1 IsEqualTo(obj) // 等于 value
2 IsBetweenAnd(obj1, obj2) // [字段1] BETWEEN 值1 AND 值2
3 StartsWith // LIEK '1%‘
4 EndsWith // LIEK '%1‘
5 IsGreaterThan // [字段1] > 值1
6 IsGreaterThanOrEqualToIsGreaterThan // [字段1] >= 值1
7 IsLessThan // [字段1] < 值1
8 IsLessThanOrEqualToIsLessThan // [字段1] <= 值1
9 WhereExpression / AndExpression // Expression 表示括号
C#:
1 .Where("1").IsGreaterThan(1)
2 .And("2").IsGreaterThanOrEqualTo(2)
3 .AndExpression("3").IsLessThan(3)
4 .AndExpression("4").IsLessThanOrEqualTo(4).And("5").StartsWith("5")
5 .AndExpression("6").EndsWith("6")
6 .ExecuteSingle<Product>();
7 //SQL:WHERE 1 > @1AND 2 >= @2 AND (3 < @3) AND (4 <= @4 AND 5 LIKE @5) AND (6 LIKE @6) 修改
view sourceprint?
1 Product product = newProduct(4);
2 product.ProductName = "amo";
3 product.Save();
4 inti = newUpdate(Product.Schema)
5 .Set(Product.ProductNameColumn).EqualTo("Chai -- Amo")
6 .Where(Product.ProductIdColumn).IsEqualTo(1)
7 .Execute();
添加
01 Product product = newProduct();
02 product.ProductName = "my xiaoli";
03 product.SupplierID = 1;
04 product.CategoryID = 1;
05 product.Discontinued = true;
06 //............
07 product.Save();
08 inti = newInsert().Into(Product.Schema,
09 "ProductName",
10 "SupplierID",
11 "CategoryID",
12 "Discontinued“
13 )
14 .Values("my amo", 1, 1, 1).Execute();
删除
1 inti = newDelete ().From<Product>()
2 .Where(Product.ProductIDColumn). IsEqualTo(1)
3 .Execute();
事务1
01 List<Insert> queries = newList<Insert>();
02 queries.Add(newInsert().Into(Product.Schema).Values("product1"));
03 queries.Add(newInsert().Into(Product.Schema).Values("product2"));
04 queries.Add(newInsert().Into(Product.Schema).Values("product3"));
05 queries.Add(newInsert().Into(Product.Schema).Values("product4"));
06 queries.Add(newInsert().Into(Product.Schema).Values("product5"));
07 queries.Add(newInsert().Into(Product.Schema).Values("product6"));
08 queries.Add(newInsert().Into(Product.Schema).Values("product7"));
09 //execute in a transaction
10 SqlQuery.ExecuteTransaction(queries);
事务2
1 using(SharedDbConnectionScope sp = newSharedDbConnectionScope())
2 {
3 using(TransactionScope scope = newTransactionScope())
4 {
5 // coding....
6 scope.Complete();
7 }
8 }
减少对ActiveRecord的依赖
1 MyProduct product = newSelect(
2 Product.ProductNameColumn,
3 Category.CategoryNameColumn
4 ).From<Product>()
5 .InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
6 .Where(Category.CategoryIDColumn).IsGreaterThan(4)
7 .ExecuteSingle<MyProduct>();
8 stringcname = product.CategoryName;
9 stringpname = product.ProductName;
自定义MyProduct实体类
01 publicclassMyProduct : ActiveRecord<MyProduct> {
02 publicstringCategoryName {
03 get{ returnGetColumnValue<string>(Category.Columns.CategoryName); }
04 set{ SetColumnValue(Category.Columns.CategoryName, value); }
05 }
06 publicstringProductName {
07 get{ returnGetColumnValue<string>(Product.Columns.ProductName); }
08 set{ SetColumnValue(Product.Columns.ProductName, value); }
09 }
10 }
01 Product product = newSelect().From<Product>()
02 .Where(Product.ProductIDColumn).IsEqualTo(4)
03 .ExecuteSingle<Product>();
04 Product product = DB.Select().From<Product>()
05 .Where("ProductID").IsEqualTo(4)
06 .ExecuteSingle<Product>();
07 Query query = newQuery(Product.Schema);
08 query.WHERE("ProductID=4").ORDER_BY("ProductID asc");
09 IDataReader dr = Product.FetchByQuery(query);
10 DataSet product = new Query(Product.Schema).WHERE("ProductID=4").
11 ORDER_BY("Prod uctID asc").ExecuteDataSet();
关联查询
1 DataSet product = newSelect(
2 Product.ProductNameColumn,
3 Category.CategoryNameColumn
4 ).From<Product>()
5 .InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
6 .Where(Category.CategoryIDColumn).IsGreaterThan(4)
7 .ExecuteDataSet();
注意. InnerJoin(f2, f1) 的使用方法。分页查询
1 List<Product> products = newSelect().From<Product>()
2 .Where(Product.ProductIDColumn).IsEqualTo(4)
3 .Paged(1, 30)
4 .ExecuteTypedList<Product>();
查询—返回对象
view sourceprint?
1 ExecuteReader(); 返回DataReader
2 ExecuteScalar(); 返回对象
3 ExecuteScalar<string>(); 返回泛型对象
4 ExecuteSingle<Product>(); 返回表实体对象
5 ExecuteTypedList<Product>(); 返回泛型表实休数据集
6 ExecuteDataSet(); 返回DataSet
7 ExecuteJoinedDataSet<强数型数据集>(); 返回关联查询 DataSet
8 Execute(); 返回执行后,数据更新条数
SQL关键词
1 IsEqualTo(obj) // 等于 value
2 IsBetweenAnd(obj1, obj2) // [字段1] BETWEEN 值1 AND 值2
3 StartsWith // LIEK '1%‘
4 EndsWith // LIEK '%1‘
5 IsGreaterThan // [字段1] > 值1
6 IsGreaterThanOrEqualToIsGreaterThan // [字段1] >= 值1
7 IsLessThan // [字段1] < 值1
8 IsLessThanOrEqualToIsLessThan // [字段1] <= 值1
9 WhereExpression / AndExpression // Expression 表示括号
C#:
1 .Where("1").IsGreaterThan(1)
2 .And("2").IsGreaterThanOrEqualTo(2)
3 .AndExpression("3").IsLessThan(3)
4 .AndExpression("4").IsLessThanOrEqualTo(4).And("5").StartsWith("5")
5 .AndExpression("6").EndsWith("6")
6 .ExecuteSingle<Product>();
7 //SQL:WHERE 1 > @1AND 2 >= @2 AND (3 < @3) AND (4 <= @4 AND 5 LIKE @5) AND (6 LIKE @6) 修改
view sourceprint?
1 Product product = newProduct(4);
2 product.ProductName = "amo";
3 product.Save();
4 inti = newUpdate(Product.Schema)
5 .Set(Product.ProductNameColumn).EqualTo("Chai -- Amo")
6 .Where(Product.ProductIdColumn).IsEqualTo(1)
7 .Execute();
添加
01 Product product = newProduct();
02 product.ProductName = "my xiaoli";
03 product.SupplierID = 1;
04 product.CategoryID = 1;
05 product.Discontinued = true;
06 //............
07 product.Save();
08 inti = newInsert().Into(Product.Schema,
09 "ProductName",
10 "SupplierID",
11 "CategoryID",
12 "Discontinued“
13 )
14 .Values("my amo", 1, 1, 1).Execute();
删除
1 inti = newDelete ().From<Product>()
2 .Where(Product.ProductIDColumn). IsEqualTo(1)
3 .Execute();
事务1
01 List<Insert> queries = newList<Insert>();
02 queries.Add(newInsert().Into(Product.Schema).Values("product1"));
03 queries.Add(newInsert().Into(Product.Schema).Values("product2"));
04 queries.Add(newInsert().Into(Product.Schema).Values("product3"));
05 queries.Add(newInsert().Into(Product.Schema).Values("product4"));
06 queries.Add(newInsert().Into(Product.Schema).Values("product5"));
07 queries.Add(newInsert().Into(Product.Schema).Values("product6"));
08 queries.Add(newInsert().Into(Product.Schema).Values("product7"));
09 //execute in a transaction
10 SqlQuery.ExecuteTransaction(queries);
事务2
1 using(SharedDbConnectionScope sp = newSharedDbConnectionScope())
2 {
3 using(TransactionScope scope = newTransactionScope())
4 {
5 // coding....
6 scope.Complete();
7 }
8 }
减少对ActiveRecord的依赖
1 MyProduct product = newSelect(
2 Product.ProductNameColumn,
3 Category.CategoryNameColumn
4 ).From<Product>()
5 .InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
6 .Where(Category.CategoryIDColumn).IsGreaterThan(4)
7 .ExecuteSingle<MyProduct>();
8 stringcname = product.CategoryName;
9 stringpname = product.ProductName;
自定义MyProduct实体类
01 publicclassMyProduct : ActiveRecord<MyProduct> {
02 publicstringCategoryName {
03 get{ returnGetColumnValue<string>(Category.Columns.CategoryName); }
04 set{ SetColumnValue(Category.Columns.CategoryName, value); }
05 }
06 publicstringProductName {
07 get{ returnGetColumnValue<string>(Product.Columns.ProductName); }
08 set{ SetColumnValue(Product.Columns.ProductName, value); }
09 }
10 }