Master-detail datagridview：如何在使用SelectCommand参数过滤master时过滤细节 [英] Master-detail datagridview: how to filter details when master is filtered by SelectCommand parameter
本文介绍了Master-detail datagridview：如何在使用SelectCommand参数过滤master时过滤细节的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在我的表单上，我有一个文本框，2个datagridviews（主 - 详细信息）和一个搜索按钮
主数据的SelectCommand DataAdapter有一个参数来限制检索到的行。
从master_table中选择*其中x_column = some_value
目前，我的SelectCommand for Detail DataAdapter是：
select * from detail_table
当我试图在主人和主人的主要基础上创建一个DataRelation时，我有一个例外：
无法启用此约束，因为并非所有值都具有相应的父值。
i认为这是因为主行由参数过滤，而细节行不是...
我该怎么办？
public partial class Form1：Form
{
private String connectionString = null ;
private OracleConnection oracleConnection = null ;
private 字符串 equQuery = 空;
OracleCommand equCmd = null ;
private OracleDataAdapter equDataAdapter = null ;
private OracleCommandBuilder equComBldr = null ;
private DataTable equDataTable = null ;
private BindingSource equBindingSource = null ;
private 字符串 partQuery = 空;
OracleCommand partCmd = null ;
private OracleDataAdapter partDataAdapter = null ;
private OracleCommandBuilder partComBldr = null ;
private DataTable partDataTable = null ;
private BindingSource partBindingSource = null ;
private DataSet dataset = null ;
private void Form1_Load（ object sender，EventArgs e）
{
oracleConnection = new OracleConnection（的connectionString）;
oracleConnection.Open（）;
// MASTER TABLE的SELECT命令
equQuery = 从bso_equipment_maint_bill_equ中选择bill_equ_id，bill_id，equ_serial_nbr，equ_cost，maintenance_date，其中bill_id =：bill_id ;
equCmd = new OracleCommand（equQuery，oracleConnection）;
equCmd.Parameters.Add（ bill_id，OracleType.Number）;
/ * 初始值，使主网格检索无负载数据* /
equCmd.Parameters [ bill_id]。值= -1;
// SELECT COMMAND FOR DETAIL TABLE ... !!! ??? ????
/ * 因为命令没有参数，所以网格正在填充ALL加载详细记录。我不知道如何只获取与检索到的主行相关的行* /
partQuery = 选择bill_equ_id，part_nbr，part_cost，来自bso_equipment_maint_bill_part的数量;
partCmd = new OracleCommand（partQuery，oracleConnection）;
// 创建适配器
equDataAdapter = new OracleDataAdapter（equCmd）;
equComBldr = new OracleCommandBuilder（equDataAdapter）;
partDataAdapter = new OracleDataAdapter（partCmd）;
partComBldr = new OracleCommandBuilder（partDataAdapter）;
// 创建和填写数据表
equDataTable = new DataTable（）;
equDataTable.TableName = bso_equipment_maint_bill_equ;
partDataTable = new DataTable（）;
partDataTable.TableName = bso_equipment_maint_bill_part;
equDataAdapter.Fill（equDataTable）;
partDataAdapter.Fill（partDataTable）;
/ * bill_equ_id是详细信息表中的外键。它是主表中的主键。它是在数据库上通过（插入前触发器）生成的* /
partDataTable.Columns [ bill_equ_id]。AutoIncrement = true ;
partDataTable.Columns [ bill_equ_id]。AutoIncrementSeed = -1;
partDataTable.Columns [ bill_equ_id]。AutoIncrementStep = -1;
// 创建数据集和关系
dataset = new DataSet（）;
dataset.Tables.Add（equDataTable）;
dataset.Tables.Add（partDataTable）;
/ * 此行引发异常：此约束无法启用为并非所有值都具有相应的父值* /
DataRelation relation = new DataRelation（ EquPartRel，dataset.Tables [ bso_equipment_maint_bill_equ]。列[ bill_equ_id]，dataset.Tables [< span class =code-string> bso_equipment_maint_bill_part]。列[ < span class =code-string> bill_equ_id]）;
dataset.Relations.Add（relation）;
// 创建绑定来源并与DATAGRIDVIEWS绑定
equBindingSource = new BindingSource（）;
equBindingSource.DataSource = dataset;
equBindingSource.DataMember = bso_equipment_maint_bill_equ;
equGrid.DataSource = equBindingSource;
equGrid.Columns [ bill_id]。可见= 假;
equGrid.Columns [ bill_equ_id]。可见= 假;
partBindingSource = new BindingSource（）;
partBindingSource.DataSource = dataset;
partBindingSource.DataMember = bso_equipment_maint_bill_part;
partGrid.DataSource = partBindingSource;
partGrid.Columns [ bill_equ_id]。可见= 假;
oracleConnection.Close（）;
} // 表单加载结束
private void searchEquButton_Click（ object sender，EventArgs e）
{
equDataAdapter.SelectCommand.Parameters [ < span class =code-string> bill_id]。Value = bill_id;
equDataAdapter.Fill（equDataTable）;
partDataAdapter.Fill（partDataTable）;
} // 结束搜索
} // class
当前在master中的数据表：
bill_equ_id bill_id equ_serial_nbr equ_cost maintenance_date
1 1 1 1000 31/08/2014
8 3 a-400 5000 30/08/2014
数据目前详情表：
bill_equ_id part_nbr part_cost数量
1 a-209 500 1
1 a-210 800 2
8 a-211 2500 2
解决方案
我不确定，但我认为问题可能是详细信息表中的复合主键。
我尝试了这段代码，它运行正常。
我使用了你在问题中提供的数据。
DataTable dtMaster = new DataTable（ bso_equipment_maint_bill_equ）;
// 我必须手动填写表格
DataColumn dcMasterPK = dtMaster.Columns.Add（ bill_equ_id， typeof （ int ））;
dtMaster.PrimaryKey = new DataColumn [] {dcMasterPK};
dtMaster.Columns.Add（ bill_id， typeof （ int ））;
dtMaster.Columns.Add（ equ_serial_nbr， typeof （ string ））;
dtMaster.Columns.Add（ equ_cost， typeof （ decimal ））;
dtMaster.Columns.Add（ maintenance_date， typeof运算（DateTime的））;
dtMaster.Rows.Add（ 1 ， 1 ， 1， 1000 ，DateTime.ParseExact（ 31/08/2014， < span class =code-string> dd / MM / yyyy， null ））;
dtMaster.Rows.Add（ 8 ， 3 ， a-400， 5000 ，DateTime.ParseExact（ 30/08/2014， dd / MM / yyyy， null ））;
DataTable dtDetail = new DataTable（ bso_equipment_maint_bill_part）;
dtDetail.Columns.Add（ bill_equ_id， typeof （ int ））;
dtDetail.Columns.Add（ part_nbr， typeof （ string ））;
dtDetail.Columns.Add（ part_cost， typeof （ decimal ））;
dtDetail.Columns.Add（ quantity， typeof （ string ））;
dtDetail.Rows.Add（ 1 ， a-209， 500 ， 1 ）;
dtDetail.Rows.Add（ 1 ， a-210， 800 ， 2 ）;
dtDetail.Rows.Add（ 8 ， a-211， 2500 ， 2 ）;
DataSet ds = new DataSet（）;
ds.Tables.Add（dtMaster）;
ds.Tables.Add（dtDetail）;
DataRelation relation = new DataRelation（ EquPartRel，ds.Tables [ bso_equipment_maint_bill_equ]。列[< span class =code-string> bill_equ_id]，ds.Tables [ bso_equipment_maint_bill_part]。列[ bill_equ_id]）;
ds.Relations.Add（relation）;
BindingSource bsMaster = new BindingSource（ds， bso_equipment_maint_bill_equ）;
BindingSource bsDetail = new BindingSource（bsMaster， EquPartRel）;
dgMaster.DataSource = bsMaster;
dgDetail.DataSource = bsDetail;
也许你应该在创建关系之前尝试打印出两个表的内容。
On my form i have a textbox, 2 datagridviews (master-detail), and a search button
SelectCommand for Master DataAdapter has a parameter to limit retrieved rows.
select * from master_table where x_column = some_value
currently, my SelectCommand for Detail DataAdapter is :
select * from detail_table
when i tried to create a DataRelation between master and detail based on master's primary
key, i got an exception :
this constraint cannot be enabled as not all values have corresponding parent values.
i think it's because master rows are filtered by a parameter, while detail rows are not...
what should i do?
public partial class Form1: Form
{
private String connectionString = null;
private OracleConnection oracleConnection = null;
private String equQuery = null;
OracleCommand equCmd = null;
private OracleDataAdapter equDataAdapter = null;
private OracleCommandBuilder equComBldr = null;
private DataTable equDataTable = null;
private BindingSource equBindingSource = null;
private String partQuery = null;
OracleCommand partCmd = null;
private OracleDataAdapter partDataAdapter = null;
private OracleCommandBuilder partComBldr = null;
private DataTable partDataTable = null;
private BindingSource partBindingSource = null;
private DataSet dataset = null;
private void Form1_Load(object sender, EventArgs e)
{
oracleConnection = new OracleConnection(connectionString);
oracleConnection.Open();
// SELECT COMMAND FOR MASTER TABLE
equQuery = "select bill_equ_id ,bill_id ,equ_serial_nbr ,equ_cost ,maintenance_date from bso_equipment_maint_bill_equ where bill_id = :bill_id";
equCmd = new OracleCommand(equQuery, oracleConnection);
equCmd.Parameters.Add("bill_id", OracleType.Number);
/* initial value to make master grid retrieve no data on load */
equCmd.Parameters["bill_id"].Value = -1;
// SELECT COMMAND FOR DETAIL TABLE… !!!???????
/* since command has not parameters, grid is being filled with ALL detail records on load. i don't know how to get only rows related to retrieved master rows */
partQuery = "select bill_equ_id , part_nbr , part_cost , quantity from bso_equipment_maint_bill_part ";
partCmd = new OracleCommand(partQuery, oracleConnection);
// CREATE ADAPTERS
equDataAdapter = new OracleDataAdapter(equCmd);
equComBldr = new OracleCommandBuilder(equDataAdapter);
partDataAdapter = new OracleDataAdapter(partCmd);
partComBldr = new OracleCommandBuilder(partDataAdapter);
// CREATE AND FILL DATA TABLES
equDataTable = new DataTable();
equDataTable.TableName = "bso_equipment_maint_bill_equ";
partDataTable = new DataTable();
partDataTable.TableName = "bso_equipment_maint_bill_part";
equDataAdapter.Fill(equDataTable);
partDataAdapter.Fill(partDataTable);
/* bill_equ_id is a foreign key in details table. It is the primary key in master table. It’s generated on database by a (before-insert trigger) */
partDataTable.Columns["bill_equ_id"].AutoIncrement = true;
partDataTable.Columns["bill_equ_id"].AutoIncrementSeed = -1;
partDataTable.Columns["bill_equ_id"].AutoIncrementStep = -1;
// CREATE DATASET AND RELATION
dataset = new DataSet();
dataset.Tables.Add(equDataTable);
dataset.Tables.Add(partDataTable);
/* this line throws an exception : this constraint cannot be enabled as not all values have corresponding parent values */
DataRelation relation = new DataRelation("EquPartRel", dataset.Tables["bso_equipment_maint_bill_equ"].Columns["bill_equ_id"], dataset.Tables["bso_equipment_maint_bill_part"].Columns["bill_equ_id"]);
dataset.Relations.Add(relation);
// CREATE BINDING SOURCES AND BIND TO DATAGRIDVIEWS
equBindingSource = new BindingSource();
equBindingSource.DataSource = dataset;
equBindingSource.DataMember = "bso_equipment_maint_bill_equ";
equGrid.DataSource = equBindingSource;
equGrid.Columns["bill_id"].Visible = false;
equGrid.Columns["bill_equ_id"].Visible = false;
partBindingSource = new BindingSource();
partBindingSource.DataSource = dataset;
partBindingSource.DataMember = "bso_equipment_maint_bill_part";
partGrid.DataSource = partBindingSource;
partGrid.Columns["bill_equ_id"].Visible = false;
oracleConnection.Close();
} // end of form load
private void searchEquButton_Click(object sender, EventArgs e)
{
equDataAdapter.SelectCommand.Parameters["bill_id"].Value = bill_id;
equDataAdapter.Fill(equDataTable);
partDataAdapter.Fill(partDataTable);
} // end search
} // class
data currently in master table :
bill_equ_id bill_id equ_serial_nbr equ_cost maintenance_date 1 1 1 1000 31/08/2014 8 3 a-400 5000 30/08/2014
data currently in detail table :
bill_equ_id part_nbr part_cost quantity
1 a-209 500 1
1 a-210 800 2
8 a-211 2500 2 解决方案
I am not sure but I think the problem might be your composite primary key in the detail table.
I tried this code and it works fine.
I used the data you provided in the question.
DataTable dtMaster = new DataTable("bso_equipment_maint_bill_equ"); // I have to fill the tables manually DataColumn dcMasterPK = dtMaster.Columns.Add("bill_equ_id", typeof(int)); dtMaster.PrimaryKey = new DataColumn[] { dcMasterPK }; dtMaster.Columns.Add("bill_id", typeof(int)); dtMaster.Columns.Add("equ_serial_nbr", typeof(string)); dtMaster.Columns.Add("equ_cost", typeof(decimal)); dtMaster.Columns.Add("maintenance_date", typeof(DateTime)); dtMaster.Rows.Add(1, 1, "1", 1000, DateTime.ParseExact("31/08/2014", "dd/MM/yyyy", null)); dtMaster.Rows.Add(8, 3, "a-400", 5000, DateTime.ParseExact("30/08/2014", "dd/MM/yyyy", null)); DataTable dtDetail = new DataTable("bso_equipment_maint_bill_part"); dtDetail.Columns.Add("bill_equ_id", typeof(int)); dtDetail.Columns.Add("part_nbr", typeof(string)); dtDetail.Columns.Add("part_cost", typeof(decimal)); dtDetail.Columns.Add("quantity", typeof(string)); dtDetail.Rows.Add(1, "a-209", 500, 1); dtDetail.Rows.Add(1, "a-210", 800, 2); dtDetail.Rows.Add(8, "a-211", 2500, 2); DataSet ds = new DataSet(); ds.Tables.Add(dtMaster); ds.Tables.Add(dtDetail); DataRelation relation = new DataRelation("EquPartRel", ds.Tables["bso_equipment_maint_bill_equ"].Columns["bill_equ_id"], ds.Tables["bso_equipment_maint_bill_part"].Columns["bill_equ_id"]); ds.Relations.Add(relation); BindingSource bsMaster = new BindingSource(ds, "bso_equipment_maint_bill_equ"); BindingSource bsDetail = new BindingSource(bsMaster, "EquPartRel"); dgMaster.DataSource = bsMaster; dgDetail.DataSource = bsDetail;
Maybe you should try to printout the contents of both tables before you create the relation.
这篇关于Master-detail datagridview：如何在使用SelectCommand参数过滤master时过滤细节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
