利用反射+自定义特性实现简单ORM(二),“无SQL”的数据库增删改查
d上一次写了简单的实现,还是基于写SQL的情况下,这次可以实现基本的单表的简单条件的(复杂条件构想中)数据增删改查,通过特性标识来完成实体与数据表的映射。
有朋友上次提出多表间的关系映射,暂时还是通过(多实体类-视图)的映射实现,虽然写起来可能会麻烦些,不过应该也符合面向对象嘛!
代码:
实体插入:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
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操作的字段必须添加此特性
例:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
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; }
}
使用示例:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
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)] 字段名和一个主键,根据主键删除
使用示例:
更新一个实体:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
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()] 参与更新的字段必须实现此特性
例:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
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; }
}
使用示例:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
People people=new People();
people.SysNo=sysNo;
people.PName = "李宇春";
people.PSex = "男";
people.PAge = 30;
ExecuteEntity.UpdateEntity<People>(people);
大至就是这样,还未完。实现更灵活的主法。
PS:有很多人说反射性能很差很差,这个先保留意见下!
有朋友上次提出多表间的关系映射,暂时还是通过(多实体类-视图)的映射实现,虽然写起来可能会麻烦些,不过应该也符合面向对象嘛!
代码:
实体插入:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/// <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操作的字段必须添加此特性
例:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
[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);
实体删除操作,主要就是跟据主键了,多条件的正在想一个好的解决方案,例:people.PName = "杨春来";
people.PSex = "男";
people.PAge = 21;
ExecuteEntity.InsertEntity<People>(people);
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/// <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);
people.SysNo=sysNo;
ExecuteEntity.DeleteEntity<People>(people);
更新一个实体:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/// <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()] 参与更新的字段必须实现此特性
例:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
[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; }
}
使用示例:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
People people=new People();
people.SysNo=sysNo;
people.PName = "李宇春";
people.PSex = "男";
people.PAge = 30;
ExecuteEntity.UpdateEntity<People>(people);
大至就是这样,还未完。实现更灵活的主法。
PS:有很多人说反射性能很差很差,这个先保留意见下!