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