Wednesday, 16 January 2013

Display Sum of Columns total in GridView Footer asp.net C#













Step 1) add gridview to aspx page

    <style type="text/css">
        .footerstyle
        {
            color:white;
            background-color:teal;
            font-family:'Comic Sans MS';
            font-size:1.8em;

        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <h1>Total Sum in the GridView asp.net C#</h1>
        <asp:GridView ID="GridView1" runat="server"
             ShowHeaderWhenEmpty="true"
            EmptyDataText="No Data Found"
             AutoGenerateColumns="false"
              ShowFooter="true"
            FooterStyle-BackColor="Red"
            FooterStyle-CssClass="footerstyle">
            <Columns>
                <asp:TemplateField HeaderText="[ProductID]">
                    <InsertItemTemplate>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <asp:Label runat="server"
                            Text='<%# Eval("ProductID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="[[ProductName]]">
                    <InsertItemTemplate>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server"
                            Text='<%# Eval("ProductName") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>

                     <asp:TemplateField HeaderText="UnitPrice">
                    <InsertItemTemplate>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server"
                            Text='<%# Eval("UnitPrice") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>

                    <asp:TemplateField HeaderText="UnitsOnOrder">
                    <InsertItemTemplate>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server"
                            Text='<%# Eval("UnitsOnOrder") %>'></asp:Label>
                    </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label runat="server" Text="Sum"></asp:Label>
                        </FooterTemplate>
                </asp:TemplateField>

            <asp:TemplateField HeaderText="Total">
                    <InsertItemTemplate>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server"
Text='<%# CalTotal( Decimal.Parse(Eval("UnitsOnOrder").ToString())*Decimal.Parse(Eval("UnitPrice").ToString())) %>'>

                        </asp:Label>
                    </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label ID="Label3" runat="server"
                                Text='<%# GetSum() %>'></asp:Label>
                        </FooterTemplate>
                </asp:TemplateField>

            </Columns>
        </asp:GridView>
    </form>
</body>



Step 2)  Calling Northwind SQL View and bind it to GridView
 
Store in the Datatable and then Bind  Datatable to gridView
void Calling_SQL_VIew_using_adonet1()
        {
        try
        {
        SqlConnection conn = new SqlConnection(@"Server=.\sqlexpress2012;
                                               database=northwind;trusted_connection=yes;");
       

        //SqlCommand sel = new SqlCommand(stroredProc);
        SqlCommand sel = new SqlCommand();
        sel.CommandType = CommandType.Text;
        sel.CommandText = "select * from [Northwind].[dbo].[Alphabetical list of products] where unitsonOrder >30";
        sel.Connection = conn;
        SqlDataAdapter ad = new SqlDataAdapter(sel);

        conn.Open();
        ad.Fill(dt);

        GridView1.DataSource = dt;
        GridView1.DataBind();
        }
        catch (SqlException ex)
        {
        Response.Write(ex.Message);
        }
        }

Step 3) Calculate Sum
              Add two methods
               1.Iterating columns and store it temp variable
               2. Footer is called at the end, step 1 has total sum of the rows.

     public string CalTotal(decimal total)
        {
        productsum += total;
        return total.ToString();
        }
        public string GetSum()
        {
        return productsum.ToString("C");
        }
Step 5)  Page Level Variables

DataTable dt = new DataTable();
decimal productsum = 0;



 Run the Page