storing multi row stored procedure data to loop through rows later in c#

Question

I have a stored procedure that will return multi-row data. In my example, we used a SqlDataReader that did the following:

            SqlDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {

                conf.T_id = dr["t_id"].ToString();
                conf.Cm_firstname = dr["cm_firstname"].ToString();
                conf.Cm_lastname = dr["cm_lastname"].ToString();
                conf.Cm_userid = Convert.ToInt32(dr["cm_userid"]);
                conf.AgeOfNotification = dr["AGEOFNOTIFICATION"].ToString();
                conf.DelegateSystem = dr["DELEGATESYSTEM"].ToString();
                conf.P_Id = Convert.ToInt32(dr["P_id"]);
            }
            dr.Close();

However, in my case, I need to store the returned info and loop through each row later to call another class with each row's data. How do I store this info and loop through later?

I was trying like this, but I'm googling it, and it doesn't look like it's possible this way.

In my sqlUtility class:

            DataSet spDataSet = new DataSet();
            ...
            sqlDataAdapter.Fill(spDataSet);
            connection.Close();
            connection.Dispose();
            returnVal.SpDataSet = spDataSet;

Then in my class that I want to loop through each row and for each row, assign to variables and call class with it:

            DataSet tmpUMData = conf.SpDataSet;
            //then I want to convert the DataSet to a SqlDataReader so I can loop through the rows:
            ??convert
            //apparently can't convert like this and do like before
            SqlDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {
                    T_id = dr["t_id"].ToString();
                    Cm_firstname = dr["cm_firstname"].ToString();
                    Cm_lastname = dr["cm_lastname"].ToString();
                    Cm_userid = Convert.ToInt32(dr["cm_userid"]);
                    AgeOfNotification = dr["AGEOFNOTIFICATION"].ToString();
                    DelegateSystem = dr["DELEGATESYSTEM"].ToString();
                    P_Id = Convert.ToInt32(dr["P_id"]);
                    addTask(T_id,Cm_firstname,...);
            }
            dr.Close();

I don't see how to convert from DataSet to SqlDataReader. It looks like I need to loop through the datarows, but I'm having trouble accessing each value in the rows.

        foreach (DataTable table in tmpUMData.Tables)
        {
            foreach (DataRow row in table.Rows)
            {
                foreach (object item in row.ItemArray)
                {
                    //I'm not sure how to get data like I do for dr.Read()
                    string t_id = item["t_id"].ToString(); //cs0021 cannot apply indexing with [] to an expression of type object
                    ... 
                    taskAdd(t_id, ...);
                }
            }
        }

I'm not seeing any good examples on how to access each data item. Any ideas? My data returned from the sql query/sp looks like this:

t_id   cm_firstname   cm_lastname   cm_userid   ageOfNotification   DelegateSystem  P_ID
12345  Joe            Shmo          123         30                  One             7890
23456  Yoda           Creature      234         60                  Other           8901
...

Why use a data reader when you've already loaded everything into a DataSet? In fact, why use a DataSet at all when you really want concrete objects? Why not use an ORM like EF Core and cache the loaded objects? — Panagiotis Kanavos, Feb 22 '21 at 14:30
If you don't want a full-featued ORM (which would still be lighter than a Dataset) you can use Dapper to load and map objects directly from query results — Panagiotis Kanavos, Feb 22 '21 at 14:32

score 0 · Accepted Answer · answered Feb 22 '21 at 14:31

assuming that yor conf is an instance of Conf class, you just have to create a List to store data:

public List<Conf> GetData()
{
.....your code

            var list = new List<Conf>();
            SqlDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {
               var conf=new Conf();
                conf.T_id = dr["t_id"].ToString();
                conf.Cm_firstname = dr["cm_firstname"].ToString();
                conf.Cm_lastname = dr["cm_lastname"].ToString();
                conf.Cm_userid = Convert.ToInt32(dr["cm_userid"]);
                conf.AgeOfNotification = dr["AGEOFNOTIFICATION"].ToString();
                conf.DelegateSystem = dr["DELEGATESYSTEM"].ToString();
                conf.P_Id = Convert.ToInt32(dr["P_id"]);
               list.Add(conf);
            }
            dr.Close();
......
return list;  // for the future use
}

you can use it like this

var list=GetData();

Very good idea. I looked at this link too, which helped. Thank you! https://stackoverflow.com/questions/23561375/objects-from-database-to-fill-list/23562637 — Michele, Feb 23 '21 at 13:09
I was trying to pull the data out of the dataSet later. It works well to put it in a list/class when I do the sql query instead. — Michele, Feb 23 '21 at 13:47

storing multi row stored procedure data to loop through rows later in c#

1 Answers1