dotnet windows forms databinding: Problem: Master / Detail + DataRelation + DataView -- dotnet windows forms databinding

Re: Problem: Master / Detail + DataRelation + DataView

Arif
9/19/2006 3:53:02 AM

Much thanks Bart, its working fine now.

Another thing:
I create data relation as follows:

Suppliers |---------< Products >----------| Categories

Suppliers and Products tables are attached with DataGrids while Categories
is attached with a ComboBox.

There are two master tables for Products table. I connected
Suppliers-Products using "relSuppliersProducts" and Categories-Products using
"relCategoriesProducts".

Suppliers datagrid is attached with Suppliers datatable while Products
datagrid with "relSuppliersProducts".

When I click on Products dataGrid the idSuppliers field is automatically
populated but idCategories field of Products is null.

If I use "relCategoriesProducts" as a DataMember for Products grid then the
association Suppliers-Products is broken.

Is there any way or I have to manually populate this idCategories field of
Products dataTable manually, maintaining the .

With thanks for your previous help,
Arif.

[quoted text, click to view]

Re: Problem: Master / Detail + DataRelation + DataView

Arif
9/19/2006 6:20:02 AM

Yes Bart, the Product table should be filtered on both : the selected grid
Supplier and selected Category from combobox. Also new products can be
entered against the current Supplier and Category.

Currently the Products table is working only with Suppliers, not with
Categories. I refer the relationship from NWIND.mdb(sample).

I see that ADO.NET is good for one table or simple table relations but for
complex relation like here I may have to use custom sql queries.

Can you please recommend any good resource for ADO.NET to work with complex
table relationships?

Arif.

[quoted text, click to view]

Re: Problem: Master / Detail + DataRelation + DataView

Bart Mermuys
9/19/2006 12:26:40 PM

Hi,

[quoted text, click to view]

Maybe but it's not entirely clear what you want. There are two parents:
CategoryComboBox and SupplierDataGrid ... What should happen to the
ProductDataGrid when the user changes one of the parents ?

Does the product table need to be filtered according to both Category and
Supplier ? Or some other behaviour ?

Greetings

[quoted text, click to view]

Re: Problem: Master / Detail + DataRelation + DataView

Bart Mermuys
9/19/2006 4:39:56 PM

Hi,

[quoted text, click to view]

Well, i see you have two options:

1) load the entire products table and do the filtering yourself using a
DataView:

private DataView categoryDV;
private DataView supplierDV;
private DataView productDV;
private Dataset ds = new Dataset();
// adapters...

private void Form1_Load(object sender, System.EventArgs e)
{
// load all tables
categoryAdapater.Fill(ds, "tbl_category");
supplierAdapter.Fill(ds, "tbl_supplier");
productAdapter.Fill(ds, "tbl_product");

// create dataviews
categoryDV = ds.Tables["tbl_category"].DefaultView;
supplierDV = ds.Tables["tbl_supplier"].DefaultView;
productDV = ds.Tables["tbl_product"].DefaultView;

// bind
catCb.DataSource = categoryDV;
catCb.DisplayMember = "category";
supDg.DataSource = supplierDV;
prodDg.DataSource = productDV;

UpdateProductFilter();

// hook up category position changed (using CurrencyManager
// returned from BindingContext).
// warning: you must always use the same DataSource
// (and DataMember) as the ones you used for binding
BindingContext[categoryDV].PositionChanged+=
new EventHandler(categoryCM_PositionChanged);

// same for supplier
BindingContext[supplierDV].PositionChanged+=
new EventHandler(supplierCM_PositionChanged);

// hook up listchanged to dedect new rows
productDV.ListChanged+=
new ListChangedEventHandler(productDV_ListChanged);
}

private void categoryCM_PositionChanged(object sender, EventArgs e)
{
UpdateProductFilter();
}

private void supplierCM_PositionChanged(object sender, EventArgs e)
{
UpdateProductFilter();
}

private void UpdateProductFilter()
{
int catID = (int) // get current category_id
((DataRowView)BindingContext[categoryDV].Current)["ID"];

int supID = (int) // get current supplier_id
((DataRowView)BindingContext[supplierDV].Current)["ID"];

productDV.RowFilter =
string.Format("(category_id = {0}) AND (supplier_id = {1})",
catID, supID);
}

private void productDV_ListChanged(object sender, ListChangedEventArgs e)
{
int catID = (int)
((DataRowView)BindingContext[categoryDV].Current)["ID"];

int supID = (int)
((DataRowView)BindingContext[supplierDV].Current)["ID"];

if ( (e.ListChangedType == ListChangedType.ItemAdded) &&
(productDV[e.NewIndex].IsNew) )
{
// set the foreign keys
productDV[e.NewIndex]["category_id"] = catID;
productDV[e.NewIndex]["supplier_id"] = supID;
}
}

OR (2) each time category or supplier changes you load only the revelant
rows using a DataAdapter with a parameterized select query. This would
require a DataAdapter which has a select query with a parameter in it ,eg:
"SELECT * FROM tbl_products WHERE category_id = ? AND supplier_id = ?"

If you do this using the designer it will add the right Parameters to the
(Select)Command' Parameter collection also, if you do it from code, you need
to add the parameters youself:
see productDataAdapter.SelectCommand.Parameters.Add(...)

Once you have setup the right DataAdapter, you can then change the code
within UpdateProductFilter:

private void UpdateProductFilter()
{
int catID = (int) // get current category_id
((DataRowView)BindingContext[categoryDV].Current)["ID"];

int supID = (int) // get current supplier_id
((DataRowView)BindingContext[supplierDV].Current)["ID"];

productAdapter.SelectCommand.Parameters("category_id").Value = catID;
productAdapter.SelectCommand.Parameters("supplier_id").Value = supID;

ds.Tables["tbl_product"].Clear();
productAdapter.Fill(ds, "tbl_product");
}

[quoted text, click to view]

Can't think of any now but parts can definitely be found on google, msdn or
codeproject....

HTH,
Greetings

[quoted text, click to view]

Re: Problem: Master / Detail + DataRelation + DataView

Arif
9/19/2006 11:18:01 PM

Much much thanks dear Bart for your detailed help. I got much idea with this.

With thanks again,
Arif.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the dotnet windows forms databinding group.