Monday 9.26.11

using System;
using System.Data;
using System.Data.SqlClient;

namespace AddParentColumnDataTable
{
class Program
{
static void Main()
{
string sqlConnectString = @"Data Source=Alfred-PC\SQLExpress;" +
"Integrated Security=SSPI;Initial Catalog=AdventureWorks;";

string sqlSelect = @"SELECT * FROM Sales.SalesOrderHeader; SELECT * FROM Sales.SalesOrderDetail";

DataSet ds = new DataSet();

//Fill the dataSet
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
da.TableMappings.Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
da.Fill(ds);

//relate the header and order tables in the dataset
DataRelation dr = new DataRelation("SalesOrderHeader_SalesORderDetail",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
ds.Relations.Add(dr);

//add the customer id column from SalesOrderHeader to the SalesOrderDetail table
ds.Tables["SalesOrderDetail"].Columns.Add("CustomerID", typeof(int), "Parent(SalesOrderHeader_SalesOrderDetail).CustomerID");

//output fields from first three header rows with detail
for (int i = 0; i < 3; i++)
{
DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
Console.WriteLine("HEADER: OrderID = {0},CustomerID = {1}",
rowHeader["SalesOrderID"], rowHeader["CustomerID"]);

foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
{
Console.WriteLine("\tDETAIL: OrderID = {0}, DetailID={1}," +
"CustomerID = {2}",
rowDetail["SalesOrderID"], rowDetail["SalesOrderDetailID"],
rowDetail["CustomerID"]);
}
}
Console.WriteLine("\nPress any key to continue");
Console.ReadKey();
}
}
}

---

using System;
using System.Data;
using System.Data.SqlClient;

namespace AddAggregateChildColumnDataTable
{
class Program
{
static void Main()
{
string sqlConnectString = @"Data Source=Alfred-PC\SQLExpress;" +
"Integrated security=SSPI; Initial Catalog=AdventureWorks;";

string sqlSelect = @"SELECT * FROM Sales.SalesOrderHeader; SELECT * FROM Sales.SalesOrderDetail;";

DataSet ds = new DataSet();

//fill the data set
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
da.TableMappings.Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
da.Fill(ds);

//relate the header and order tables in teh dataset
DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
ds.Relations.Add(dr);

//add a column to the SalesOrderHeader table summing all LineTotal valeus in SalesOrderDetail
ds.Tables["SalesOrderHeader"].Columns.Add("SumDetailLineTotal", typeof(decimal), "SUM(Child.LineTotal)");

//output fields from the first three header rows with detail
for (int i = 0; i < 3; i++)
{
DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
Console.WriteLine("HEADER: OrderID = {0}, CustomerID = {1}, " +
"SumDetailLineTotal = {2}", rowHeader["SalesOrderID"], rowHeader["CustomerID"],
rowHeader["SumDetailLineTotal"]);

foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
{
Console.WriteLine("\tDetail: OrderID = {0}, DetailID = {1}, " +
"LineTotal = {2}", rowDetail["SalesOrderID"], rowDetail["SalesOrderDetailID"], rowDetail["LineTotal"]);
}
}
}
}
}

---

public class Customer
{

public int CustomerID { get; set; }
public string Name { get; set; }
public string City { get; set; }


public Customer()
{}

public DataSet GetCustomers()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable("Customers");
dt.Columns.Add("CustomerId", typeof(System.Int32));
dt.Columns.Add("CustomerName", typeof(System.String));
dt.Columns.Add("CustomerCity", typeof(System.String));

dt.Rows.Add(new object[] { 1, "Test Customer", "Glasgow" });

ds.Tables.Add(dt);
return ds;
}
}