This way
HTML:
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtId" runat="server" />
<asp:GridView ID="gvPopulation" runat="server" OnRowDataBound="gvPopulation_OnRowDataBound"
AutoGenerateColumns="false">
<Columns>
<asp:BoundField />
<asp:BoundField DataField="Person" HeaderText="Person" />
<asp:BoundField DataField="Total_male" HeaderText="Male" />
<asp:BoundField DataField="Total_female" HeaderText="Female" />
</Columns>
</asp:GridView>
<asp:Button runat="server" OnClick="GetPopulation" Text="GET" />
</div>
</form>
C#:
protected void GetPopulation(object sender, EventArgs e)
{
this.PopulateGrid(int.Parse(this.txtId.Text.Trim()));
}
protected void gvPopulation_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (e.Row.RowIndex == 0)
{
e.Row.Cells[0].Text = "Total";
}
if (e.Row.RowIndex == 1)
{
e.Row.Cells[0].Text = "Total Child";
}
if (e.Row.RowIndex == 2)
{
e.Row.Cells[0].Text = "Total Old";
}
}
}
private void PopulateGrid(int id)
{
string strcon = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strcon))
{
using (SqlCommand cmd = new SqlCommand("GET_Population", con))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", id);
DataSet ds = new DataSet();
da.Fill(ds);
this.gvPopulation.DataSource = ds;
this.gvPopulation.DataBind();
con.Close();
}
}
}
}
SQL:
-- GET_Population 1
CREATE PROCEDURE GET_Population
@Id INT
AS
BEGIN
SET NOCOUNT ON;
SELECT Total_male + Total_female AS Person, Total_male,Total_female from PopulationTable
WHERE Id = @Id
UNION ALL
SELECT Total_child_male + Total_child_female AS Person, Total_child_male,Total_child_female from PopulationTable
WHERE Id = @Id
UNION ALL
SELECT Total_old_male + Total_old_female AS Person, Total_old_male,Total_old_female from PopulationTable
WHERE Id = @Id
END
GO
here i have calculated based on Id (for perticulare row)
if you want for all the rows then you can call this StoreProcedure
-- GET_Population
ALTER PROCEDURE GET_Population
AS
BEGIN
SELECT SUM(Total_male) + SUM(Total_female) AS Person, SUM(Total_male) AS Total_male ,SUM(Total_female) Total_female FROM PopulationTable
UNION ALL
SELECT SUM(Total_child_male) + SUM(Total_child_female) AS Person, SUM(Total_child_male) Total_male,SUM(Total_child_female) Total_female FROM PopulationTable
UNION ALL
SELECT SUM(Total_old_male) + SUM(Total_old_female) AS Person, SUM(Total_old_male) Total_male,SUM(Total_old_female) Total_female FROM PopulationTable
END
GO
Then code will be
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
PopulateGrid();
}
}
protected void GetPopulation(object sender, EventArgs e)
{
this.PopulateGrid();
}
protected void gvPopulation_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (e.Row.RowIndex == 0)
{
e.Row.Cells[0].Text = "Total";
}
if (e.Row.RowIndex == 1)
{
e.Row.Cells[0].Text = "Total Child";
}
if (e.Row.RowIndex == 2)
{
e.Row.Cells[0].Text = "Total Old";
}
}
}
private void PopulateGrid()
{
string strcon = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strcon))
{
using (SqlCommand cmd = new SqlCommand("GET_Population", con))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
this.gvPopulation.DataSource = ds;
this.gvPopulation.DataBind();
con.Close();
}
}
}
}