/// <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); }