【LeanEAP.NET】精益企业应用平台实战----创建AccountGroup表和DTO

分别在MSSQL和Oracle创建不同的AccountGroup表，创建相应的影射表结构的AccountGroupTable。尽管表的字段名、数据类型不同，通过一定的规则可以得到属性名相同的AccountGroupTable，这样可以实现数据库切换而不修改逻辑代码。

在MSSQL中创建一个AccountGroup的表

View Code

1 CREATE TABLE [AccountGroup] (
2 [Id] [int]  IDENTITY (1, 1)  NOT NULL,
3 [Name] [varchar]  (64) NULL,
4 [Parent] [int]  NULL,
5 [Visible] [bit]  NULL,
6 [UpdateUser] [varchar]  (32) NULL,
7 [UpdateDate] [datetime]  NULL)

MSSQL的AccountGroupTable代码

View Code

  1 public class AccountGroupTable : EAP.Data.DbTable
  2     {
  3 
  4         private const string ___tableName = "AccountGroup";
  5 
  6         #region Member Field Region
  7 
  8         private EAP.Data.DbColumn _Id;
  9 
 10         private EAP.Data.DbColumn _Name;
 11 
 12         private EAP.Data.DbColumn _Parent;
 13 
 14         private EAP.Data.DbColumn _Visible;
 15 
 16         private EAP.Data.DbColumn _UpdateUser;
 17 
 18         private EAP.Data.DbColumn _UpdateDate;
 19         #endregion
 20 
 21         public AccountGroupTable() :
 22             this(___tableName)
 23         {
 24         }
 25 
 26         public AccountGroupTable(string alias) :
 27             base(___tableName, alias)
 28         {
 29             _Id = new EAP.Data.DbColumn((alias + ".Id"), System.Data.DbType.Int32);
 30             _Name = new EAP.Data.DbColumn((alias + ".Name"), System.Data.DbType.AnsiString);
 31             _Parent = new EAP.Data.DbColumn((alias + ".Parent"), System.Data.DbType.Int32);
 32             _Visible = new EAP.Data.DbColumn((alias + ".Visible"), System.Data.DbType.Boolean);
 33             _UpdateUser = new EAP.Data.DbColumn((alias + ".UpdateUser"), System.Data.DbType.AnsiString);
 34             _UpdateDate = new EAP.Data.DbColumn((alias + ".UpdateDate"), System.Data.DbType.DateTime);
 35         }
 36 
 37         #region Member Property Region
 38 
 39         /// <summary>
 40         /// <para>主键</para>
 41         /// <para>DbType:System.Data.DbType.Int32</para>
 42         /// </summary>
 43         public EAP.Data.DbColumn Id
 44         {
 45             get
 46             {
 47                 return _Id;
 48             }
 49         }
 50 
 51         /// <summary>
 52         /// <para>名称</para>
 53         /// <para>DbType:System.Data.DbType.AnsiString</para>
 54         /// </summary>
 55         public EAP.Data.DbColumn Name
 56         {
 57             get
 58             {
 59                 return _Name;
 60             }
 61         }
 62 
 63         /// <summary>
 64         /// <para>上级组别</para>
 65         /// <para>DbType:System.Data.DbType.Int32</para>
 66         /// </summary>
 67         public EAP.Data.DbColumn Parent
 68         {
 69             get
 70             {
 71                 return _Parent;
 72             }
 73         }
 74 
 75         /// <summary>
 76         /// <para>可见性</para>
 77         /// <para>DbType:System.Data.DbType.Boolean</para>
 78         /// </summary>
 79         public EAP.Data.DbColumn Visible
 80         {
 81             get
 82             {
 83                 return _Visible;
 84             }
 85         }
 86 
 87         /// <summary>
 88         /// <para>修改者</para>
 89         /// <para>DbType:System.Data.DbType.AnsiString</para>
 90         /// </summary>
 91         public EAP.Data.DbColumn UpdateUser
 92         {
 93             get
 94             {
 95                 return _UpdateUser;
 96             }
 97         }
 98 
 99         /// <summary>
100         /// <para>修改时间</para>
101         /// <para>DbType:System.Data.DbType.DateTime</para>
102         /// </summary>
103         public EAP.Data.DbColumn UpdateDate
104         {
105             get
106             {
107                 return _UpdateDate;
108             }
109         }
110         #endregion
111     }

在Oracle中创建一个ACCOUNT_GROUP的表

View Code

 1 create table ACCOUNT_GROUP
 2 (
 3   id          number(9),
 4   name        varchar2(64),
 5   parent      number(9),
 6   visible     number(1),
 7   update_user varchar2(32),
 8   update_date date
 9 )
10 ;
11 -- Add comments to the table 
12 comment on table ACCOUNT_GROUP
13   is '账户组别';
14 -- Add comments to the columns 
15 comment on column ACCOUNT_GROUP.id
16   is '主键 ';
17 comment on column ACCOUNT_GROUP.name
18   is '名称';
19 comment on column ACCOUNT_GROUP.parent
20   is '上级组别';
21 comment on column ACCOUNT_GROUP.visible
22   is '可见性';
23 comment on column ACCOUNT_GROUP.update_user
24   is '更新者';
25 comment on column ACCOUNT_GROUP.update_date
26   is '更新时间';
27 
28 create sequence ACCOUNT_GROUP_ID_SEQ
29 minvalue 1
30 maxvalue 999999999999
31 start with 1
32 increment by 1
33 cache 20;
34 
35 CREATE OR REPLACE TRIGGER ACCOUNT_GROUP_AUTOID_TR
36 BEFORE INSERT
37 ON ACCOUNT_GROUP REFERENCING NEW AS NEW OLD AS OLD
38 FOR EACH ROW
39 when (
40 NEW.ID IS NULL or NEW.Key IS NULL
41 )
42 BEGIN
43    SELECT ACCOUNT_GROUP_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
44 END;

Oracle的AccountGroupTable代码

View Code

  1 public class AccountGroupTable : EAP.Data.DbTable
  2     {
  3 
  4         private const string ___tableName = "ACCOUNT_GROUP";
  5 
  6         #region Member Field Region
  7 
  8         private EAP.Data.DbColumn _Id;
  9 
 10         private EAP.Data.DbColumn _Name;
 11 
 12         private EAP.Data.DbColumn _Parent;
 13 
 14         private EAP.Data.DbColumn _Visible;
 15 
 16         private EAP.Data.DbColumn _UpdateUser;
 17 
 18         private EAP.Data.DbColumn _UpdateDate;
 19         #endregion
 20 
 21         public AccountGroupTable() :
 22             this(___tableName)
 23         {
 24         }
 25 
 26         public AccountGroupTable(string alias) :
 27             base(___tableName, alias)
 28         {
 29             _Id = new EAP.Data.DbColumn((alias + ".ID"), System.Data.DbType.Int32);
 30             _Name = new EAP.Data.DbColumn((alias + ".NAME"), System.Data.DbType.AnsiString);
 31             _Parent = new EAP.Data.DbColumn((alias + ".PARENT"), System.Data.DbType.Int32);
 32             _Visible = new EAP.Data.DbColumn((alias + ".VISIBLE"), System.Data.DbType.Int16);
 33             _UpdateUser = new EAP.Data.DbColumn((alias + ".UPDATE_USER"), System.Data.DbType.AnsiString);
 34             _UpdateDate = new EAP.Data.DbColumn((alias + ".UPDATE_DATE"), System.Data.DbType.DateTime);
 35         }
 36 
 37         #region Member Property Region
 38 
 39         /// <summary>
 40         /// <para>主键 </para>
 41         /// <para>DbType:System.Data.DbType.Int32</para>
 42         /// </summary>
 43         public EAP.Data.DbColumn Id
 44         {
 45             get
 46             {
 47                 return _Id;
 48             }
 49         }
 50 
 51         /// <summary>
 52         /// <para>名称</para>
 53         /// <para>DbType:System.Data.DbType.AnsiString</para>
 54         /// </summary>
 55         public EAP.Data.DbColumn Name
 56         {
 57             get
 58             {
 59                 return _Name;
 60             }
 61         }
 62 
 63         /// <summary>
 64         /// <para>上级组别</para>
 65         /// <para>DbType:System.Data.DbType.Int32</para>
 66         /// </summary>
 67         public EAP.Data.DbColumn Parent
 68         {
 69             get
 70             {
 71                 return _Parent;
 72             }
 73         }
 74 
 75         /// <summary>
 76         /// <para>可见性</para>
 77         /// <para>DbType:System.Data.DbType.Int16</para>
 78         /// </summary>
 79         public EAP.Data.DbColumn Visible
 80         {
 81             get
 82             {
 83                 return _Visible;
 84             }
 85         }
 86 
 87         /// <summary>
 88         /// <para>更新者</para>
 89         /// <para>DbType:System.Data.DbType.AnsiString</para>
 90         /// </summary>
 91         public EAP.Data.DbColumn UpdateUser
 92         {
 93             get
 94             {
 95                 return _UpdateUser;
 96             }
 97         }
 98 
 99         /// <summary>
100         /// <para>更新时间</para>
101         /// <para>DbType:System.Data.DbType.DateTime</para>
102         /// </summary>
103         public EAP.Data.DbColumn UpdateDate
104         {
105             get
106             {
107                 return _UpdateDate;
108             }
109         }
110         #endregion
111     }

AccountGroupModel代码

View Code

  1 [System.Serializable()]
  2     public class AccountGroupModels : System.Collections.Generic.List<AccountGroupModel>
  3     {
  4 
  5         public AccountGroupModels()
  6         {
  7         }
  8 
  9         public AccountGroupModels(System.Collections.Generic.IEnumerable<AccountGroupModel> collection) :
 10             base(collection)
 11         {
 12         }
 13     }
 14 
 15     /// <summary>
 16     /// <para></para>
 17     /// </summary>
 18     [System.Serializable()]
 19     public partial class AccountGroupModel
 20     {
 21 
 22         #region Member Field Region
 23         private int _Id;
 24 
 25         private string _Name;
 26 
 27         private int _Parent;
 28 
 29         private bool _Visible;
 30 
 31         private string _UpdateUser;
 32 
 33         private System.DateTime _UpdateDate;
 34         #endregion
 35 
 36         #region Member Property Region
 37         /// <summary>
 38         /// <para>主键</para>
 39         /// </summary>
 40         [EAP.Data.ColumnInfoAttribute(Visible = false, Queryable = true, DbColumn = "Id")]
 41         public int Id
 42         {
 43             get
 44             {
 45                 return _Id;
 46             }
 47             set
 48             {
 49                 _Id = value;
 50             }
 51         }
 52 
 53         /// <summary>
 54         /// <para>名称</para>
 55         /// </summary>
 56         [EAP.Data.ColumnInfoAttribute(DbColumn = "Name")]
 57         public string Name
 58         {
 59             get
 60             {
 61                 return _Name;
 62             }
 63             set
 64             {
 65                 _Name = value;
 66             }
 67         }
 68 
 69         /// <summary>
 70         /// <para>上级组别</para>
 71         /// </summary>
 72         [EAP.Data.ColumnInfoAttribute(DbColumn = "Parent")]
 73         public int Parent
 74         {
 75             get
 76             {
 77                 return _Parent;
 78             }
 79             set
 80             {
 81                 _Parent = value;
 82             }
 83         }
 84 
 85         /// <summary>
 86         /// <para>可见性</para>
 87         /// </summary>
 88         [EAP.Data.ColumnInfoAttribute(DbColumn = "Visible")]
 89         public bool Visible
 90         {
 91             get
 92             {
 93                 return _Visible;
 94             }
 95             set
 96             {
 97                 _Visible = value;
 98             }
 99         }
100 
101         /// <summary>
102         /// <para>修改者</para>
103         /// </summary>
104         [EAP.Data.ColumnInfoAttribute(DbColumn = "UpdateUser")]
105         public string UpdateUser
106         {
107             get
108             {
109                 return _UpdateUser;
110             }
111             set
112             {
113                 _UpdateUser = value;
114             }
115         }
116 
117         /// <summary>
118         /// <para>修改时间</para>
119         /// </summary>
120         [EAP.Data.ColumnInfoAttribute(DbColumn = "UpdateDate")]
121         public System.DateTime UpdateDate
122         {
123             get
124             {
125                 return _UpdateDate;
126             }
127             set
128             {
129                 _UpdateDate = value;
130             }
131         }
132         #endregion
133     }

在MSSQL中Id是自增长的int类型，新增后可以通过SELECT SCOPE_IDENTITY()得到Id的值。在Oracle中没有自增长类型，需要创建一个Sequence，然后通过触发器把Sequence的nextval插入到ID列，然后程序中可以读取Sequence的currval得到ID。

AccountGroupModel中Visible属性是bool类型，保存到MSSQL中是bit类型的值，而Oracle没有bit，可以使用Number(1)：

MSSQL的AccountGroupTable中 _Visible = new EAP.Data.DbColumn((alias + ".Visible"), System.Data.DbType.Boolean);

Oracle的AccountGroupTable中 _Visible = new EAP.Data.DbColumn((alias + ".VISIBLE"), System.Data.DbType.Int16);

【LeanEAP.NET】精益企业应用平台----系列目录

posted on 2012-05-30 23:55 Eric.rz.Liang 阅读(702) 评论(0) 编辑收藏举报