Data Relation

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hello guys
Please check the attached code
It is simple console based application in C# using
Northwind database.

The result if I uncomment the line

For OrderID 10248
1)Product 11 : Quantity 12
2)Product 42 : Quantity 10
3)Product 72 : Quantity 5
Total Quantity Before Fill : 27
Total Quantity After Fill : 27

If I comment the line
For OrderID 10248
1)Product 11 : Quantity 12
2)Product 42 : Quantity 10
3)Product 72 : Quantity 5
Total Quantity Before Fill : *******
Total Quantity After Fill : 27

I am not able to get the value. I puts *'s just to fill
the blank space.

The code ---

static void Main(string[] args)
{
string myConnString = "Persist Security Info=True;User
ID=sa;pwd=;Initial Catalog=Northwind;Data
Source=127.0.0.1";

SqlDataAdapter daOrder = new SqlDataAdapter("SELECT
OrderID,CustomerID,OrderDate FROM ORDERS",myConnString);
DataTable dtOrders = new DataTable
("Orders");

daOrder.FillSchema(dtOrders,SchemaType.Source);

SqlDataAdapter daOrderDetails = new SqlDataAdapter("SELECT
* FROM [Order Details]",myConnString);
DataTable dtOrderDetails = new DataTable("OrderDetails");
daOrderDetails.FillSchema
(dtOrderDetails,SchemaType.Source);

DataSet dsOrdersData = new DataSet("OrdersData");
dsOrdersData.Tables.Add(dtOrders);
dsOrdersData.Tables.Add(dtOrderDetails);

DataRelation drOrderNOrderDeatils = new DataRelation
("ORDER_N_OrderDetails",dtOrders.Columns
["OrderID"],dtOrderDetails.Columns["OrderID"]);
dsOrdersData.Relations.Add(drOrderNOrderDeatils);

dtOrders.Columns.Add("SumOfQuantityBeforeFill",typeof
(int),"Sum(Child(ORDER_N_OrderDetails).Quantity)");

//Comment, uncomment following line and check the result
dtOrderDetails.Columns.Add("ParentOrderID",typeof
(int),"Parent.OrderID");

daOrder.Fill(dtOrders);
daOrderDetails.Fill(dtOrderDetails);

dtOrders.Columns.Add("SumOfQuantityAfterFill",typeof
(int),"Sum(Child(ORDER_N_OrderDetails).Quantity)");

int i;
foreach(DataRow drOrder in dtOrders.Rows){
Console.WriteLine("For OrderID {0}",drOrder.ItemArray[0]);
i = 1;
foreach(DataRow drOrderDetails in drOrder.GetChildRows
("ORDER_N_OrderDetails")){
Console.WriteLine("{0})Product {1} : Quantity
{2}",i,drOrderDetails[1],drOrderDetails[3]);
i++;
}
Console.WriteLine("Total Quantity Before Fill :
{0} ",drOrder.ItemArray[3]);
Console.WriteLine("Total Quantity After Fill :
{0} ",drOrder.ItemArray[4]);
break;
}
Console.ReadLine();
}
 
Hi Tom,

Welcome to world of expression columns weirdness.
If you add the code
daOrder.AcceptChangesDuringFill = false;

before fill it also works.

I think that the problem is that expression column isn't calculated because
it doesn't detect that data has changed.

Go figure.


--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com



Tom said:
Hello guys
Please check the attached code
It is simple console based application in C# using
Northwind database.

The result if I uncomment the line

For OrderID 10248
1)Product 11 : Quantity 12
2)Product 42 : Quantity 10
3)Product 72 : Quantity 5
Total Quantity Before Fill : 27
Total Quantity After Fill : 27

If I comment the line
For OrderID 10248
1)Product 11 : Quantity 12
2)Product 42 : Quantity 10
3)Product 72 : Quantity 5
Total Quantity Before Fill : *******
Total Quantity After Fill : 27

I am not able to get the value. I puts *'s just to fill
the blank space.

The code ---

static void Main(string[] args)
{
string myConnString = "Persist Security Info=True;User
ID=sa;pwd=;Initial Catalog=Northwind;Data
Source=127.0.0.1";

SqlDataAdapter daOrder = new SqlDataAdapter("SELECT
OrderID,CustomerID,OrderDate FROM ORDERS",myConnString);
DataTable dtOrders = new DataTable
("Orders");

daOrder.FillSchema(dtOrders,SchemaType.Source);

SqlDataAdapter daOrderDetails = new SqlDataAdapter("SELECT
* FROM [Order Details]",myConnString);
DataTable dtOrderDetails = new DataTable("OrderDetails");
daOrderDetails.FillSchema
(dtOrderDetails,SchemaType.Source);

DataSet dsOrdersData = new DataSet("OrdersData");
dsOrdersData.Tables.Add(dtOrders);
dsOrdersData.Tables.Add(dtOrderDetails);

DataRelation drOrderNOrderDeatils = new DataRelation
("ORDER_N_OrderDetails",dtOrders.Columns
["OrderID"],dtOrderDetails.Columns["OrderID"]);
dsOrdersData.Relations.Add(drOrderNOrderDeatils);

dtOrders.Columns.Add("SumOfQuantityBeforeFill",typeof
(int),"Sum(Child(ORDER_N_OrderDetails).Quantity)");

//Comment, uncomment following line and check the result
dtOrderDetails.Columns.Add("ParentOrderID",typeof
(int),"Parent.OrderID");

daOrder.Fill(dtOrders);
daOrderDetails.Fill(dtOrderDetails);

dtOrders.Columns.Add("SumOfQuantityAfterFill",typeof
(int),"Sum(Child(ORDER_N_OrderDetails).Quantity)");

int i;
foreach(DataRow drOrder in dtOrders.Rows){
Console.WriteLine("For OrderID {0}",drOrder.ItemArray[0]);
i = 1;
foreach(DataRow drOrderDetails in drOrder.GetChildRows
("ORDER_N_OrderDetails")){
Console.WriteLine("{0})Product {1} : Quantity
{2}",i,drOrderDetails[1],drOrderDetails[3]);
i++;
}
Console.WriteLine("Total Quantity Before Fill :
{0} ",drOrder.ItemArray[3]);
Console.WriteLine("Total Quantity After Fill :
{0} ",drOrder.ItemArray[4]);
break;
}
Console.ReadLine();
}
Click to expand...
 
-----Original Message-----
Hi Tom,

Welcome to world of expression columns weirdness.
If you add the code NO LUCK AT ALL :(
daOrder.AcceptChangesDuringFill = false;

before fill it also works.

I think that the problem is that expression column isn't calculated because
it doesn't detect that data has changed.
Click to expand...

But why it works when i add expression column in child
table ????

regards

Tom
Go figure.


--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com



Tom said:
Hello guys
Please check the attached code
It is simple console based application in C# using
Northwind database.

The result if I uncomment the line

For OrderID 10248
1)Product 11 : Quantity 12
2)Product 42 : Quantity 10
3)Product 72 : Quantity 5
Total Quantity Before Fill : 27
Total Quantity After Fill : 27

If I comment the line
For OrderID 10248
1)Product 11 : Quantity 12
2)Product 42 : Quantity 10
3)Product 72 : Quantity 5
Total Quantity Before Fill : *******
Total Quantity After Fill : 27

I am not able to get the value. I puts *'s just to fill
the blank space.

The code ---

static void Main(string[] args)
{
string myConnString = "Persist Security Info=True;User
ID=sa;pwd=;Initial Catalog=Northwind;Data
Source=127.0.0.1";

SqlDataAdapter daOrder = new SqlDataAdapter("SELECT
OrderID,CustomerID,OrderDate FROM ORDERS",myConnString);
DataTable dtOrders = new DataTable
("Orders");

daOrder.FillSchema(dtOrders,SchemaType.Source);

SqlDataAdapter daOrderDetails = new SqlDataAdapter ("SELECT
* FROM [Order Details]",myConnString);
DataTable dtOrderDetails = new DataTable ("OrderDetails");
daOrderDetails.FillSchema
(dtOrderDetails,SchemaType.Source);

DataSet dsOrdersData = new DataSet("OrdersData");
dsOrdersData.Tables.Add(dtOrders);
dsOrdersData.Tables.Add(dtOrderDetails);

DataRelation drOrderNOrderDeatils = new DataRelation
("ORDER_N_OrderDetails",dtOrders.Columns
["OrderID"],dtOrderDetails.Columns["OrderID"]);
dsOrdersData.Relations.Add(drOrderNOrderDeatils);

dtOrders.Columns.Add("SumOfQuantityBeforeFill",typeof
(int),"Sum(Child(ORDER_N_OrderDetails).Quantity)");

//Comment, uncomment following line and check the result
dtOrderDetails.Columns.Add("ParentOrderID",typeof
(int),"Parent.OrderID");

daOrder.Fill(dtOrders);
daOrderDetails.Fill(dtOrderDetails);

dtOrders.Columns.Add("SumOfQuantityAfterFill",typeof
(int),"Sum(Child(ORDER_N_OrderDetails).Quantity)");

int i;
foreach(DataRow drOrder in dtOrders.Rows){
Console.WriteLine("For OrderID {0}",drOrder.ItemArray [0]);
i = 1;
foreach(DataRow drOrderDetails in drOrder.GetChildRows
("ORDER_N_OrderDetails")){
Console.WriteLine("{0})Product {1} : Quantity
{2}",i,drOrderDetails[1],drOrderDetails[3]);
i++;
}
Console.WriteLine("Total Quantity Before Fill :
{0} ",drOrder.ItemArray[3]);
Console.WriteLine("Total Quantity After Fill :
{0} ",drOrder.ItemArray[4]);
break;
}
Console.ReadLine();
}
Click to expand...


.
Click to expand...
 
But why it works when i add expression column in child
table ????
Click to expand...

Probably because of your fill order - parent rows already exist when child
ones are filled.
 
Back
Top