CODEWITHSUNDEEP
c#sql
Dawson
Dawson

Reputation: 573

How to loop for as many times as there are rows in SQL result

I have a Person object, which has a list of Reports.

public class Person
{
    public string IdNum { get; set; }
    public string LastName { get; set; }
    public Int32 LocFlag { get; set; }
    public IList<Report> Reports { get; set; }
}

public class Report
{
    public long ReportNum { get; set; }
    public DateTime? ReceivedDate { get; set; }
    public string Subject { get; set; }
}

In my data layer, I want to use 2 SQL Queries to create the Person objects and add them to a list. The point where I'm stuck is - how to loop for only as many times as there are rows in the SQL result for SQL 1.

Each row should be saved as a new Person object and added to the list.

 List<Person> personList = new List<Person>();
 //SQL 1 - loop as many times as there are results, each loop adding a new Person to list

{
     personList.Add(_context.Database.SqlQuery<Person>(
                    @"SELECT id_txt AS IdNum
                , lastname_txt AS LastName
                , LOCATION_FLG AS LocFlag
                FROM fn_PersonList(@locationId)",
                    new SqlParameter("@locId", locID)
                    ).Single());
 }

 // second sql query to add Report list to the already created Person list

     foreach (Person t in personList)
     {
        i++;
        personList[i].Reports.Insert(i,
        _context.Database.SqlQuery<Report>(
        @"SELECT ID_NUM
            ,  RECEIVED_DTE
            , SUBJECT_TXT                                           
            FROM  fn_Work_Person(@pdNumber, t)  
            WHERE LocID =  & locationID  
            ORDER BY ID_NUM DESC",
            new SqlParameter("@locationId", locationID)).Single());
    }
    return personList;

Upvotes: 0

Views: 111

Answers (2)

Quantumplate
Quantumplate

Reputation: 1104

Try this...

personList = _context.Database.SqlQuery<Person>(
                @"SELECT id_txt AS IdNum
            , lastname_txt AS LastName
            , LOCATION_FLG AS LocFlag
            FROM fn_PersonList(@locationId)",
                new SqlParameter("@locId", locID)
                ).ToList();

SqlQuery is probably returning IEnumerable<Person> and just needs to be converted to a list.

Upvotes: 2

David BS
David BS

Reputation: 1892

You may utilize @@Rowcount after the SQL1 to get number of rows or, create an Autonumber field in table (I will call it c_Serial) and get it through "output inserted.c_Serial", just after the FROM clause. You will get the number of registers selected in both cases.

            @"SELECT id_txt AS IdNum
            , lastname_txt AS LastName
            , LOCATION_FLG AS LocFlag
            FROM fn_PersonList(@locationId) output inserted.c_serial",
                new SqlParameter("@locId", locID)
                ).Single());

or (I will use the SQL syntax since it´s clear to me)

            Declare Counter int

            SELECT id_txt AS IdNum
            , lastname_txt AS LastName
            , LOCATION_FLG AS LocFlag
            FROM fn_PersonList(@locationId)

           set @Counter=@@Rowcount

Good luck

Upvotes: 0

Related Questions