locked
List inside another list RRS feed

  • Question

  • User-2042189214 posted

    Hi,

    How to pass a list of data inside another list in the DAL o the stored procedure in c#

    Sunday, June 14, 2020 6:11 PM

All replies

  • User-1204637165 posted

    Give more information. you can post the datas that you want to pass so people can help you. This information is to scanty to give any reasonable help .

    Sunday, June 14, 2020 6:40 PM
  • User1120430333 posted

    A List<T> can be strong typed to a class. The class can have a public property that is a List<T> strong typed to a class.

    public class EmployeeDTO
    {
        public string Name {get; set;}
    
       //some other public properties 
     
       public Addresses List<AddressDTO> {get; set;} = new List<AddressDTO>();
    }
    
    ==========================================
    
    var employees = new List<EmployeeDTO)();
     
    var emp = new EmployeeDTO();
    
    emp.Name = "test"'
    var addr = new AddressDTO();
    
    addr.Street = "Some street address";
    emp.Addresses.Add(addr);
    
    employees.Add(emp);
    
    
    

     https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    Sunday, June 14, 2020 9:50 PM
  • User-2042189214 posted

    i need to pass data in this format

    "TemplateId":51,
    "ActivityList":[
    {"Activity":"Turning the fly wheel by",
    "ActivityInnerList":[
    {"CatId":1,"Hazard":"H1","Consequence":"C1","IrrId":3,"ExistingControl":"E1","AdditionalMitigatingMeasures":"A1","RrrId":5,
    "ResponsiblityId":7,"CloseDate":"07/07/2020"},
    {"CatId":3,"Hazard":"H2","Consequence":"C2","IrrId":3,"ExistingControl":"E2","AdditionalMitigatingMeasures":"A2","RrrId":5,
    "ResponsiblityId":7,"CloseDate":"06/06/2020"}
    ]
    },
    {"Activity":"Turning2 the fly wheel by"
    ,
    "ActivityInnerList":[
    {"CatId":4,"Hazard":"H3","Consequence":"C3","IrrId":3,"ExistingControl":"E3","AdditionalMitigatingMeasures":"A3","RrrId":5,
    "ResponsiblityId":7,"CloseDate":"08/08/2020"},
    {"CatId":2,"Hazard":"H4","Consequence":"C4","IrrId":3,"ExistingControl":"E4","AdditionalMitigatingMeasures":"A4","RrrId":5,
    "ResponsiblityId":7,"CloseDate":"09/09/2020"}
    ]
    }
    ]

    I try to pass data like this for ActivityList

    cmd.Parameters.Add(new SqlParameter("@TemplateId", SqlDbType.Int)).Value = RiskAssessmentFormAddModel.TemplateId;
    cmd.Parameters.Add(new SqlParameter("@ActivityList", SqlDbType.Structured)).Value = CreateSqlDataRecordsActivityList(RiskAssessmentFormAddModel.ActivityList);

    But how i pass the ActivityInnerList which is inside the ActivityList

    This is my problem.Please provide any solution for this

    Monday, June 15, 2020 4:44 AM
  • User-2042189214 posted

    I call the ActivityList by

    private static IEnumerable<SqlDataRecord> CreateSqlDataRecordsActivityList(List<ActivityListModel> ActivityList)
    {
    SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
    new SqlMetaData("Activity", SqlDbType.VarChar,1000),
    });

    foreach (var item in ActivityList)
    {
    record.SetSqlString(0, item.Activity);
    yield return record;
    }
    }

    Monday, June 15, 2020 4:46 AM
  • User-474980206 posted

    Please review table variables in sql, and using them. A table variable can not have a column that is also a table variable. 

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql?view=sql-server-ver15

    You will need to union all the sub list entries into a second table variable, and pass this table.

    Monday, June 15, 2020 2:50 PM