C# How to store Pivot data into List<T>

Question

I am calling a store procedure and SP return data pivot way like below sample

+-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+
|     Section     |      LineItem      | Broker Code |    Broker Name    | 2012 FYA | 2013 FYA | 1Q 2014A  | 2Q 2014A |
+-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+
| Consensus Model | Net Revenue        | ZB          | B Securities      |          |          |    204.45 |   205.00 |
| Consensus Model | Net Revenue        | TU          | Cantor Fitzgerald |   101.33 |          |    204.45 |   201.00 |
| Consensus Model | Cost of Goods Sold | ZB          | B Securities      |          |          |    204.45 |   205.00 |
| Consensus Model | Cost of Goods Sold | TU          | Cantor Fitzgerald |   101.33 |          |    204.45 |   201.00 |
+-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+

i easily can store the data into datatable but i am interested to know how could i store data into List<T> which return store procedure. i do not want to use Datatable in the middle.

this is my class where i like to store the above pivot data

        public class Data
        {
            public string Section { get; set; }
            public string Lineitem { get; set; }
            public string BrokerCode { get; set; }
            public string BrokerName { get; set; }
            public string Period { get; set; }
            public string PeriodValue { get; set; }
        }

Thanks

Answer 1

Please try if the following code can work for you.

Because the first four properties are fixed, we only need to process the following data.

        static void Main(string[] args)  
        {  
            string connString = @"";  
            using (SqlConnection conn = new SqlConnection(connString))  
            {  
                conn.Open();  
                using (SqlCommand command = new SqlCommand("select * from Test1", conn))  
                {  
                    SqlDataReader dataReader = command.ExecuteReader();  
                    List<Data> datas = new List<Data>();  
                    while (dataReader.Read())  
                    {  
                        string section = dataReader.GetString(1);  
                        string lineitem = dataReader.GetString(1);  
                        string brokerCode = dataReader.GetString(2);  
                        string brokerName = dataReader.GetString(3);  
                        int count = dataReader.FieldCount;  
                        for (int i = 4; i < count; i++)  
                        {  
                            Data data = new Data()  
                            {  
                                Section = section,  
                                Lineitem = lineitem,  
                                BrokerCode = brokerCode,  
                                BrokerName = brokerName,  
                                Period = dataReader.GetName(i),  
                                PeriodValue = dataReader.GetString(i)  
                            };  
                            datas.Add(data);  
                        }  
                    }  
                    Console.WriteLine();  
                }  
            }  

I didn't use a stored procedure, but this should be easy to modify.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.