/// <summary> /// 查询员工数量 /// </summary> /// <param name="employeeSearcher">员工查询对象</param> /// <param name="tran">中间事务对象</param> /// <returns>返回查询到的数量</returns> public long Count(EmployeeSearcher employeeSearcher, ICTransaction tran) { object count = 0; long result = 0; EmployeeSearcher querySearcher = null; MssqlQueryParser queryParser = new MssqlQueryParser(); StringBuilder query = new StringBuilder(); query.AppendLine(@"SELECT "); query.AppendLine(@" COUNT(E.EmployeeId) "); query.AppendLine(@"FROM "); query.AppendLine(@" Employee E "); if (employeeSearcher != null) { querySearcher = (EmployeeSearcher)employeeSearcher.Clone(); querySearcher.TableName = "E"; if (querySearcher.CurrCompany != null) { querySearcher.CurrCompany.TableName = "C"; query.AppendLine(@"LEFT JOIN "); query.AppendLine(@" [Company] C ON(C.CompanyId = E.CompanyId) "); } if (querySearcher.CurrDepartment != null) { querySearcher.CurrDepartment.TableName = "D"; query.AppendLine(@"LEFT JOIN "); query.AppendLine(@" [Department] D ON(D.DepartmentId = E.DepartmentId) "); } if (querySearcher.CurrPosition != null) { querySearcher.CurrPosition.TableName = "P"; query.AppendLine(@"LEFT JOIN "); query.AppendLine(@" [Position] P ON(P.PositionId = E.PositionId) "); } } queryParser.SearcherParse(querySearcher); if (!string.IsNullOrEmpty(queryParser.ConditionString)) { query.AppendLine(@"WHERE "); query.AppendLine(@" " + queryParser.ConditionString); } if (tran != null) { DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction; count = MssqlHelper.ExecuteScalar(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection); } else { count = MssqlHelper.ExecuteScalar(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection); } return(long.TryParse(count.ToString(), out result) ? result : 0); }
/// <summary> /// 根据指定条件删除部门 /// </summary> /// <param name="departmentSearcher">部门查询对象</param> /// <param name="tran">中间事务对象</param> public void Delete(DepartmentSearcher departmentSearcher, ICTransaction tran) { int effectCount = 0; DepartmentSearcher querySearcher = null; MssqlQueryParser queryParser = new MssqlQueryParser(); StringBuilder query = new StringBuilder(); query.AppendLine(@"DELETE FROM "); query.AppendLine(@" Department "); query.AppendLine(@"WHERE "); query.AppendLine(@" DepartmentId IN ("); query.AppendLine(@" SELECT "); query.AppendLine(@" D.DepartmentId "); query.AppendLine(@" FROM "); query.AppendLine(@" Department D "); if (departmentSearcher != null) { querySearcher = (DepartmentSearcher)departmentSearcher.Clone(); querySearcher.TableName = "D"; if (querySearcher.CurrCompany != null) { querySearcher.CurrCompany.TableName = "C"; query.AppendLine(@"LEFT JOIN "); query.AppendLine(@" Company C ON(C.CompanyId = D.CompanyId) "); } } queryParser.SearcherParse(querySearcher); if (!string.IsNullOrEmpty(queryParser.ConditionString)) { query.AppendLine(@"WHERE "); query.AppendLine(@" " + queryParser.ConditionString); } query.AppendLine(@"); "); try { if (tran != null) { DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction; effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection); } else { effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection); } } catch (SqlException sex) { if (sex.ErrorCode == (int)ResultCode.FKError) { throw new ResponseException((int)ResultCode.FKError, "DELETE Department"); } } }
/// <summary> /// 根据指定条件删除公司 /// </summary> /// <param name="companySearcher">公司查询对象</param> /// <param name="tran">中间事务对象</param> public void Delete(CompanySearcher companySearcher, ICTransaction tran) { int effectCount = 0; CompanySearcher querySearcher = null; MssqlQueryParser queryParser = new MssqlQueryParser(); StringBuilder query = new StringBuilder(); query.AppendLine(@"DELETE FROM "); query.AppendLine(@" Company "); if (companySearcher != null) { querySearcher = (CompanySearcher)companySearcher.Clone(); querySearcher.TableName = "C"; } queryParser.SearcherParse(querySearcher); if (!string.IsNullOrEmpty(queryParser.ConditionString)) { query.AppendLine(@"WHERE "); query.AppendLine(@" " + queryParser.ConditionString); } try { if (tran != null) { DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction; effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection); } else { effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection); } } catch (SqlException sex) { if (sex.ErrorCode == (int)ResultCode.FKError) { throw new ResponseException((int)ResultCode.FKError, "DELETE Company"); } } }
/// <summary> /// 查询公司数量 /// </summary> /// <param name="companySearcher">公司查询对象</param> /// <param name="tran">中间事务对象</param> /// <returns>返回查询到的数量</returns> public long Count(CompanySearcher companySearcher, ICTransaction tran) { object count = 0; long result = 0; CompanySearcher querySearcher = null; MssqlQueryParser queryParser = new MssqlQueryParser(); StringBuilder query = new StringBuilder(); query.AppendLine(@"SELECT "); query.AppendLine(@" COUNT(C.CompanyId) "); query.AppendLine(@"FROM "); query.AppendLine(@" Company C "); if (companySearcher != null) { querySearcher = (CompanySearcher)companySearcher.Clone(); querySearcher.TableName = "C"; } queryParser.SearcherParse(querySearcher); if (!string.IsNullOrEmpty(queryParser.ConditionString)) { query.AppendLine(@"WHERE "); query.AppendLine(@" " + queryParser.ConditionString); } if (tran != null) { DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction; count = MssqlHelper.ExecuteScalar(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection); } else { count = MssqlHelper.ExecuteScalar(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection); } return(long.TryParse(count.ToString(), out result) ? result : 0); }
/// <summary> /// 根据指定条件查找公司分页集合 /// </summary> /// <param name="companySearcher">公司查询对象</param> /// <param name="pager">分页对象</param> /// <param name="tran">中间事务对象</param> /// <returns>返回查找到的分页集合,包括按条件可查询到的所有记录数和当前分页的DataTable数据</returns> public PageDataTable FindDataTable(CompanySearcher companySearcher, Pager pager, ICTransaction tran) { CompanySearcher querySearcher = null; MssqlQueryParser queryParser = new MssqlQueryParser(); PageDataTable pDataTable = new PageDataTable(); DataSet resultSet = null; StringBuilder query = new StringBuilder(); StringBuilder conditionQuery = new StringBuilder(); StringBuilder sortQuery = new StringBuilder(); if (companySearcher != null) { querySearcher = (CompanySearcher)companySearcher.Clone(); querySearcher.TableName = "C"; } queryParser.SearcherParse(querySearcher); if (!string.IsNullOrEmpty(queryParser.ConditionString)) { conditionQuery.AppendLine(@"WHERE "); conditionQuery.AppendLine(@" " + queryParser.ConditionString); } if (!string.IsNullOrEmpty(queryParser.SortString)) { sortQuery.AppendLine(@"ORDER BY "); sortQuery.AppendLine(@" " + queryParser.SortString); } query.AppendLine(@"SELECT "); query.AppendLine(@" C.[CompanyId] "); query.AppendLine(@" ,C.[CompanyCode] "); query.AppendLine(@" ,C.[Name] "); query.AppendLine(@" ,C.[Address] "); query.AppendLine(@" ,C.[ParentCompanyId] "); query.AppendLine(@" ,C.[RVersion] "); query.AppendLine(@" ,C.[Status] "); query.AppendLine(@" ,C.[CreaterId] "); query.AppendLine(@" ,C.[CreateTime] "); query.AppendLine(@" ,C.[UpdatorId] "); query.AppendLine(@" ,C.[UpdateTime] "); query.AppendLine(@"FROM "); query.AppendLine(@" [Company] C "); query.AppendLine(conditionQuery.ToString()); query.AppendLine(sortQuery.ToString()); query.AppendLine(@"; "); if (tran != null) { DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction; if (pager != null) { resultSet = MssqlHelper.ExecuteDataSet(dbTran, CommandType.Text, query.ToString(), pager, "Company", queryParser.ParamCollection); } else { resultSet = MssqlHelper.ExecuteDataSet(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection); } } else { if (pager != null) { resultSet = MssqlHelper.ExecuteDataSet(this.CurrentConnectionString, CommandType.Text, query.ToString(), pager, "Company", queryParser.ParamCollection); } else { resultSet = MssqlHelper.ExecuteDataSet(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection); } } if (resultSet != null) { if (pager != null) { pDataTable.PageIndex = pager.CurrentPage; } pDataTable.TotalCount = this.Count(companySearcher, tran); pDataTable.RecordList = resultSet.Tables[0]; } return(pDataTable); }
/// <summary> /// 根据指定条件查找员工分页集合 /// </summary> /// <param name="employeeSearcher">员工查询对象</param> /// <param name="pager">分页对象</param> /// <param name="tran">中间事务对象</param> /// <returns>返回查找到的分页集合,包括按条件可查询到的所有记录数和当前分页的DataTable数据</returns> public PageDataTable FindDataTable(EmployeeSearcher employeeSearcher, Pager pager, ICTransaction tran) { EmployeeSearcher querySearcher = null; MssqlQueryParser queryParser = new MssqlQueryParser(); PageDataTable pDataTable = new PageDataTable(); DataSet resultSet = null; StringBuilder query = new StringBuilder(); StringBuilder joinQuery = new StringBuilder(); StringBuilder conditionQuery = new StringBuilder(); StringBuilder sortQuery = new StringBuilder(); if (employeeSearcher != null) { querySearcher = (EmployeeSearcher)employeeSearcher.Clone(); querySearcher.TableName = "E"; if (querySearcher.CurrCompany != null) { querySearcher.CurrCompany.TableName = "C"; joinQuery.AppendLine(@"LEFT JOIN "); joinQuery.AppendLine(@" [Company] C ON(C.CompanyId = E.CompanyId) "); } if (querySearcher.CurrDepartment != null) { querySearcher.CurrDepartment.TableName = "D"; joinQuery.AppendLine(@"LEFT JOIN "); joinQuery.AppendLine(@" [Department] D ON(D.DepartmentId = E.DepartmentId) "); } if (querySearcher.CurrPosition != null) { querySearcher.CurrPosition.TableName = "P"; joinQuery.AppendLine(@"LEFT JOIN "); joinQuery.AppendLine(@" [Position] P ON(P.PositionId = E.PositionId) "); } } queryParser.SearcherParse(querySearcher); if (!string.IsNullOrEmpty(queryParser.ConditionString)) { conditionQuery.AppendLine(@"WHERE "); conditionQuery.AppendLine(@" " + queryParser.ConditionString); } if (!string.IsNullOrEmpty(queryParser.SortString)) { sortQuery.AppendLine(@"ORDER BY "); sortQuery.AppendLine(@" " + queryParser.SortString); } query.AppendLine(@"SELECT "); query.AppendLine(@" E.[EmployeeId] "); query.AppendLine(@" ,E.[EmployeeCode] "); query.AppendLine(@" ,E.[Name] "); query.AppendLine(@" ,E.[Birthday] "); query.AppendLine(@" ,E.[Sex] "); query.AppendLine(@" ,E.[CompanyId] "); query.AppendLine(@" ,E.[DepartmentId] "); query.AppendLine(@" ,E.[PositionId] "); query.AppendLine(@" ,E.[Rand] "); query.AppendLine(@" ,E.[RVersion] "); query.AppendLine(@" ,E.[Status] "); query.AppendLine(@" ,E.[CreaterId] "); query.AppendLine(@" ,E.[CreateTime] "); query.AppendLine(@" ,E.[UpdatorId] "); query.AppendLine(@" ,E.[UpdateTime] "); query.AppendLine(@" ,E.[StartWorkDate] "); query.AppendLine(@" ,E.[JoinDate] "); query.AppendLine(@"FROM "); query.AppendLine(@" [Employee] E "); query.AppendLine(joinQuery.ToString()); query.AppendLine(conditionQuery.ToString()); query.AppendLine(sortQuery.ToString()); query.AppendLine(@"; "); if (tran != null) { DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction; if (pager != null) { resultSet = MssqlHelper.ExecuteDataSet(dbTran, CommandType.Text, query.ToString(), pager, "Employee", queryParser.ParamCollection); } else { resultSet = MssqlHelper.ExecuteDataSet(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection); } } else { if (pager != null) { resultSet = MssqlHelper.ExecuteDataSet(this.CurrentConnectionString, CommandType.Text, query.ToString(), pager, "Employee", queryParser.ParamCollection); } else { resultSet = MssqlHelper.ExecuteDataSet(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection); } } if (resultSet != null) { if (pager != null) { pDataTable.PageIndex = pager.CurrentPage; } pDataTable.TotalCount = this.Count(employeeSearcher, tran); pDataTable.RecordList = resultSet.Tables[0]; } return(pDataTable); }