珂珂的个人博客 - 一个程序猿的个人网站
第二篇是写一个orm帮助类EntityHelper 该类是泛型类 T 就是实体类型
还定义了一个委托public delegate object NewIDHandler(); 用于生成主键ID，因为我们有时候并不会让数据库来生成主键，比如我们用到多数据库同步的时候，自动递增可能不是太方便.
至于里面的IDbHelper 接口 请参照 <<一个通用的数据组件>>
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
namespace FYJ.Data.Entity
{
public delegate object NewIDHandler();
////// 提供实体通用的基本操作方法
//////实体类型public class EntityHelperwhere T : new()
{
private string tableName;
private string primary;
private NewIDHandler newID;
public EntityHelper(string tableName, string primary, NewIDHandler newID)
{
this.tableName = tableName;
this.primary = primary;
this.newID = newID;
}
public void SetTableName(string tableName)
{
this.tableName = tableName;
}
#region private
////// 获取一个实体不为null属性的IDataParameter参数
////////////internal ListGetIDataParameters(T model, IDbHelper dbHelper)
{
PropertyInfo[] pis = model.GetType().GetProperties();
Listparames = new List();
foreach (PropertyInfo pi in pis)
{
string parameterName = pi.Name;
object[] atts = pi.GetCustomAttributes(typeof(ColumnAttribute), false);
if (atts != null || atts.Length <= 0) { ColumnAttribute ca = (ColumnAttribute)atts[0]; parameterName = ca.Name; } object obj = pi.GetValue(model, null); if (obj != null) //如果属性的值不为null 才将其加入到参数中 { DbParameter parameter = dbHelper.CreateParameter(); parameter.ParameterName = "@" + parameterName; parameter.Value = obj; parames.Add(parameter); } } return parames; } ////// 根据参数获取sql 语句
/////////对于字符串是否使用like///internal string GetIDataParameterWhere(ref ListcmdParms)
{
string where = "";
if (cmdParms != null && cmdParms.Count > 0)
{
where += " WHERE ";
foreach (IDataParameter parameter in cmdParms)
{
where += parameter.ParameterName.Substring(1) + "=" + parameter.ParameterName + " AND ";
}
where = where.Substring(0, where.LastIndexOf("AND")); //移除最后的and
}
return where;
}
#endregion
#region 查询数据
////// 获取一条实体数据
///////////////public T GetModel(string guid, IDbHelper dbHelper)
{
T model = default(T);
string parameterPre = dbHelper.DbHelperTypeEnum == DbHelperType.Oracle ? ":" : "@"; //参数前缀
string sql = "SELECT * FROM " + this.tableName + " WHERE " + this.primary + "=" + parameterPre + this.primary;
DataTable dt = dbHelper.GetDataTable(sql, dbHelper.CreateParameter(parameterPre + this.primary, guid));
if (dt.Rows.Count == 1)
{
DataConvert convert = new DataConvert();
model = convert.DataTableToModel(dt)[0];
}
return model;
}
////// 获取一条实体数据
//////主键Id值///访问的列 如果为空则访问所有列//////public T GetModel(long id, IDbHelper dbHelper)
{
return GetModel(id + "", dbHelper);
}
public bool Exists(T model, IDbHelper dbHelper)
{
Listparameters = new List();
parameters = GetIDataParameters(model, dbHelper);
DataTable dt = dbHelper.GetDataTable("SELECT 1 FROM " + this.tableName + " " + GetIDataParameterWhere(ref parameters), parameters.ToArray());
if (dt == null || dt.Rows.Count == 0)
{
return false;
}
return true;
}
public DataTable GetDataTable(T model, IDbHelper dbHelper, string[] accessProperty = null)
{
Listparameters = new List();
parameters = GetIDataParameters(model, dbHelper);
string select = "";
if (accessProperty != null)
{
select += accessProperty + ",";
}
else
{
select = "*";
}
select = select.TrimEnd(',');
DataTable dt = dbHelper.GetDataTable("SELECT " + select + " FROM " + this.tableName + " " + GetIDataParameterWhere(ref parameters), parameters.ToArray());
return dt;
}
#endregion
#region 新增
public int Add(T model, IDbHelper dbHelper)
{
string parameterPre = dbHelper.DbHelperTypeEnum == DbHelperType.Oracle ? ":" : "@"; //参数前缀
PropertyInfo[] pis = model.GetType().GetProperties();
String sql = "insert into {0} ({1}) values ({2})";
String col = "";
String val = "";
Listparames = new List();
foreach (PropertyInfo pi in pis)
{
string parameterName = pi.Name;
object[] atts = pi.GetCustomAttributes(typeof(ColumnAttribute), false);
if (atts != null && atts.Length > 0)
{
ColumnAttribute ca = (ColumnAttribute)atts[0];
parameterName = ca.Name;
}
DbParameter parameter = dbHelper.CreateParameter();
object obj = pi.GetValue(model, null);
DateTime date = DateTime.Now;
if (parameterName == "ADD_DATE")
{
obj = date;
}
if (parameterName == "UPDATE_DATE")
{
obj = date;
}
//如果是主键
if (parameterName.Equals(this.primary,StringComparison.CurrentCultureIgnoreCase))
{
if (obj == null || obj.ToString() == "" || obj.ToString() == "0")
{
obj = newID();
//newID.Invoke();
}
}
if (obj != null)
{
col += parameterName + ",";
val += parameterPre + parameterName + ",";
parameter.ParameterName = parameterPre + parameterName;
parameter.Value = obj;
parames.Add(parameter);
}
}
col = col.TrimEnd(',');
val = val.TrimEnd(',');
sql = String.Format(sql, this.tableName, col, val);
return dbHelper.ExecuteSql(sql, parames.ToArray());
}
#endregion
#region 修改
////// 修改数据
//////新实体///要更新的列///public int Update(T model, IDbHelper dbHelper)
{
string parameterPre = dbHelper.DbHelperTypeEnum == DbHelperType.Oracle ? ":" : "@"; //参数前缀
String sql = "update {0} set {1} where " + this.primary + "=" + parameterPre + this.primary;
String col = "";
Listparames = new List();
object id = 0;
String pkName = null;
PropertyInfo[] pis = model.GetType().GetProperties();
foreach (PropertyInfo pi in pis)
{
string parameterName = pi.Name;
object obj = pi.GetValue(model, null); //获取属性值
if (parameterName == "UPDATE_DATE")
{
obj = DateTime.Now;
}
if (obj != null) //如果属性值不为空才进行更新
{
//如果不是主键
if (!this.primary.Equals(parameterName,StringComparison.CurrentCultureIgnoreCase))
{
DbParameter parameter = dbHelper.CreateParameter();
col += parameterName + "=" + parameterPre + parameterName + ",";
parameter.ParameterName = parameterPre + parameterName;
parameter.Value = obj;
parames.Add(parameter);
}
else
{
DbParameter parameter = dbHelper.CreateParameter();
parameter.ParameterName = parameterPre + parameterName;
parameter.Value = obj;
parames.Add(parameter);
}
}
}
col = col.TrimEnd(',');
sql = String.Format(sql, this.tableName, col, pkName, id);
return dbHelper.ExecuteSql(sql, parames.ToArray());
}
#endregion
#region 删除
////// 根据GUID删除多条数据
////////////public int Delete(string[] guids, IDbHelper dbHelper)
{
String ids = "";
foreach (string guid in guids)
{
ids += "'" + guid + "',";
}
ids = ids.TrimEnd(',');
T model = new T();
String sql = "delete from {0} where {1} in ({2})";
sql = String.Format(sql,this.tableName, this.primary, ids);
return dbHelper.ExecuteSql(sql);
}
////////// 根据ID删除多条数据
//////////////////////public int Delete(long[] accessId, IDbHelper dbHelper)
//{
// String ids = "";
// foreach (long l in accessId)
// {
// ids += l + ",";
// }
// ids = ids.TrimEnd(',');
// T model = new T();
// String sql = "delete from {0} where {1} in ({2})";
// sql = String.Format(sql,this.tableName,this.primary, ids);
// return dbHelper.ExecuteSql(sql);
//}
public int Update(string[] guids, IDbHelper dbHelper, string fieldName, string value)
{
String ids = "";
foreach (string guid in guids)
{
ids += "'" + guid + "',";
}
ids = ids.TrimEnd(',');
T model = new T();
String sql = "UPDATE {0} SET {1}='{2}' where {3} in ({4})";
sql = String.Format(sql, this.tableName, fieldName, value, this.primary, ids);
return dbHelper.ExecuteSql(sql);
}
//public int Update(long[] accessId, IDbHelper dbHelper, string fieldName, string value)
//{
// String ids = "";
// foreach (long l in accessId)
// {
// ids += l + ",";
// }
// ids = ids.TrimEnd(',');
// T model = new T();
// String sql = "UPDATE {0} SET {1}='{2}' where {3} in ({4})";
// sql = String.Format(sql, this.tableName, fieldName, value,this.primary, ids);
// return dbHelper.ExecuteSql(sql);
//}
#endregion
}
}