使用DataRelation產生兩個資料表關聯。
範例:資料庫的aaa、bbb、ccc資料表, 三個資料表產生關聯
(拉三個DataGridView、bindingSource)
參考資料: http://msdn.microsoft.com/zh-tw/library/c12c1kx4.aspx
http://msdn.microsoft.com/zh-tw/library/27ax959w(v=vs.80).aspx
http://www.dotblogs.com.tw/jeff-yeh/archive/2009/01/09/6710.aspx
private void Form1_Load(object sender, System.EventArgs e)
{
dgvMstr.DataSource = bsMstr;
dgvDetl.DataSource = bsDetl;
dgvVal.DataSource = bsVal;
GetData();
}
private void GetData()
{
OleDbConnection cn = new OleDbConnection();
cn.ConnectionString = "資料庫連線";
DataSet ds = new DataSet();
ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
try
{
cn.Open();
string sqlMstr = "SELECT l_date, a_no, type, name FROM aaa ";
OleDbDataAdapter mstrDataAdapter = new OleDbDataAdapter(sqlMstr, cn);
mstrDataAdapter.Fill(ds, "aaa");
string sqlDetl = "SELECT l_date, a_no, result, p_no FROM bbb ";
OleDbDataAdapter detlDataAdapter = new OleDbDataAdapter(sqlDetl, cn);
detlDataAdapter.Fill(ds, "bbb");
string sqlVal = "SELECT l_date, result, p_no FROM ccc ";
OleDbDataAdapter ValDataAdapter = new OleDbDataAdapter(sqlVal, cn);
ValDataAdapter.Fill(ds, "ccc");
cn.Close();
DataRelation relation = new DataRelation("aaaOrders",
ds.Tables["aaa"].Columns["a_no"],
ds.Tables["bbb"].Columns["a_no"]);
ds.Relations.Add(relation);
DataRelation relation2 = new DataRelation("bbbOrders",
ds.Tables["bbb"].Columns["p_no"],
ds.Tables["ccc"].Columns["p_no"]);
ds.Relations.Add(relation2);
bsMstr.DataSource = ds;
bsMstr.DataMember = "aaa";
bsDetl.DataSource = bsMstr;
bsDetl.DataMember = "aaaOrders";
bsVal.DataSource = bsDetl;
bsVal.DataMember = "bbbOrders";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
=====================================================================
DataColumn [] parentColumns= new System.Data.DataColumn[3];
parentColumns[0] = ds.Tables["aaa"].Columns["l_date"];
parentColumns[1] = ds.Tables["aaa"].Columns["a_no"];
parentColumns[2] = ds.Tables["aaa"].Columns["p_no"];
DataColumn[] childColumns = new System.Data.DataColumn[3];
childColumns[0] = ds.Tables["bbb"].Columns["l_date"];
childColumns[1] = ds.Tables["bbb"].Columns["a_no"];
childColumns[2] = ds.Tables["bbb"].Columns["p_no"];
DataRelation relation = new DataRelation("aaaOrders", parentColumns, childColumns);
ds.Relations.Add(relation);
bsMstr.DataSource = ds;
bsMstr.DataMember = "aaa";
bsDetl.DataSource = bsMstr;
bsDetl.DataMember = "aaaOrders";