Mega Code Archive

 

Categories / C# Tutorial / ADO Net

 

Use a relationship with a calculated column

using System; using System.Data; using System.Data.SqlClient; public class CalculatedColumn {     public static void Main()      {         string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";         string SQL = "SELECT * FROM Categories";         SqlConnection con = new SqlConnection(connectionString);         SqlCommand cmd = new SqlCommand(SQL, con);         SqlDataAdapter adapter = new SqlDataAdapter(cmd);         DataSet ds = new DataSet();         con.Open();         adapter.Fill(ds, "Categories");              cmd.CommandText = "SELECT * FROM Products";         adapter.Fill(ds, "Products");         con.Close();         DataColumn parentCol = ds.Tables["Categories"].Columns["CategoryID"];         DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"];         DataRelation relation = new DataRelation("Cat_Prod", parentCol,childCol);         ds.Relations.Add(relation);         ds.Tables["Categories"].Columns.Add("AveragePrice", typeof(Decimal), "AVG(Child(Cat_Prod).UnitPrice)");         ds.Tables["Categories"].Columns.Add("TotalPrice", typeof(Decimal), "SUM(Child(Cat_Prod).UnitPrice)");         foreach (DataRow row in ds.Tables["Categories"].Rows)         {             Console.WriteLine(row["CategoryName"]);             Console.WriteLine(row["AveragePrice"]);             Console.WriteLine(row["TotalPrice"]);         }     } }