NDO中的ActiveRecord 简介 2——强类型的活动记录

Region和Products表的SCHEMA代码，Region表中的主键时赋值方式，Products中的主键是自动增一的

CREATE   TABLE   [ dbo ] . [ Region ]  (
     [ RegionID ]   [ int ]   PRIMARY   KEY ,
     [ RegionDescription ]   [ nchar ]  ( 50 )  NOT   NULL  
) 
CREATE   TABLE   [ dbo ] . [ Products ]  (
     [ ProductID ]   [ int ]   IDENTITY  ( 1 ,  1 )  PRIMARY   KEY ,
     [ ProductName ]   [ nvarchar ]  ( 40 )  NULL  ,
     [ SupplierID ]   [ int ]   NULL  ,
     [ CategoryID ]   [ int ]   NULL  ,
     [ QuantityPerUnit ]   [ nvarchar ]  ( 20 )  NULL  ,
     [ UnitPrice ]   [ money ]   NULL  ,
     [ UnitsInStock ]   [ smallint ]   NULL  ,
     [ UnitsOnOrder ]   [ smallint ]   NULL  ,
     [ ReorderLevel ]   [ smallint ]   NULL  ,
     [ Discontinued ]   [ bit ]   NOT   NULL  
) 

强类型活动记录类Region和Product代码:Region的ID属性包含get和set,而Product的ProductID属性只需要get即可，因为Product类映射的Products表的主键是自动增一的。

public   class  Region:ActiveRecord
1_34_257_Closed_Text.style.display='none'; Codehighlighter1_34_257_Open_Image.style.display='inline'; Codehighlighter1_34_257_Open_Text.style.display='inline';" src="/Images/OutliningIndicators/ContractedBlock.gif" align=top>     {
        public new int ID
        {
            get { return (int)base.ID;}
            set { base.ID = value;}
        }

        public string Description
        {
            get{ return (string)this["RegionDescription"];}
            set { this["RegionDescription"] = value;}
        }

    }

     public   class  Product:ActiveRecord 
     {
        public Product():base("Products"){}
        

        public int ProductID 
        {
            get 
            {
                return (int)this.ID;
            }
        }

        public string ProductName 
        {
            get 
            {
                return this["ProductName"].ToString();
            }
            set 
            {
                this["ProductName"] = value;
            }
        }

        public int SupplierID 
        {
            get 
            {
                return (int)this.GetValue("SupplierID");
            }
            set 
            {
                this.SetValue("SupplierID", value);
            }
        }

        public int CategoryID 
        {
            get 
            {
                return (int)this.GetValue("CategoryID");
            }
            set 
            {
                this.SetValue("CategoryID", value);
            }
        }

        public string QuantityPerUnit 
        {
            get 
            {
                return this.GetValue("QuantityPerUnit").ToString();
            }
            set 
            {
                this.SetValue("QuantityPerUnit", value);
            }
        }

        public decimal UnitPrice 
        {
            get 
            {
                return (decimal)this.GetValue("UnitPrice");
            }
            set 
            {
                this.SetValue("UnitPrice", value);
            }
        }

        public short UnitsInStock 
        {
            get 
            {
                return (short)this.GetValue("UnitsInStock");
            }
            set 
            {
                this.SetValue("UnitsInStock", value);
            }
        }

        public short UnitsOnOrder 
        {
            get 
            {
                return (short)this.GetValue("UnitsOnOrder");
            }
            set 
            {
                this.SetValue("UnitsOnOrder", value);
            }
        }

        public short ReorderLevel 
        {
            get 
            {
                return (short)this.GetValue("ReorderLevel");
            }
            set 
            {
                this.SetValue("ReorderLevel", value);
            }
        }

        public bool Discontinued 
        {
            get 
            {
                return Convert.ToBoolean(this.GetValue("Discontinued"));
            }
            set 
            {
                this.SetValue("Discontinued", value);
            }
        }

应用例子：

   1 数据加载Load ,或LoadByKey返回数据库中的一条记录，如果有多条只加载第一条记录

Product product  =   new  Product();
product.LoadByKey( 1 );
this .Text  =  product.ProductName  +   " -------- "   +  product.SupplierID.ToString();

 

SQL  输出

SELECT  
     [ ProductID ] ,
     [ ProductName ] ,
     [ SupplierID ] ,
     [ CategoryID ] ,
     [ QuantityPerUnit ] ,
     [ UnitPrice ] ,
     [ UnitsInStock ] ,
     [ UnitsOnOrder ] ,
     [ ReorderLevel ] ,
     [ Discontinued ]
FROM
     [ Products ]     
WHERE
     [ ProductID ]    =  @ProductID

2 数据加载 2

Product product  =   new   Products() ;
product.ID  =   1 ;
product.Load();
this.Text = product.ProductName + "--------" + product.SupplierID.ToString();

SQL  输出同上

3 返回所有记录

Product product  =   new  Product();
IList products  =  product.Find();
    foreach (Product p  in  products)
             {
                Console.WriteLine(p.ProductName);
            }

SQL 输出

SELECT  
     [ ProductID ] ,
     [ ProductName ] ,
     [ SupplierID ] ,
     [ CategoryID ] ,
     [ QuantityPerUnit ] ,
     [ UnitPrice ] ,
     [ UnitsInStock ] ,
     [ UnitsOnOrder ] ,
     [ ReorderLevel ] ,
     [ Discontinued ]
FROM
     [ Products ]

4 分页操作，取第3页，每页5条记录

Product product  =   new  Product();
IList products  =  product.Find( 2 , 5 );
foreach (Product p  in  products)
{
    Console.WriteLine(p.ProductName);
}

SQL  输出

SELECT   TOP   5   *    FROM  Products  WHERE  ProductID  >  ( SELECT   MAX (ProductID)  FROM  (  SELECT   TOP   10  ProductID  FROM  Products  ORDER   BY  ProductID  ASC )  AS  TMP)  ORDER   BY  ProductID  ASC

4.2 分页操作2，取第3页，每页5条记录

Product product  =   new  Product();
product.Discontinued  = false ;
IList products  =  product.Find( 2 , 5 );
foreach (Product p  in  products)
{
                Console.WriteLine(p.ProductName);
}

SQL  输出

SELECT   TOP   5   *    FROM  (  SELECT   *   FROM   [ Products ]
WHERE
     [ Discontinued ] = @Discontinued )  AS  TMP  WHERE  TMP.ProductID  >  ( SELECT   MAX (TMP3.ProductID)  FROM  (  SELECT   TOP   10  TMP2.ProductID  FROM  (  SELECT   *   FROM
     [ Products ]
WHERE
     [ Discontinued ] = @Discontinued )  AS  TMP2  ORDER   BY  TMP2.ProductID  ASC )  AS  TMP3)  ORDER   BY  TMP.ProductID  ASC

5  多条件查询

Product product  =   new  Product();
product.SupplierID  =   7 ;
product.CategoryID  =   3 ;
IList products  =  product.Find();
foreach (Product p  in  products)
{
    Console.WriteLine(p.ProductName);
}

SQL  输出

SELECT  
     [ ProductID ] ,
     [ ProductName ] ,
     [ SupplierID ] ,
     [ CategoryID ] ,
     [ QuantityPerUnit ] ,
     [ UnitPrice ] ,
     [ UnitsInStock ] ,
     [ UnitsOnOrder ] ,
     [ ReorderLevel ] ,
     [ Discontinued ]
FROM
     [ Products ]
WHERE
     [ SupplierID ] = @SupplierID  AND
     [ CategoryID ] = @CategoryID

5.2 多条件查询，通过Condition

Product product  =   new  Product();
Condition con  =  product.SelectCommand
    .NewCondition( " SupplierID " )  ==   7  

        &&  product.SelectCommand.NewCondition( " CategoryID " )  ==   3 ;
product.SelectCommand.Where(con);
IList products  =  product.Find();
foreach (Product p  in  products)
{
                Console.WriteLine(p.ProductName);
}

SQL  输出同上

6  数据插入

Region rg  =   new  Region();
rg.ID  =   7 ;
rg.Description  =   " 77 " ;           
rg.Save();

SQL 输出

INSERT   INTO   [ Region ]  (
     [ RegionID ] ,
     [ RegionDescription ]
)  VALUES  (
    @RegionID,
    @RegionDescription
)

6.2 数据插入2

Product product  =   new  Product();
product.ProductName  = " Test ActiveRecord! " ;
product.SupplierID  =   7 ;
product.CategoryID  =   3 ;
product.QuantityPerUnit  =   " test ! test ! " ;
product.UnitPrice  =   0.5m ;
product.UnitsInStock  =   5 ;
product.UnitsOnOrder  =   3 ;
product.ReorderLevel  =   10 ;
product.Discontinued  =   true ;
product.Save();
Console.WriteLine(product.ProductID);

SQL  输出

INSERT   INTO   [ Products ]  (
     [ ProductName ] ,
     [ SupplierID ] ,
     [ CategoryID ] ,
     [ QuantityPerUnit ] ,
     [ UnitPrice ] ,
     [ UnitsInStock ] ,
     [ UnitsOnOrder ] ,
     [ ReorderLevel ] ,
     [ Discontinued ]
)  VALUES  (
    @ProductName,
    @SupplierID,
    @CategoryID,
    @QuantityPerUnit,
    @UnitPrice,
    @UnitsInStock,
    @UnitsOnOrder,
    @ReorderLevel,
    @Discontinued
);
select   CAST ( SCOPE_IDENTITY()  AS  BIGINT)

6.3  数据插入3，部分字段的插入

Product product  =   new  Product();
product.ProductName  = " Test ActiveRecord! " ;
product.SupplierID  =   7 ;
product.CategoryID  =   3 ;
product.QuantityPerUnit  =   " test ! test ! " ;
product.Discontinued  =   true ;
product.Save();
Console.WriteLine(product.ProductID);

SQL 输出

INSERT   INTO   [ Products ]  (
     [ ProductName ] ,
     [ SupplierID ] ,
     [ CategoryID ] ,
     [ QuantityPerUnit ] ,
     [ Discontinued ]
)  VALUES  (
    @ProductName,
    @SupplierID,
    @CategoryID,
    @QuantityPerUnit,
    @Discontinued
)

select CAST( SCOPE_IDENTITY() AS BIGINT)

7 数据更新

Region  rg  =   new   Region() ;
rg.ID  =  7 ;

   rg.Load();
rg. Description   =  "7777" ;           

    rg.Save();

SQL 输出

SELECT  
     [ RegionID ] ,
     [ RegionDescription ]
FROM
     [ Region ]
WHERE
     [ RegionID ] = @RegionID

UPDATE   [ Region ]   SET  
     [ RegionDescription ]   =  @RegionDescription
WHERE
    RegionID  =  @RegionID

7.2 数据更新，有选择性的只更新“脏”列

Product product  =   new  Product();
product.ID  =   93 ;
product.Load();
product.ProductName  = " Test Update ActiveRecord! " ;
product.QuantityPerUnit  =   " test Update! test Update! " ;
product.Discontinued  =   true ;
product.Save();

SQL  输出

SELECT  
     [ ProductID ] ,
     [ ProductName ] ,
     [ SupplierID ] ,
     [ CategoryID ] ,
     [ QuantityPerUnit ] ,
     [ UnitPrice ] ,
     [ UnitsInStock ] ,
     [ UnitsOnOrder ] ,
     [ ReorderLevel ] ,
     [ Discontinued ]
FROM
     [ Products ]
WHERE
     [ ProductID ] = @ProductID

UPDATE   [ Products ]   SET  
     [ ProductName ]   =  @ProductName,
     [ QuantityPerUnit ]   =  @QuantityPerUnit,
     [ Discontinued ]   =  @Discontinued
WHERE
    ProductID  =  @ProductID

 

7.3 数据删除

Region  =   new Region() ;
rg.ID = 6 ;
rg.Delete();

SQL  输出

DELETE   FROM   [ Region ]
WHERE
    RegionID  =  @RegionID

1.5 数据删除2（条件删除（活动记录里面封装了Table数据库表的元数据对象里面），也可以以Filter方式进行删除或Where子句的方式进行删除)

Region  rg  =   new Region();           

   Condition con  =  rg.DeleteCommand.NewCondition(rg.Table.Key.Columns[ 0 ].Name)  ==   6 ;
con.Execute();

SQL  输出 同上

上一篇