locked
Show My Data In Pivot Format Using Repeater Or Gridview RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my table data from sql

    CREATE TABLE [dbo].[DummyTable](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Batch_No] [varchar](250) NULL,
    	[Total_Qty] [float] NULL,
    	[Department] [varchar](250) NULL,
    	[Qty] [float] NULL,
    	[Value] [float] NULL,
     CONSTRAINT [PK_DummyTable] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[DummyTable] ON
    INSERT [dbo].[DummyTable] ([ID], [Batch_No], [Total_Qty], [Department], [Qty], [Value]) VALUES (1, N'B1', 100, N'Dep1', 50, 1000)
    INSERT [dbo].[DummyTable] ([ID], [Batch_No], [Total_Qty], [Department], [Qty], [Value]) VALUES (2, N'B1', 100, N'Dep2', 60, 2000)
    INSERT [dbo].[DummyTable] ([ID], [Batch_No], [Total_Qty], [Department], [Qty], [Value]) VALUES (3, N'B2', 150, N'Dep1', 10, 4000)
    INSERT [dbo].[DummyTable] ([ID], [Batch_No], [Total_Qty], [Department], [Qty], [Value]) VALUES (4, N'B2', 150, N'Dep2', 20, 5000)
    INSERT [dbo].[DummyTable] ([ID], [Batch_No], [Total_Qty], [Department], [Qty], [Value]) VALUES (5, N'B3', 200, N'Dep2', 60, 10000)
    SET IDENTITY_INSERT [dbo].[DummyTable] OFF
    

    I want result set like this using repeater control...

    How to make this above report using repeater asp.net 2.0 C#

    Tuesday, August 14, 2018 12:08 PM

Answers

  • User-893317190 posted

    Can We Set Dep1 & Dep2 Dynamically

    Without passing this Dep1 & Dep2..

    Base On Resultset The Dep1, Dep2, Should be create..?

    Possible?

    Hi Gopi.MCA,

    If you only don't want to use Dep1 and Dep2 in your code behind , this is possible.

    You only need to  select distinct department in your database so that you could get departments from database.

    Below is my code.

    if (!IsPostBack){
    
                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WebFormCases.Models.supplyModelConnectionString"].ConnectionString))
                    {
                        using (SqlCommand com = new SqlCommand("select distinct Department from DummyTable order by Department;select * from DummyTable order by Batch_No,Department", con))
                        {
    
                            con.Open();
    
                            List<string> departments = new List<string>();
    
                       
    
                            List<Batch> list = new List<Batch>();
                            using (SqlDataReader reader = com.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        departments.Add(reader.GetString(0));
                                    }
    
    
                                    reader.NextResult();
                                    //declare a variable to save the current BatchNo
                                    // to decide whether to create a new Batch
                                    string BatchNo = null;
    
    
                                    // declare a variable to store the total qty
                                    Double totalQty = 0;
                                    // declare a variable to store the total value
                                    Double totalValue = 0;
    
                                    //declare a variable to save the current Batch
                                    Batch batch = null;
                                    while (reader.Read())
                                    {
                                        totalQty += reader.GetDouble(4);
                                        totalValue += reader.GetDouble(5);
    
                                        if (BatchNo == null)
                                        {
                                            // set the Batch property
                                            batch = new Batch();
                                            batch.BatchNo = reader.GetString(1);
                                            BatchNo = batch.BatchNo;
    
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == departments[0])
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == departments[1])
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
                                        }
                                        if (BatchNo != null && BatchNo != reader.GetString(1))
                                        {
                                            //create a new batch and add the old batch to the list
                                            batch.TotalQty = batch.Dep1Qty + batch.Dep2Qty;
                                            batch.TotalValue = batch.Dep1Value + batch.Dep2Value;
                                            list.Add(batch);
                                            batch = new Batch();
                                            batch.BatchNo = reader.GetString(1);
                                            BatchNo = batch.BatchNo;
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == departments[0])
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == departments[1])
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
    
                                        }
                                        if (BatchNo != null && BatchNo == reader.GetString(1))
                                        {
    
                                            // only set the property of the batch
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == departments[0])
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == departments[1])
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
    
                                        }
    
                                    }
    
                                    //add the last batch
                                    batch.TotalQty = batch.Dep1Qty + batch.Dep2Qty;
                                    batch.TotalValue = batch.Dep1Value + batch.Dep2Value;
                                    list.Add(batch);
    
    
                                    Repeater1.DataSource = list;
                                    Repeater1.DataBind();
    
    
                                    //get the footer of the reperter
                                    Control footerControl = Repeater1.Controls[1 + list.Count];
    
                                    // set the totalqty and total value
                                    Literal literal = footerControl.FindControl("TotalQTY") as Literal;
                                    literal.Text = totalQty.ToString();
                                    literal = footerControl.FindControl("TotalValue") as Literal;
                                    literal.Text = totalValue.ToString();
    
    
    
                                }
    
    
    
                            }
                        }
    
                    }
    
                }

    But if  you have three departments or more , the code should be changed more  and it will be  more difficult.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 15, 2018 9:48 AM

All replies

  • User-1716253493 posted

    There are headertemplate and itemtemplate in repeater control

    Wednesday, August 15, 2018 1:15 AM
  • User-893317190 posted

    Hi  Gopi.MCA,

    You could use sql to group your data fist and reorganize them in your code behind.

    Below is my code.

      <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" integrity="sha384-WskhaSGFgHYWDcbwN70/dfYBj47jz9qbsMId/iRN3ewGhXQFZCSftd1LZCfmhktB" crossorigin="anonymous">
    </head>
    <body>
        <form id="form1" runat="server">
            
            <asp:Repeater ID="Repeater1" runat="server"   >
                <HeaderTemplate>
    
                     <table class="table table-bordered bg-secondary">
               <thead>
    
                   <tr> <th class="text-center">BatchNo</th><th class="text-center">Total Qty</th> <th class="text-center" colspan="2">Dep1</th><th class="text-center" colspan="2">Dep2</th><th class="text-center" colspan="2">Total For Dep1&Dep2</th></tr>
                   <tr> <th></th> <th></th> <th class="text-center">Qty</th><th class="text-center">Value</th><th class="text-center">Qty</th><th class="text-center">Value</th><th class="text-center">Total Qty</th><th class="text-center">Total Value</th>   </tr>
    
               </thead>
              <tbody>
                  
                </HeaderTemplate>
           <ItemTemplate>
        
                <tr>
                    <td class="text-center">
              <%# Eval("BatchNo") %></td>
                    
                        <td class="text-center">      <%# Eval("Total_Qty") %></td>
                      <td class="text-center">      <%# Eval("Dep1Qty") %></td>
                     <td class="text-center">      <%# Eval("Dep1Value") %></td>
                      <td class="text-center">      <%# Eval("Dep2Qty") %></td>
                     <td class="text-center">      <%# Eval("Dep2Value") %></td>
                      <td class="text-center">      <%# Eval("TotalQty") %></td>
                     <td class="text-center">      <%# Eval("TotalValue") %></td>
                </tr>
           </ItemTemplate>
                  
                
                <FooterTemplate>
    
                     <tr><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td></tr>
                    <tr><td></td><td></td><td></td><td></td><td></td><td class="text-center">Total</td><td class="text-center">
                        <asp:Literal ID="TotalQTY" runat="server"></asp:Literal></td><td class="text-center">  <asp:Literal ID="TotalValue" runat="server"></asp:Literal></td></tr>
              </tbody>
            </table>
             
    
                </FooterTemplate>
                    
                  </asp:Repeater>
               
    </form>
    
    

    And code behind.

    public partial class RepeaterAggregate : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
                if (!IsPostBack){
    
                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WebFormCases.Models.supplyModelConnectionString"].ConnectionString))
                    {
                        using (SqlCommand com = new SqlCommand("select * from DummyTable order by Batch_No,Department", con))
                        {
    
                            con.Open();
    
    
                            List<Batch> list = new List<Batch>();
                            using (SqlDataReader reader = com.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
    
                                    //declare a variable to save the current BatchNo
                                    // to decide whether to create a new Batch
                                    string BatchNo = null;
    
    
                                    // declare a variable to store the total qty
                                    Double totalQty = 0;
                                    // declare a variable to store the total value
                                    Double totalValue = 0;
    
                                    //declare a variable to save the current Batch
                                    Batch batch = null;
                                    while (reader.Read())
                                    {
                                        totalQty += reader.GetDouble(4);
                                        totalValue += reader.GetDouble(5);
    
                                        if (BatchNo == null)
                                        {
                                            // set the Batch property
                                            batch = new Batch();
                                            batch.BatchNo = reader.GetString(1);
                                            BatchNo = batch.BatchNo;
    
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == "Dep1")
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == "Dep2")
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
                                        }
                                        if (BatchNo != null && BatchNo != reader.GetString(1))
                                        {
                                            //create a new batch and add the old batch to the list
                                            batch.TotalQty = batch.Dep1Qty + batch.Dep2Qty;
                                            batch.TotalValue = batch.Dep1Value + batch.Dep2Value;
                                            list.Add(batch);
                                            batch = new Batch();
                                            batch.BatchNo = reader.GetString(1);
                                            BatchNo = batch.BatchNo;
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == "Dep1")
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == "Dep2")
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
    
                                        }
                                        if (BatchNo != null && BatchNo == reader.GetString(1))
                                        {
    
                                            // only set the property of the batch
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == "Dep1")
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == "Dep2")
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
    
                                        }
    
                                    }
    
                                    //add the last batch
                                    batch.TotalQty = batch.Dep1Qty + batch.Dep2Qty;
                                    batch.TotalValue = batch.Dep1Value + batch.Dep2Value;
                                    list.Add(batch);
    
    
                                    Repeater1.DataSource = list;
                                    Repeater1.DataBind();
    
    
                                    //get the footer of the reperter
                                    Control footerControl = Repeater1.Controls[1 + list.Count];
    
                                    // set the totalqty and total value
                                    Literal literal = footerControl.FindControl("TotalQTY") as Literal;
                                    literal.Text = totalQty.ToString();
                                    literal = footerControl.FindControl("TotalValue") as Literal;
                                    literal.Text = totalValue.ToString();
    
    
    
                                }
    
    
    
                            }
                        }
    
                    }
    
                }
    
            }
            //define a model
            public class Batch
            {
                public string BatchNo { get; set; }
                public Double Total_Qty { get; set; }
                public Double Dep1Qty { get; set; }
                public Double Dep1Value { get; set; }
                public Double Dep2Qty { get; set; }
                public Double Dep2Value { get; set; }
                public Double TotalQty { get; set; }
                public Double TotalValue { get; set; }
    
            }
    

    The result.

    Best regards ,

    Ackerly Xu

    Wednesday, August 15, 2018 6:16 AM
  • User-807418713 posted

    Hello Ackerly Xu

    Thanks for your code

    Index was outside the bounds of the array.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.IndexOutOfRangeException: Index was outside the bounds of the array.

    Wednesday, August 15, 2018 8:43 AM
  • User-893317190 posted

    Hi Gopi.MCA,

    I'm sorry.Because c#2.0 doesn't support Auto-Implemented Properties , so you meet the error. Please change the model as follows.

    public class Batch
            {
                private string _BatchNo;
                public string BatchNo {
                    get
                    {
                        return _BatchNo;
                    }
                    set
                    {
                        _BatchNo= value  ;
                    }
                }
    
                private Double _Total_Qty;
    
                public  Double Total_Qty
                {
                    get
                    {
                        return _Total_Qty;
                    }
                    set
                    {
                        _Total_Qty = value;
                    }
                }
    
                private Double _Dep1Qty;
                public Double Dep1Qty
                {
                    get
                    {
                        return _Dep1Qty;
                    }
                    set
                    {
                        _Dep1Qty = value;
                    }
                }
                private Double _Dep1Value;
                public Double Dep1Value
                {
                    get
                    {
                        return _Dep1Value;
                    }
                    set
                    {
                        _Dep1Value = value;
                    }
                }
    
                private Double _Dep2Qty;
                public Double Dep2Qty
                {
                    get
                    {
                        return _Dep2Qty;
                    }
                    set
                    {
                        _Dep2Qty = value;
                    }
                }
         
               
    
                private Double _Dep2Value;
                public Double Dep2Value
                {
                    get
                    {
                        return _Dep2Value;
                    }
                    set
                    {
                        _Dep2Value = value;
                    }
                }
    
                private Double _TotalQty;
                public Double TotalQty
                {
                    get
                    {
                        return _TotalQty;
                    }
                    set
                    {
                        _TotalQty = value;
                    }
                }
    
                private Double _TotalValue;
                public Double TotalValue
                {
                    get
                    {
                        return _TotalValue;
                    }
                    set
                    {
                        _TotalValue = value;
                    }
                }
    
            }

    Best regards,

    Ackerly Xu

    Wednesday, August 15, 2018 9:03 AM
  • User-807418713 posted

    Hello Ackerly Xu

    Thanks For The Code.. Im very much thanks to you for your help..

    Can We Set Dep1 & Dep2 Dynamically

    Without passing this Dep1 & Dep2..

    Base On Resultset The Dep1, Dep2, Should be create..?

    Possible?

    Wednesday, August 15, 2018 9:15 AM
  • User-893317190 posted

    Can We Set Dep1 & Dep2 Dynamically

    Without passing this Dep1 & Dep2..

    Base On Resultset The Dep1, Dep2, Should be create..?

    Possible?

    Hi Gopi.MCA,

    If you only don't want to use Dep1 and Dep2 in your code behind , this is possible.

    You only need to  select distinct department in your database so that you could get departments from database.

    Below is my code.

    if (!IsPostBack){
    
                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WebFormCases.Models.supplyModelConnectionString"].ConnectionString))
                    {
                        using (SqlCommand com = new SqlCommand("select distinct Department from DummyTable order by Department;select * from DummyTable order by Batch_No,Department", con))
                        {
    
                            con.Open();
    
                            List<string> departments = new List<string>();
    
                       
    
                            List<Batch> list = new List<Batch>();
                            using (SqlDataReader reader = com.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        departments.Add(reader.GetString(0));
                                    }
    
    
                                    reader.NextResult();
                                    //declare a variable to save the current BatchNo
                                    // to decide whether to create a new Batch
                                    string BatchNo = null;
    
    
                                    // declare a variable to store the total qty
                                    Double totalQty = 0;
                                    // declare a variable to store the total value
                                    Double totalValue = 0;
    
                                    //declare a variable to save the current Batch
                                    Batch batch = null;
                                    while (reader.Read())
                                    {
                                        totalQty += reader.GetDouble(4);
                                        totalValue += reader.GetDouble(5);
    
                                        if (BatchNo == null)
                                        {
                                            // set the Batch property
                                            batch = new Batch();
                                            batch.BatchNo = reader.GetString(1);
                                            BatchNo = batch.BatchNo;
    
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == departments[0])
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == departments[1])
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
                                        }
                                        if (BatchNo != null && BatchNo != reader.GetString(1))
                                        {
                                            //create a new batch and add the old batch to the list
                                            batch.TotalQty = batch.Dep1Qty + batch.Dep2Qty;
                                            batch.TotalValue = batch.Dep1Value + batch.Dep2Value;
                                            list.Add(batch);
                                            batch = new Batch();
                                            batch.BatchNo = reader.GetString(1);
                                            BatchNo = batch.BatchNo;
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == departments[0])
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == departments[1])
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
    
                                        }
                                        if (BatchNo != null && BatchNo == reader.GetString(1))
                                        {
    
                                            // only set the property of the batch
                                            batch.Total_Qty = reader.GetDouble(2);
                                            if (reader.GetString(3) == departments[0])
                                            {
                                                batch.Dep1Qty = reader.GetDouble(4);
                                                batch.Dep1Value = reader.GetDouble(5);
                                            }
                                            if (reader.GetString(3) == departments[1])
                                            {
                                                batch.Dep2Qty = reader.GetDouble(4);
                                                batch.Dep2Value = reader.GetDouble(5);
                                            }
    
                                        }
    
                                    }
    
                                    //add the last batch
                                    batch.TotalQty = batch.Dep1Qty + batch.Dep2Qty;
                                    batch.TotalValue = batch.Dep1Value + batch.Dep2Value;
                                    list.Add(batch);
    
    
                                    Repeater1.DataSource = list;
                                    Repeater1.DataBind();
    
    
                                    //get the footer of the reperter
                                    Control footerControl = Repeater1.Controls[1 + list.Count];
    
                                    // set the totalqty and total value
                                    Literal literal = footerControl.FindControl("TotalQTY") as Literal;
                                    literal.Text = totalQty.ToString();
                                    literal = footerControl.FindControl("TotalValue") as Literal;
                                    literal.Text = totalValue.ToString();
    
    
    
                                }
    
    
    
                            }
                        }
    
                    }
    
                }

    But if  you have three departments or more , the code should be changed more  and it will be  more difficult.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 15, 2018 9:48 AM