MVC Looping record RSS

4 replies

Last post Jan 04, 2019 11:47 AM by shabbir_215

‹ Previous Thread|Next Thread ›

Shortcuts

Related Links

shabbir_215

Member

162 Points

633 Posts

MVC Looping record

Jan 03, 2019 05:19 PM|shabbir_215|LINK

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

- Reply
mgebhard
All-Star

38071 Points

15528 Posts

Re: MVC Looping record

Jan 03, 2019 05:56 PM|mgebhard|LINK

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.

Yuki Tao

Contributor

2510 Points

1044 Posts

Re: MVC Looping record

Jan 04, 2019 05:41 AM|Yuki Tao|LINK

Hi shabbir_215,

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

Please remember to "Mark as Answer" if suggestion helps.

shabbir_215

Member

162 Points

633 Posts

Re: MVC Looping record

Jan 04, 2019 09:38 AM|shabbir_215|LINK

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

shabbir_215

Member

162 Points

633 Posts

Re: MVC Looping record

Jan 04, 2019 11:47 AM|shabbir_215|LINK

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

‹ Previous Thread|Next Thread ›