考虑到软件使用在客户端，同时想简化代码的实现，就写了一个泛型的数据访问对象基类，并不是特别健全，按道理应该参数化的方式实现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[][] == DBNull.Value)
{
return "";
}
else
{
return (Convert.ToInt64(dt.Rows[][]) + ).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 > )
{
foreach (PropertyInfo property in properties)
{
if (dt.Rows[][property.Name] != DBNull.Value)
{
if (property.PropertyType == typeof(string))
{
t.GetProperty(property.Name).SetValue(entity, dt.Rows[][property.Name], null);
}
else if (property.PropertyType == typeof(int?))
{
t.GetProperty(property.Name).SetValue(entity, Convert.ToInt32(dt.Rows[][property.Name]), null);
}
else if (property.PropertyType == typeof(DateTime?))
{
t.GetProperty(property.Name).SetValue(entity, Convert.ToDateTime(dt.Rows[][property.Name]), null);
}
else if (property.PropertyType == typeof(long?))
{
t.GetProperty(property.Name).SetValue(entity, Convert.ToInt64(dt.Rows[][property.Name]), null);
}
else if (property.PropertyType == typeof(double?))
{
t.GetProperty(property.Name).SetValue(entity, Convert.ToDouble(dt.Rows[][property.Name]), null);
}
else
{
t.GetProperty(property.Name).SetValue(entity, dt.Rows[][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)