/// <summary>
/// Converting the objParams parameter to a database parameter list
/// </summary>
/// <param name="objParams"></param>
/// <returns></returns>
public virtual List <IDbDataParameter> GetDbParamList(object objParams)
{
List <IDbDataParameter> dbParams = null;
if (objParams == null)
{
return(dbParams);
}
var tableColumns = new AttributeBuilder().GetColumnInfos(objParams);
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>
/// Execution of query data based on SQL statements and parameters
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="cmdText"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public virtual DbEntity Query <T>(string cmdText, object objParams)
{
DbEntity dbEntity = null;
if (string.IsNullOrEmpty(cmdText))
{
return(dbEntity);
}
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
if (tableEntity == null)
{
return(dbEntity);
}
dbEntity = new DbEntity()
{
DbType = DbFactory.DbType,
TableEntity = tableEntity,
CommandText = cmdText
};
dbEntity.DbParams = DbFactory.GetDbParamList(objParams);
return(dbEntity);
}
/// <summary>
/// Query the total number of data according to the filtration condition and filtration condition parameters.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereSql"></param>
/// <param name="whereObjParams"></param>
/// <returns></returns>
public virtual DbEntity QueryTotalPageCount <T>(string whereSql, object whereObjParams)
{
DbEntity dbEntity = null;
Type type = typeof(T);
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
if (tableEntity == null)
{
return(dbEntity);
}
dbEntity = new DbEntity()
{
DbType = DbFactory.DbType,
TableEntity = tableEntity
};
var dbParams = new List <IDbDataParameter>();
string dbOperator = DbFactory.GetDbOperator();
List <TableColumnAttribute> whereColumns = attributeBuilder.GetColumnInfos(whereObjParams);
StringBuilder sqlBuild = new StringBuilder("select count(*) from {tableName} {whereCriteria}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
HandleWhereParam(whereSql, whereColumns, ref sqlBuild, ref dbParams);
dbEntity.CommandText = sqlBuild.ToString();
dbEntity.DbParams = dbParams;
return(dbEntity);
}
/// <summary>
///Querying the corresponding data according to the primary key
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public virtual DbEntity QueryById <T>(object id)
{
DbEntity dbEntity = null;
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;
dbEntity = new DbEntity()
{
DbType = DbFactory.DbType,
TableEntity = tableEntity
};
string dbOperator = DbFactory.GetDbOperator();
List <IDbDataParameter> dbParams = new List <IDbDataParameter>();
StringBuilder sqlBuild = new StringBuilder("select * from {tableName} where {columnName}={dbOperator}{columnName}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
sqlBuild.Replace("{columnName}", pkColumn.ColumnName);
sqlBuild.Replace("{dbOperator}", dbOperator);
var dbParam = DbFactory.GetDbParam(pkColumn);
dbParams.Add(dbParam);
dbEntity.CommandText = sqlBuild.ToString();
dbEntity.DbParams = dbParams;
return(dbEntity);
}
/// <summary>
/// Modifying the corresponding data based on the modified parameters and primary key values
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="updateObjParams"></param>
/// <param name="id"></param>
/// <returns></returns>
public virtual DbEntity UpdateById <T>(object updateObjParams, object id)
{
DbEntity dbEntity = null;
Type type = typeof(T);
if (updateObjParams == null)
{
return(dbEntity);
}
if (id == null)
{
return(dbEntity);
}
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
if (tableEntity == null)
{
return(dbEntity);
}
List <TableColumnAttribute> updateColumns = attributeBuilder.GetColumnInfos(updateObjParams);
if (updateColumns == null)
{
return(dbEntity);
}
var pkColumn = attributeBuilder.GetPkColumnInfo(type);
if (pkColumn == null)
{
return(dbEntity);
}
pkColumn.ColumnValue = id;
dbEntity = new DbEntity()
{
DbType = DbFactory.DbType,
TableEntity = tableEntity
};
string dbOperator = DbFactory.GetDbOperator();
var dbParams = new List <IDbDataParameter>();
StringBuilder sqlBuild = new StringBuilder("update {tableName} set {updateCriteria} where {columnName}={dbOperator}{columnName}");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
sqlBuild.Replace("{columnName}", pkColumn.ColumnName);
sqlBuild.Replace("{dbOperator}", dbOperator);
HandleUpdateParam(updateColumns, ref sqlBuild, ref dbParams);
var dbParam = DbFactory.GetDbParam(pkColumn);
dbParams.Add(dbParam);
dbEntity.CommandText = sqlBuild.ToString();
dbEntity.DbParams = dbParams;
return(dbEntity);
}
/// <summary>
/// Add single data
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public virtual DbEntity Insert <T>(T entity)
{
DbEntity dbEntity = null;
Type type = typeof(T);
if (entity == null)
{
return(dbEntity);
}
var attributeBuilder = new AttributeBuilder();
var tableEntity = attributeBuilder.GetTableInfo(type);
if (tableEntity == null)
{
return(dbEntity);
}
List <TableColumnAttribute> columns = attributeBuilder.GetColumnInfos(type, entity);
if (columns == null)
{
return(dbEntity);
}
if (columns.Count == 0)
{
return(dbEntity);
}
columns = columns.Where(a => !a.IsAutoIncrement).ToList();
if (columns == null)
{
return(dbEntity);
}
if (columns.Count == 0)
{
return(dbEntity);
}
dbEntity = new DbEntity()
{
DbType = DbFactory.DbType,
TableEntity = tableEntity
};
List <IDbDataParameter> dbParams = new List <IDbDataParameter>();
string dbOperatore = DbFactory.GetDbOperator();
StringBuilder sqlBuild = new StringBuilder("insert into {tableName}({columnNames}) values({columnValues})");
sqlBuild.Replace("{tableName}", tableEntity.TableName);
StringBuilder columnNameSql = new StringBuilder();
StringBuilder columnValueSql = new StringBuilder();
int i = 0;
foreach (var column in columns)
{
columnNameSql.Append(column.ColumnName);
columnValueSql.Append(dbOperatore + column.ColumnName);
if (i != columns.Count - 1)
{
columnNameSql.Append(",");
columnValueSql.Append(",");
}
var dbParam = DbFactory.GetDbParam(column);
dbParams.Add(dbParam);
i++;
}
sqlBuild.Replace("{columnNames}", columnNameSql.ToString());
sqlBuild.Replace("{columnValues}", columnValueSql.ToString());
var autoIncrementSql = DbFactory.GetIncrementSql(tableEntity.IsGetIncrementValue);
sqlBuild.Append(autoIncrementSql);
dbEntity.CommandText = sqlBuild.ToString();
dbEntity.DbParams = dbParams;
return(dbEntity);
}