locked
MVC Looping record RRS feed

  • Question

  • User-1256377279 posted

    Hi All,

    I have query, below is my code

     for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
                                {
    
                                    var tbl = new IamsLoad.IamsLoadTable();
    
                                    tbl.Reference = workSheet.Cells[rowIterator, Int32.Parse(ConfigurationManager.AppSettings["Reference"].ToString().Split('|')[0])].Text;
    
                                    tbl.ItemTypeId = oGen.ItemTypeIDGetViaName(workSheet.Cells[rowIterator, Int32.Parse(ConfigurationManager.AppSettings["Level"].ToString().Split('|')[0])].Text);
    
                                    tbl.RecordId = oGen.GetNextRecordID(tbl.ItemTypeId);
    
                                    tbl.Structure = tbl.ItemTypeId == 32 ? string.Empty : TempData["PrevRecID"] == null ? tbl.RecordId : TempData["PrevRecID"].ToString();
    
                                    if (TempData["ItemID"] != null)
                                    {
                                        TempData["PrevRecID"] = (int)TempData["ItemID"] == tbl.ItemTypeId ? TempData["PrevRecID"] : tbl.RecordId;
                                    }
                                    else
                                    {
                                        TempData["PrevRecID"] = tbl.RecordId;
                                    }
                                   
                                    
    
                                    TempData["ItemID"] = tbl.ItemTypeId;
    
    
                                    importlisttbl.Add(tbl);
                                    TempData["ExcelTblData"] = importlisttbl;
    
                                }

    Below is the Output

    RecordID Reference ItemTypeId StatusID Structure
    032-003418967 DUMMY Add MS 89245 32 6
    036-003418968 DUMMY Add MS 89245/7 36 6 032-003418967
    037-003418969 Add MS 89245/7/1-6 37 6 036-003418968
    040-003418970 Add MS 89245/7/1 40 6 037-003418969
    040-003418971 Add MS 89245/7/2 40 6 040-003418970
    040-003418972 Add MS 89245/7/3 40 6 040-003418970
    040-003418973 Add MS 89245/7/4 40 6 040-003418970
    040-003418974 Add MS 89245/7/5 40 6 040-003418970
    040-003418975 Add MS 89245/7/6 40 6 040-003418970
    037-003418976 Add MS 89245/7/7-15 37 6 040-003418970
    040-003418977 Add MS 89245/7/7 40 6 037-003418976
    040-003418978 Add MS 89245/7/8 40 6 040-003418977

    Expected Output

    RecordID Reference ItemTypeId StatusID Structure
    032-003418967 DUMMY Add MS 89245 32 6
    036-003418968 DUMMY Add MS 89245/7 36 6 032-003418967
    037-003418969 Add MS 89245/7/1-6 37 6 036-003418968
    040-003418970 Add MS 89245/7/1 40 6 037-003418969
    040-003418971 Add MS 89245/7/2 40 6 037-003418969
    040-003418972 Add MS 89245/7/3 40 6 037-003418969
    040-003418973 Add MS 89245/7/4 40 6 037-003418969
    040-003418974 Add MS 89245/7/5 40 6 037-003418969
    040-003418975 Add MS 89245/7/6 40 6 037-003418969
    037-003418976 Add MS 89245/7/7-15 37 6 036-003418968
    040-003418977 Add MS 89245/7/7 40 6 037-003418976
    040-003418978 Add MS 89245/7/8 40 6 037-003418976

    I want to update the Structure Column based on the expected output.

    Thank you very much

    Shabbir

    Thursday, January 3, 2019 5:19 PM

All replies

  • User475983607 posted

    I assume you forgot to add the Structure column the View/Razor Page.  Unfortunately, you did not post the most import bits so you'll need to review your markup.

    Thursday, January 3, 2019 5:56 PM
  • User1520731567 posted

    Hi shabbir_215,

    tbl.Structure = tbl.ItemTypeId == 32 ? string.Empty : TempData["PrevRecID"] == null ? tbl.RecordId : TempData["PrevRecID"].ToString();
    
                                    if (TempData["ItemID"] != null)
                                    {
                                        TempData["PrevRecID"] = (int)TempData["ItemID"] == tbl.ItemTypeId ? TempData["PrevRecID"] : tbl.RecordId;
                                    }
                                    else
                                    {
                                        TempData["PrevRecID"] = tbl.RecordId;
                                    }

    According to your code,I still can't see your business logic for the time being.

    Some variables are not clear.

    Best Regards.

    Yuki Tao

    Friday, January 4, 2019 5:41 AM
  • User-1256377279 posted

    Thanks Yuki for the reply.

    Business logic is to set Structure column based on the RecordID and ITemType, there is excel file which reads the column Level(ItemTypeID) and set the structure column. 

    Level Reference
    Fonds DUMMY Add MS 89245
    Series DUMMY Add MS 89245/7
    Sub-series Add MS 89245/7/1-6
    File Add MS 89245/7/1
    File Add MS 89245/7/2
    File Add MS 89245/7/3
    File Add MS 89245/7/4
    File Add MS 89245/7/5
    File Add MS 89245/7/6
    Sub-series Add MS 89245/7/7-15
    File Add MS 89245/7/7
    File Add MS 89245/7/8
    File Add MS 89245/7/9
    File Add MS 89245/7/10
    File Add MS 89245/7/11
    File Add MS 89245/7/12
    File Add MS 89245/7/13
    File Add MS 89245/7/14
    File Add MS 89245/7/15

    When the Level = Fonds than structure column should be empty, but if Level is other than fonds it should create RecordID as 036-00123333 for Level series and structure should be 032-001233366 which is previous row.

    below is code for Upload Action result

     HttpPostedFileBase file = Request.Files["fileUpl"];
                    if (Path.GetExtension(file.FileName) == ".xlsx")
                    {
    
                        if ((file != null) && (file.ContentLength != 0) && !string.IsNullOrEmpty(file.FileName))
                        {
                            string fileName = file.FileName;
                            string fileContentType = file.ContentType;
                            byte[] fileBytes = new byte[file.ContentLength];
                            var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
    
                            var importlisttbl = new List<IamsLoad.IamsLoadTable>();
                            var importlistmandat = new List<IamsLoad.IamsLoadMandat>();
                            var importlistnonmandat = new List<IamsLoad.IamsLoadNonMandat>();
    
                            using (var package = new ExcelPackage(file.InputStream))
                            {
                                var currentSheet = package.Workbook.Worksheets;
                                var workSheet = currentSheet.First();
                                var noOfCol = workSheet.Dimension.End.Column;
                                var noOfRow = workSheet.Dimension.End.Row;
    
                                
                                // Reading the excel data and assigning to the Entity
    
                                #region IamsLoad & TCRecords Table Fields
                                
                                for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
                                {
    
                                    var tbl = new IamsLoad.IamsLoadTable();
    
                                    tbl.Reference = workSheet.Cells[rowIterator, Int32.Parse(ConfigurationManager.AppSettings["Reference"].ToString().Split('|')[0])].Text;
    
                                    tbl.ItemTypeId = oGen.ItemTypeIDGetViaName(workSheet.Cells[rowIterator, Int32.Parse(ConfigurationManager.AppSettings["Level"].ToString().Split('|')[0])].Text);
    
                                    tbl.StatusId = Int32.Parse(ConfigurationManager.AppSettings["StatusID"].ToString());
    
                                    tbl.RecordId = oGen.GetNextRecordID(tbl.ItemTypeId);
    
                                    tbl.Structure = tbl.ItemTypeId == 32 ? string.Empty : TempData["PrevRecID"] == null ? tbl.RecordId : TempData["PrevRecID"].ToString();
    
                                    if (TempData["ItemID"] != null)
                                    {
                                        TempData["PrevRecID"] = (int)TempData["ItemID"] == tbl.ItemTypeId ? TempData["PrevRecID"] : tbl.RecordId;
                                    }
                                    else
                                    {
                                        TempData["PrevRecID"] = tbl.RecordId;
                                    }
                                   
                                    
    
                                    TempData["ItemID"] = tbl.ItemTypeId;
    
    
                                    importlisttbl.Add(tbl);
                                    TempData["ExcelTblData"] = importlisttbl;
    
                                }
                                #endregion

    Below is code View

    @model IEnumerable<IamsDataLib.Models.IamsLoad.IamsLoadTable>
    @if (Model != null)
                {
        @Html.Label("Table field")
        <div class="table-responsive">
            <table id="resultTableTarget" class="table table-bordered table-hover mb-none gridview" cellspacing="0" aria-describedby="resultTableTarget_info" role="grid" style="width: 100%; display: block; overflow-x: auto; white-space: nowrap;">
                <thead style="background-color: #555555;color:#FFFFFF">
                    <tr role="row">
                        <th aria-controls="resultTableTarget" rowspan="1" colspan="1">RecordID</th>
                        <th aria-controls="resultTableTarget" rowspan="1" colspan="1">Reference</th>
                        <th aria-controls="resultTableTarget" rowspan="1" colspan="1">ItemTypeId</th>
                        <th aria-controls="resultTableTarget" rowspan="1" colspan="1">StatusID</th>
                        <th aria-controls="resultTableTarget" rowspan="1" colspan="1">Structure</th>
                    </tr>
                </thead>
                <tbody>
    
                    @if (Model.Count() == 0)
                    {
                        <tr>
                            <td rowspan="1" colspan="5">No records found</td>
                        </tr> }
                    else
                    {
                        {
                            foreach (var exldt in Model.ToList())
                            {
                                <tr>
                                    <td>@exldt.RecordId</td>
                                    <td>@exldt.Reference</td>
                                    <td>@exldt.ItemTypeId</td>
                                    <td>@exldt.StatusId</td>
                                    <td>@exldt.Structure</td>
                                </tr>}
                        }
                    }
    
    
    
    
                </tbody>
            </table>
        </div>
    }
    

    Import.CS

    #region Namespaces
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    #endregion
    
    namespace IamsDataLib.Models
    { 
    
    public class IamsLoad
    
        {
            public struct IamsLoadTable
            {
                public string RecordId { get; set; }
                public int ItemTypeId { get; set; }
                public int StatusId { get; set; }
                public string Structure { get; set; }
                public int CataloguerId { get; set; }
                public DateTime CataloguedDate { get; set; }
                public string Reference { get; set; }
                public string RecordFormat { get; set; }
                public string ModifiedBy { get; set; }
                public DateTime? ModifiedDate { get; set; }
                
            }
         }
    }
    

    BAL.cs

    namespace IamsDataLib.Gen.Bal
    {
        public class General
        {
            private IamsDbContext db = new IamsDbContext();
    
    
            public Int32 ItemTypeIDGetViaName(String ItemNm)
            {
                Int32 ItemTypeID = (from st in db.TcItemType
                                    where st.ItemTypeName.ToLower() == ItemNm
                                    select st.ItemTypeId).First();
                return ItemTypeID;
            }
    
            public string GetNextRecordID(int ItemTypeID)
            {
                try
                {
                    //Get the recordID
                    var ID = db.Database.SqlQuery<int>("exec SprTcReadRecordId").FirstOrDefault<int>();
                    string RecId = "0" + ItemTypeID.ToString() + "-00" + ID.ToString();
    
                   //// Increment the record in database
                   // var RecordID = new SqlParameter
                   // {
                   //     ParameterName = "@recordId",
                   //     Value = ID + 1,
                   //     DbType = System.Data.DbType.Int32
                   // };
                   // var r = db.Database.ExecuteSqlCommand("exec SprTcUpdateRecordId @recordId", RecordID);
    
    
    
                    return RecId;
                }
                catch (Exception ex)
                {
                    throw ex;
                }          
            }
    

    Let me know if i missed anything

    Many Thanks,

    Shabbir

    Friday, January 4, 2019 9:38 AM
  • User-1256377279 posted

    Hi Guys,

    I have try to resolve some bit, but still require more logic, as per below table i want to fix structure column in blue should pick 036-003419010 instead of 040-003419013

    RecordID Reference ItemTypeId StatusID Structure
    032-003419009 DUMMY Add MS 89245 32 6
    036-003419010 DUMMY Add MS 89245/7 36 6 032-003419009
    037-003419011 Add MS 89245/7/1-6 37 6 036-003419010
    040-003419012 Add MS 89245/7/1 40 6 037-003419011
    040-003419013 Add MS 89245/7/2 40 6 037-003419011
    037-003419014 Add MS 89245/7/7-15 37 6 040-003419013
    040-003419015 Add MS 89245/7/7 40 6 037-003419014
    040-003419016 Add MS 89245/7/8 40 6 037-003419014

    I have attached my revised code, remaining code can be refer from above post.

                                for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
                                {
    
                                    var tbl = new IamsLoad.IamsLoadTable();
    
                                    tbl.Reference = workSheet.Cells[rowIterator, Int32.Parse(ConfigurationManager.AppSettings["Reference"].ToString().Split('|')[0])].Text;
    
                                    tbl.ItemTypeId = oGen.ItemTypeIDGetViaName(workSheet.Cells[rowIterator, Int32.Parse(ConfigurationManager.AppSettings["Level"].ToString().Split('|')[0])].Text);
    
                                    tbl.StatusId = Int32.Parse(ConfigurationManager.AppSettings["StatusID"].ToString());
    
                                    tbl.RecordId = oGen.GetNextRecordID(tbl.ItemTypeId);
    
                                    tbl.Structure = tbl.ItemTypeId == 32 ? string.Empty : tbl.RecordId;
    
                                    if (importlisttbl.Count != 0)
                                    {
                                        for (int row = 1; row <= importlisttbl.Count; row++)
                                        {
                                            if (importlisttbl[row - 1].ItemTypeId != tbl.ItemTypeId)
                                            {
                                                tbl.Structure = importlisttbl[row - 1].RecordId.ToString();
                                            }
    
                                        }
                                    }
    
                                    importlisttbl.Add(tbl);
                                    TempData["ExcelTblData"] = importlisttbl;
    
                                }
                                

    Many Thanks,

    Shabbir

    Friday, January 4, 2019 11:47 AM