public async Task <string> CreateCopyVersionAsync(string classCode, string versionCode, string newVersion, string userId, bool copyRelations) { if (await this.IsClassVersionExistAsync(classCode, versionCode) == false) { return($"Грешка!!! Няма версия източник с код: {classCode} {versionCode}"); } if (await this.IsClassVersionExistAsync(classCode, newVersion) == true) { return($"Грешка!!! Вече има версия с код: {classCode} {newVersion}"); } var sourceVersion = await this.db.ClassVersions.Where(v => v.Classif == classCode && v.Version == versionCode).FirstOrDefaultAsync(); var brandNewVersion = new TC_Classif_Vers() { Classif = sourceVersion.Classif, Version = newVersion, Remarks = sourceVersion.Remarks, ByLow = sourceVersion.ByLow, Publications = sourceVersion.Publications, UseAreas = sourceVersion.UseAreas, Parent = sourceVersion.Parent, Valid_From = sourceVersion.Valid_From, Valid_To = sourceVersion.Valid_To }; var connectionString = Configuration.GetSection("ConnectionStrings:DefaultConnection").Value; SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand("ALTER TABLE ClassItems NOCHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode"); SqlCommand comend = new SqlCommand("ALTER TABLE ClassItems CHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode"); com.Connection = con; comend.Connection = con; con.Open(); com.ExecuteNonQuery(); await this.db.Database.BeginTransactionAsync(); try { await this.db.ClassVersions.AddAsync(brandNewVersion); await this.db.SaveChangesAsync(); var items = await this.db.ClassItems.Where(i => i.Classif == classCode && i.Version == versionCode).ToListAsync(); foreach (var item in items) { var newItem = new TC_Classif_Items() { Classif = item.Classif, Version = brandNewVersion.Version, Description = item.Description, DescriptionShort = item.DescriptionShort, DescriptionEng = item.DescriptionEng, Includes = item.Includes, IncludesMore = item.IncludesMore, IncludesNo = item.IncludesNo, IsLeaf = item.IsLeaf, ItemCode = item.ItemCode, ItemLevel = item.ItemLevel, EntryTime = DateTime.Now, IsDeleted = item.IsDeleted, OrderNo = item.OrderNo, OtherCode = item.OtherCode, ParentItemCode = item.ParentItemCode, EnteredByUserId = userId, }; await this.db.ClassItems.AddAsync(newItem); } await this.db.SaveChangesAsync(); } catch (Exception) { this.db.Database.RollbackTransaction(); comend.ExecuteNonQuery(); con.Close(); return($"Грешка!!! Възникна проблем при създаването на новата версия."); } this.db.Database.CommitTransaction(); comend.ExecuteNonQuery(); con.Close(); var result = $"Версия с код: {classCode} {newVersion} беше създадена успешно \n"; if (copyRelations) { await this.db.Database.BeginTransactionAsync(); try { var sourceRelations = await this.db.ClassRelationsTypes.Where(r => r.SrcClassifId == classCode && r.SrcVersionId == versionCode).ToListAsync(); var destRelations = await this.db.ClassRelationsTypes.Where(r => r.DestClassifId == classCode && r.DestVersionId == versionCode).ToListAsync(); foreach (var rel in sourceRelations) { var newRelationType = new TC_Classif_Rel_Types() { SrcClassifId = classCode, SrcVersionId = newVersion, DestClassifId = rel.DestClassifId, DestVersionId = rel.DestVersionId, Description = $"Копие от релация \"{rel.Description}\". Сменена версия \"{versionCode}\" с версия \"{newVersion}\"", Valid_From = rel.Valid_From, Valid_To = rel.Valid_To }; await this.db.ClassRelationsTypes.AddAsync(newRelationType); await this.db.SaveChangesAsync(); var relItemsList = await this.db.ClassRelations.Where(ri => ri.RelationTypeId == rel.Id).ToListAsync(); foreach (var relItem in relItemsList) { var newRelation = new TC_Classif_Rels() { RelationTypeId = newRelationType.Id, SrcClassif = relItem.SrcClassif, SrcVer = newRelationType.SrcVersionId, SrcItemId = relItem.SrcItemId, DestClassif = relItem.DestClassif, DestVer = relItem.DestVer, DestItemId = relItem.DestItemId, EntryTime = DateTime.Now, EnteredByUserId = userId }; await this.db.ClassRelations.AddAsync(newRelation); } await this.db.SaveChangesAsync(); } foreach (var rel in destRelations) { var newRelationType = new TC_Classif_Rel_Types() { SrcClassifId = classCode, SrcVersionId = rel.SrcVersionId, DestClassifId = rel.DestClassifId, DestVersionId = newVersion, Description = $"Копие от релация \"{rel.Description}\". Сменена версия \"{versionCode}\" с версия \"{newVersion}\"", Valid_From = rel.Valid_From, Valid_To = rel.Valid_To }; await this.db.ClassRelationsTypes.AddAsync(newRelationType); await this.db.SaveChangesAsync(); var relItemsList = await this.db.ClassRelations.Where(ri => ri.RelationTypeId == rel.Id).ToListAsync(); foreach (var relItem in relItemsList) { var newRelation = new TC_Classif_Rels() { RelationTypeId = newRelationType.Id, SrcClassif = relItem.SrcClassif, SrcVer = relItem.SrcVer, SrcItemId = relItem.SrcItemId, DestClassif = relItem.DestClassif, DestVer = newRelationType.DestVersionId, DestItemId = relItem.DestItemId, EntryTime = DateTime.Now, EnteredByUserId = userId }; await this.db.ClassRelations.AddAsync(newRelation); } await this.db.SaveChangesAsync(); } } catch (Exception) { this.db.Database.RollbackTransaction(); return(result + $"Грешка!!! Възникна проблем при копирането релационните таблици."); } this.db.Database.CommitTransaction(); result = result + Environment.NewLine + $"Копирането на релационните таблици беше успешно."; } return(result); }
private async Task AddSingleItem(SqlConnection con, SqlCommand com, SqlCommand comend, TC_Classif_Items item) { con.Open(); com.Connection = con; comend.Connection = con; com.ExecuteNonQuery(); await this.db.ClassItems.AddAsync(item); await this.db.SaveChangesAsync(); comend.ExecuteNonQuery(); comend.Dispose(); com.Dispose(); con.Close(); }
public async Task <string> AddNewItemAsync(string classCode, string versionCode, string newItemId, string description, string parentItemCode, bool isLeaf, string userId) { var checkMetaData = await this.versions.IsClassVersionExistAsync(classCode, versionCode); if (!checkMetaData) { return($"Грешка! Няма такава версия {classCode} {versionCode}"); } checkMetaData = await this.ItemExistAsync(classCode, versionCode, newItemId); if (checkMetaData) { return($"Грешка! Вече има елемент с този код: {newItemId}"); } int maxLevel = this.db.ClassItems.Where(i => i.Classif == classCode && i.Version == versionCode).Select(i => i.ItemLevel).DefaultIfEmpty(0).Max(); var newItem = new TC_Classif_Items(); try { newItem.Classif = classCode; newItem.Version = versionCode; newItem.ItemCode = newItemId; newItem.Description = description; newItem.DescriptionShort = description; newItem.EnteredByUserId = userId; if (!string.IsNullOrEmpty(parentItemCode)) { var parentItem = await this.db.ClassItems.Where(i => i.Classif == classCode && i.Version == versionCode && i.ItemCode == parentItemCode).FirstOrDefaultAsync(); if (parentItem != null) { newItem.ParentItemCode = parentItemCode; newItem.ItemLevel = parentItem.ItemLevel + 1; } else { return($"Няма родителски елемент с код {parentItemCode}"); } } else { newItem.ItemLevel = 1; } newItem.EntryTime = DateTime.Now; if (isLeaf && newItem.ItemLevel < maxLevel && maxLevel >= 1) { return($"Има листа с по-висок ранг! Листата трябва да са с еднакъв ранг."); } newItem.IsLeaf = isLeaf; await this.db.ClassItems.AddAsync(newItem); await this.db.SaveChangesAsync(); return("Създаването на елемент беше успешно."); } catch (Exception) { return("Грешка! Наличие на дълго поле."); } }
public async Task <string> AddItemsCollection(List <AddNewClassItemsServiceModel> items) { if (!IsClassificationExist(items[0].Classif)) { return($"Няма класификация с код {items[0].Classif}"); } var connectionString = Configuration.GetSection("ConnectionStrings:DefaultConnection").Value; SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand("ALTER TABLE ClassItems NOCHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode"); SqlCommand comend = new SqlCommand("ALTER TABLE ClassItems CHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode"); com.Connection = con; comend.Connection = con; con.Open(); com.ExecuteNonQuery(); //SqlTransaction transaction = con.BeginTransaction(IsolationLevel.Serializable); int i = 0; await this.db.Database.BeginTransactionAsync(); try { for (i = 0; i <= items.Count() - 1; i++) { var newItemDb = new TC_Classif_Items() { Classif = items[i].Classif, Version = items[i].Version, ItemCode = items[i].ItemCode, Description = items[i].Description, DescriptionShort = items[i].DescriptionShort, DescriptionEng = items[i].DescriptionEng, Includes = items[i].Includes, IncludesMore = items[i].IncludesMore, IncludesNo = items[i].IncludesNo, EnteredByUserId = items[i].EnteredByUserId, EntryTime = items[i].EntryTime, IsLeaf = items[i].IsLeaf, ItemLevel = items[i].ItemLevel, OrderNo = items[i].OrderNo, ParentItemCode = items[i].ParentItemCode, IsDeleted = false }; await this.db.ClassItems.AddAsync(newItemDb); } await this.db.SaveChangesAsync(); } catch (Exception) { this.db.Database.RollbackTransaction(); comend.ExecuteNonQuery(); con.Close(); return(string.Concat($"Ред N:{i}", " ", items[i].Classif, " ", items[i].Version, " ", items[i].ItemCode, " има грешка (плюс минус един ред!)")); } this.db.Database.CommitTransaction(); //transaction.Commit(); comend.ExecuteNonQuery(); con.Close(); return("success"); }