考虑到软件使用在客户端，同时想简化代码的实现，就写了一个泛型的数据访问对象基类，并不是特别健全，按道理应该参数化的方式实现insert和update,暂未使用参数化，抽时间改进。
/// <summary> /// DAO基类 实体名必须要与数据表字段名一致 /// </summary> /// <typeparam name="T"></typeparam> public class BaseDao<T> where T : new() { protected DataModule dataModule = new DataModule(); /// <summary> /// 表名 /// </summary> public virtual string TableName { get; set; } /// <summary> /// 主键ID /// </summary> public virtual string PrimaryKey { get; set; } /// <summary> /// 实体属性 /// </summary> private PropertyInfo[] properties = null; /// <summary> /// 实体类型 /// </summary> private readonly Type t = typeof(T); public BaseDao() { t = typeof(T); properties = t.GetProperties(); } public BaseDao(string tableName, string primaryKey) : this() { this.TableName = tableName; this.PrimaryKey = primaryKey; } public string GetMaxID() { string sql = "select max(cast(" + PrimaryKey + " as int)) as MaxId from " + TableName; DataTable dt = dataModule.GetDataTable(sql); if (dt.Rows[0][0] == DBNull.Value) { return "1"; } else { return (Convert.ToInt64(dt.Rows[0][0]) + 1).ToString(); } } /// <summary> /// 清除实体字段 /// </summary> /// <param name="entity"></param> public void ClearT(ref T entity) { entity = default(T); entity = new T(); } /// <summary> /// 获取实体 /// </summary> /// <param name="id"></param> /// <returns></returns> public T GetT(string id) { string sql = "select * from " + TableName + " where " + PrimaryKey + "='" + id + "'"; DataTable dt = dataModule.GetDataTable(sql); T entity = new T(); return SetEntityValue(dt, entity); } /// <summary> /// 根据多个条件获取实体 /// </summary> /// <param name="entity"></param> /// <returns></returns> public T GetT(T entity) { StringBuilder sql = new StringBuilder("select * from " + TableName + " where "); string where = GetWhereConditionSQL(entity); sql.Append(where); DataTable dt = dataModule.GetDataTable(sql.ToString()); return SetEntityValue(dt, entity); } /// <summary> /// 保存 /// </summary> /// <param name="e"></param> /// <returns></returns> public bool InsertT(T entity) { StringBuilder sql = new StringBuilder(""); if (string.IsNullOrEmpty(TableName)) { TableName = t.FullName.TrimStart((t.Namespace + ".").ToArray()); } t.GetProperty(PrimaryKey).SetValue(entity, GetMaxID(), null); sql.Append(" Insert into " + TableName + " ( "); StringBuilder insertFields = new StringBuilder(""); StringBuilder insertValues = new StringBuilder(""); foreach (PropertyInfo property in properties) { if (property.GetValue(entity, null) != null) { insertFields.Append("" + property.Name + ","); if (property.PropertyType == typeof(string)) { insertValues.Append("'" + property.GetValue(entity, null).ToString() + "',"); } else if (property.PropertyType == typeof(DateTime?)) { insertValues.Append("'" + Convert.ToDateTime(property.GetValue(entity, null)).ToString("yyyy-MM-dd HH:mm:ss") + "',"); } else if (property.PropertyType == typeof(int?) || property.PropertyType == typeof(long?) || property.PropertyType == typeof(double?) || property.PropertyType == typeof(float?) || property.PropertyType == typeof(decimal?)) { insertValues.Append("" + property.GetValue(entity, null).ToString() + ","); } else { insertValues.Append("'" + property.GetValue(entity, null).ToString() + "',"); } } } sql.Append(insertFields.ToString().TrimEnd(',')); sql.Append(" ) VALUES ( "); sql.Append(insertValues.ToString().TrimEnd(',')); sql.Append(")"); return dataModule.ExcuteSql(sql.ToString()); } public bool UpdateT(T entity) { StringBuilder sql = new StringBuilder(""); //获取主键ID的值 string id = string.Empty; if (t.GetProperty(PrimaryKey).GetValue(entity, null) != null) { id = t.GetProperty(PrimaryKey).GetValue(entity, null).ToString(); } if (string.IsNullOrEmpty(TableName)) { TableName = t.FullName.TrimStart((t.Namespace + ".").ToArray()); } sql.Append(" update " + TableName + " set "); StringBuilder updateValues = new StringBuilder(""); foreach (PropertyInfo property in properties) { if (property.GetValue(entity, null) != null) { if (property.PropertyType == typeof(string)) { updateValues.Append(property.Name + "='" + property.GetValue(entity, null) + "',"); } else if (property.PropertyType == typeof(DateTime?)) { updateValues.Append(property.Name + "='" + Convert.ToDateTime(property.GetValue(entity, null)).ToString("yyyy-MM-dd HH:mm:ss") + "',"); } else if (property.PropertyType == typeof(int?) || property.PropertyType == typeof(long?) || property.PropertyType == typeof(double?) || property.PropertyType == typeof(float?) || property.PropertyType == typeof(decimal?)) { updateValues.Append(property.Name + "=" + property.GetValue(entity, null) + ","); } else { updateValues.Append(property.Name + "='" + property.GetValue(entity, null) + "',"); } }
else
{
updateValues.Append(property.Name + "=null,");
} } sql.Append(updateValues.ToString().TrimEnd(',')); sql.Append(" where " + PrimaryKey + "=" + id); return dataModule.ExcuteSql(sql.ToString()); } /// <summary> /// 根据多个字段删除实体 /// </summary> /// <param name="entity"></param> /// <returns></returns> public bool DeleteT(T entity) { StringBuilder sql = new StringBuilder("delete from " + TableName + " where "); string where = GetWhereConditionSQL(entity); sql.Append(where); return dataModule.ExcuteSql(sql.ToString()); } /// <summary> /// 根据主键删除实体 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool DeleteT(string id) { StringBuilder sql = new StringBuilder("delete from " + TableName + " where " + PrimaryKey + "='" + id + "'"); return dataModule.ExcuteSql(sql.ToString()); } /// <summary> /// 获取where 条件sql /// </summary> /// <param name="entity"></param> /// <returns></returns> private string GetWhereConditionSQL(T entity) { StringBuilder whereCondition = new StringBuilder(""); foreach (PropertyInfo property in properties) { if (property.GetValue(entity, null) != null) { if (property.PropertyType == typeof(string)) { whereCondition.Append(" " + property.Name + "='" + property.GetValue(entity, null) + "' and"); } else if (property.PropertyType == typeof(DateTime?)) { whereCondition.Append(" " + property.Name + "='" + Convert.ToDateTime(property.GetValue(entity, null)).ToString("yyyy-MM-dd HH:mm:ss") + "' and"); } else if (property.PropertyType == typeof(int?) || property.PropertyType == typeof(long?) || property.PropertyType == typeof(double?) || property.PropertyType == typeof(float?) || property.PropertyType == typeof(decimal?)) { whereCondition.Append(" " + property.Name + "=" + property.GetValue(entity, null) + " and"); } else { whereCondition.Append(" " + property.Name + "='" + property.GetValue(entity, null) + "' and"); } if (property.Name == PrimaryKey) { break; } } } return whereCondition.ToString().TrimEnd("and".ToArray()); } /// <summary> /// 设置实体属性值 /// </summary> /// <param name="dt"></param> /// <param name="entity"></param> /// <returns></returns> private T SetEntityValue(DataTable dt, T entity) { if (dt != null && dt.Rows.Count > 0) { foreach (PropertyInfo property in properties) { if (dt.Rows[0][property.Name] != DBNull.Value) { if (property.PropertyType == typeof(string)) { t.GetProperty(property.Name).SetValue(entity, dt.Rows[0][property.Name], null); } else if (property.PropertyType == typeof(int?)) { t.GetProperty(property.Name).SetValue(entity, Convert.ToInt32(dt.Rows[0][property.Name]), null); } else if (property.PropertyType == typeof(DateTime?)) { t.GetProperty(property.Name).SetValue(entity, Convert.ToDateTime(dt.Rows[0][property.Name]), null); } else if (property.PropertyType == typeof(long?)) { t.GetProperty(property.Name).SetValue(entity, Convert.ToInt64(dt.Rows[0][property.Name]), null); } else if (property.PropertyType == typeof(double?)) { t.GetProperty(property.Name).SetValue(entity, Convert.ToDouble(dt.Rows[0][property.Name]), null); } else { t.GetProperty(property.Name).SetValue(entity, dt.Rows[0][property.Name], null); } } } return entity; } else { return default(T); } } }
如何使用，通过将表名和主键字段名传入进去，如果多个字段是主键的情况下，可以建一个主键列，然后将多个主键的列改为索引，因为任何一个表都可以创建出一个主键列，所以暂时不影响我使用
public BaseDao<TrafficEvent> EventDao = new BaseDao<TrafficEvent>("Sj_Event", "EventId");
UI层可以同过这种方式直接调用，目前可以暂时满足我的开发
eventBiz.EventDao.InsertT(trafficEvent)
eventBiz.EventDao.UpdateT(trafficEvent)