主细节datagridview：如果主键是自动生成的，如何插入主记录及其详细信息？ [英] Master-detail datagridview: how to insert master record and its details if primary key is auto-generated?
本文介绍了主细节datagridview：如果主键是自动生成的，如何插入主记录及其详细信息？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在我的WinForms应用程序中。我有两个DataGridViews（主 - 细节）。
我的主表的主键（bill_equ_id）是通过（插入前触发器）在服务器上生成的，因此用户不会手动插入。这个键是我的详细信息表中的外键。
i如果用户同时插入主记录和它的详细记录，则不知道如何执行插入操作，因为只有主记录已插入数据库才能获得外键详细信息。
任何人都可以帮我处理InsertCommand查询只需一次保存按钮点击即可插入主设备和细节？
这是我创建网格的代码...
< br $>
In my WinForms App. I have two DataGridViews (master-detail).
The primary key (bill_equ_id) of my master table is generated on the server by a (before-insert trigger), so it's not manually inserted by the user. this key is a foreign key in my detail table.
i don't know how to perform insert operation if user is inserting both a master record and it's details records together, because foreign key for details is not available until master record is already inserted into DB.
can anyone help me with InsertCommand query that can handle insert into both master and detail on a single save-button click ?
here is my code for creating Grids...
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("my connection string bla bla");
oracleConnection.Open();
// MASTER SELECT CMD
equQuery = "select bill_equ_id ,bill_id ,equ_serial_nbr ,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);
// DETAIL SELECT CMD
partQuery = "select bill_equ_id , part_nbr , part_cost , quantity " +
" from bso_equipment_maint_bill_part where bill_equ_id in " +
" (select bill_equ_id from bso_equipment_maint_bill_equ where bill_id = :bill_id) ";
partCmd = new OracleCommand(partQuery, oracleConnection);
partCmd.Parameters.Add("bill_id", OracleType.Number);
// CREATE ADAPTERS
equDataAdapter = new OracleDataAdapter(equCmd);
equComBldr = new OracleCommandBuilder(equDataAdapter);
partDataAdapter = new OracleDataAdapter(partCmd);
partComBldr = new OracleCommandBuilder(partDataAdapter);
// HERE I NEED TO PROVIDE INSERTCOMMAND'S ************************
equCmd = new OracleCommand(" insert into bso_equipment_maint_bill_equ (bill_id ,equ_serial_nbr ,maintenance_date) " +
"values (:bill_id , :equ_serial_nbr , to_date( :maintenance_date) )returning bill_equ_id into :temp_bill_equ_id " ) ;
equCmd.Parameters.Add("temp_bill_equ_id", OracleType.Number).Direction = ParameterDirection.ReturnValue;
equCmd.Parameters.Add("bill_id", OracleType.Number);
equCmd.Parameters.Add("equ_serial_nbr", OracleType.VarChar, 64, "equ_serial_nbr");
equCmd.Parameters.Add("maintenance_date", OracleType.VarChar, 10, "maintenance_date");
equDataAdapter.InsertCommand = equCmd;
partCmd = new OracleCommand("insert into bso_equipment_maint_bill_part (bill_equ_id , part_nbr , part_cost , quantity) " +
"values (:bill_equ_id , :part_nbr , :part_cost , :quantity ) ");
partCmd.Parameters.Add("bill_equ_id", OracleType.Number);
partCmd.Parameters.Add("part_nbr", OracleType.VarChar, 64, "part_nbr");
partCmd.Parameters.Add("part_cost", OracleType.Number);
partCmd.Parameters["part_cost"].SourceColumn = "part_cost";
partCmd.Parameters.Add("quantity", OracleType.Number, 10, "quantity");
partDataAdapter.InsertCommand = partCmd;
// CREATE TABLES
equDataTable = new DataTable("bso_equipment_maint_bill_equ");
partDataTable = new DataTable("bso_equipment_maint_bill_part");
equDataTable.Columns.Add("bill_equ_id", typeof(int));
equDataTable.PrimaryKey = new DataColumn[] { equDataTable.Columns["bill_equ_id"] };
equDataTable.Columns.Add("bill_id", typeof(int));
equDataTable.Columns.Add("equ_serial_nbr", typeof(string));
equDataTable.Columns.Add("maintenance_date", typeof(DateTime));
partDataTable.Columns.Add("bill_equ_id", typeof(int));
partDataTable.Columns.Add("part_nbr", typeof(string));
partDataTable.Columns.Add("part_cost", typeof(decimal));
partDataTable.Columns.Add("quantity", typeof(string));
// CREATE DATASET AND RELATION
dataset = new DataSet();
dataset.Tables.Add(equDataTable);
dataset.Tables.Add(partDataTable);
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"] , true);
dataset.Relations.Add(relation);
// CREATE BINDING SOURCES AND BIND TO DATAGRIDS
equBindingSource = new BindingSource(dataset, "bso_equipment_maint_bill_equ");
equGrid.DataSource = equBindingSource;
//equGrid.Columns["bill_id"].Visible = false;
//equGrid.Columns["bill_equ_id"].Visible = false;
partBindingSource = new BindingSource(equBindingSource, "EquPartRel");
partGrid.DataSource = partBindingSource;
//partGrid.Columns["bill_equ_id"].Visible = false;
oracleConnection.Close();
} // FORM LOAD
private void searchBillButton_Click(object sender, EventArgs e)
{
equDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32( billID.Text );
partDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32(billID.Text);
equDataAdapter.Fill(equDataTable);
partDataAdapter.Fill(partDataTable);
}
private void saveBillButton_Click(object sender, EventArgs e)
{
equDataAdapter.InsertCommand.Parameters["bill_id"].Value = Int32.Parse( billIDText.Text);
equDataAdapter.Update(equDataTable);
int bill_equ_id = Convert.ToInt32( equDataAdapter.InsertCommand.Parameters["temp_bill_equ_id"].Value.ToString());
partDataAdapter.InsertCommand.Parameters["bill_equ_id"].Value = bill_equ_id;
partDataAdapter.Update(partDataTable);
partDataTable.Clear();
equDataTable.Clear();
equDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_equ"]);
partDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_part"]);
}
} // class
推荐答案
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)
{
connectionString = "Data Source=bla;Persist Security Info=True;User ID=bla;Password=bla;Unicode=True";
oracleConnection = new OracleConnection(connectionString);
oracleConnection.Open();
// SELECT QUERY
equQuery = "select bill_id , equ_serial_nbr ,equ_cost ,maintenance_date from bso_equipment_maint_bill_equ where bill_id = :bill_id";
partQuery = "select bill_id , equ_serial_nbr , part_nbr , part_cost , quantity from bso_equipment_maint_bill_part where bill_id = :bill_id and equ_serial_nbr in " +
" (select equ_serial_nbr from bso_equipment_maint_bill_equ where bill_id = :bill_id) ";
// SELECT COMMAND WITH PARAMS
equCmd = new OracleCommand(equQuery, oracleConnection);
equCmd.Parameters.Add("bill_id", OracleType.Number);
partCmd = new OracleCommand(partQuery, oracleConnection);
partCmd.Parameters.Add("bill_id", OracleType.Number);
// CREATE ADAPTERS
equDataAdapter = new OracleDataAdapter(equCmd);
equComBldr = new OracleCommandBuilder(equDataAdapter);
partDataAdapter = new OracleDataAdapter(partCmd);
partComBldr = new OracleCommandBuilder(partDataAdapter);
// INSERT COMMANDS
equCmd = new OracleCommand(" insert into bso_equipment_maint_bill_equ (bill_id ,equ_serial_nbr ,equ_cost ,maintenance_date) " +
"values (:bill_id , :equ_serial_nbr , :equ_cost , to_date( :maintenance_date) )");
equCmd.Parameters.Add("bill_id", OracleType.Number);
equCmd.Parameters["bill_id"].SourceColumn = "bill_id";
equCmd.Parameters.Add("equ_serial_nbr", OracleType.VarChar, 64, "equ_serial_nbr");
equCmd.Parameters.Add("equ_cost", OracleType.Number);
equCmd.Parameters["equ_cost"].SourceColumn = "equ_cost";
equCmd.Parameters.Add("maintenance_date", OracleType.VarChar, 10, "maintenance_date");
equDataAdapter.InsertCommand = equCmd;
partCmd = new OracleCommand("insert into bso_equipment_maint_bill_part (bill_id , equ_serial_nbr , part_nbr , part_cost , quantity) " +
"values (:bill_id , :equ_serial_nbr , :part_nbr , :part_cost , :quantity ) ");
partCmd.Parameters.Add("bill_id", OracleType.Number);
partCmd.Parameters["bill_id"].SourceColumn = "bill_id";
partCmd.Parameters.Add("equ_serial_nbr", OracleType.VarChar, 64, "equ_serial_nbr");
partCmd.Parameters.Add("part_nbr", OracleType.VarChar, 64, "part_nbr");
partCmd.Parameters.Add("part_cost", OracleType.Number);
partCmd.Parameters["part_cost"].SourceColumn = "part_cost";
partCmd.Parameters.Add("quantity", OracleType.Number, 10, "quantity");
partDataAdapter.InsertCommand = partCmd;
// CREATE TABLES
equDataTable = new DataTable("bso_equipment_maint_bill_equ");
partDataTable = new DataTable("bso_equipment_maint_bill_part");
equDataTable.Columns.Add("bill_id", typeof(int));
equDataTable.Columns.Add("equ_serial_nbr", typeof(string));
equDataTable.PrimaryKey = new DataColumn[] { equDataTable.Columns["bill_id"], equDataTable.Columns["equ_serial_nbr"] };
equDataTable.Columns.Add("equ_cost", typeof(decimal));
equDataTable.Columns.Add("maintenance_date", typeof(DateTime));
partDataTable.Columns.Add("bill_id", typeof(int));
partDataTable.Columns.Add("equ_serial_nbr", typeof(string));
partDataTable.Columns.Add("part_nbr", typeof(string));
partDataTable.PrimaryKey = new DataColumn[] { partDataTable.Columns["bill_id"], partDataTable.Columns["equ_serial_nbr"], partDataTable.Columns["part_nbr"] };
partDataTable.Columns.Add("part_cost", typeof(decimal));
partDataTable.Columns.Add("quantity", typeof(int));
// CREATE DATASET AND RELATION
dataset = new DataSet();
dataset.Tables.Add(equDataTable);
dataset.Tables.Add(partDataTable);
DataRelation relation = new DataRelation("EquPartRel", new DataColumn[] { equDataTable.Columns["bill_id"], dataset.Tables["bso_equipment_maint_bill_equ"].Columns["equ_serial_nbr"] },
new DataColumn[] { partDataTable.Columns["bill_id"], dataset.Tables["bso_equipment_maint_bill_part"].Columns["equ_serial_nbr"] }, true);
dataset.Relations.Add(relation);
// CREATE BINDING SOURCES AND BIND TO DATAGRIDS
equBindingSource = new BindingSource(dataset, "bso_equipment_maint_bill_equ");
equGrid.DataSource = equBindingSource;
equGrid.Width = 340;
equGrid.Columns["bill_id"].Visible = false;
partBindingSource = new BindingSource(equBindingSource, "EquPartRel");
partGrid.DataSource = partBindingSource;
partGrid.Width = 315;
partGrid.Columns["bill_id"].Visible = false;
partGrid.Columns["equ_serial_nbr"].Visible = false;
oracleConnection.Close();
} // FORM LOAD
private void searchBillButton_Click(object sender, EventArgs e)
{
equDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32(billIDText.Text);
partDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32(billIDText.Text);
equDataAdapter.Fill(equDataTable);
partDataAdapter.Fill(partDataTable);
}
private void saveBillButton_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow row in equGrid.Rows)
row.Cells["bill_id"].Value = bill_id;
foreach (DataGridViewRow row in partGrid.Rows)
row.Cells["bill_id"].Value = bill_id;
equDataAdapter.SelectCommand.Parameters["bill_id"].Value = bill_id;
partDataAdapter.SelectCommand.Parameters["bill_id"].Value = bill_id;
// I HAD TO CALL UPDATE ON DELTED ROWS OF DETAIL GRID BEFORE ANYTHING ELSE BECAUSE :
// IF I DELETE A MASTER ROW, DETAIL ROWS ARE DELETED AUTOMATICALLY DUE TO DATARELATION BETWEEN MASTER & DETAIL
// SO WHEN I CALL:
// DATAADAPTER.UPDATE(MASTER);
// DATAADAPTER.UPDATE(DETAIL);
// I GET COCURRENCY EXCEPTION... THE REASON:
// DATAADAPTER.UPDATE(MASTER) CAUSES MASTER RECORD TO BE DELETED FROM DATABASE,
// AND ITS DETAILS RECORDS GET AUTOMATICALLY DELETED FROM DB, TOO (ON DELETE CASCADE).
// SO, WHEN DATAADAPTER.UPDATE(DETAIL) IS CALLED, IT FINDS THAT DETAIL RECORDS ARE MISSING FROM DATABASE,
// AND AN EXCEPTION IS THROWN...
DataTable deleted_details = partDataTable.GetChanges(DataRowState.Deleted);
if (deleted_details != null) partDataAdapter.Update(deleted_details);
equDataAdapter.Update(equDataTable);
DataTable added_details = partDataTable.GetChanges(DataRowState.Added);
if (added_details != null) partDataAdapter.Update(added_details);
DataTable modified_details = partDataTable.GetChanges(DataRowState.Modified);
if (modified_details != null) partDataAdapter.Update(modified_details);
partDataTable.Clear();
equDataTable.Clear();
equDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_equ"]);
partDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_part"]);
}
} // class
我不确定为什么要将数据插入数据库在Form1_Load中。
在Form1_Load中，您刚从数据库中获取要在数据网格中显示的数据，对吗？
I认为这是一个很好的情况，在Mastr表中有一个备用键可以用来获得一个唯一的行而不使用主键。
也许bill_id和equ_serial_nbr可以用于此目的吗？
I am not sure why you insert data to the database in the Form1_Load.
In Form1_Load you have just fetched data from the database that you want to show in the datagrids, right?
I think this is a case when it would be nice to have an alternate key in the Mastr table that can be used to get a unique row without using the primary key.
Maybe "bill_id" and "equ_serial_nbr" can be used for this purpose?
private void saveBillButton_Click(object sender, EventArgs e)
{
equBindingSource.EndEdit(); // Applies changes to the data tables
int masterID = 0;
// This only takes care of added rows, not updated or deleted rows
foreach (DataRow drMaster in dataset.Tables["bso_equipment_maint_bill_equ"].Rows)
{
if (drMaster.RowState == DataRowState.Added)
{
// Here you insert data into the master table
// INSERT INTO bso_equipment_maint_bill_equ all columns except
masterID = <last inserted="" id="">;
}
else
{
// SQL query to get the primary key from the database
// Assuming you have an alternate key
String.Format(@"
SELECT bill_equ_id FROM bso_equipment_maint_bill_equ
WHERE bill_id = '{0}'
AND equ_serial_nbr = '{1}'", drMaster["bill_id"], drMaster["equ_serial_nbr"]);
masterID = <the received="" id="">;
}
foreach (DataRow drDetail in drMaster.GetChilds("EquPartRel"))
{
if (drDetail.RowState == DataRowState.Added)
{
// Here you insert data into the detail table
// Use the value of masterID as value to set for
// column bso_equipment_maint_bill_part.bill_equ_id
}
}
}
dataset.AcceptChanges();
}
</the></last>
您还应该在创建表格时再次添加此代码
这将是永远不会存储在数据库中的临时密钥
You also should add this code again where you create your table
This will be a temporary key that never is stored in the DB
DataColumn dc = equDataTable.Columns.Add("bill_equ_id", typeof(int));
dc.AutoIncrement = true;
添加此行从数据库加载数据集时的代码。
Add this line of code when you have loaded the dataset from the DB.
dataset.AcceptChanges();
It将确保您只处理更改的行。
我希望这个伪代码可以帮助您。
It will make sure you only handle the changed rows.
I hope this "pseudo code" will help you on the way.
首先插入您的主数据但返回同一查询中的id值
插入表值（'1525'，'1/1 / 2014'）选择@@ IDENTITY
并使用此值设置详细外键
然后插入详细数据
first insert your master data but return the id value in the same query
insert into table values('1525' ,'1/1/2014' ) select @@IDENTITY
and set details foreign key with this value
then insert details data
这篇关于主细节datagridview：如果主键是自动生成的，如何插入主记录及其详细信息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
