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

T.Zacks 3,986 Reputation points
2021-09-14T16:02:38.323+00:00

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

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,524 questions
0 comments No comments
{count} votes

Accepted answer
  1. Timon Yang-MSFT 9,576 Reputation points
    2021-09-16T01:41:25.137+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful