首页 > 代码库 > 十四、EnterpriseFrameWork框架核心类库之简易ORM

十四、EnterpriseFrameWork框架核心类库之简易ORM

      在写本章前先去网上找了一下关于ORM的相关资料,以为本章做准备,发现很多东西今天才了解,所以在这里也对ORM做不了太深入的分析,但还是浅谈一下EFW框架中的设计的简易ORM;文中有一点讲得很有道理,Dao与ORM的区别,Dao是对数据库操作的封装,编写的代码方式像一种设计方法,而ORM支持对象与数据结构的映射,更像一种代码开发工具,有了这个工具会让我们开发代码更简单方便;但是同一类工具有简单的也有复杂的,比如文字工具有简单的Notepad,也有复杂的Word,不是说有了复杂的简单的工具就不需要了,这要看你具体的场合了,你就只是想记录几段文字肯定用Notepad就已经足够了;同样道理,EFW框架中的ORM就想一个Notepad,如果在开发中想解决复杂的数据问题怎么办,那么建议你结合框架中的DAO方式一起使用;

 

接下来我将从下面几个方面对框架中的ORM进行说明:

1.简易ORM包含哪些功能

在我的想法中ORM主要是实现实体与数据库表之间数据转换的工具,让我们对数据库的操作不需要编写SQL语句,只要操作实体就可以了;而实体操作数据库又可以分为一对一单表操作、一对多多表操作,多对多关联表操作等等;而在本框架中只是实现了单表操作,但对其他操作方式怎么办了,就需要结合框架中的DAO来完成;还有在单表操作中,支持外键ID转换为名称显示;

ORM设计类关系图

 

2.如何使用

首先创建实体类,并配置好与数据库表的映射,但实体较多的时候此操作可以用一些代码生成工具来完成;

Book实体

 1   [Serializable] 2     [Table(TableName = "Books", EntityType = EntityType.Table, IsGB = true)] 3     public class Book : EFWCoreLib.CoreFrame.BusinessArchitecture.AbstractEntity 4     { 5         private int id; 6         [Column(FieldName = "Id", DataKey = true, Match = "", IsInsert = false)] 7         public int Id 8         { 9             get { return id; }10             set { id = value; }11         }12         private string bookName;13         [Column(FieldName = "BookName", DataKey = false, Match = "", IsInsert = true)]14         public string BookName15         {16             get { return bookName; }17             set { bookName = value; }18         }19         private decimal buyPrice;20         [Column(FieldName = "BuyPrice", DataKey = false, Match = "", IsInsert = true)]21         public decimal BuyPrice22         {23             get { return buyPrice; }24             set { buyPrice = value; }25         }26         private DateTime buyDate;27         [Column(FieldName = "BuyDate", DataKey = false, Match = "", IsInsert = true)]28         public DateTime BuyDate29         {30             get { return buyDate; }31             set { buyDate = value; }32         }33         private int flag;34         [Column(FieldName = "Flag", DataKey = false, Match = "", IsInsert = true)]35         public int Flag36         {37             get { return flag; }38             set { flag = value; }39         }40     }
View Code

 

控制器调用实体的代码

 1  public void TestEntity() 2         { 3             //创建实体对象实例 4             Book book = NewObject<Book>(); 5  6             //1.根据id获取一条记录 7             book= book.getmodel(1) as Book; 8  9             //2.修改或者新增一条记录10             book.BookName = "人月神话";11             book.BuyPrice = 23;12             book.BuyDate = Convert.ToDateTime("2014-01-01");13             book.save();14 15             //3.根据id删除表数据16             book.delete(1);17 18             //4.获取表所有记录转换为List实体对象19             List<Book> booklist = book.getlist<Book>();20 21             //5.获取表所有记录转换为DataTable22             DataTable dt = book.gettable();23         }
View Code

 

由上面可见,用此种方式操作数据库是如此的简单,只需要使用实体的save()、delete()、getmodel()、getlist()、gettable()等方法;

3.实体的自定义标签TableAttribute和ColumnAttribute

下面详细说明一下实体的TableAttribute标签和ColumnAttribute标签的属性意思;

TableAttribute标签

名称

类型

说明

默认值

TableName

string

映射的表名

 

EntityType

EntityType

设置实体类型,Table表、View视图

Table

IsGB

bool

是否国标(机构内共享)

false

Alias

string

别名

 

 

ColumnAttribute标签

名称

类型

说明

默认值

FieldName

string

字段名称

 

DataKey

bool

是否为主键

false

IsInsert

bool

是否插入到数据库

true

Match

string

值的匹配条件

 

Alias

string

别名

 

 

还有一个实体映射到多个表也可以,直接实体上配置多个TableAttribute标签和属性上配置多个ColumnAttribute标签,不过要用标签的Alias参数区分,再在操作实体的时候指定相应别名就行了。

4.接口IORM和实体抽象基类AbstractEntity

接口IORM定义了ORM的所有操作方法,需要增强ORM的功能可以扩展此接口;

  1  /// <summary>  2     /// 简易ORM接口,其中alias别名是指实体配置的自定义标签Table属性Alias的值  3     /// </summary>  4     interface IORM  5     {  6         /// <summary>  7         /// 插入或更新到数据库  8         /// </summary>  9         /// <returns></returns> 10         int save(); 11         /// <summary> 12         /// 插入或更新到数据库 13         /// </summary> 14         /// <param name="alias">实体别名</param> 15         /// <returns></returns> 16         int save(string alias); 17         /// <summary> 18         /// 根据ID获取实体数据 19         /// </summary> 20         /// <returns></returns> 21         object getmodel(); 22         /// <summary> 23         /// 指定key和别名获取实体数据 24         /// </summary> 25         /// <param name="key"></param> 26         /// <param name="alias"></param> 27         /// <returns></returns> 28         object getmodel(object key, string alias); 29         /// <summary> 30         /// 指定key获取实体数据 31         /// </summary> 32         /// <param name="key"></param> 33         /// <returns></returns> 34         object getmodel(object key); 35         /// <summary> 36         /// 指定key和别名删除实体数据 37         /// </summary> 38         /// <param name="key"></param> 39         /// <param name="alias"></param> 40         /// <returns></returns> 41         int delete(object key, string alias); 42         /// <summary> 43         /// 指定key删除实体数据 44         /// </summary> 45         /// <param name="key"></param> 46         /// <returns></returns> 47         int delete(object key); 48         /// <summary> 49         /// 根据ID删除实体数据 50         /// </summary> 51         /// <returns></returns> 52         int delete(); 53         /// <summary> 54         /// 获取实体List对象集合 55         /// </summary> 56         /// <typeparam name="T"></typeparam> 57         /// <returns></returns> 58         System.Collections.Generic.List<T> getlist<T>(); 59         /// <summary> 60         /// 获取实体List对象集合,根据where条件过滤 61         /// </summary> 62         /// <typeparam name="T"></typeparam> 63         /// <param name="where"></param> 64         /// <returns></returns> 65         System.Collections.Generic.List<T> getlist<T>(string where); 66         /// <summary> 67         /// 获取实体List对象集合,根据where条件过滤 68         /// </summary> 69         /// <typeparam name="T"></typeparam> 70         /// <param name="pageInfo">分页</param> 71         /// <param name="where"></param> 72         /// <returns></returns> 73         System.Collections.Generic.List<T> getlist<T>(PageInfo pageInfo, string where); 74         /// <summary> 75         /// 获取实体List对象集合,根据where条件过滤 76         /// </summary> 77         /// <typeparam name="T"></typeparam> 78         /// <param name="pageInfo"></param> 79         /// <param name="where"></param> 80         /// <param name="alias"></param> 81         /// <returns></returns> 82         System.Collections.Generic.List<T> getlist<T>(PageInfo pageInfo, string where, string alias); 83         /// <summary> 84         /// 获取实体datatable 85         /// </summary> 86         /// <returns></returns> 87         System.Data.DataTable gettable(); 88         /// <summary> 89         ///  获取实体datatable,where条件过滤 90         /// </summary> 91         /// <param name="where"></param> 92         /// <returns></returns> 93         System.Data.DataTable gettable(string where); 94         /// <summary> 95         ///  获取实体datatable,分页 96         /// </summary> 97         /// <param name="pageInfo"></param> 98         /// <param name="where"></param> 99         /// <param name="alias"></param>100         /// <returns></returns>101         System.Data.DataTable gettable(PageInfo pageInfo, string where, string alias);102         /// <summary>103         ///  获取实体datatable,分页104         /// </summary>105         /// <param name="pageInfo"></param>106         /// <param name="where"></param>107         /// <returns></returns>108         System.Data.DataTable gettable(PageInfo pageInfo, string where);109     }
View Code

 

实体抽象基类AbstractEntity,实现了对IORM接口所有操作方法,还有所有实体必须继承此基类;

  1 /// <summary>  2     /// 实体基类  3     /// </summary>  4     public abstract class AbstractEntity:AbstractBusines,IORM  5     {  6         #region IORM 成员  7         protected void SetEntityValue(string propertyName, object model, object value)  8         {  9             PropertyInfo property = model.GetType().GetProperty(propertyName); 10             property.SetValue(model, ConvertValue(property.PropertyType.FullName, value), null); 11         } 12         protected object ConvertValue(string PropertyType, object value) 13         { 14             if (value =http://www.mamicode.com/= null) return null; 15  16             if (value.GetType().FullName == "System.Guid") 17             { 18                 return value.ToString(); 19             } 20  21             switch (PropertyType) 22             { 23                 case "System.DBNull": 24                     return null; 25                 case "System.Int32": 26                     value = http://www.mamicode.com/value == DBNull.Value ? 0 : value; 27                     value = value =http://www.mamicode.com/= null ? 0 : value; 28                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? 0 : value; 29                     return Convert.ToInt32(value); 30                 case "System.Int64": 31                     value = http://www.mamicode.com/value == DBNull.Value ? 0 : value; 32                     value = value =http://www.mamicode.com/= null ? 0 : value; 33                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? 0 : value; 34                     return Convert.ToInt64(value); 35                 case "System.Decimal": 36                     value = http://www.mamicode.com/value == DBNull.Value ? 0 : value; 37                     value = value =http://www.mamicode.com/= null ? 0 : value; 38                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? 0 : value; 39                     return Convert.ToDecimal(value); 40                 case "System.DateTime": 41                     value = http://www.mamicode.com/value == DBNull.Value ? new DateTime() : value; 42                     value = value =http://www.mamicode.com/= null ? new DateTime() : value; 43                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? new DateTime() : value; 44                     return Convert.ToDateTime(value); 45             } 46  47  48             value = http://www.mamicode.com/value == DBNull.Value ? null : value; 49             return value; 50         } 51         private object ToObject(System.Data.IDataReader dataReader, object _obj, string alias) 52         { 53             Type type = _obj.GetType(); 54             object obj = ((ICloneable)_obj).Clone(); 55             System.Collections.Hashtable filedValue = http://www.mamicode.com/new System.Collections.Hashtable(); 56             for (int index = 0; index < dataReader.FieldCount; index++) 57             { 58                 filedValue.Add(dataReader.GetName(index), dataReader[index]); 59             } 60             foreach (System.Reflection.PropertyInfo property in type.GetProperties()) 61             { 62                 SetEntityValue(property.Name, obj, filedValue[property.Name]); 63             } 64             ((IbindDb)obj).BindDb(_oleDb, _container); 65             return obj; 66         } 67  68         public int save() { return save(null); } 69         public int save(string alias) 70         { 71             OrmAnalysis ormAnalysis; 72  73             ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType); 74             ormAnalysis.Alias = alias; 75             ormAnalysis.Db = _oleDb; 76             object keyVal = ormAnalysis.GetEntityDataKeyValue(this); 77  78             if (keyVal == null || (keyVal.GetType().Equals(typeof(int)) && Convert.ToInt32(keyVal) == 0)) 79             { 80  81                 string strsql = ormAnalysis.GetInsertSQL(this); 82                 int ret = 0; 83                 ret = _oleDb.InsertRecord(strsql); 84                 ormAnalysis.SetEntityValue(ormAnalysis.GetEntityDataKeyPropertyName(this), this, ret); 85  86                 return ret; 87             } 88             else 89             { 90                 string strsql = ormAnalysis.GetUpdateSQL(this); 91                 return _oleDb.DoCommand(strsql); 92             } 93         } 94  95         public int delete() { return delete(null, null); } 96         public int delete(object key) { return delete(key, null); } 97         public int delete(object key, string alias) 98         { 99             OrmAnalysis ormAnalysis;100 101             ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);102             ormAnalysis.Alias = alias;103             ormAnalysis.Db = _oleDb;104             object keyVal = key == null ? ormAnalysis.GetEntityDataKeyValue(this) : key;105             string strsql = ormAnalysis.GetDeleteSQL(this.GetType(), keyVal);106             return _oleDb.DoCommand(strsql);107         }108 109         public object getmodel() { return getmodel(null, null); }110         public object getmodel(object key) { return getmodel(key, null); }111         public object getmodel(object key, string alias)112         {113             OrmAnalysis ormAnalysis;114 115             ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);116             ormAnalysis.Alias = alias;117             ormAnalysis.Db = _oleDb;118             object value = http://www.mamicode.com/null;119             object keyVal = key == null ? ormAnalysis.GetEntityDataKeyValue(this) : key;120 121             string strsql = ormAnalysis.GetEntitySQL(this.GetType(), keyVal);122             System.Data.IDataReader result = _oleDb.GetDataReader(strsql);123 124             if (result.Read())125             {126                 value = http://www.mamicode.com/ToObject(result, this, alias);127 128             }129             result.Close();130             result.Dispose();131 132             return value;133         }134         public List<T> getlist<T>()135         {136             return getlist<T>(null, null, null);137         }138         public List<T> getlist<T>(string where) { return getlist<T>(null, where, null); }139         public List<T> getlist<T>(PageInfo pageInfo, string where) { return getlist<T>(pageInfo, where, null); }140         public List<T> getlist<T>(PageInfo pageInfo, string where, string alias)141         {142             OrmAnalysis ormAnalysis;143             ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);144             ormAnalysis.Alias = alias;145             ormAnalysis.Db = _oleDb;146 147             string strsql = ormAnalysis.GetListSQL(this.GetType(), where, pageInfo);148 149             IDataReader result = _oleDb.GetDataReader(strsql);150             List<T> resultList = new List<T>();151             while (result.Read())152             {153                 resultList.Add((T)ToObject(result, this, alias));154             }155             result.Close();156             result.Dispose();157             return resultList;158         }159         public DataTable gettable()160         {161             return gettable(null, null, null);162         }163         public DataTable gettable(string where) { return gettable(null, where, null); }164         public DataTable gettable(PageInfo pageInfo, string where) { return gettable(pageInfo, where,null); }165         public DataTable gettable(PageInfo pageInfo, string where, string alias)166         {167             OrmAnalysis ormAnalysis;168             ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);169             ormAnalysis.Alias = alias;170             ormAnalysis.Db = _oleDb;171 172             string strsql = ormAnalysis.GetListSQL(this.GetType(), where, pageInfo);173 174             return _oleDb.GetDataTable(strsql);175         }176 177         #endregion178     }
View Code

 

5.ORM解析,根据实体标签配置生成相应SQL语句

实体怎么操作数据库的,就是根据实体配置信息转换为sql语句再执行,由于不同数据库之间的差异,所以生成的sql语句会不一样,这里的设计用了工厂模式;

 

OrmAnalysis解析为sql语句基类,定义了生成不同sql语句的操作方法;包括插入语句、删除语句、获取数据语句等

  1 /// <summary>  2     /// ORM映射关系解析基类  3     /// </summary>  4     abstract public class OrmAnalysis  5     {  6   7         private AbstractDatabase _Db;  8         /// <summary>  9         /// 数据库对象 10         /// </summary> 11         public AbstractDatabase Db 12         { 13             get 14             { 15                 return _Db; 16             } 17             set 18             { 19                 _Db = value; 20             } 21         } 22  23         private string _alias; 24         /// <summary> 25         /// 别名 26         /// </summary> 27         public string Alias 28         { 29             get { return _alias; } 30             set { _alias = value; } 31         } 32  33         protected string JoinWhere(bool isgb, string strWhere) 34         { 35             if (IsJoinWorkId(isgb)) 36             { 37                 strWhere = "WorkId = " + Db.WorkId + (string.IsNullOrEmpty(strWhere) ? " " : " and " + strWhere); 38             } 39  40             string where = string.IsNullOrEmpty(strWhere) ? "" : ("where " + strWhere); 41             return where; 42         } 43  44         protected bool IsJoinWorkId(bool isgb) 45         { 46             if (AppGlobal.IsSaas == true && isgb == false && Db.WorkId > 0) 47             { 48                 return true; 49             } 50  51             return false; 52         } 53  54  55         #region 解析实体属性 56         protected TableAttributeInfo GetTableAttributeInfo(Type type) 57         { 58             List<Entity_Attribute> entityAttrList = (List<Entity_Attribute>)AppGlobal.cache.GetData("entityAttributeList"); 59             Entity_Attribute EAttr = entityAttrList.Find(x => x.ObjType.Equals(type)); 60             if (EAttr == null) throw new Exception("此对象没有配置实体自定义属性"); 61             TableAttributeInfo tableAttrInfo = EAttr.TableAttributeInfoList.Find(x => x.Alias == Alias); 62             //if (tableAttrInfo) throw new Exception("找不到相同别名的表自定义属性"); 63             return tableAttrInfo; 64         } 65  66         protected TableAttributeInfo GetTableAttributeInfo(object model) 67         { 68             return GetTableAttributeInfo(model.GetType()); 69         } 70  71         protected object GetEntityValue(string propertyName, object model) 72         { 73             object data = http://www.mamicode.com/model.GetType().GetProperty(propertyName).GetValue(model, null); 74             if (model.GetType().GetProperty(propertyName).PropertyType.FullName == "System.DateTime" && Convert.ToDateTime(data) == default(DateTime)) 75             { 76                 data = http://www.mamicode.com/Convert.ToDateTime("1900/01/01 00:00:00"); 77             } 78  79             return data; 80         } 81  82         public void SetEntityValue(string propertyName, object model, object value) 83         { 84             PropertyInfo property = model.GetType().GetProperty(propertyName); 85             property.SetValue(model, ConvertValue(property.PropertyType.FullName, value), null); 86         } 87  88         protected object ConvertValue(string PropertyType, object value) 89         { 90             if (value.GetType().FullName == "System.Guid") 91             { 92                 return value.ToString(); 93             } 94  95             switch (PropertyType) 96             { 97                 case "System.DBNull": 98                     return null; 99                 case "System.Int32":100                     value = http://www.mamicode.com/value == DBNull.Value ? 0 : value;101                     value = value =http://www.mamicode.com/= null ? 0 : value;102                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? 0 : value;103                     return Convert.ToInt32(value);104                 case "System.Int64":105                     value = http://www.mamicode.com/value == DBNull.Value ? 0 : value;106                     value = value =http://www.mamicode.com/= null ? 0 : value;107                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? 0 : value;108                     return Convert.ToInt64(value);109                 case "System.Decimal":110                     value = http://www.mamicode.com/value == DBNull.Value ? 0 : value;111                     value = value =http://www.mamicode.com/= null ? 0 : value;112                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? 0 : value;113                     return Convert.ToDecimal(value);114                 case "System.DateTime":115                     value = http://www.mamicode.com/value == DBNull.Value ? new DateTime() : value;116                     value = value =http://www.mamicode.com/= null ? new DateTime() : value;117                     value = http://www.mamicode.com/value.ToString().Trim() == "" ? new DateTime() : value;118                     return Convert.ToDateTime(value);119             }120 121 122             value = http://www.mamicode.com/value == DBNull.Value ? null : value;123             return value;124         }125 126         protected string ConvertDBValue(object value)127         {128             if(value=http://www.mamicode.com/=null) return "NULL";129             130             string PropertyType = value.GetType().FullName;131             switch (PropertyType)132             {133                 case "System.String":134                     return "" + value.ToString() + "";135                 case "System.DateTime":136                     return "" + value.ToString() + "";137                 case  "System.Guid":138                     return "" + value.ToString() + "";139                 case "System.Boolean":140                     return "" + value.ToString() + "";141             }142 143             return value.ToString();144         }145 146         public object GetEntityDataKeyValue(object model)147         {148             TableAttributeInfo tableAttribute = GetTableAttributeInfo(model);149             return GetEntityValue(tableAttribute.DataKeyPropertyName, model);150         }151 152         public string GetEntityDataKeyPropertyName(object model)153         {154             TableAttributeInfo tableAttribute = GetTableAttributeInfo(model);155             return tableAttribute.DataKeyPropertyName;156         }157         #endregion158 159         /// <summary>160         /// 向数据库中增加一条数据161         /// </summary>162         /// <param name="model">要写入的实体</param>163         /// <returns></returns>164         public abstract string GetInsertSQL(object model);165 166         /// <summary>167         /// 根据给定实例更新一条记录168         /// </summary>169         /// <param name="command">数据库命令执行对象</param>170         /// <returns></returns>171         public abstract string GetUpdateSQL(object model);172 173 174         /// <summary>175         /// 根据key删除一条记录176         /// </summary>177         /// <returns></returns>178         public abstract string GetDeleteSQL<T>(object key);179         public abstract string GetDeleteSQL(Type type,object key);180 181 182         /// <summary>183         /// 根据唯一ID得到一个对象实体184         /// </summary>185         /// <returns></returns>186         public abstract string GetEntitySQL<T>(object key);187         public abstract string GetEntitySQL(Type type, object key);188         /// <summary>189         /// 得到实体对象集合sql190         /// </summary>191         /// <typeparam name="T"></typeparam>192         /// <returns></returns>193         public abstract string GetListSQL<T>();194         public abstract string GetListSQL<T>(string strWhere);195         public abstract string GetListSQL<T>(string strWhere,PageInfo pageInfo);196         public abstract string GetListSQL(Type type,string strWhere);197         public abstract string GetListSQL(Type type,string strWhere,PageInfo pageInfo);198        199     }
View Code

 

SqlServerOrmAnalysis类,针对SqlServer数据库的实现

  1 /// <summary>  2     /// 基于ORM实现sqlserver数据库的ORM  3     /// </summary>  4     public class SqlServerOrmAnalysis:OrmAnalysis  5     {  6           7         public override string GetInsertSQL(object model)  8         {  9             string strsql = ""; 10             try 11             { 12                 Dictionary<string, object> dicsql = new Dictionary<string, object>(); 13  14                 TableAttributeInfo tableAttribute = GetTableAttributeInfo(model); 15                 List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList; 16  17                 for (int i = 0; i < columnAttributeCollection.Count; i++) 18                 { 19  20                     ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i]; 21  22                     if (columnAttributeInfo.DataKey == true && columnAttributeInfo.Match == "Custom:Guid")//赋值给自增长ID 23                     { 24                         object obj = GetEntityValue(columnAttributeInfo.PropertyName, model); 25                         obj = obj == null ? Guid.NewGuid().ToString() : obj; 26  27                         SetEntityValue(columnAttributeInfo.PropertyName, model, obj); 28  29                         dicsql.Add(columnAttributeInfo.FieldName, obj); 30                     } 31                     else 32                     { 33  34                         if (columnAttributeInfo.IsInsert == true) 35                         { 36                             object obj = GetEntityValue(columnAttributeInfo.PropertyName, model); 37                             //obj = obj == null ? DBNull.Value : obj; 38                             dicsql.Add(columnAttributeInfo.FieldName, obj); 39                         } 40                     } 41                 } 42  43                 string fields = ""; 44                 string values = ""; 45                 strsql = "insert into {0} ({1}) values({2})"; 46  47                 if (IsJoinWorkId(tableAttribute.IsGB)) 48                 { 49                     dicsql.Add("WorkId", Db.WorkId); 50                 } 51  52                 foreach (KeyValuePair<string, object> val in dicsql) 53                 { 54                     fields += (fields == "" ? "" : ",") + val.Key; 55                     values += (values == "" ? "" : ",") + ConvertDBValue(val.Value); 56                 } 57  58                 return string.Format(strsql, tableAttribute.TableName, fields, values); 59             } 60             catch (Exception err) 61             { 62                 throw new Exception(err.Message + "SQL:" + strsql); 63             } 64         } 65  66         public override string GetUpdateSQL(object model) 67         { 68             string strsql = ""; 69             string where = ""; 70             try 71             { 72                 Dictionary<string, object> dicsql = new Dictionary<string, object>(); 73  74  75                 TableAttributeInfo tableAttribute = GetTableAttributeInfo(model); 76                 List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList; 77  78                 for (int i = 0; i < columnAttributeCollection.Count; i++) 79                 { 80  81                     ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i]; 82  83                     if (columnAttributeInfo.DataKey == false) 84                     { 85                         object obj = GetEntityValue(columnAttributeInfo.PropertyName, model); 86                         dicsql.Add(columnAttributeInfo.FieldName, obj); 87                     } 88  89                     if (columnAttributeInfo.DataKey == true) 90                     { 91                         object obj = GetEntityValue(columnAttributeInfo.PropertyName, model); 92                         where = columnAttributeInfo.FieldName + "=" + ConvertDBValue(obj); 93                     } 94                 } 95  96                 string field_values = ""; 97  98                 strsql = "update  {0} set {1} where {2}"; 99 100                 foreach (KeyValuePair<string, object> val in dicsql)101                 {102                     field_values += (field_values == "" ? "" : ",") + val.Key + "=" + ConvertDBValue(val.Value);103                 }104 105                 return string.Format(strsql, tableAttribute.TableName, field_values, where);106             }107             catch (Exception err)108             {109                 throw new Exception(err.Message + "SQL:" + strsql);110             }111         }112 113         public override string GetDeleteSQL<T>(object key)114         {115             return GetDeleteSQL(typeof(T), key);116         }117 118         public override string GetDeleteSQL(Type type, object key)119         {120             string strsql = "";121             string where = "";122             try123             {124                 125                 TableAttributeInfo tableAttribute = GetTableAttributeInfo(type);126                 List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList;127 128                 for (int i = 0; i < columnAttributeCollection.Count; i++)129                 {130 131                     ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i];132                     if (columnAttributeInfo.DataKey == true)133                     {134                         object obj = key;135                         where = columnAttributeInfo.FieldName + "=" + ConvertDBValue(obj);136                     }137                 }138 139                 strsql = "delete from  {0}  where {1}";140 141                 return string.Format(strsql, tableAttribute.TableName, where);142             }143             catch (Exception err)144             {145                 throw new Exception(err.Message + "SQL:" + strsql);146             }147         }148 149         public override string GetEntitySQL<T>(object key)150         {151             return GetEntitySQL(typeof(T), key);152         }153 154         public override string GetEntitySQL(Type type, object key)155         {156             string strsql = "";157             string fields = "";158             string where = "";159             try160             {161 162                 TableAttributeInfo tableAttribute = GetTableAttributeInfo(type);163                 List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList;164 165                 for (int i = 0; i < columnAttributeCollection.Count; i++)166                 {167 168                     ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i];169 170                     fields += (fields == "" ? "" : ",") + columnAttributeInfo.FieldName + " as " + columnAttributeInfo.PropertyName;171 172                     if (columnAttributeInfo.DataKey == true)173                     {174                         object obj = key;175                         where = columnAttributeInfo.FieldName + "=" + ConvertDBValue(obj);176                     }177                 }178 179 180                 strsql = "select {0} from {1} where {2}";181 182                 return string.Format(strsql, fields, tableAttribute.TableName, where);183             }184             catch (Exception err)185             {186                 throw new Exception(err.Message + "SQL:" + strsql);187             }188         }189 190         public override string GetListSQL<T>()191         {192             return GetListSQL(typeof(T), null, null);193         }194 195         public override string GetListSQL<T>(string strWhere)196         {197             return GetListSQL(typeof(T), strWhere, null);198         }199 200         public override string GetListSQL<T>(string strWhere, EFWCoreLib.CoreFrame.DbProvider.SqlPagination.PageInfo pageInfo)201         {202             return GetListSQL(typeof(T), strWhere, pageInfo);203         }204 205         public override string GetListSQL(Type type, string strWhere)206         {207             return GetListSQL(type, strWhere, null);208         }209 210         public override string GetListSQL(Type type, string strWhere, EFWCoreLib.CoreFrame.DbProvider.SqlPagination.PageInfo pageInfo)211         {212             string strsql = "";213             string fields = "";214             string where = "";215             try216             {217 218                 TableAttributeInfo tableAttribute = GetTableAttributeInfo(type);219                 List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList;220 221                 for (int i = 0; i < columnAttributeCollection.Count; i++)222                 {223                     ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i];224                     fields += (fields == "" ? "" : ",") + columnAttributeInfo.FieldName + " as " + columnAttributeInfo.PropertyName;225                 }226 227                 where = JoinWhere(tableAttribute.IsGB, strWhere);228                 strsql = "select {0} from {1} {2}";229                 strsql = string.Format(strsql, fields, tableAttribute.TableName + " as T1", where);230 231                 if (pageInfo != null)232                 {233                     if (pageInfo.KeyName == null)234                         pageInfo.KeyName = tableAttribute.DataKeyFieldName;235                     strsql = EFWCoreLib.CoreFrame.DbProvider.SqlPagination.SqlPage.FormatSql(strsql, pageInfo, Db);236                 }237                 return strsql;238             }239             catch (Exception err)240             {241                 throw new Exception(err.Message + "SQL:" + strsql);242             }243         }244 245        246     }
View Code

 

OracleOrmAnalysis类,正对Oracle数据库的实现

  1  public class OracleOrmAnalysis:OrmAnalysis  2     {  3         public override DbCommand GetAddCommand(DbCommand command, object model,ref object orderid)  4         {  5             StringBuilder strSql = new StringBuilder();  6             List<DbParameter> parameters = new List<DbParameter>();  7             TableAttribute tableAttribute = GetTableAttribute(model);  8             List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(model);  9             try 10             { 11                 strSql.Append("insert into " + tableAttribute.TableName + "("); 12                 for (int i = 0; i < columnAttributeCollection.Count; i++) 13                 { 14                     if (columnAttributeCollection[i].IsInsert == true) 15                     { 16                         strSql.Append(columnAttributeCollection[i].FieldName);  17                         if (i != (columnAttributeCollection.Count - 1)) 18                         { 19                             strSql.Append(","); 20                         } 21                     } 22                 } 23  24                 strSql.Append(")"); 25                 strSql.Append(" values ("); 26  27  28                 DbParameter parameter; 29  30                 for (int i = 0; i < columnAttributeCollection.Count;i++ ) 31                 { 32                     ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i]; 33  34                     if (columnAttributeInfo.IsInsert == true) 35                     { 36                         object obj = AttributeFunction.GetAttributeValue(columnAttributeInfo.FieldName, columnAttributeInfo.Match, model, Alias); 37                         if (obj == null) 38                             obj = ""; 39                         strSql.Append(":" + columnAttributeInfo.FieldName); 40                         if (i != (columnAttributeCollection.Count - 1)) 41                         { 42                             strSql.Append(","); 43                         } 44                         parameter = command.CreateParameter(); 45                         parameter.ParameterName = columnAttributeInfo.FieldName; 46                         parameter.Value =http://www.mamicode.com/ obj; 47                         if (columnAttributeInfo.DataKey == true)//赋值给自增长ID 48                         { 49                             string strsql = "SELECT OrderID_" + tableAttribute.TableName + ".nextval FROM dual"; 50                             orderid = Db.GetDataResult(strsql); 51                             parameter.Value =http://www.mamicode.com/ Convert.ToInt32(orderid); 52                             AttributeFunction.SetAttributeValue(columnAttributeInfo.FieldName, columnAttributeInfo.Match, model, orderid, Alias); 53                         } 54                         parameters.Add(parameter); 55                     } 56                 } 57  58                 strSql.Append(")"); 59  60                 command.CommandText = strSql.ToString(); 61                 command.Parameters.AddRange(parameters.ToArray()); 62                 return command; 63             } 64             catch (Exception err) 65             { 66                 throw new Exception(err.Message + "SQL:" + strSql.ToString()); 67             } 68         } 69  70         public override DbCommand GetAddCommand(DbCommand command, object model, string[] filedNames, string[] filedvalues, bool[] Isquotation,ref object orderid) 71         { 72             Type type = model.GetType(); 73  74             StringBuilder strSql = new StringBuilder(); 75             List<DbParameter> parameters = new List<DbParameter>(); 76  77             TableAttribute tableAttribute = GetTableAttribute(type); 78             List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(type,tableAttribute); 79  80             try 81             { 82                 strSql.Append("insert into " + tableAttribute.TableName + "("); 83                 //如果此表有自增长列就 84                 bool b = false; 85                 if (tableAttribute.KeyFieldName != null) 86                 { 87                     //查询序列得到自增长ID 88                     string strsql = "SELECT OrderID_" + tableAttribute.TableName + ".nextval FROM dual"; 89                     orderid = Db.GetDataResult(strsql); 90                     //查找传入的参数是否存在此自增长字段,如果存在则改为新的ID 91                      92                     for (int i = 0; i < filedNames.Length; i++) 93                     { 94                         if (filedNames[i].ToUpper() == tableAttribute.KeyFieldName.ToUpper()) 95                         { 96                             filedvalues[i] = orderid.ToString(); 97                             b = true; 98                             break; 99                         }100                     }101                     //如果不存在则添加该字段102                     if (b == false)103                     {104                         strSql.Append(tableAttribute.KeyFieldName + ",");105                     }106                 }107 108                 //strSql.Append("insert into " + tableAttribute.TableName + "( workid ," + tableAttribute.KeyFieldName + ",");109                 for (int i = 0; i < filedNames.Length; i++)110                 {111                     strSql.Append(filedNames[i]);112                     if (i != filedNames.Length - 1)113                     {114                         strSql.Append(",");115                     }116                 }117                 strSql.Append(")");118                 strSql.Append(" values (");119 120                 //如果存在字增长 并且 在传入的参数没有的话 就追加此值121                 if (tableAttribute.KeyFieldName != null && b==false)122                 {123                     strSql.Append(orderid.ToString() + ",");124                 }125 126                 for (int i = 0; i < filedvalues.Length; i++)127                 {128                     if (Isquotation[i])129                     {130                         strSql.Append("" + filedvalues[i] + "");131                     }132                     else133                     {134                         strSql.Append(filedvalues[i]);135                     }136                     if (i != filedvalues.Length - 1)137                     {138                         strSql.Append(",");139                     }140                 }141                 strSql.Append(")");142 143                 command.CommandText = strSql.ToString();144                 return command;145             }146             catch (Exception err)147             {148                 throw new Exception(err.Message + "SQL:" + strSql.ToString());149             }150         }151 152         public override DbCommand GetUpdateCommand(DbCommand command, object model)153         {154             StringBuilder strSql = new StringBuilder();155             List<DbParameter> parameters = new List<DbParameter>();156             TableAttribute tableAttribute = GetTableAttribute(model);157             List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(model);158 159             strSql.Append("update " + tableAttribute.TableName + " set ");160             for (int i = 0; i < columnAttributeCollection.Count; i++)161             {162                 if (!columnAttributeCollection[i].DataKey)163                 {164                     object obj = AttributeFunction.GetAttributeValue(columnAttributeCollection[i].FieldName, columnAttributeCollection[i].Match, model, Alias);165                     if (obj == null)166                         obj = "";167                     strSql.Append(columnAttributeCollection[i].FieldName + "=:" + columnAttributeCollection[i].FieldName);168                     if (i != (columnAttributeCollection.Count - 1))169                     {170                         strSql.Append(",");171                     }172                     DbParameter parameter = command.CreateParameter();173                     parameter.ParameterName = columnAttributeCollection[i].FieldName;174                     parameter.Value =http://www.mamicode.com/ obj;175                     if (obj.GetType().FullName == "System.DateTime") parameter.DbType = System.Data.DbType.DateTime;176                     parameters.Add(parameter);177                 }178             }179             foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)180             {181                 if (columnAttributeInfo.DataKey)182                 {183                     strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);184                     DbParameter parameter = command.CreateParameter();185                     parameter.ParameterName = columnAttributeInfo.FieldName;186                     parameter.Value =http://www.mamicode.com/ AttributeFunction.GetAttributeValue(columnAttributeInfo.FieldName, columnAttributeInfo.Match, model,Alias);187                     parameters.Add(parameter);188                     break;189                 }190             }191             command.CommandText = strSql.ToString();192             command.Parameters.AddRange(parameters.ToArray());193             return command;194         }195 196         public override DbCommand GetDeleteCommand<T>(DbCommand command, int id)197         {198             Type type = typeof(T);199 200             StringBuilder strSql = new StringBuilder();201             List<DbParameter> parameters = new List<DbParameter>();202 203             TableAttribute tableAttribute = GetTableAttribute(type);204             List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(type);205 206             try207             {208                 strSql.Append("delete from  " + tableAttribute.TableName);209                 foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)210                 {211                     if (columnAttributeInfo.DataKey)212                     {213                         strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);214                         DbParameter parameter = command.CreateParameter();215                         parameter.ParameterName = columnAttributeInfo.FieldName;216                         parameter.Value =http://www.mamicode.com/ id;217                         parameters.Add(parameter);218                         break;219                     }220                 }221                 command.CommandText = strSql.ToString();222                 command.Parameters.AddRange(parameters.ToArray());223                 return command;224             }225             catch (Exception err)226             {227                 throw new Exception(err.Message + "SQL:" + strSql.ToString());228             }229         }230 231         public override DbCommand GetExistsCommand<T>(DbCommand command, int id)232         {233             Type type = typeof(T);234 235             StringBuilder strSql = new StringBuilder();236             List<DbParameter> parameters = new List<DbParameter>();237 238             TableAttribute tableAttribute = GetTableAttribute(type);239             List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(type);240 241             try242             {243                 strSql.Append("select count(1) from " + tableAttribute.TableName);244                 foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)245                 {246                     if (columnAttributeInfo.DataKey)247                     {248                         strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);249                         DbParameter parameter = command.CreateParameter();250                         parameter.ParameterName = columnAttributeInfo.FieldName;251                         parameter.Value =http://www.mamicode.com/ id;252                         parameters.Add(parameter);253                         break;254                     }255                 }256                 command.CommandText = strSql.ToString();257                 command.Parameters.AddRange(parameters.ToArray());258                 return command;259             }260             catch (Exception err)261             {262                 throw new Exception(err.Message + "SQL:" + strSql.ToString());263             }264         }265 266         public override DbCommand GetSearchModelCommand<T>(DbCommand command, int id)267         {268             StringBuilder strSql = new StringBuilder();269             List<DbParameter> parameters = new List<DbParameter>();270             TableAttribute tableAttribute = GetTableAttribute(typeof(T));271             List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(typeof(T));272 273             try274             {275                 strSql.Append("select   ");276                 for (int i = 0; i < columnAttributeCollection.Count; i++)277                 {278 279                     strSql.Append(columnAttributeCollection[i].FieldName + " as " + columnAttributeCollection[i].FieldName);280                     if (i != (columnAttributeCollection.Count - 1))281                     {282                         strSql.Append(",");283                     }284 285                 }286                 strSql.Append(" from " + tableAttribute.TableName + " ");287                 foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)288                 {289                     if (columnAttributeInfo.DataKey)290                     {291                         strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);292                         DbParameter parameter = command.CreateParameter();293                         parameter.ParameterName = columnAttributeInfo.FieldName;294                         parameter.Value =http://www.mamicode.com/ id;295                         parameters.Add(parameter);296                         break;297                     }298                 }299                 command.CommandText = strSql.ToString();300                 command.Parameters.AddRange(parameters.ToArray());301                 return command;302             }303             catch (Exception err)304             {305                 throw new Exception(err.Message + "SQL:" + strSql.ToString());306             }307         }308 309         public override DbCommand GetDeleteCommand<T>(DbCommand command, object key)310         {311             throw new NotImplementedException();312         }313 314         public override DbCommand GetSearchModelCommand<T>(DbCommand command, object key)315         {316             throw new NotImplementedException();317         }318 319         public override DbCommand GetExistsCommand<T>(DbCommand command, object key)320         {321             throw new NotImplementedException();322         }323 324         public override DbCommand GetDeleteCommand(Type type, DbCommand command, object key)325         {326             throw new NotImplementedException();327         }328 329         public override DbCommand GetSearchModelCommand(Type type, DbCommand command, object key)330         {331             throw new NotImplementedException();332         }333     }
View Code

 

OrmAnalysisFactory类,根据数据库类型创建上面对应解析类的工厂方法

 1  /// <summary> 2     /// ORM解析类创建工厂 3     /// </summary> 4     class OrmAnalysisFactory 5     { 6         /// <summary> 7         /// 根据数据库类型,创建ORM解析对象 8         /// </summary> 9         /// <param name="databaseType"></param>10         /// <returns></returns>11         public static OrmAnalysis CreateOrmAnalysisObject(DatabaseType databaseType)12         {13             switch (databaseType)14             {15                 //case DatabaseType.Oracle:16                 //    return new OracleOrmAnalysis();17 18                 //case DatabaseType.IbmDb2:19                 //    return new DB2OrmAnalysis();20 21                 case DatabaseType.SqlServer2005:22                     return new SqlServerOrmAnalysis();23             }24 25             throw new Exception("没有实现该数据库");26         }
View Code

 

如此你还可以扩展针对其他数据库的ORM实现,如DB2、MySql等

6.简易ORM对比其他ORM好处

1)首先学习上手容易,不像如Hibernate、iBatis,光掌握哪些配置文件都头大了;

2)还有就是当对数据结构出现比较大的调整时,那对Hibernate的配置调整的工作量就大了,因为想这些小项目要的就是速度,先编码先实现功能再说;

3)一个项目团队中新手还是比较多的,要他们一下就理解Hibernate的工作原理还是有难度的,而在开发过程中碰到这方面的问题肯定无从下手,影响开发效率;

4)还有就是执行效率更好一些;

当然不是说Hibernate这类强大的工具就很差,还是文章之前所说的根据场合来选择,而EFW框架本来最适合的就是中小项目的开发;

 

对象角色建模(ORM)提供了概念性的、易于理解的模型化数据的方法。

ORM方法论基于三个核心原则:
  · 简单。以最基本的形式建模数据。 
  · 传达性。数据库结构被任何人都能理解的语言文档化。
  · 精确性。基于数据模型创建正确标准化了的结构。

 

 DAO模式是标准的J2EE设计模式之一.开发人员使用这个模式把底层的数据访问操作和上层的商务逻辑分开.一个典型的DAO实现有下列几个组件:

  1. 一个DAO工厂类;   

        2. 一个DAO接口;  

     3. 一个实现DAO接口的具体类;   

4. 数据传递对象(有些时候叫做值对象).

 

ORM之硬伤

http://www.cnblogs.com/Barton131420/archive/2007/01/07/613955.html

DAO模式与ORM概念

http://jeromecen1021.blog.163.com/blog/static/1885152712011542145124/

十四、EnterpriseFrameWork框架核心类库之简易ORM