G
Guest
I am having a problem with advanced databinding. I think it is a problem with
RowFilters. Here is an example program:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace RelationExpressionFilter
{
/// <summary>
/// We need a new Windows Form Application, with a Button and three
dataGridViews which
/// we will wire together by hand in code . I know this is old
fashioned, but I am trying
/// to get to the bottom of some problems with ADO functionality, so
please bear with me.
/// </summary>
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// Create a dataset.
DataSet ds = new DataSet("myds");
DataTable dtContact;
DataTable dtContactInfo;
DataRelation dr;
// Create two tables: a Contact table and a ContactInfo table.
// A contact represents a person. A ContactInfo represents
information
// about the person. We keep a historical list of ContactInfo,
using a sequence
// number.
// The one which is considered to be current is identified by
the Contact's
// ActiveSeq column. While a simpler mechanism of identifying
the current ContactInfo
// could be propsed (e.g. Max(Seq)), that would not meet all of
the requirements.
// It is possible for the active information to, for example,
toggle between two
// ContactInfo rows (e.g., snowbird address entries)
dtContact = new DataTable("Contact");
dtContact.Columns.Add("ContactID",
System.Type.GetType("System.Int32"));
dtContact.Columns.Add("ActiveSeq",
System.Type.GetType("System.Int32"));
dtContact.Columns.Add("MaxSeq",
System.Type.GetType("System.Int32"));
ds.Tables.Add(dtContact);
dtContactInfo = new DataTable("ContactInfo");
dtContactInfo.Columns.Add("ContactID",
System.Type.GetType("System.Int32"));
dtContactInfo.Columns.Add("Seq",
System.Type.GetType("System.Int32"));
dtContactInfo.Columns.Add("LName",
System.Type.GetType("System.String"));
dtContactInfo.Columns.Add("FName",
System.Type.GetType("System.String"));
dtContactInfo.Columns.Add("Address",
System.Type.GetType("System.String"));
ds.Tables.Add(dtContactInfo);
//Add relation for foreign key
dr = new DataRelation("ContactInfo_Contact",
dtContact.Columns["ContactID"],
dtContactInfo.Columns["ContactID"]);
ds.Relations.Add(dr);
// Add some contacts
dtContact.Rows.Add(new object[] { 1, 3, 3 }); // Dale
dtContact.Rows.Add(new object[] { 2, 1, 1 }); // Roy
dtContact.Rows.Add(new object[] { 3, 1, 1 }); // Kimo
// And add some initial ContactInfo for them
dtContactInfo.Rows.Add(new object[] { 1, 1, "Evans", "Dale", "1
Mockingbird Way" });
dtContactInfo.Rows.Add(new object[] { 2, 1, "Rogers", "Roy",
"123 Blue Sky Highway" });
dtContactInfo.Rows.Add(new object[] { 3, 1, "Sabe", "Kimo", "45
Silver Bullet Lane" });
// And add some updated ContactInfo for Dale
dtContactInfo.Rows.Add(new object[] { 1, 2, "Rogers", "Dale",
"123 Blue Sky Highway" });
dtContactInfo.Rows.Add(new object[] { 1, 3, "Evans", "Dale", "45
Silver Bullet Lane" });
// Bind the first DataGridView to the contact table's default
view
DataView contactView = dtContact.DefaultView;
contactView.Sort = "ContactID";
this.dataGridView1.DataSource = contactView;
// Bind the second DataGridView to active ContactInfo using the
default view
// and a RowFilter
DataView contactInfoView = dtContactInfo.DefaultView;
contactInfoView.Sort = "ContactID, Seq";
contactInfoView.RowFilter = "Parent.ActiveSeq = Seq";
this.dataGridView2.DataSource = contactInfoView;
// Changes in grid 2 update the position in grid 1 and vice versa
dataGridView1_CurrentCellChanged(null, null);
// Bind the third DataGrid view to historical ContactInfo info
// ie, don't filter out inactive entries
DataView historicalContactInfoView =
ds.DefaultViewManager.CreateDataView(dtContactInfo);
historicalContactInfoView.Sort = "ContactID, Seq";
historicalContactInfoView.RowFilter = "";
this.dataGridView3.DataSource = historicalContactInfoView;
dataGridView2_CurrentCellChanged(null, null);
// That's the setup - press the button to proceed
}
private void button1_Click(object sender, EventArgs e)
{
// When the button is clicked, we want to make the currently
selected
// historical ContactInfo row (in DataGridView3) be the active
ContactInfo
// row
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dataGridView3.DataSource];
DataRowView drv = (DataRowView)cm.Current;
int contactID = (int)drv["ContactID"];
int seq = (int)drv["Seq"];
cm =
(CurrencyManager)this.BindingContext[dataGridView1.DataSource];
drv = (DataRowView)cm.Current;
drv.BeginEdit();
drv["ActiveSeq"] = seq;
drv.EndEdit();
// The first DataGridView updated, but the second one did not
// The second datagrid should show the active ContactInfo row
for Dale
// Does anyone know why this is happening and how to fix this?
}
private void dataGridView2_CurrentCellChanged(object sender,
EventArgs e)
{
// Find out who is selected
int contactID;
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dataGridView2.DataSource];
DataRowView drv = (DataRowView)cm.Current;
contactID = (int)drv["ContactID"];
// Let's only show history for the currently selected contact
if (dataGridView3.DataSource != null)
{
((DataView)dataGridView3.DataSource).RowFilter = "ContactID
= " + contactID;
}
// This is artificial, but update DataGridView1 if we change
contacts
cm =
(CurrencyManager)this.BindingContext[dataGridView1.DataSource];
int position =
((DataView)dataGridView1.DataSource).Find(contactID);
cm.Position = position;
}
private void dataGridView1_CurrentCellChanged(object sender,
EventArgs e)
{
// Let's update the current record in DataGridView2
if (dataGridView2.DataSource != null)
{
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dataGridView1.DataSource];
if (cm.Position < cm.List.Count)
{
DataRowView drv = (DataRowView)cm.Current;
int contactID = (int)drv["ContactID"];
int seq = (int)drv["ActiveSeq"];
int position =
((DataView)dataGridView2.DataSource).Find(new object[] { contactID, seq });
cm =
(CurrencyManager)this.BindingContext[dataGridView2.DataSource];
cm.Position = position;
}
}
}
}
}
RowFilters. Here is an example program:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace RelationExpressionFilter
{
/// <summary>
/// We need a new Windows Form Application, with a Button and three
dataGridViews which
/// we will wire together by hand in code . I know this is old
fashioned, but I am trying
/// to get to the bottom of some problems with ADO functionality, so
please bear with me.
/// </summary>
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// Create a dataset.
DataSet ds = new DataSet("myds");
DataTable dtContact;
DataTable dtContactInfo;
DataRelation dr;
// Create two tables: a Contact table and a ContactInfo table.
// A contact represents a person. A ContactInfo represents
information
// about the person. We keep a historical list of ContactInfo,
using a sequence
// number.
// The one which is considered to be current is identified by
the Contact's
// ActiveSeq column. While a simpler mechanism of identifying
the current ContactInfo
// could be propsed (e.g. Max(Seq)), that would not meet all of
the requirements.
// It is possible for the active information to, for example,
toggle between two
// ContactInfo rows (e.g., snowbird address entries)
dtContact = new DataTable("Contact");
dtContact.Columns.Add("ContactID",
System.Type.GetType("System.Int32"));
dtContact.Columns.Add("ActiveSeq",
System.Type.GetType("System.Int32"));
dtContact.Columns.Add("MaxSeq",
System.Type.GetType("System.Int32"));
ds.Tables.Add(dtContact);
dtContactInfo = new DataTable("ContactInfo");
dtContactInfo.Columns.Add("ContactID",
System.Type.GetType("System.Int32"));
dtContactInfo.Columns.Add("Seq",
System.Type.GetType("System.Int32"));
dtContactInfo.Columns.Add("LName",
System.Type.GetType("System.String"));
dtContactInfo.Columns.Add("FName",
System.Type.GetType("System.String"));
dtContactInfo.Columns.Add("Address",
System.Type.GetType("System.String"));
ds.Tables.Add(dtContactInfo);
//Add relation for foreign key
dr = new DataRelation("ContactInfo_Contact",
dtContact.Columns["ContactID"],
dtContactInfo.Columns["ContactID"]);
ds.Relations.Add(dr);
// Add some contacts
dtContact.Rows.Add(new object[] { 1, 3, 3 }); // Dale
dtContact.Rows.Add(new object[] { 2, 1, 1 }); // Roy
dtContact.Rows.Add(new object[] { 3, 1, 1 }); // Kimo
// And add some initial ContactInfo for them
dtContactInfo.Rows.Add(new object[] { 1, 1, "Evans", "Dale", "1
Mockingbird Way" });
dtContactInfo.Rows.Add(new object[] { 2, 1, "Rogers", "Roy",
"123 Blue Sky Highway" });
dtContactInfo.Rows.Add(new object[] { 3, 1, "Sabe", "Kimo", "45
Silver Bullet Lane" });
// And add some updated ContactInfo for Dale
dtContactInfo.Rows.Add(new object[] { 1, 2, "Rogers", "Dale",
"123 Blue Sky Highway" });
dtContactInfo.Rows.Add(new object[] { 1, 3, "Evans", "Dale", "45
Silver Bullet Lane" });
// Bind the first DataGridView to the contact table's default
view
DataView contactView = dtContact.DefaultView;
contactView.Sort = "ContactID";
this.dataGridView1.DataSource = contactView;
// Bind the second DataGridView to active ContactInfo using the
default view
// and a RowFilter
DataView contactInfoView = dtContactInfo.DefaultView;
contactInfoView.Sort = "ContactID, Seq";
contactInfoView.RowFilter = "Parent.ActiveSeq = Seq";
this.dataGridView2.DataSource = contactInfoView;
// Changes in grid 2 update the position in grid 1 and vice versa
dataGridView1_CurrentCellChanged(null, null);
// Bind the third DataGrid view to historical ContactInfo info
// ie, don't filter out inactive entries
DataView historicalContactInfoView =
ds.DefaultViewManager.CreateDataView(dtContactInfo);
historicalContactInfoView.Sort = "ContactID, Seq";
historicalContactInfoView.RowFilter = "";
this.dataGridView3.DataSource = historicalContactInfoView;
dataGridView2_CurrentCellChanged(null, null);
// That's the setup - press the button to proceed
}
private void button1_Click(object sender, EventArgs e)
{
// When the button is clicked, we want to make the currently
selected
// historical ContactInfo row (in DataGridView3) be the active
ContactInfo
// row
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dataGridView3.DataSource];
DataRowView drv = (DataRowView)cm.Current;
int contactID = (int)drv["ContactID"];
int seq = (int)drv["Seq"];
cm =
(CurrencyManager)this.BindingContext[dataGridView1.DataSource];
drv = (DataRowView)cm.Current;
drv.BeginEdit();
drv["ActiveSeq"] = seq;
drv.EndEdit();
// The first DataGridView updated, but the second one did not
// The second datagrid should show the active ContactInfo row
for Dale
// Does anyone know why this is happening and how to fix this?
}
private void dataGridView2_CurrentCellChanged(object sender,
EventArgs e)
{
// Find out who is selected
int contactID;
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dataGridView2.DataSource];
DataRowView drv = (DataRowView)cm.Current;
contactID = (int)drv["ContactID"];
// Let's only show history for the currently selected contact
if (dataGridView3.DataSource != null)
{
((DataView)dataGridView3.DataSource).RowFilter = "ContactID
= " + contactID;
}
// This is artificial, but update DataGridView1 if we change
contacts
cm =
(CurrencyManager)this.BindingContext[dataGridView1.DataSource];
int position =
((DataView)dataGridView1.DataSource).Find(contactID);
cm.Position = position;
}
private void dataGridView1_CurrentCellChanged(object sender,
EventArgs e)
{
// Let's update the current record in DataGridView2
if (dataGridView2.DataSource != null)
{
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dataGridView1.DataSource];
if (cm.Position < cm.List.Count)
{
DataRowView drv = (DataRowView)cm.Current;
int contactID = (int)drv["ContactID"];
int seq = (int)drv["ActiveSeq"];
int position =
((DataView)dataGridView2.DataSource).Find(new object[] { contactID, seq });
cm =
(CurrencyManager)this.BindingContext[dataGridView2.DataSource];
cm.Position = position;
}
}
}
}
}