public void SaveChangesResourceCostDetail(ResourceCostDetailList resourceCostDetail) { var connectionString = _configuration.GetConnectionString("DefaultConnection"); using (var connection = new MySqlConnection(connectionString)) { connection.Open(); var transaction = connection.BeginTransaction(); try { if (resourceCostDetail?.UpdateCostDetailList?.Count > 0) { foreach (var UpdatedResource in resourceCostDetail.UpdateCostDetailList) { var sCommand = "Update tbl_ResourceCost set OnsiteCost=@OnsiteCost, OffshoreCost=@OffshoreCost, CreatedBy=@CreatedBy where id=" + UpdatedResource.Id; using (var cmd = new MySqlCommand(sCommand.ToString(), connection)) { cmd.Parameters.AddRange(new MySqlParameter[] { new MySqlParameter { ParameterName = "@OnsiteCost", Value = UpdatedResource.OnsiteCost }, new MySqlParameter { ParameterName = "@OffshoreCost", Value = UpdatedResource.OffshoreCost }, new MySqlParameter { ParameterName = "@CreatedBy", Value = UpdatedResource.CreatedBy != null ? UpdatedResource.CreatedBy.ToUpper() : "ADMIN" }, }); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } } if (resourceCostDetail?.InsertCostDetailList?.Count > 0) { var sCommand = new StringBuilder("INSERT INTO tbl_ResourceCost (Practice, Skill ,Competency, OnsiteCost, OffshoreCost, CreatedDate, CreatedBy, IsActive ) VALUES "); List <string> Rows = new List <string>(); foreach (var resourceInsertCostDetail in resourceCostDetail.InsertCostDetailList) { Rows.Add(string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}', {7})", MySqlHelper.EscapeString(resourceInsertCostDetail.Practice.ToUpper()), MySqlHelper.EscapeString(resourceInsertCostDetail.Skill.ToUpper()), MySqlHelper.EscapeString(resourceInsertCostDetail.Competency.ToUpper()), resourceInsertCostDetail.OnsiteCost, resourceInsertCostDetail.OffshoreCost, String.Format("{0:s}", resourceInsertCostDetail.CreatedDate), MySqlHelper.EscapeString(resourceInsertCostDetail.CreatedBy != null ? resourceInsertCostDetail.CreatedBy.ToUpper() : "ADMIN"), resourceInsertCostDetail.IsActive )); } sCommand.Append(string.Join(",", Rows)); sCommand.Append(";"); using (var cmd = new MySqlCommand(sCommand.ToString(), connection)) { cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } if (resourceCostDetail?.DeleteCostDetailList?.Count > 0) { var records = resourceCostDetail.DeleteCostDetailList.Select(o => o.Id).ToList(); var sCommand = new StringBuilder("DELETE FROM tbl_ResourceCost WHERE ID IN (" + String.Join(",", records) + ")"); using (var cmd = new MySqlCommand(sCommand.ToString(), connection)) { cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { connection.Close(); } } }
public List <ResourceCostDetail> SaveResourceCostChanges([FromBody] ResourceCostDetailList saveResourceCostDetail) { _resourceCostProcessor.SaveChangesResourceCostDetail(saveResourceCostDetail); return(_resourceCostProcessor.GetResourceCostDetails()); }