ASP.NET Tutorial/ADO.net Database/DataColumn
Calculated Column
<source lang="csharp">
<%@ Page language="c#" Inherits="CalculatedColumn" CodeFile="Default.aspx.cs" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML>
<HEAD>
<title>CalculatedColumn</title>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:GridView id="GridView1" runat="server"></asp:GridView>
</form>
</body>
</HTML> File: Default.aspx.cs using System; using System.Collections; using System.ruponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient;
public partial class CalculatedColumn : System.Web.UI.Page {
protected void Page_Load(object sender, System.EventArgs e)
{
string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(connectionString);
string sqlCat = "SELECT CategoryID, CategoryName FROM Categories";
string sqlProd = "SELECT ProductName, CategoryID, UnitPrice FROM Products";
SqlDataAdapter da = new SqlDataAdapter(sqlCat, con);
DataSet ds = new DataSet();
try
{
con.Open();
da.Fill(ds, "Categories");
da.SelectCommand.rumandText = sqlProd;
da.Fill(ds, "Products");
}
finally
{
con.Close();
}
DataRelation relat = new DataRelation("CatProds",ds.Tables["Categories"].Columns["CategoryID"],ds.Tables["Products"].Columns["CategoryID"]);
ds.Relations.Add(relat);
DataColumn count = new DataColumn("Products (#)", typeof(int),"COUNT(Child(CatProds).CategoryID)");
DataColumn max = new DataColumn("Most Expensive Product", typeof(decimal),"MAX(Child(CatProds).UnitPrice)");
DataColumn min = new DataColumn("Least Expensive Product", typeof(decimal),"MIN(Child(CatProds).UnitPrice)");
ds.Tables["Categories"].Columns.Add(count);
ds.Tables["Categories"].Columns.Add(max);
ds.Tables["Categories"].Columns.Add(min);
GridView1.DataSource = ds.Tables["Categories"];
GridView1.DataBind();
}
}</source>
