question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked TimonYang-MSFT commented

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


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



dotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TimonYang-MSFT avatar image
1 Vote"
TimonYang-MSFT answered TimonYang-MSFT commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

as per your sql this data will not return in pivot format from sql server end then why two loop used there?

please tell me sample data return from sql server side. i am not able to visualize how data would be looking like in sql server end which we are going to read and populate List<T>.

please bit elaborate. thanks

0 Votes 0 ·

@TZacks-2728
I explicitly define the table like this:

132860-capture.png

             while (dataReader.Read())
             {
                ......
                 for (int i = 4; i < count; i++)
                 { 
                     ......
                 }
             }

The while loop is to read each row, in each row, use a for loop to get each period.
If you add a new period later, there is no need to modify this part of the code.

1 Vote 1 ·
capture.png (12.7 KiB)