/// <summary>
/// 根据主键值删除数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public virtual DbEntity Delete <T>(object id)
{
DbEntity dbEntity = null;
if (id == null)
{
return(dbEntity);
}
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
var pkColumn = attributeBuilder.GetPkColumnInfo(type);
if (pkColumn == null)
{
return(dbEntity);
}
pkColumn.ColumnValue = id;
string dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
StringBuilder sqlBuilder = new StringBuilder("delete from {tableName} {whereCriteria}");
sqlBuilder.Replace("{tableName}", tableEntity.TableName);
//处理过滤字段参数
var whereItem = HandleWhereParam("", new List <TableColumnAttribute>()
{
pkColumn
}, sqlBuilder, dbParams);
sqlBuilder = whereItem.Item1;
dbParams = whereItem.Item2;
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuilder.ToString(),
DbParams = dbParams
};
return(dbEntity);
}
/// <summary>
/// 根据实体主键值修改其它字段数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public virtual DbEntity Update <T>(T entity)
{
DbEntity dbEntity = null;
if (entity == null)
{
return(dbEntity);
}
var type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
var columns = attributeBuilder.GetColumnInfos(type, entity);
if (columns.Count < 0)
{
return(dbEntity);
}
var pkColumn = columns.Where(a => a.IsPrimaryKey).FirstOrDefault();
if (pkColumn == null || columns.Where(a => !a.IsPrimaryKey).FirstOrDefault() == null)
{
return(dbEntity);
}
var updateColumns = columns.Where(a => !a.IsPrimaryKey).ToList();
var dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
var sqlBuild = new StringBuilder("update {tableName} set {updateCriteria} where {pkColumn}={dbOperator}{pkColumn}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
sqlBuild.Replace("{pkColumn}", pkColumn.ColumnName);
sqlBuild.Replace("{dbOperator}", dbOperator);
HandleUpdateParam(updateColumns, ref sqlBuild, ref dbParams);
dbParams.Add(DbFactory.GetDbParam(pkColumn));
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuild.ToString(),
DbParams = dbParams
};
return(dbEntity);
}
/// <summary>
/// 根据查询条件、where参数查询实体对应数据总数量
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereSql">过滤条件Sql</param>
/// <param name="whereParam">过滤条件字段名及字段值参数,例:new {Uname="joyet",Age = 110}</param>
/// <returns></returns>
public virtual DbEntity QueryTotalPageCount <T>(string whereSql, object whereObjParam)
{
DbEntity dbEntity = null;
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
var dbParams = new List <IDbDataParameter>();
string dbOperator = DbFactory.GetDbParamOperator();
List <TableColumnAttribute> whereColumns = attributeBuilder.GetColumnInfos(whereObjParam);
StringBuilder sqlBuild = new StringBuilder("select count(*) from {tableName} {whereCriteria}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
HandleWhereParam(whereSql, whereColumns, ref sqlBuild, ref dbParams);
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuild.ToString(),
DbParams = dbParams,
};
return(dbEntity);
}
/// <summary>
/// 根据查询字段、过滤条件参数(参数名和参数值)查询数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryColumns">要查询字段</param>
/// <param name="whereSql">过滤条件Sql</param>
/// <param name="whereParam">过滤条件参数(参数名和参数值),例:new {Uname="joyet",Age = 110}</param>
/// <returns></returns>
public virtual DbEntity Query <T>(string queryColumns, string whereSql, object whereParam)
{
DbEntity dbEntity = null;
if (string.IsNullOrEmpty(queryColumns))
{
queryColumns = "*";
}
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
string dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
List <TableColumnAttribute> whereColumns = new AttributeBuilder().GetColumnInfos(whereParam);
StringBuilder sqlBuilder = new StringBuilder("select {queryColumns} from {tableName} {whereCriteria}");
sqlBuilder.Replace("{tableName}", tableEntity.TableName);
//处理查询字段参数
var queryColumnItem = HandleQueryColumnParam(queryColumns, "", sqlBuilder);
sqlBuilder = queryColumnItem.Item1;
//处理过滤字段参数
var whereItem = HandleWhereParam(whereSql, whereColumns, sqlBuilder, dbParams);
sqlBuilder = whereItem.Item1;
dbParams = whereItem.Item2;
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuilder.ToString(),
DbParams = dbParams,
};
return(dbEntity);
}
/// <summary>
/// 修改字段名及字段值参数、主键值修改实体对应数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="updateParam">修改字段名及字段值参数,例:new {Uname="joyet",Age = 110}</param>
/// <param name="id"></param>
/// <returns></returns>
public virtual DbEntity Update <T>(object updateParam, object id)
{
DbEntity dbEntity = null;
if (updateParam == null || id == null)
{
return(dbEntity);
}
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
var updateColumns = attributeBuilder.GetColumnInfos(updateParam);
var pkColumn = attributeBuilder.GetPkColumnInfo(type);
if (updateColumns.Count < 0 || pkColumn == null)
{
return(dbEntity);
}
pkColumn.ColumnValue = id;
var dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
StringBuilder sqlBuild = new StringBuilder("update {tableName} set {updateCriteria} where {pkColumn}={dbOperator}{pkColumn}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
sqlBuild.Replace("{pkColumn}", pkColumn.ColumnName);
sqlBuild.Replace("{dbOperator}", dbOperator);
HandleUpdateParam(updateColumns, ref sqlBuild, ref dbParams);
dbParams.Add(DbFactory.GetDbParam(pkColumn));
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuild.ToString(),
DbParams = dbParams,
};
return(dbEntity);
}
/// <summary>
/// 添加单条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public virtual DbEntity Insert <T>(T entity)
{
DbEntity dbEntity = null;
if (entity == null)
{
return(dbEntity);
}
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
TableAttribute tableEntity = attributeBuilder.GetTableInfo(type);
var columns = new AttributeBuilder().GetColumnInfos(type, entity);
if (columns == null || columns.Count < 0)
{
return(dbEntity);
}
if (columns.Where(a => !a.IsAutoIncrement).FirstOrDefault() == null)
{
return(dbEntity);
}
var noAutoIncrementColumns = columns.Where(a => !a.IsAutoIncrement && !a.IsIgnoreAdd).ToList();
var dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
var sqlBuilder = new StringBuilder("insert into {tableName}({columnNames}) values({columnValues});");
sqlBuilder.Replace("{tableName}", tableEntity.TableName);
#region 处理添加字段及参数SQL语句
var addItem = HandleAddColumnValueParam(noAutoIncrementColumns, sqlBuilder, dbParams);
sqlBuilder = addItem.Item1;
dbParams = addItem.Item2;
#endregion
#region 处理自动增长列Sql
var autoIncrementColumn = columns.Where(a => a.IsAutoIncrement).FirstOrDefault();
if (tableEntity.IsGetAutoIncrementValue && autoIncrementColumn != null)
{
var dbTypeValue = Convert.ToInt32(CurrentDbType);
switch (dbTypeValue)
{
case 1:
case 2:
sqlBuilder.Append(GetAutoIncrementSql());
break;
case 3:
sqlBuilder.AppendFormat(GetAutoIncrementSql(), autoIncrementColumn.ColumnName);
break;
case 4:
sqlBuilder.AppendFormat(GetAutoIncrementSql(), tableEntity.TableName);
break;
}
}
else
{
tableEntity.IsGetAutoIncrementValue = false;
}
#endregion
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuilder.ToString(),
DbParams = dbParams
};
return(dbEntity);
}