locked
Convert My Gridview Data To My Format RRS feed

  • Question

  • User-807418713 posted

    Hello

    My Asp.net 2.0 C# Gridview Look Like This

    ItemName Belongs To Qunatity
    Apple City 1 5
    Apple City 2 40
    Apple City 3 25
    Mango City 7 10
    Mango City 9 20
    Mango City 10 30

    But I Want Like This below

    Belongs To Qunatity
     Apple
    City 1 5
    City 2 40
    City 3 25
    Total 70
    Mango
    City 7 10
    City 9 20
    City 10 30
    Total 60

    How To Do So

    Wednesday, July 25, 2018 8:34 AM

Answers

  • User283571144 posted

    Hi Gopi.MCA ,

    You could group your data and combine you original data with your grouped data.

    I have made a sample to make a sample.

    ASPX:

    <form id="form1" runat="server">
            <div>
                 <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="false" >
     
                     <Columns>
                         <asp:BoundField DataField="BelongsTo" HeaderText="BelognsTo" HeaderStyle-Font-Bold="false" />
                         <asp:BoundField DataField="Qunatity" HeaderText="Qunatity" HeaderStyle-Font-Bold="false" />
                     </Columns>
                 </asp:GridView>
            </div>
           
        </form>
    

    Code-behind:

      public SqlDataReader GetDataReader(string sql)
            {
                SqlConnection con = new SqlConnection(constr);
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    try
                    {
                       
                        con.Open();
                        
                        return com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                    catch (Exception)
                    {
                        con.Close();
                        con.Dispose();
     
                        throw;
                    }
     
                }
            }
            public List<List<Fruit>> GetData()
            {
                string sql = "select ItemName,BelongsTo,Qunatity from fruits;select itemName, sum(qunatity) from fruits group by itemName";
                List<List<Fruit>> list = new List<List<Fruit>>();
                List<Fruit> originalfruits = new List<Fruit>();
                List<Fruit> groupedFruits = new List<Fruit>();
                //add originalfruits and groupedFruits to the list
                list.Add(originalfruits);
                list.Add(groupedFruits);
                using (SqlDataReader reader = GetDataReader(sql))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Fruit fruit = new Fruit();
                            fruit.BelongsTo = reader.GetString(1);
                            fruit.ItemName = reader.GetString(0);
                            fruit.Qunatity = reader.GetInt32(2);
                            originalfruits.Add(fruit);
                        }
                       
     
                    }
    //get the second datasource
                   if(reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            Fruit fruit = new Fruit();
                            fruit.BelongsTo = "Total";
                            fruit.ItemName = reader.GetString(0);
                            fruit.Qunatity = reader.GetInt32(1);
                            groupedFruits.Add(fruit);
                        }
     
                    }
                    return list;
                }
    
    
     
    private string constr = ConfigurationManager.ConnectionStrings["WebFormCases.Models.supplyModelConnectionString"].ConnectionString;
           
      protected void Page_Load(object sender, EventArgs e)
            {
                //create a list to store data
                List<Fruit> fruits = new List<Fruit>();
     
                //get the original data
     
                List<Fruit> originData = GetData()[0];
                //create the first data , make its BelognsTo the itemName of the fist row of original data to record the itemName
                Fruit fruit = new Fruit
                {
                    BelongsTo = originData[0].ItemName
                };
               
                //add the data to the list
                fruits.Add(fruit);
     
                // group the original data by its itemName
     
                List<Fruit> groupedData = GetData()[1];
               
                //loop through the original data
                for (int i=0;i<originData.Count;i++)
                {
                    if (originData[i].ItemName != fruit.BelongsTo)
                  
                    {
                        // if item.ItemName doesn't equal to recorded itemName
                        // it indicates a new group should be started
                        //so you should get the corresponding  grouped data from the variable query
                        // through  f.itemName == fruit.BelongsTo
                        for (int j = 0; j < groupedData.Count; j++)
                        {
                            if (groupedData[j].ItemName == fruit.BelongsTo)
                            {
                                Fruit tFruit = new Fruit { BelongsTo = "Total", Qunatity = groupedData[j].Qunatity };
                                fruits.Add(tFruit);
                            }
                        }
                      
                       
     
                        //add itemName to the start of the group
                        fruit = new Fruit
                        {
                            BelongsTo = originData[i].ItemName
                        };
                       
                        fruits.Add(fruit);
                      
                    }
                    //add the original data to the list 
                    fruits.Add(originData[i]);
     
                    
                }
                // add the last aggregate data
                
                 for (int j = 0; j < groupedData.Count; j++)
                        {
                            if (groupedData[j].ItemName == originData[(originData.Count - 1)].ItemName)
                            {
                                Fruit last = new Fruit { BelongsTo = "Total", Qunatity = groupedData[j].Qunatity };
                                fruits.Add(last);
                            }
                        }
                  
                //bind data
                GridView1.DataSource =fruits;
                GridView1.DataBind();
            }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
     
                    Fruit drv = e.Row.DataItem as Fruit;
                    // make apple and monge thicker
                    if (drv.Qunatity==null)
                    {
                        e.Row.Cells[0].Font.Bold = true;
                      
                        //make the second cell invisible
                        e.Row.Cells[1].Visible = false;
                      
                    }
                    if (drv.BelongsTo == "Total")
                    {
                        //make  aggregate data thicker
                        e.Row.Cells[0].Font.Bold = true;
                        e.Row.Cells[1].Font.Bold = true;
                    }
                  
                }
            }
    
    

    Model:

    public partial class Fruit
        {
            [StringLength(50)]
            public string ItemName { get; set; }
     
            [StringLength(50)]
            public string BelongsTo { get; set; }
     
            public int? Qunatity { get; set; }
     
            [Key]
            public int itemid { get; set; }
    }
     
    

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 8:18 AM