Monday, 12 September 2016

Manipulating DataTable Adding Column values of multiple rows and binding it to GridView in ADO.NET

11:40    No comments

Let's see with an example:
We are creating a table

Create table Test(
Sno int,
Name varchar(20),
TotalAmount int,
TotalQuantity int,
BKNID int
)

insert into test
values
(1,'xyz',34,56,1),(2,'xyz',45,87,1),(3,'xyz',89,34,1),
(4,'abc',44,32,2),(5,'abc',45,32,2),(6,'abc',67,54,2)



In the aspx file put the following code


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="gdvDataAll" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>
            <br /><br />
        </div>
        <div>
            <asp:GridView ID="gdvEmlpoyee" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>
        </div>
    </form>
</body>
</html>

  In code behind add the following code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace ADONET
{
    public partial class WorkingwithDataTable : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("Select * from Test", sqlcon);
                SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                DataTable dt = ds.Tables[0];
                gdvDataAll.DataSource = dt;
                gdvDataAll.DataBind();
                int count = dt.Rows.Count;
                for (int i = 0; i < count - 1; )
                {
                    if (Convert.ToInt32(dt.Rows[i]["BKNID"]) == Convert.ToInt32(dt.Rows[i + 1]["BKNID"]))
                    {
                        dt.Rows[i]["TotalAmount"] = Convert.ToInt32(dt.Rows[i]["TotalAmount"]) + Convert.ToInt32(dt.Rows[i + 1]["TotalAmount"]);
                        dt.Rows[i]["TotalQuantity"] = Convert.ToInt32(dt.Rows[i]["TotalQuantity"]) + Convert.ToInt32(dt.Rows[i + 1]["TotalQuantity"]);
                        dt.Rows.RemoveAt(i + 1);
                        count--;
                    }
                    else
                    {
                        i++;
                    }
                }
                gdvEmlpoyee.DataSource = dt;
                gdvEmlpoyee.DataBind();
                sqlcon.Close();
            }
        }
    }
}

Lets Execute our application we will get the following output


0 comments:

Post a Comment