转: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); } } } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步