写了一个数据库操作类，达到通过实体自动映射数据库进行查询，添加，删除，修改操作,啥也不说了，直接上代码：
反回一个实体，通过枚举参数确定使用属性/特性进行字段映射(转款做表和主键映射)
Code
/// <summary>
/// 获取单个实体
/// </summary>
/// <typeparam name="TEntity">实体(泛型)</typeparam>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="exeEntityType">按属性/特性映射</param>
/// <param name="paramers">参数数组</param>
/// <returns>实体</returns>
public static TEntity GetEntity<TEntity>(string cmdText, CommandType commandType,AppEnum.ExeEntityType exeEntityType,params DbParameter[] paramers) where TEntity:new()
{
TEntity entity = new TEntity();
using (IDataReader reader = DbHelper.CreateDataReader(cmdText, commandType, paramers))
{
if (reader.Read())
{
Type entityType = entity.GetType();
PropertyInfo[] propertyInfos=entityType.GetProperties();
foreach(PropertyInfo property in propertyInfos)
{
if (exeEntityType == AppEnum.ExeEntityType.isAttribute)
{
DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);
if (datafieldAttribute == null)
throw new AssionException("AppEnum.ExeEntityType枚举为 isAttribute 时,实体类需要指定特性！");
if (!(reader[datafieldAttribute.FieldName] is DBNull))
property.SetValue(entity, reader[datafieldAttribute.FieldName], null);
}
else if (exeEntityType == AppEnum.ExeEntityType.isProperty)
{
if (!(reader[property.Name] is DBNull))
property.SetValue(entity, reader[property.Name], null);
}
}
}
reader.Close();
reader.Dispose();
}
return entity;
}
返回一个实体集合，类似上面的
Code
/// <summary>
/// 获取实体集合
/// </summary>
/// <typeparam name="TEntity">实体(泛型)</typeparam>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="exeEntityType">按属性/特性映射</param>
/// <param name="paramers">参数数组</param>
/// <returns>实体集合</returns>
public static IList<TEntity> GetEntityList<TEntity>(string cmdText, CommandType commandType, AppEnum.ExeEntityType exeEntityType, params DbParameter[] paramers) where TEntity : new()
{
IList<TEntity> entityList = new List<TEntity>();
using (IDataReader reader = DbHelper.CreateDataReader(cmdText, commandType, paramers))
{
while (reader.Read())
{
TEntity entity = new TEntity();
Type entityType = entity.GetType();
PropertyInfo[] propertyInfos = entityType.GetProperties();
foreach (PropertyInfo property in propertyInfos)
{
if (exeEntityType == AppEnum.ExeEntityType.isAttribute)
{
DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);
if (datafieldAttribute == null)
throw new AssionException("AppEnum.ExeEntityType枚举为 isAttribute 时,实体类需要指定特性！");
if (!(reader[datafieldAttribute.FieldName] is DBNull))
property.SetValue(entity, reader[datafieldAttribute.FieldName], null);
}
else if (exeEntityType == AppEnum.ExeEntityType.isProperty)
{
if (!(reader[property.Name] is DBNull))
property.SetValue(entity, reader[property.Name], null);
}
}
entityList.Add(entity);
}
}
return entityList;
}
执行SQL实体映射操作,可以写INSERT、UPDATE、DELETE 操作，这样灵活些，抽时间再完成个自动映射生成SQL的，不过灵活性就差些了
Code
/// <summary>
/// 插入，更新，删除数据库实体
/// </summary>
/// <typeparam name="TEntity">实体(泛型)</typeparam>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="exeEntityType">按属性/特性映射</param>
/// <param name="entity">实体</param>
/// <returns>影响行数</returns>
public static int ExeEntity<TEntity>(string cmdText, CommandType commandType, AppEnum.ExeEntityType exeEntityType,TEntity entity) where TEntity : new()
{
Type entityType = entity.GetType();
PropertyInfo[] propertyInfos = entityType.GetProperties();
List<DbParameter> paramerList = new List<DbParameter>();
foreach (PropertyInfo property in propertyInfos)
{
if (exeEntityType == AppEnum.ExeEntityType.isAttribute)
{
DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);
if (datafieldAttribute == null)
throw new AssionException("AppEnum.ExeEntityType枚举为 isAttribute 时,实体类需要指定特性！");
object oval=property.GetValue(entity,null);
oval = oval == null ? DBNull.Value : oval;
paramerList.Add(DbHelper.CreateParamer("@" + datafieldAttribute.FieldName, oval));
}
else if (exeEntityType == AppEnum.ExeEntityType.isProperty)
{
object oval = property.GetValue(entity, null);
oval = oval == null ? DBNull.Value : oval;
paramerList.Add(DbHelper.CreateParamer("@" + property.Name, oval));
}
}
int retval=DbHelper.ExecuteNonQuery(cmdText, commandType, paramerList.ToArray());
return retval;
}
下面是字段的自定义特性和获取特性的代码：
Code
/// <summary>
/// 自定义特性：映射数据库字段名
/// </summary>
[AttributeUsage(AttributeTargets.Struct|AttributeTargets.Property|AttributeTargets.Field)]
public class DataFieldAttribute:Attribute
{
private string _fieldName; //字段名
public string FieldName
{
get { return _fieldName; }
set { _fieldName = value; }
}
public DataFieldAttribute(string fieldName)
{
this._fieldName = fieldName;
}
}
/// <summary>
/// 获取DataFieldAttribute特性
/// </summary>
/// <param name="property">属性</param>
/// <returns>DataFieldAttribute</returns>
private static DataFieldAttribute GetDataFieldAttribute(PropertyInfo property)
{
object[] oArr=property.GetCustomAttributes(true);
for(int i=0;i<oArr.Length;i++)
{
if(oArr[i] is DataFieldAttribute)
return (DataFieldAttribute)oArr[i];
}
return null;
}
使用示例：
首先实体加上特性：
Code
public class People
{
[PrimaryKey()]
[DataField("DbName")]
public string Name { get; set; }
[DataField("DbTel")]
public string tel { get; set; }
}
调用代码：
获取一个实体集合：
IList<People> peopleList = ExecuteEntity.GetEntityList<People>("SELECT DbName,DbTel FROM Test", CommandType.Text, AppEnum.ExeEntityType.isAttribute, null);
向数据库插入实体数据：
ExecuteEntity.ExeEntity<People>("INSERT INTO Test (DbName,DbTel) VALUES (@DbName,@DbTel)", CommandType.Text, AppEnum.ExeEntityType.isAttribute, people);
最后再附上一个基础的数据库操作类：
Code
/// <summary>
/// 通用数据库帮助类
/// </summary>
public static class DbHelper
{
#region 连接配置读取
/// <summary>
/// 数据库类型
/// </summary>
private static readonly string dataProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
/// <summary>
/// 反射数据库类型
/// </summary>
private static readonly DbProviderFactory dataFactory = DbProviderFactories.GetFactory(dataProvider);
/// <summary>
/// 数据库连接字符串
/// </summary>
private static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
#endregion
#region 连接命令
/// <summary>
/// 创建连接
/// </summary>
/// <returns>dbconnection</returns>
public static DbConnection CreateConnection()
{
DbConnection dbConn = dataFactory.CreateConnection();
dbConn.ConnectionString = connectionString;
return dbConn;
}
/// <summary>
/// 创建命令
/// </summary>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="paramers">参数数组</param>
/// <returns>command</returns>
public static DbCommand CreateCommand(string cmdText,CommandType commandType,params DbParameter[] paramers)
{
DbConnection dbConn=CreateConnection();
dbConn.Open();
DbCommand dbCmd = dataFactory.CreateCommand();
dbCmd.Connection = dbConn;
dbCmd.CommandText = cmdText;
dbCmd.CommandType = commandType;
if(paramers!=null)
dbCmd.Parameters.AddRange(paramers);
return dbCmd;
}
#endregion
#region 数据执行方法
/// <summary>
/// 创建带参数的只读器
/// </summary>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="paramers">参数数组</param>
/// <returns>reader</returns>
public static DbDataReader CreateDataReader(string cmdText, CommandType commandType, params DbParameter[] paramers)
{
DbDataReader reader = null;
reader = CreateCommand(cmdText, commandType, paramers).ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
/// <summary>
/// 创建无参数的只读器
/// </summary>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <returns>reader</returns>
public static DbDataReader CreateDataReader(string cmdText, CommandType commandType)
{
DbDataReader reader = null;
reader = CreateCommand(cmdText, commandType, null).ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
/// <summary>
/// 执行一个带参数的SQL/存储过程
/// </summary>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="paramers">参数数组</param>
/// <returns>影响行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType commandType, params DbParameter[] paramers)
{
int retval = CreateCommand(cmdText, commandType, paramers).ExecuteNonQuery();
return retval;
}
/// <summary>
/// 执行一个不带参数的SQL/存储过程
/// </summary>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <returns>影响行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType commandType)
{
int retval = CreateCommand(cmdText, commandType, null).ExecuteNonQuery();
return retval;
}
/// <summary>
/// 执行一个带参数的SQL/存储过程有事务的
/// </summary>
/// <param name="tran">事务</param>
/// <param name="cmdText">命令</param>
/// <param name="commmandType">命令类型</param>
/// <param name="paramers">参数数组</param>
/// <returns>影响行数</returns>
public static int ExecuteNonQuery(DbTransaction tran,string cmdText,CommandType commmandType,params DbParameter[] paramers)
{
DbConnection dbConn = tran.Connection;
DbCommand dbCmd=dataFactory.CreateCommand();
dbCmd.Connection=dbConn;
dbCmd.CommandType=commmandType;
dbCmd.CommandText=cmdText;
dbCmd.Parameters.AddRange(paramers);
dbCmd.Transaction = tran;
int retval=dbCmd.ExecuteNonQuery();
return retval;
}
/// <summary>
/// 执行一个无参数的SQL/存储过程有事务的
/// </summary>
/// <param name="tran">事务</param>
/// <param name="cmdText">命令</param>
/// <param name="commmandType">命令类型</param>
/// <returns>影响行数</returns>
public static int ExecuteNonQuery(DbTransaction tran, string cmdText, CommandType commmandType)
{
DbConnection dbConn = tran.Connection;
DbCommand dbCmd = dataFactory.CreateCommand();
dbCmd.Connection = dbConn;
dbCmd.CommandType = commmandType;
dbCmd.CommandText = cmdText;
dbCmd.Transaction = tran;
int retval = dbCmd.ExecuteNonQuery();
return retval;
}
/// <summary>
/// 执行一个带参数的SQL/存储过程返回首行首列
/// </summary>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="paramers">参数数组</param>
/// <returns>值</returns>
public static object ExecuteScalar(string cmdText, CommandType commandType, params DbParameter[] paramers)
{
object retval = CreateCommand(cmdText,commandType,paramers).ExecuteScalar();
return retval;
}
/// <summary>
/// 执行一个无参数的SQL/存储过程返回首行首列
/// </summary>
/// <param name="cmdText">命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="paramers">参数数组</param>
/// <returns>值</returns>
public static object ExecuteScalar(string cmdText, CommandType commandType)
{
object retval = CreateCommand(cmdText,commandType,null).ExecuteScalar();
return retval;
}
#endregion
#region 创建参数方法
/// <summary>
/// 创建参数
/// </summary>
/// <param name="pName">参数名</param>
/// <param name="pValue">参数值</param>
/// <returns>参数</returns>
public static DbParameter CreateParamer(string pName, object pValue)
{
DbParameter paramer = dataFactory.CreateParameter();
paramer.ParameterName = pName;
paramer.Value = pValue;
return paramer;
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="pName">参数名</param>
/// <param name="pValue">参数值</param>
/// <param name="pType">参数类型</param>
/// <returns>参数</returns>
public static DbParameter CreateParamer(string pName, object pValue, DbType pType)
{
DbParameter paramer = dataFactory.CreateParameter();
paramer.ParameterName = pName;
paramer.Value = pValue;
paramer.DbType = pType;
return paramer;
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="pName">参数名</param>
/// <param name="pValue">参数值</param>
/// <param name="pType">参数类型</param>
/// <param name="pSize">长度</param>
/// <returns>参数</returns>
public static DbParameter CreateParamer(string pName, object pValue, DbType pType, int pSize)
{
DbParameter paramer = dataFactory.CreateParameter();
paramer.ParameterName = pName;
paramer.Value = pValue;
paramer.DbType = pType;
paramer.Size = pSize;
return paramer;
}
#endregion
}
-----------------------------------------------------------------------------------------------------------
d上一次写了简单的实现，还是基于写SQL的情况下，这次可以实现基本的单表的简单条件的(复杂条件构想中)数据增删改查，通过特性标识来完成实体与数据表的映射。
有朋友上次提出多表间的关系映射，暂时还是通过(多实体类-视图)的映射实现，虽然写起来可能会麻烦些，不过应该也符合面向对象嘛！
代码：
实体插入：
Code
/// <summary>
/// 向数据库插入一个实体
/// </summary>
/// <typeparam name="TEntity">实体泛型</typeparam>
/// <param name="entity">实体</param>
/// <returns>影响行数</returns>
public static int InsertEntity<TEntity>(TEntity entity) where TEntity : new()
{
StringBuilder sb = new StringBuilder(); //主SQL
StringBuilder sbParamer = new StringBuilder(); //SQL参数部分
sb.Append("INSERT INTO");
Type entityType = entity.GetType();
DataTbNameAttribute datatbNameAttribute = GetDataTbNameAttribute(entityType);
if (datatbNameAttribute == null)
throw new AssionException("实体类没有指定DataTbName表名特性！");
sb.Append(" " + datatbNameAttribute.TbName+"("); //利用特性DataTbName生成表名
sbParamer.Append("(");
PropertyInfo[] propertyInfos = entityType.GetProperties();
List<DbParameter> paramerList = new List<DbParameter>(); //参数集合
for (int i = 0; i < propertyInfos.Length; i++)
{
DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(propertyInfos[i]);
DataInsertAttribute datainsertAttribute = GetDataInsertAttribute(propertyInfos[i]);
//必须同时拥有DataField和DataInsert两个特性才参与插入数据库字段
if (datafieldAttribute == null || datainsertAttribute == null)
continue;
//利用特性DataField名生成字段和参数
sb.Append(datafieldAttribute.FieldName + ",");
sbParamer.Append("@" + datafieldAttribute.FieldName + ",");
//获取值
object oval=propertyInfos[i].GetValue(entity,null);
oval=oval==null?DBNull.Value:oval;
//向参数集合添加参数
paramerList.Add(DbHelper.CreateParamer("@" + datafieldAttribute.FieldName, oval));
}
//截取掉最后一个多于的参数分隔','符号
sb.Remove(sb.Length - 1, 1);
sbParamer.Remove(sbParamer.Length - 1, 1);
//拼接最终SQL
sb.Append(") VALUES ");
sb.Append(sbParamer.ToString()+")");
//调用数据库操作执行Insert SQL
return DbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, paramerList.ToArray());
}
使用相应实体类需要三个特性：
[DataTbName("People")] 表名,类唯一
[DataField("Name",IsPrimaryKey=true)] 字段，主键可以加上IsPrimaryKey不参与Insert
[DataInsert()] 参加Insert操作的字段必须添加此特性
例：
Code
[DataTbName("People")]
public class People
{
[DataField("SysNo", IsPrimaryKey = true)]
public int SysNo { get; set; }
[DataField("PName")]
[DataInsert()]
public string PName { get; set; }
[DataField("PSex")]
[DataInsert()]
[DataUpdate()]
public string PSex { get; set; }
[DataField("PAge")]
[DataInsert()]
public int PAge { get; set; }
}
使用示例：
People people = new People();
people.PName = "杨春来";
people.PSex = "男";
people.PAge = 21;
ExecuteEntity.InsertEntity<People>(people);
实体删除操作，主要就是跟据主键了，多条件的正在想一个好的解决方案，例：
Code
/// <summary>
/// 删除一个实体
/// </summary>
/// <typeparam name="TEntity">实体泛型</typeparam>
/// <param name="entity">实体</param>
/// <returns>影响行数</returns>
public static int DeleteEntity<TEntity>(TEntity entity)where TEntity:new()
{
StringBuilder sb = new StringBuilder(); //SQL语句
Type entityType = entity.GetType();
//表名特性
DataTbNameAttribute datatbnameAttribute = GetDataTbNameAttribute(entityType);
if(datatbnameAttribute==null)
throw new AssionException("实体类没有指定DataTbName表名特性！");
sb.Append("DELETE " + datatbnameAttribute.TbName + " WHERE ");
PropertyInfo[] propertyInfos = entityType.GetProperties();
List<DbParameter> paramerList = new List<DbParameter>();
foreach (PropertyInfo property in propertyInfos)
{
DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);
if (datafieldAttribute == null && datafieldAttribute.IsPrimaryKey != true)
continue;
sb.Append(datafieldAttribute.FieldName + "=" + "@" + datafieldAttribute.FieldName);
object oval=property.GetValue(entity,null);
oval=oval==null?DBNull.Value:oval;
paramerList.Add(DbHelper.CreateParamer("@" + datafieldAttribute.FieldName, oval));
break;
}
return DbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, paramerList.ToArray());
}
实体类需要两个特性：
[DataTbName("People")] 标识表名
[DataField("Name",IsPrimaryKey=true)] 字段名和一个主键，根据主键删除
使用示例：
People people=new People();
people.SysNo=sysNo;
ExecuteEntity.DeleteEntity<People>(people);
更新一个实体：
Code
/// <summary>
/// 更新一个实体
/// </summary>
/// <typeparam name="TEntity">实体泛型</typeparam>
/// <param name="entity">实体</param>
/// <returns>影响行数</returns>
public static int UpdateEntity<TEntity>(TEntity entity)where TEntity:new()
{
StringBuilder sb = new StringBuilder(); //SQL语句
StringBuilder sbWhere = new StringBuilder(); //条件SQL
Type entityType = entity.GetType();
//表名特性
DataTbNameAttribute datatbnameAttribute = GetDataTbNameAttribute(entityType);
if (datatbnameAttribute == null)
throw new AssionException("实体类没有指定DataTbName表名特性！");
sb.Append("UPDATE " + datatbnameAttribute.TbName + " SET ");
sbWhere.Append(" WHERE ");
PropertyInfo[] propertyInfos = entityType.GetProperties();
List<DbParameter> paramerList = new List<DbParameter>();
foreach (PropertyInfo property in propertyInfos)
{
, DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);
DataUpdateAttribute dataupdateAttribute = GetDataUpdateAttribute(property);
//没有DataField和DataUpdate特性或为主键的不参与更新
if (dataupdateAttribute == null || datafieldAttribute==null || datafieldAttribute.IsPrimaryKey==true)
continue;
sb.Append(datafieldAttribute.FieldName + "=@" + datafieldAttribute.FieldName + ",");
object oval=property.GetValue(entity,null);
oval=oval==null?DBNull.Value:oval;
paramerList.Add(DbHelper.CreateParamer("@" + datafieldAttribute.FieldName, oval));
}
foreach (PropertyInfo property in propertyInfos)
{
DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);
//没有DataField和DataUpdate特性或为主键的不参与更新
if (datafieldAttribute != null && datafieldAttribute.IsPrimaryKey == true)
{
sbWhere.Append(datafieldAttribute.FieldName + "=@" + datafieldAttribute.FieldName);
object oval = property.GetValue(entity, null);
oval = oval == null ? DBNull.Value : oval;
paramerList.Add(DbHelper.CreateParamer("@" + datafieldAttribute.FieldName, oval));
break;
}
else
continue;
}
sb.Remove(sb.Length - 1, 1);
sb.Append(sbWhere.ToString());
return DbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, paramerList.ToArray());
}
需要特性：
[DataTbName("People")] 表名
[DataField("Name",IsPrimaryKey=true)] 字段名和一个主键
[DataUpdate()] 参与更新的字段必须实现此特性
例：
Code
[DataTbName("People")]
public class People
{
[DataField("SysNo", IsPrimaryKey = true)]
public int SysNo { get; set; }
[DataField("PName")]
[DataUpdate()]
public string PName { get; set; }
[DataField("PSex")]
[DataUpdate()]
public string PSex { get; set; }
[DataField("PAge")]
[DataUpdate()]
public int PAge { get; set; }
}
使用示例：
Code
People people=new People();
people.SysNo=sysNo;
people.PName = "李宇春";
people.PSex = "男";
people.PAge = 30;
ExecuteEntity.UpdateEntity<People>(people);