SubSonic基本操作

//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); } } }</