/// <summary>
/// 返回数据库参数对象列表
/// </summary>
/// <param name="objParam"></param>
/// <returns></returns>
public virtual List <IDbDataParameter> GetDbParamList(object objParam)
{
List <IDbDataParameter> dbParams = null;
if (objParam == null)
{
return(dbParams);
}
var tableColumns = new AttributeBuilder().GetColumnInfos(objParam);
if (tableColumns == null)
{
return(dbParams);
}
if (tableColumns.Count == 0)
{
return(dbParams);
}
dbParams = new List <IDbDataParameter>();
foreach (TableColumnAttribute tableColumn in tableColumns)
{
var dbParam = GetDbParam(tableColumn);
dbParams.Add(dbParam);
}
return(dbParams);
}
/// <summary>
/// 根据查询字段、过滤条件Sql、过滤条件字段名及字段值参数修改实体对应数据
/// </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 whereObjParam)
{
DbEntity dbEntity = null;
if (string.IsNullOrEmpty(queryColumns))
{
return(dbEntity);
}
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(whereObjParam);
StringBuilder sqlBuild = new StringBuilder("select {queryColumns} from {tableName} {whereCriteria}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
HandleQueryColumParam(queryColumns, "", ref sqlBuild);
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="whereSql">过滤条件Sql</param>
/// <param name="whereParam">过滤条件参数(参数名和参数值),例:new {Uname="joyet",Age = 110}</param>
/// <returns></returns>
public virtual DbEntity QueryTotalPageCount <T>(string whereSql, object whereParam)
{
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(whereParam);
StringBuilder sqlBuilder = new StringBuilder("select count(*) from {tableName} {whereCriteria}");
sqlBuilder.Replace("{tableName}", tableEntity.TableName);
//处理过滤字段参数
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="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 sqlBuild = new StringBuilder("delete from {tableName} where {pkColumn}={dbOperator}{pkColumn}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
sqlBuild.Replace("{pkColumn}", pkColumn.ColumnName);
sqlBuild.Replace("{dbOperator}", dbOperator);
dbParams.Add(DbFactory.GetDbParam(pkColumn));
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuild.ToString(),
DbParams = dbParams
};
return(dbEntity);
}
/// <summary>
/// 根据修改字段名及字段值参数、过滤条件Sql、过滤条件字段名及字段值参数修改实体对应数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="updateParam">修改字段名及字段值参数,例:new {Uname="joyet",Age = 110}</param>
/// <param name="whereSql">过滤条件Sql</param>
/// <param name="whereParam">过滤条件字段名及字段值参数,例:new {UserId=1}</param>
/// <returns></returns>
public virtual DbEntity Update <T>(object updateParam, string whereSql, object whereParam)
{
DbEntity dbEntity = null;
Type type = typeof(T);
if (updateParam == null)
{
return(dbEntity);
}
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
List <TableColumnAttribute> updateColumns = attributeBuilder.GetColumnInfos(updateParam);
if (updateColumns.Count < 0)
{
return(dbEntity);
}
string dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
List <TableColumnAttribute> whereColumns = attributeBuilder.GetColumnInfos(whereParam);
StringBuilder sqlBuild = new StringBuilder("update {tableName} set {updateCriteria} {whereCriteria}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
HandleUpdateParam(updateColumns, ref sqlBuild, ref dbParams);
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="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();
var updateColumns = columns.Where(a => !a.IsPrimaryKey).ToList();
if (pkColumn == null || updateColumns.Count < 0)
{
return(dbEntity);
}
var dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
var sqlBuilder = new StringBuilder("update {tableName} set {updateCriteria} {whereCriteria}");
sqlBuilder.Replace("{tableName}", tableEntity.TableName);
//处理更改字段参数
var updateItem = HandleUpdateParam(updateColumns, sqlBuilder, dbParams);
sqlBuilder = updateItem.Item1;
dbParams = updateItem.Item2;
//处理过滤字段参数
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="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>();
var sqlBuilder = new StringBuilder("update {tableName} set {updateCriteria} {whereCriteria}");
sqlBuilder.Replace("{tableName}", tableEntity.TableName);
//处理更改字段参数
var updateItem = HandleUpdateParam(updateColumns, sqlBuilder, dbParams);
sqlBuilder = updateItem.Item1;
dbParams = updateItem.Item2;
//处理过滤字段参数
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="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.Count < 0)
{
return(dbEntity);
}
if (columns.Where(a => !a.IsAutoIncrement).FirstOrDefault() == null)
{
return(dbEntity);
}
var noneAutoIncrementColumns = columns.Where(a => !a.IsAutoIncrement).ToList();
var dbOperator = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
var sqlBuild = new StringBuilder("insert into {tableName}({columnNames}) values({columnValues})");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
var columnNameList = new List <string>();
var paramColumnList = new List <string>();
foreach (var column in noneAutoIncrementColumns)
{
columnNameList.Add(column.ColumnName);
paramColumnList.Add(dbOperator + column.ColumnName);
dbParams.Add(DbFactory.GetDbParam(column));
}
sqlBuild.Replace("{columnNames}", string.Join(",", columnNameList));
sqlBuild.Replace("{columnValues}", string.Join(",", paramColumnList));
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sqlBuild.ToString(),
DbParams = dbParams
};
return(dbEntity);
}
/// <summary>
/// 根据sql语句、过滤条件参数(参数名和参数值)查询数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">sql语句/参数化SQL语句/存储过程</param>
/// <param name="objParam">过滤条件参数(参数名和参数值),例:new {Uname="joyet",Age = 110}</param>
/// <returns></returns>
public virtual DbEntity Query <T>(string sql, object objParam)
{
DbEntity dbEntity = null;
if (string.IsNullOrEmpty(sql))
{
return(dbEntity);
}
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
dbEntity = new DbEntity()
{
TableEntity = tableEntity,
CommandText = sql,
DbParams = DbFactory.GetDbParamList(objParam)
};
return(dbEntity);
}
/// <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>
/// 根据过滤条件Sql、过滤条件字段名及字段值参数删除实体对应数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereSql">过滤条件Sql</param>
/// <param name="whereParam">过滤条件字段名及字段值参数,例:new {Uname="joyet",Age = 110}</param>
/// <returns></returns>
public virtual DbEntity Delete <T>(string whereSql, object whereParam)
{
DbEntity dbEntity = null;
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
var dbOperatore = DbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
var whereColumns = attributeBuilder.GetColumnInfos(whereParam);
StringBuilder sqlBuild = new StringBuilder("delete 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="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);
}