在写本章前先去网上找了一下关于ORM的相关资料，以为本章做准备，发现很多东西今天才了解，所以在这里也对ORM做不了太深入的分析，但还是浅谈一下EFW框架中的设计的简易ORM；文中有一点讲得很有道理，Dao与ORM的区别，Dao是对数据库操作的封装，编写的代码方式像一种设计方法，而ORM支持对象与数据结构的映射，更像一种代码开发工具，有了这个工具会让我们开发代码更简单方便；但是同一类工具有简单的也有复杂的，比如文字工具有简单的Notepad，也有复杂的Word，不是说有了复杂的简单的工具就不需要了，这要看你具体的场合了，你就只是想记录几段文字肯定用Notepad就已经足够了；同样道理，EFW框架中的ORM就想一个Notepad，如果在开发中想解决复杂的数据问题怎么办，那么建议你结合框架中的DAO方式一起使用；
本文要点：
1.简易ORM包含哪些功能
2.如何使用
3.实体的自定义标签TableAttribute和ColumnAttribute
4.接口IORM和实体抽象基类AbstractEntity
5.ORM解析，根据实体标签配置生成相应SQL语句
6.简易ORM对比其他ORM好处
接下来我将从下面几个方面对框架中的ORM进行说明：
1.简易ORM包含哪些功能
在我的想法中ORM主要是实现实体与数据库表之间数据转换的工具，让我们对数据库的操作不需要编写SQL语句，只要操作实体就可以了；而实体操作数据库又可以分为一对一单表操作、一对多多表操作，多对多关联表操作等等；而在本框架中只是实现了单表操作，但对其他操作方式怎么办了，就需要结合框架中的DAO来完成；还有在单表操作中，支持外键ID转换为名称显示；
ORM设计类关系图
2.如何使用
首先创建实体类，并配置好与数据库表的映射，但实体较多的时候此操作可以用一些代码生成工具来完成；
Book实体
[Serializable]
[Table(TableName = "Books", EntityType = EntityType.Table, IsGB = true)]
public class Book : EFWCoreLib.CoreFrame.BusinessArchitecture.AbstractEntity
{
private int id;
[Column(FieldName = "Id", DataKey = true, Match = "", IsInsert = false)]
public int Id
{
get { return id; }
set { id = value; }
}
private string bookName;
[Column(FieldName = "BookName", DataKey = false, Match = "", IsInsert = true)]
public string BookName
{
get { return bookName; }
set { bookName = value; }
}
private decimal buyPrice;
[Column(FieldName = "BuyPrice", DataKey = false, Match = "", IsInsert = true)]
public decimal BuyPrice
{
get { return buyPrice; }
set { buyPrice = value; }
}
private DateTime buyDate;
[Column(FieldName = "BuyDate", DataKey = false, Match = "", IsInsert = true)]
public DateTime BuyDate
{
get { return buyDate; }
set { buyDate = value; }
}
private int flag;
[Column(FieldName = "Flag", DataKey = false, Match = "", IsInsert = true)]
public int Flag
{
get { return flag; }
set { flag = value; }
}
}
控制器调用实体的代码
public void TestEntity()
{
//创建实体对象实例
Book book = NewObject<Book>(); //1.根据id获取一条记录
book= book.getmodel() as Book; //2.修改或者新增一条记录
book.BookName = "人月神话";
book.BuyPrice = ;
book.BuyDate = Convert.ToDateTime("2014-01-01");
book.save(); //3.根据id删除表数据
book.delete(); //4.获取表所有记录转换为List实体对象
List<Book> booklist = book.getlist<Book>(); //5.获取表所有记录转换为DataTable
DataTable dt = book.gettable();
}
由上面可见，用此种方式操作数据库是如此的简单，只需要使用实体的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的功能可以扩展此接口；
/// <summary>
/// 简易ORM接口,其中alias别名是指实体配置的自定义标签Table属性Alias的值
/// </summary>
interface IORM
{
/// <summary>
/// 插入或更新到数据库
/// </summary>
/// <returns></returns>
int save();
/// <summary>
/// 插入或更新到数据库
/// </summary>
/// <param name="alias">实体别名</param>
/// <returns></returns>
int save(string alias);
/// <summary>
/// 根据ID获取实体数据
/// </summary>
/// <returns></returns>
object getmodel();
/// <summary>
/// 指定key和别名获取实体数据
/// </summary>
/// <param name="key"></param>
/// <param name="alias"></param>
/// <returns></returns>
object getmodel(object key, string alias);
/// <summary>
/// 指定key获取实体数据
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
object getmodel(object key);
/// <summary>
/// 指定key和别名删除实体数据
/// </summary>
/// <param name="key"></param>
/// <param name="alias"></param>
/// <returns></returns>
int delete(object key, string alias);
/// <summary>
/// 指定key删除实体数据
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
int delete(object key);
/// <summary>
/// 根据ID删除实体数据
/// </summary>
/// <returns></returns>
int delete();
/// <summary>
/// 获取实体List对象集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
System.Collections.Generic.List<T> getlist<T>();
/// <summary>
/// 获取实体List对象集合，根据where条件过滤
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
System.Collections.Generic.List<T> getlist<T>(string where);
/// <summary>
/// 获取实体List对象集合，根据where条件过滤
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageInfo">分页</param>
/// <param name="where"></param>
/// <returns></returns>
System.Collections.Generic.List<T> getlist<T>(PageInfo pageInfo, string where);
/// <summary>
/// 获取实体List对象集合，根据where条件过滤
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageInfo"></param>
/// <param name="where"></param>
/// <param name="alias"></param>
/// <returns></returns>
System.Collections.Generic.List<T> getlist<T>(PageInfo pageInfo, string where, string alias);
/// <summary>
/// 获取实体datatable
/// </summary>
/// <returns></returns>
System.Data.DataTable gettable();
/// <summary>
/// 获取实体datatable，where条件过滤
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
System.Data.DataTable gettable(string where);
/// <summary>
/// 获取实体datatable，分页
/// </summary>
/// <param name="pageInfo"></param>
/// <param name="where"></param>
/// <param name="alias"></param>
/// <returns></returns>
System.Data.DataTable gettable(PageInfo pageInfo, string where, string alias);
/// <summary>
/// 获取实体datatable，分页
/// </summary>
/// <param name="pageInfo"></param>
/// <param name="where"></param>
/// <returns></returns>
System.Data.DataTable gettable(PageInfo pageInfo, string where);
}
实体抽象基类AbstractEntity，实现了对IORM接口所有操作方法，还有所有实体必须继承此基类；
/// <summary>
/// 实体基类
/// </summary>
public abstract class AbstractEntity:AbstractBusines,IORM
{
#region IORM 成员
protected void SetEntityValue(string propertyName, object model, object value)
{
PropertyInfo property = model.GetType().GetProperty(propertyName);
property.SetValue(model, ConvertValue(property.PropertyType.FullName, value), null);
}
protected object ConvertValue(string PropertyType, object value)
{
if (value == null) return null; if (value.GetType().FullName == "System.Guid")
{
return value.ToString();
} switch (PropertyType)
{
case "System.DBNull":
return null;
case "System.Int32":
value = value == DBNull.Value ? : value;
value = value == null ? : value;
value = value.ToString().Trim() == "" ? : value;
return Convert.ToInt32(value);
case "System.Int64":
value = value == DBNull.Value ? : value;
value = value == null ? : value;
value = value.ToString().Trim() == "" ? : value;
return Convert.ToInt64(value);
case "System.Decimal":
value = value == DBNull.Value ? : value;
value = value == null ? : value;
value = value.ToString().Trim() == "" ? : value;
return Convert.ToDecimal(value);
case "System.DateTime":
value = value == DBNull.Value ? new DateTime() : value;
value = value == null ? new DateTime() : value;
value = value.ToString().Trim() == "" ? new DateTime() : value;
return Convert.ToDateTime(value);
} value = value == DBNull.Value ? null : value;
return value;
}
private object ToObject(System.Data.IDataReader dataReader, object _obj, string alias)
{
Type type = _obj.GetType();
object obj = ((ICloneable)_obj).Clone();
System.Collections.Hashtable filedValue = new System.Collections.Hashtable();
for (int index = ; index < dataReader.FieldCount; index++)
{
filedValue.Add(dataReader.GetName(index), dataReader[index]);
}
foreach (System.Reflection.PropertyInfo property in type.GetProperties())
{
SetEntityValue(property.Name, obj, filedValue[property.Name]);
}
((IbindDb)obj).BindDb(_oleDb, _container);
return obj;
} public int save() { return save(null); }
public int save(string alias)
{
OrmAnalysis ormAnalysis; ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);
ormAnalysis.Alias = alias;
ormAnalysis.Db = _oleDb;
object keyVal = ormAnalysis.GetEntityDataKeyValue(this); if (keyVal == null || (keyVal.GetType().Equals(typeof(int)) && Convert.ToInt32(keyVal) == ))
{ string strsql = ormAnalysis.GetInsertSQL(this);
int ret = ;
ret = _oleDb.InsertRecord(strsql);
ormAnalysis.SetEntityValue(ormAnalysis.GetEntityDataKeyPropertyName(this), this, ret); return ret;
}
else
{
string strsql = ormAnalysis.GetUpdateSQL(this);
return _oleDb.DoCommand(strsql);
}
} public int delete() { return delete(null, null); }
public int delete(object key) { return delete(key, null); }
public int delete(object key, string alias)
{
OrmAnalysis ormAnalysis; ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);
ormAnalysis.Alias = alias;
ormAnalysis.Db = _oleDb;
object keyVal = key == null ? ormAnalysis.GetEntityDataKeyValue(this) : key;
string strsql = ormAnalysis.GetDeleteSQL(this.GetType(), keyVal);
return _oleDb.DoCommand(strsql);
} public object getmodel() { return getmodel(null, null); }
public object getmodel(object key) { return getmodel(key, null); }
public object getmodel(object key, string alias)
{
OrmAnalysis ormAnalysis; ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);
ormAnalysis.Alias = alias;
ormAnalysis.Db = _oleDb;
object value = null;
object keyVal = key == null ? ormAnalysis.GetEntityDataKeyValue(this) : key; string strsql = ormAnalysis.GetEntitySQL(this.GetType(), keyVal);
System.Data.IDataReader result = _oleDb.GetDataReader(strsql); if (result.Read())
{
value = ToObject(result, this, alias); }
result.Close();
result.Dispose(); return value;
}
public List<T> getlist<T>()
{
return getlist<T>(null, null, null);
}
public List<T> getlist<T>(string where) { return getlist<T>(null, where, null); }
public List<T> getlist<T>(PageInfo pageInfo, string where) { return getlist<T>(pageInfo, where, null); }
public List<T> getlist<T>(PageInfo pageInfo, string where, string alias)
{
OrmAnalysis ormAnalysis;
ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);
ormAnalysis.Alias = alias;
ormAnalysis.Db = _oleDb; string strsql = ormAnalysis.GetListSQL(this.GetType(), where, pageInfo); IDataReader result = _oleDb.GetDataReader(strsql);
List<T> resultList = new List<T>();
while (result.Read())
{
resultList.Add((T)ToObject(result, this, alias));
}
result.Close();
result.Dispose();
return resultList;
}
public DataTable gettable()
{
return gettable(null, null, null);
}
public DataTable gettable(string where) { return gettable(null, where, null); }
public DataTable gettable(PageInfo pageInfo, string where) { return gettable(pageInfo, where,null); }
public DataTable gettable(PageInfo pageInfo, string where, string alias)
{
OrmAnalysis ormAnalysis;
ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);
ormAnalysis.Alias = alias;
ormAnalysis.Db = _oleDb; string strsql = ormAnalysis.GetListSQL(this.GetType(), where, pageInfo); return _oleDb.GetDataTable(strsql);
} #endregion
}
5.ORM解析，根据实体标签配置生成相应SQL语句
实体怎么操作数据库的，就是根据实体配置信息转换为sql语句再执行，由于不同数据库之间的差异，所以生成的sql语句会不一样，这里的设计用了工厂模式；
OrmAnalysis解析为sql语句基类，定义了生成不同sql语句的操作方法；包括插入语句、删除语句、获取数据语句等
/// <summary>
/// ORM映射关系解析基类
/// </summary>
abstract public class OrmAnalysis
{ private AbstractDatabase _Db;
/// <summary>
/// 数据库对象
/// </summary>
public AbstractDatabase Db
{
get
{
return _Db;
}
set
{
_Db = value;
}
} private string _alias;
/// <summary>
/// 别名
/// </summary>
public string Alias
{
get { return _alias; }
set { _alias = value; }
} protected string JoinWhere(bool isgb, string strWhere)
{
if (IsJoinWorkId(isgb))
{
strWhere = "WorkId = " + Db.WorkId + (string.IsNullOrEmpty(strWhere) ? " " : " and " + strWhere);
} string where = string.IsNullOrEmpty(strWhere) ? "" : ("where " + strWhere);
return where;
} protected bool IsJoinWorkId(bool isgb)
{
if (AppGlobal.IsSaas == true && isgb == false && Db.WorkId > )
{
return true;
} return false;
} #region 解析实体属性
protected TableAttributeInfo GetTableAttributeInfo(Type type)
{
List<Entity_Attribute> entityAttrList = (List<Entity_Attribute>)AppGlobal.cache.GetData("entityAttributeList");
Entity_Attribute EAttr = entityAttrList.Find(x => x.ObjType.Equals(type));
if (EAttr == null) throw new Exception("此对象没有配置实体自定义属性");
TableAttributeInfo tableAttrInfo = EAttr.TableAttributeInfoList.Find(x => x.Alias == Alias);
//if (tableAttrInfo) throw new Exception("找不到相同别名的表自定义属性");
return tableAttrInfo;
} protected TableAttributeInfo GetTableAttributeInfo(object model)
{
return GetTableAttributeInfo(model.GetType());
} protected object GetEntityValue(string propertyName, object model)
{
object data = model.GetType().GetProperty(propertyName).GetValue(model, null);
if (model.GetType().GetProperty(propertyName).PropertyType.FullName == "System.DateTime" && Convert.ToDateTime(data) == default(DateTime))
{
data = Convert.ToDateTime("1900/01/01 00:00:00");
} return data;
} public void SetEntityValue(string propertyName, object model, object value)
{
PropertyInfo property = model.GetType().GetProperty(propertyName);
property.SetValue(model, ConvertValue(property.PropertyType.FullName, value), null);
} protected object ConvertValue(string PropertyType, object value)
{
if (value.GetType().FullName == "System.Guid")
{
return value.ToString();
} switch (PropertyType)
{
case "System.DBNull":
return null;
case "System.Int32":
value = value == DBNull.Value ? : value;
value = value == null ? : value;
value = value.ToString().Trim() == "" ? : value;
return Convert.ToInt32(value);
case "System.Int64":
value = value == DBNull.Value ? : value;
value = value == null ? : value;
value = value.ToString().Trim() == "" ? : value;
return Convert.ToInt64(value);
case "System.Decimal":
value = value == DBNull.Value ? : value;
value = value == null ? : value;
value = value.ToString().Trim() == "" ? : value;
return Convert.ToDecimal(value);
case "System.DateTime":
value = value == DBNull.Value ? new DateTime() : value;
value = value == null ? new DateTime() : value;
value = value.ToString().Trim() == "" ? new DateTime() : value;
return Convert.ToDateTime(value);
} value = value == DBNull.Value ? null : value;
return value;
} protected string ConvertDBValue(object value)
{
if(value==null) return "NULL"; string PropertyType = value.GetType().FullName;
switch (PropertyType)
{
case "System.String":
return "'" + value.ToString() + "'";
case "System.DateTime":
return "'" + value.ToString() + "'";
case "System.Guid":
return "'" + value.ToString() + "'";
case "System.Boolean":
return "'" + value.ToString() + "'";
} return value.ToString();
} public object GetEntityDataKeyValue(object model)
{
TableAttributeInfo tableAttribute = GetTableAttributeInfo(model);
return GetEntityValue(tableAttribute.DataKeyPropertyName, model);
} public string GetEntityDataKeyPropertyName(object model)
{
TableAttributeInfo tableAttribute = GetTableAttributeInfo(model);
return tableAttribute.DataKeyPropertyName;
}
#endregion /// <summary>
/// 向数据库中增加一条数据
/// </summary>
/// <param name="model">要写入的实体</param>
/// <returns></returns>
public abstract string GetInsertSQL(object model); /// <summary>
/// 根据给定实例更新一条记录
/// </summary>
/// <param name="command">数据库命令执行对象</param>
/// <returns></returns>
public abstract string GetUpdateSQL(object model); /// <summary>
/// 根据key删除一条记录
/// </summary>
/// <returns></returns>
public abstract string GetDeleteSQL<T>(object key);
public abstract string GetDeleteSQL(Type type,object key); /// <summary>
/// 根据唯一ID得到一个对象实体
/// </summary>
/// <returns></returns>
public abstract string GetEntitySQL<T>(object key);
public abstract string GetEntitySQL(Type type, object key);
/// <summary>
/// 得到实体对象集合sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public abstract string GetListSQL<T>();
public abstract string GetListSQL<T>(string strWhere);
public abstract string GetListSQL<T>(string strWhere,PageInfo pageInfo);
public abstract string GetListSQL(Type type,string strWhere);
public abstract string GetListSQL(Type type,string strWhere,PageInfo pageInfo); }
SqlServerOrmAnalysis类，针对SqlServer数据库的实现
/// <summary>
/// 基于ORM实现sqlserver数据库的ORM
/// </summary>
public class SqlServerOrmAnalysis:OrmAnalysis
{ public override string GetInsertSQL(object model)
{
string strsql = "";
try
{
Dictionary<string, object> dicsql = new Dictionary<string, object>(); TableAttributeInfo tableAttribute = GetTableAttributeInfo(model);
List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList; for (int i = ; i < columnAttributeCollection.Count; i++)
{ ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i]; if (columnAttributeInfo.DataKey == true && columnAttributeInfo.Match == "Custom:Guid")//赋值给自增长ID
{
object obj = GetEntityValue(columnAttributeInfo.PropertyName, model);
obj = obj == null ? Guid.NewGuid().ToString() : obj; SetEntityValue(columnAttributeInfo.PropertyName, model, obj); dicsql.Add(columnAttributeInfo.FieldName, obj);
}
else
{ if (columnAttributeInfo.IsInsert == true)
{
object obj = GetEntityValue(columnAttributeInfo.PropertyName, model);
//obj = obj == null ? DBNull.Value : obj;
dicsql.Add(columnAttributeInfo.FieldName, obj);
}
}
} string fields = "";
string values = "";
strsql = "insert into {0} ({1}) values({2})"; if (IsJoinWorkId(tableAttribute.IsGB))
{
dicsql.Add("WorkId", Db.WorkId);
} foreach (KeyValuePair<string, object> val in dicsql)
{
fields += (fields == "" ? "" : ",") + val.Key;
values += (values == "" ? "" : ",") + ConvertDBValue(val.Value);
} return string.Format(strsql, tableAttribute.TableName, fields, values);
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strsql);
}
} public override string GetUpdateSQL(object model)
{
string strsql = "";
string where = "";
try
{
Dictionary<string, object> dicsql = new Dictionary<string, object>(); TableAttributeInfo tableAttribute = GetTableAttributeInfo(model);
List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList; for (int i = ; i < columnAttributeCollection.Count; i++)
{ ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i]; if (columnAttributeInfo.DataKey == false)
{
object obj = GetEntityValue(columnAttributeInfo.PropertyName, model);
dicsql.Add(columnAttributeInfo.FieldName, obj);
} if (columnAttributeInfo.DataKey == true)
{
object obj = GetEntityValue(columnAttributeInfo.PropertyName, model);
where = columnAttributeInfo.FieldName + "=" + ConvertDBValue(obj);
}
} string field_values = ""; strsql = "update {0} set {1} where {2}"; foreach (KeyValuePair<string, object> val in dicsql)
{
field_values += (field_values == "" ? "" : ",") + val.Key + "=" + ConvertDBValue(val.Value);
} return string.Format(strsql, tableAttribute.TableName, field_values, where);
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strsql);
}
} public override string GetDeleteSQL<T>(object key)
{
return GetDeleteSQL(typeof(T), key);
} public override string GetDeleteSQL(Type type, object key)
{
string strsql = "";
string where = "";
try
{ TableAttributeInfo tableAttribute = GetTableAttributeInfo(type);
List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList; for (int i = ; i < columnAttributeCollection.Count; i++)
{ ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i];
if (columnAttributeInfo.DataKey == true)
{
object obj = key;
where = columnAttributeInfo.FieldName + "=" + ConvertDBValue(obj);
}
} strsql = "delete from {0} where {1}"; return string.Format(strsql, tableAttribute.TableName, where);
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strsql);
}
} public override string GetEntitySQL<T>(object key)
{
return GetEntitySQL(typeof(T), key);
} public override string GetEntitySQL(Type type, object key)
{
string strsql = "";
string fields = "";
string where = "";
try
{ TableAttributeInfo tableAttribute = GetTableAttributeInfo(type);
List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList; for (int i = ; i < columnAttributeCollection.Count; i++)
{ ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i]; fields += (fields == "" ? "" : ",") + columnAttributeInfo.FieldName + " as " + columnAttributeInfo.PropertyName; if (columnAttributeInfo.DataKey == true)
{
object obj = key;
where = columnAttributeInfo.FieldName + "=" + ConvertDBValue(obj);
}
} strsql = "select {0} from {1} where {2}"; return string.Format(strsql, fields, tableAttribute.TableName, where);
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strsql);
}
} public override string GetListSQL<T>()
{
return GetListSQL(typeof(T), null, null);
} public override string GetListSQL<T>(string strWhere)
{
return GetListSQL(typeof(T), strWhere, null);
} public override string GetListSQL<T>(string strWhere, EFWCoreLib.CoreFrame.DbProvider.SqlPagination.PageInfo pageInfo)
{
return GetListSQL(typeof(T), strWhere, pageInfo);
} public override string GetListSQL(Type type, string strWhere)
{
return GetListSQL(type, strWhere, null);
} public override string GetListSQL(Type type, string strWhere, EFWCoreLib.CoreFrame.DbProvider.SqlPagination.PageInfo pageInfo)
{
string strsql = "";
string fields = "";
string where = "";
try
{ TableAttributeInfo tableAttribute = GetTableAttributeInfo(type);
List<ColumnAttributeInfo> columnAttributeCollection = tableAttribute.ColumnAttributeInfoList; for (int i = ; i < columnAttributeCollection.Count; i++)
{
ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i];
fields += (fields == "" ? "" : ",") + columnAttributeInfo.FieldName + " as " + columnAttributeInfo.PropertyName;
} where = JoinWhere(tableAttribute.IsGB, strWhere);
strsql = "select {0} from {1} {2}";
strsql = string.Format(strsql, fields, tableAttribute.TableName + " as T1", where); if (pageInfo != null)
{
if (pageInfo.KeyName == null)
pageInfo.KeyName = tableAttribute.DataKeyFieldName;
strsql = EFWCoreLib.CoreFrame.DbProvider.SqlPagination.SqlPage.FormatSql(strsql, pageInfo, Db);
}
return strsql;
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strsql);
}
} }
OracleOrmAnalysis类，正对Oracle数据库的实现
public class OracleOrmAnalysis:OrmAnalysis
{
public override DbCommand GetAddCommand(DbCommand command, object model,ref object orderid)
{
StringBuilder strSql = new StringBuilder();
List<DbParameter> parameters = new List<DbParameter>();
TableAttribute tableAttribute = GetTableAttribute(model);
List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(model);
try
{
strSql.Append("insert into " + tableAttribute.TableName + "(");
for (int i = ; i < columnAttributeCollection.Count; i++)
{
if (columnAttributeCollection[i].IsInsert == true)
{
strSql.Append(columnAttributeCollection[i].FieldName);
if (i != (columnAttributeCollection.Count - ))
{
strSql.Append(",");
}
}
} strSql.Append(")");
strSql.Append(" values ("); DbParameter parameter; for (int i = ; i < columnAttributeCollection.Count;i++ )
{
ColumnAttributeInfo columnAttributeInfo = columnAttributeCollection[i]; if (columnAttributeInfo.IsInsert == true)
{
object obj = AttributeFunction.GetAttributeValue(columnAttributeInfo.FieldName, columnAttributeInfo.Match, model, Alias);
if (obj == null)
obj = "";
strSql.Append(":" + columnAttributeInfo.FieldName);
if (i != (columnAttributeCollection.Count - ))
{
strSql.Append(",");
}
parameter = command.CreateParameter();
parameter.ParameterName = columnAttributeInfo.FieldName;
parameter.Value = obj;
if (columnAttributeInfo.DataKey == true)//赋值给自增长ID
{
string strsql = "SELECT OrderID_" + tableAttribute.TableName + ".nextval FROM dual";
orderid = Db.GetDataResult(strsql);
parameter.Value = Convert.ToInt32(orderid);
AttributeFunction.SetAttributeValue(columnAttributeInfo.FieldName, columnAttributeInfo.Match, model, orderid, Alias);
}
parameters.Add(parameter);
}
} strSql.Append(")"); command.CommandText = strSql.ToString();
command.Parameters.AddRange(parameters.ToArray());
return command;
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strSql.ToString());
}
} public override DbCommand GetAddCommand(DbCommand command, object model, string[] filedNames, string[] filedvalues, bool[] Isquotation,ref object orderid)
{
Type type = model.GetType(); StringBuilder strSql = new StringBuilder();
List<DbParameter> parameters = new List<DbParameter>(); TableAttribute tableAttribute = GetTableAttribute(type);
List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(type,tableAttribute); try
{
strSql.Append("insert into " + tableAttribute.TableName + "(");
//如果此表有自增长列就
bool b = false;
if (tableAttribute.KeyFieldName != null)
{
//查询序列得到自增长ID
string strsql = "SELECT OrderID_" + tableAttribute.TableName + ".nextval FROM dual";
orderid = Db.GetDataResult(strsql);
//查找传入的参数是否存在此自增长字段，如果存在则改为新的ID for (int i = ; i < filedNames.Length; i++)
{
if (filedNames[i].ToUpper() == tableAttribute.KeyFieldName.ToUpper())
{
filedvalues[i] = orderid.ToString();
b = true;
break;
}
}
//如果不存在则添加该字段
if (b == false)
{
strSql.Append(tableAttribute.KeyFieldName + ",");
}
} //strSql.Append("insert into " + tableAttribute.TableName + "( workid ," + tableAttribute.KeyFieldName + ",");
for (int i = ; i < filedNames.Length; i++)
{
strSql.Append(filedNames[i]);
if (i != filedNames.Length - )
{
strSql.Append(",");
}
}
strSql.Append(")");
strSql.Append(" values ("); //如果存在字增长 并且 在传入的参数没有的话 就追加此值
if (tableAttribute.KeyFieldName != null && b==false)
{
strSql.Append(orderid.ToString() + ",");
} for (int i = ; i < filedvalues.Length; i++)
{
if (Isquotation[i])
{
strSql.Append("'" + filedvalues[i] + "'");
}
else
{
strSql.Append(filedvalues[i]);
}
if (i != filedvalues.Length - )
{
strSql.Append(",");
}
}
strSql.Append(")"); command.CommandText = strSql.ToString();
return command;
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strSql.ToString());
}
} public override DbCommand GetUpdateCommand(DbCommand command, object model)
{
StringBuilder strSql = new StringBuilder();
List<DbParameter> parameters = new List<DbParameter>();
TableAttribute tableAttribute = GetTableAttribute(model);
List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(model); strSql.Append("update " + tableAttribute.TableName + " set ");
for (int i = ; i < columnAttributeCollection.Count; i++)
{
if (!columnAttributeCollection[i].DataKey)
{
object obj = AttributeFunction.GetAttributeValue(columnAttributeCollection[i].FieldName, columnAttributeCollection[i].Match, model, Alias);
if (obj == null)
obj = "";
strSql.Append(columnAttributeCollection[i].FieldName + "=:" + columnAttributeCollection[i].FieldName);
if (i != (columnAttributeCollection.Count - ))
{
strSql.Append(",");
}
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = columnAttributeCollection[i].FieldName;
parameter.Value = obj;
if (obj.GetType().FullName == "System.DateTime") parameter.DbType = System.Data.DbType.DateTime;
parameters.Add(parameter);
}
}
foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)
{
if (columnAttributeInfo.DataKey)
{
strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = columnAttributeInfo.FieldName;
parameter.Value = AttributeFunction.GetAttributeValue(columnAttributeInfo.FieldName, columnAttributeInfo.Match, model,Alias);
parameters.Add(parameter);
break;
}
}
command.CommandText = strSql.ToString();
command.Parameters.AddRange(parameters.ToArray());
return command;
} public override DbCommand GetDeleteCommand<T>(DbCommand command, int id)
{
Type type = typeof(T); StringBuilder strSql = new StringBuilder();
List<DbParameter> parameters = new List<DbParameter>(); TableAttribute tableAttribute = GetTableAttribute(type);
List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(type); try
{
strSql.Append("delete from " + tableAttribute.TableName);
foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)
{
if (columnAttributeInfo.DataKey)
{
strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = columnAttributeInfo.FieldName;
parameter.Value = id;
parameters.Add(parameter);
break;
}
}
command.CommandText = strSql.ToString();
command.Parameters.AddRange(parameters.ToArray());
return command;
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strSql.ToString());
}
} public override DbCommand GetExistsCommand<T>(DbCommand command, int id)
{
Type type = typeof(T); StringBuilder strSql = new StringBuilder();
List<DbParameter> parameters = new List<DbParameter>(); TableAttribute tableAttribute = GetTableAttribute(type);
List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(type); try
{
strSql.Append("select count(1) from " + tableAttribute.TableName);
foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)
{
if (columnAttributeInfo.DataKey)
{
strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = columnAttributeInfo.FieldName;
parameter.Value = id;
parameters.Add(parameter);
break;
}
}
command.CommandText = strSql.ToString();
command.Parameters.AddRange(parameters.ToArray());
return command;
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strSql.ToString());
}
} public override DbCommand GetSearchModelCommand<T>(DbCommand command, int id)
{
StringBuilder strSql = new StringBuilder();
List<DbParameter> parameters = new List<DbParameter>();
TableAttribute tableAttribute = GetTableAttribute(typeof(T));
List<ColumnAttributeInfo> columnAttributeCollection = GetColumnAttributeCollection(typeof(T)); try
{
strSql.Append("select ");
for (int i = ; i < columnAttributeCollection.Count; i++)
{ strSql.Append(columnAttributeCollection[i].FieldName + " as " + columnAttributeCollection[i].FieldName);
if (i != (columnAttributeCollection.Count - ))
{
strSql.Append(",");
} }
strSql.Append(" from " + tableAttribute.TableName + " ");
foreach (ColumnAttributeInfo columnAttributeInfo in columnAttributeCollection)
{
if (columnAttributeInfo.DataKey)
{
strSql.Append(" where " + columnAttributeInfo.FieldName + "=:" + columnAttributeInfo.FieldName);
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = columnAttributeInfo.FieldName;
parameter.Value = id;
parameters.Add(parameter);
break;
}
}
command.CommandText = strSql.ToString();
command.Parameters.AddRange(parameters.ToArray());
return command;
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL:" + strSql.ToString());
}
} public override DbCommand GetDeleteCommand<T>(DbCommand command, object key)
{
throw new NotImplementedException();
} public override DbCommand GetSearchModelCommand<T>(DbCommand command, object key)
{
throw new NotImplementedException();
} public override DbCommand GetExistsCommand<T>(DbCommand command, object key)
{
throw new NotImplementedException();
} public override DbCommand GetDeleteCommand(Type type, DbCommand command, object key)
{
throw new NotImplementedException();
} public override DbCommand GetSearchModelCommand(Type type, DbCommand command, object key)
{
throw new NotImplementedException();
}
}
OrmAnalysisFactory类，根据数据库类型创建上面对应解析类的工厂方法
/// <summary>
/// ORM解析类创建工厂
/// </summary>
class OrmAnalysisFactory
{
/// <summary>
/// 根据数据库类型，创建ORM解析对象
/// </summary>
/// <param name="databaseType"></param>
/// <returns></returns>
public static OrmAnalysis CreateOrmAnalysisObject(DatabaseType databaseType)
{
switch (databaseType)
{
//case DatabaseType.Oracle:
// return new OracleOrmAnalysis(); //case DatabaseType.IbmDb2:
// return new DB2OrmAnalysis(); case DatabaseType.SqlServer2005:
return new SqlServerOrmAnalysis();
} throw new Exception("没有实现该数据库");
}
如此你还可以扩展针对其他数据库的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/