How do you take a row of data in a data table and put in multiple rows in gridview c#

Question

I have a table with a one row for each customer. Columns in the table are like customer number, payment 1, payment 2, payment 3, payment4, payment 5, payment date 1, payment date 2, payment date 3, payment date 4, payment date 5. What I need to do is display a gridview in asp.net c# like the following:

Payment	Date
Payment 1	Payment Date 1
Payment 2	Payment Date 2

Then Payment 3 and Payment Date 3 below that and so on.

My query in the stored procedure selects payment 1, payment 2, payment 3, payment4, payment 5, payment date 1, payment date 2, payment date 3, payment date 4, payment date 5 from the table where the in-customernumber = the table customer number.

I can get all of the data on one row, but I need it split out into the 5 rows. Any idea how to do this?

Thanks in advance.

Answer 1

@RD, Welcome to Microsoft Q&A, you could try the following code to show split your current result into many rows in c#.

            DataTable dt = new DataTable();
            dt.Columns.Add("CustomerId", typeof(int));
            dt.Columns.Add("Payment1", typeof(string));
            dt.Columns.Add("Payment2", typeof(string));
            dt.Columns.Add("Payment3", typeof(string));
            dt.Columns.Add("Payment4", typeof(string));
            dt.Columns.Add("Payment5", typeof(string));
            dt.Columns.Add("PaymentDate1", typeof(DateTime));
            dt.Columns.Add("PaymentDate2", typeof(DateTime));
            dt.Columns.Add("PaymentDate3", typeof(DateTime));
            dt.Columns.Add("PaymentDate4", typeof(DateTime));
            dt.Columns.Add("PaymentDate5", typeof(DateTime));
            dt.Rows.Add(1001, "p1", "p2", "p3", "p4", "p5", DateTime.Parse("2020-01-01"), DateTime.Parse("2020-01-02"), DateTime.Parse("2020-01-03"), DateTime.Parse("2020-01-04"), DateTime.Parse("2020-01-05"));
            dt.Rows.Add(1002, "p5", "p6", "p7", "p8", "p9", DateTime.Parse("2021-01-01"), DateTime.Parse("2021-01-02"), DateTime.Parse("2021-01-03"), DateTime.Parse("2021-01-04"), DateTime.Parse("2021-01-05"));
            var result = dt.Select("CustomerId=1001").CopyToDataTable();
            DataTable newdt = new DataTable();
            newdt.Columns.Add("Payment", typeof(string));
            newdt.Columns.Add("Date", typeof(DateTime));
            foreach (DataRow item in result.Rows)
            {
                var arr1 = item.ItemArray.Skip(1).Take(5).ToList();
                var arr2 = item.ItemArray.Skip(6).Take(5).ToList();
                for (int i = 0; i < arr1.Count(); i++)
                {
                    newdt.Rows.Add(arr1[i], arr2[i]);
                }
            }
            

Note: To make it easier to test your code, I've used Datatable instead of your query from the database.

Finally, we could check the newdt's result:

Hope my code could help you.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

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.