In this blog, we will learn how to find total sum from two
tables and show the output in a Gridview.
Suppose there are two tables named as
t1 and t2 respectively. In table t1 there are three columns as id, date, amount
and in table t2 contains id, name, address. We need to display id, name,
address and total amount from two tables.
The scenario is
T1 table data
id date amount
1 10/05/2000 100
2 10/05/2001 200
3 12/05/2000 300
1 10/05/2000 232
2 14/05/2000 100
T2 table data
id name address
1 Raj Pune
2
Ravi Mumbai
3
Rahul Delhi
I need the output as below
id name address Total
1 Raj Pune 332
2
Ravi Mumbai 300
3
Rahul Delhi 300
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!DOCTYPE html
PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
</div>
<asp:GridView ID="GridView1"
runat="server">
</asp:GridView>
</form>
</body>
</html>
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial
class _Default
: System.Web.UI.Page
{
string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string str = null;
SqlCommand com;
protected void Page_Load(object
sender, EventArgs e)
{
SqlConnection con = new SqlConnection(strConnString);
con.Open();
str = "select t1.id,
t2.name, t2.address, sum(t1.amount)from t2 inner join t1 on t2.id=t1.id group
by t1.id, t2.name, t2.address";
com = new SqlCommand(str, con);
SqlDataReader reader =
com.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
con.Close();
}
}
Thanks for reading.