public static int Insert(DepartmentInfo dataInfo) { var sqlString = $@"INSERT INTO {TableName} ( {nameof(DepartmentInfo.SiteId)}, {nameof(DepartmentInfo.DepartmentName)}, {nameof(DepartmentInfo.UserNames)}, {nameof(DepartmentInfo.Taxis)} ) VALUES ( @{nameof(DepartmentInfo.SiteId)}, @{nameof(DepartmentInfo.DepartmentName)}, @{nameof(DepartmentInfo.UserNames)}, @{nameof(DepartmentInfo.Taxis)} )"; var parameters = new List <IDataParameter> { Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.SiteId), dataInfo.SiteId), Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.DepartmentName), dataInfo.DepartmentName), Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.UserNames), dataInfo.UserNames), Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.Taxis), dataInfo.Taxis) }; var departmentId = Context.DatabaseApi.ExecuteNonQueryAndReturnId(TableName, nameof(DepartmentInfo.Id), Context.ConnectionString, sqlString, parameters.ToArray()); DepartmentManager.ClearCache(dataInfo.SiteId); return(departmentId); }
public int Insert(DepartmentInfo departmentInfo) { using (var conn = GetConnection()) { conn.Open(); using (var trans = conn.BeginTransaction()) { try { var parentDepartmentInfo = GetDepartmentInfo(departmentInfo.ParentId); InsertWithTrans(parentDepartmentInfo, departmentInfo, trans); trans.Commit(); } catch { trans.Rollback(); throw; } } } DepartmentManager.ClearCache(); return(departmentInfo.Id); }
public int Insert(DepartmentInfo departmentInfo) { var departmentId = _repository.Insert(departmentInfo); DepartmentManager.ClearCache(departmentInfo.SiteId); return(departmentId); }
public static void Delete(int siteId, int departmentId) { var sqlString = $"DELETE FROM {TableName} WHERE {nameof(DepartmentInfo.Id)} = {departmentId}"; Context.DatabaseApi.ExecuteNonQuery(Context.ConnectionString, sqlString); DepartmentManager.ClearCache(siteId); }
private void SetTaxisSubtract(int id, string parentsPath, int subtractNum) { var path = PageUtils.FilterSql(parentsPath); string sqlString = $"UPDATE siteserver_Department SET Taxis = Taxis - {subtractNum} WHERE Id = {id} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'"; ExecuteNonQuery(sqlString); DepartmentManager.ClearCache(); }
private void UpdateSubtractChildrenCount(string parentsPath, int subtractNum) { if (!string.IsNullOrEmpty(parentsPath)) { var sqlString = string.Concat("UPDATE siteserver_Department SET ChildrenCount = ChildrenCount - ", subtractNum, " WHERE Id IN (", PageUtils.FilterSql(parentsPath), ")"); ExecuteNonQuery(sqlString); DepartmentManager.ClearCache(); } }
private void SetTaxisAdd(int departmentId, string parentsPath, int addNum) { var path = PageUtils.FilterSql(parentsPath); string sqlString = $"UPDATE bairong_Department SET Taxis = Taxis + {addNum} WHERE DepartmentID = {departmentId} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'"; ExecuteNonQuery(sqlString); DepartmentManager.ClearCache(); }
private void SetTaxisAdd(int id, string parentsPath, int addNum) { var path = AttackUtils.FilterSql(parentsPath); var sqlString = $"UPDATE siteserver_Department SET Taxis = Taxis + {addNum} WHERE Id = {id} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'"; ExecuteNonQuery(sqlString); DepartmentManager.ClearCache(); }
public void UpdateCountOfAdmin() { var idList = DepartmentManager.GetDepartmentIdList(); foreach (var departmentId in idList) { var count = DataProvider.AdministratorDao.GetCountByDepartmentId(departmentId); string sqlString = $"UPDATE {TableName} SET CountOfAdmin = {count} WHERE Id = {departmentId}"; ExecuteNonQuery(sqlString); } DepartmentManager.ClearCache(); }
public void UpdateCountOfAdmin() { var departmentIdList = DepartmentManager.GetDepartmentIdList(); foreach (var departmentId in departmentIdList) { string sqlString = $"UPDATE bairong_Department SET CountOfAdmin = (SELECT COUNT(*) AS CountOfAdmin FROM bairong_Administrator WHERE DepartmentID = {departmentId}) WHERE DepartmentID = {departmentId}"; ExecuteNonQuery(sqlString); } DepartmentManager.ClearCache(); }
public void Delete(int id) { var departmentInfo = GetDepartmentInfo(id); if (departmentInfo != null) { var idList = new List <int>(); if (departmentInfo.ChildrenCount > 0) { idList = GetIdListForDescendant(id); } idList.Add(id); string sqlString = $"DELETE FROM siteserver_Department WHERE Id IN ({TranslateUtils.ToSqlInStringWithoutQuote(idList)})"; int deletedNum; using (var conn = GetConnection()) { conn.Open(); using (var trans = conn.BeginTransaction()) { try { deletedNum = ExecuteNonQuery(trans, sqlString); if (deletedNum > 0) { string sqlStringTaxis = $"UPDATE siteserver_Department SET Taxis = Taxis - {deletedNum} WHERE (Taxis > {departmentInfo.Taxis})"; ExecuteNonQuery(trans, sqlStringTaxis); } trans.Commit(); } catch { trans.Rollback(); throw; } } } UpdateIsLastNode(departmentInfo.ParentId); UpdateSubtractChildrenCount(departmentInfo.ParentsPath, deletedNum); } DepartmentManager.ClearCache(); }
public void Update(DepartmentInfo departmentInfo) { var updateParms = new IDataParameter[] { GetParameter(ParmName, DataType.VarChar, 255, departmentInfo.DepartmentName), GetParameter(ParmCode, DataType.VarChar, 50, departmentInfo.Code), GetParameter(ParmParentsPath, DataType.VarChar, 255, departmentInfo.ParentsPath), GetParameter(ParmParentsCount, DataType.Integer, departmentInfo.ParentsCount), GetParameter(ParmChildrenCount, DataType.Integer, departmentInfo.ChildrenCount), GetParameter(ParmIsLastNode, DataType.VarChar, 18, departmentInfo.IsLastNode.ToString()), GetParameter(ParmSummary, DataType.VarChar, 255, departmentInfo.Summary), GetParameter(ParmCountOfAdmin, DataType.Integer, departmentInfo.CountOfAdmin), GetParameter(ParmId, DataType.Integer, departmentInfo.Id) }; ExecuteNonQuery(SqlUpdate, updateParms); DepartmentManager.ClearCache(); }
public static void Update(DepartmentInfo departmentInfo) { var sqlString = $@"UPDATE {TableName} SET {nameof(DepartmentInfo.SiteId)} = @{nameof(DepartmentInfo.SiteId)}, {nameof(DepartmentInfo.DepartmentName)} = @{nameof(DepartmentInfo.DepartmentName)}, {nameof(DepartmentInfo.UserNames)} = @{nameof(DepartmentInfo.UserNames)}, {nameof(DepartmentInfo.Taxis)} = @{nameof(DepartmentInfo.Taxis)} WHERE Id = @Id"; var parameters = new List <IDataParameter> { Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.SiteId), departmentInfo.SiteId), Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.DepartmentName), departmentInfo.DepartmentName), Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.UserNames), departmentInfo.UserNames), Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.Taxis), departmentInfo.Taxis), Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.Id), departmentInfo.Id) }; Context.DatabaseApi.ExecuteNonQuery(Context.ConnectionString, sqlString, parameters.ToArray()); DepartmentManager.ClearCache(departmentInfo.SiteId); }
private void InsertWithTrans(DepartmentInfo parentInfo, DepartmentInfo departmentInfo, IDbTransaction trans) { if (parentInfo != null) { departmentInfo.ParentsPath = parentInfo.ParentsPath + "," + parentInfo.Id; departmentInfo.ParentsCount = parentInfo.ParentsCount + 1; var maxTaxis = GetMaxTaxisByParentPath(departmentInfo.ParentsPath); if (maxTaxis == 0) { maxTaxis = parentInfo.Taxis; } departmentInfo.Taxis = maxTaxis + 1; } else { departmentInfo.ParentsPath = "0"; departmentInfo.ParentsCount = 0; var maxTaxis = GetMaxTaxisByParentPath("0"); departmentInfo.Taxis = maxTaxis + 1; } var sqlInsert = "INSERT INTO siteserver_Department (DepartmentName, Code, ParentID, ParentsPath, ParentsCount, ChildrenCount, IsLastNode, Taxis, AddDate, Summary, CountOfAdmin) VALUES (@DepartmentName, @Code, @ParentID, @ParentsPath, @ParentsCount, @ChildrenCount, @IsLastNode, @Taxis, @AddDate, @Summary, @CountOfAdmin)"; var insertParms = new IDataParameter[] { GetParameter(ParmName, DataType.VarChar, 255, departmentInfo.DepartmentName), GetParameter(ParmCode, DataType.VarChar, 50, departmentInfo.Code), GetParameter(ParmParentId, DataType.Integer, departmentInfo.ParentId), GetParameter(ParmParentsPath, DataType.VarChar, 255, departmentInfo.ParentsPath), GetParameter(ParmParentsCount, DataType.Integer, departmentInfo.ParentsCount), GetParameter(ParmChildrenCount, DataType.Integer, 0), GetParameter(ParmIsLastNode, DataType.VarChar, 18, true.ToString()), GetParameter(ParmTaxis, DataType.Integer, departmentInfo.Taxis), GetParameter(ParmAddDate, DataType.DateTime, departmentInfo.AddDate), GetParameter(ParmSummary, DataType.VarChar, 255, departmentInfo.Summary), GetParameter(ParmCountOfAdmin, DataType.Integer, departmentInfo.CountOfAdmin) }; string sqlString = $"UPDATE siteserver_Department SET {SqlUtils.ToPlusSqlString("Taxis")} WHERE (Taxis >= {departmentInfo.Taxis})"; ExecuteNonQuery(trans, sqlString); departmentInfo.Id = ExecuteNonQueryAndReturnId(TableName, nameof(DepartmentInfo.Id), trans, sqlInsert, insertParms); if (!string.IsNullOrEmpty(departmentInfo.ParentsPath)) { sqlString = $"UPDATE siteserver_Department SET {SqlUtils.ToPlusSqlString("ChildrenCount")} WHERE Id IN ({PageUtils.FilterSql(departmentInfo.ParentsPath)})"; ExecuteNonQuery(trans, sqlString); } sqlString = $"UPDATE siteserver_Department SET IsLastNode = '{false}' WHERE ParentID = {departmentInfo.ParentId}"; ExecuteNonQuery(trans, sqlString); //sqlString = // $"UPDATE siteserver_Department SET IsLastNode = '{true}' WHERE (Id IN (SELECT TOP 1 Id FROM siteserver_Department WHERE ParentID = {departmentInfo.ParentId} ORDER BY Taxis DESC))"; sqlString = $"UPDATE siteserver_Department SET IsLastNode = '{true}' WHERE (Id IN ({SqlUtils.ToInTopSqlString("siteserver_Department", "Id", $"WHERE ParentID = {departmentInfo.ParentId}", "ORDER BY Taxis DESC", 1)}))"; ExecuteNonQuery(trans, sqlString); DepartmentManager.ClearCache(); }
public void Delete(int siteId, int departmentId) { _repository.Delete(departmentId); DepartmentManager.ClearCache(siteId); }
public void Update(DepartmentInfo departmentInfo) { _repository.Update(departmentInfo); DepartmentManager.ClearCache(departmentInfo.SiteId); }