List inside another list

Question

User-2042189214 posted

Hi,

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

Answer 1

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 .

Answer 2

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

Answer 3

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

Answer 4

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;
}
}

Answer 5

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.